Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Show 0 value as result of query

Status
Not open for further replies.
Big0range

Big0range

IS-IT--Management
Aug 18, 2003
76
US
Howdy. I have a couple of queries I run, whereby I take the results and paste into a spreadsheet. The data returned by the query is broken down by hour. If there is no data for the hour, I would like to see a 0, but the query does not return anything at all. I then have to copy data to a separate spreadsheet, find missing hours, insert 0s manually, then paste. HOW can I get the results to list a 0 where needed as a function of the query??

(I hope this request was clear!)
 
Sort by date Sort by votes
Sorry, I'm a SQL idiot. I'm a networker easing into a new work situation involving pulling reports from a SQL database.

I've heard the term "left join" and read the word "coalesce" once in a novel (but I do know the definition!)

How would one put the terms into action in a SQL environment?

Thanks so much, buy the way, for the quick response!!
 
Upvote 0 Downvote
I don't know what your data looks like but here is an example
create table hours(id int)
declare @id int
select @id =1
while @id <= 24
begin
insert into hours
select @id
select @id =@id +1
end

create table DataHours (id int)
insert into DataHours
select 1 union all
select 12 union all
select 5 union all
select 8 union all
select 6

select h.id,coalesce(d.id,0) from DataHours d right join hours h on d.id = h.id
order by h.id

select h.id,coalesce(d.id,0) from hours h left join DataHours d on d.id = h.id
order by h.id

Denis The SQL Menace
SQL blog:
Personal Blog:
 
Upvote 0 Downvote

I guess the better way for us to help you is that you post the query you ran. Thanks
 
Upvote 0 Downvote
Great idea - Here's the query:

select datepart(dd,DateTime),datepart(hh,DateTime),count(datepart(hh,DateTime))
from Table1
where DateTime >= '2005-12-01 00:00:00.000'
and DateTime < '2006-01-01 00:00:00.000'
and datepart(hh,DateTime) between 8 and 19
and datepart(dw,DateTime) BETWEEN 2 and 6
and ((Variable4 = 'V4') OR (TID = 1234))
and PID = 5678
and Variable6 IN ('Group1','Group2','Group3')
group by datepart(dd,DateTime),datepart(hh,DateTime)
order by datepart(dd,DateTime)

I was looking up left joins and see it needs 2 or more tables, where this return 1 table, although I see your suggestion creates new tables.
 
Upvote 0 Downvote
Quite a lazy way, tho u could use something similar:

Code: 
create table table1
(
yourdate datetime ,
yourvalue int 
)

insert into table1 values ('2005-12-01 10:00:00.000',1)
insert into table1 values ('2005-12-01 20:00:00.000',2)
insert into table1 values ('2005-12-01 01:00:00.000',3)
insert into table1 values ('2005-12-02 01:00:00.000',4)
insert into table1 values ('2006-01-01 02:00:00.000',5)

select year(yourdate) as Year,month(yourdate) as Month,day(yourdate)  as Day,			
SUM(case when DATEPART(hh, yourdate)  = 0 then yourvalue else 0 end) '0',							
SUM(case when DATEPART(hh, yourdate)  = 1 then yourvalue else 0 end) '1',							
SUM(case when DATEPART(hh, yourdate)  = 2 then yourvalue else 0 end) '2',							
SUM(case when DATEPART(hh, yourdate)  = 3 then yourvalue else 0 end) '3',							
SUM(case when DATEPART(hh, yourdate)  = 4 then yourvalue else 0 end) '4',							
SUM(case when DATEPART(hh, yourdate)  = 5 then yourvalue else 0 end) '5',							
SUM(case when DATEPART(hh, yourdate)  = 6 then yourvalue else 0 end) '6',							
SUM(case when DATEPART(hh, yourdate)  = 7 then yourvalue else 0 end) '7',							
SUM(case when DATEPART(hh, yourdate)  = 8 then yourvalue else 0 end) '8',							
SUM(case when DATEPART(hh, yourdate)  = 9 then yourvalue else 0 end) '9',							
SUM(case when DATEPART(hh, yourdate)  = 10 then yourvalue else 0 end) '10',							
SUM(case when DATEPART(hh, yourdate)  = 11 then yourvalue else 0 end) '11',							
SUM(case when DATEPART(hh, yourdate)  = 12 then yourvalue else 0 end) '12',							
SUM(case when DATEPART(hh, yourdate)  = 13 then yourvalue else 0 end) '13',							
SUM(case when DATEPART(hh, yourdate)  = 14 then yourvalue else 0 end) '14',							
SUM(case when DATEPART(hh, yourdate)  = 15 then yourvalue else 0 end) '15',							
SUM(case when DATEPART(hh, yourdate)  = 16 then yourvalue else 0 end) '16',							
SUM(case when DATEPART(hh, yourdate)  = 17 then yourvalue else 0 end) '17',							
SUM(case when DATEPART(hh, yourdate)  = 18 then yourvalue else 0 end) '18',							
SUM(case when DATEPART(hh, yourdate)  = 19 then yourvalue else 0 end) '19',							
SUM(case when DATEPART(hh, yourdate)  = 20 then yourvalue else 0 end) '20',							
SUM(case when DATEPART(hh, yourdate)  = 21 then yourvalue else 0 end) '21',							
SUM(case when DATEPART(hh, yourdate)  = 22 then yourvalue else 0 end) '22',							
SUM(case when DATEPART(hh, yourdate)  = 23 then yourvalue else 0 end) '23'							
from table1										
group by year(yourdate),month(yourdate),day(yourdate)
go
drop table table1
 
Upvote 0 Downvote
This should work in theory

select h.id,coalesce(DayPart,0),coalesce(Hourpart,0),coalesce(Countpart,0)
from
(select datepart(dd,DateTime) as DayPart,
datepart(hh,DateTime) as Hourpart,count(datepart(hh,DateTime)) as Countpart
from Table1
where DateTime >= '2005-12-01 00:00:00.000'
and DateTime < '2006-01-01 00:00:00.000'
and datepart(hh,DateTime) between 8 and 19
and datepart(dw,DateTime) BETWEEN 2 and 6
and ((Variable4 = 'V4') OR (TID = 1234))
and PID = 5678
and Variable6 IN ('Group1','Group2','Group3')
group by datepart(dd,DateTime),datepart(hh,DateTime)) d
right join hours h on d.Hourpart = h.id
order by h.id

Denis The SQL Menace
SQL blog:
Personal Blog:
 
Upvote 0 Downvote
For

datepart(hh,DateTime)

just do

IsNull(datepart(hh,DateTime), 0)

and coalesce would do the same thing but accepts any number of potentially null items.
 
Upvote 0 Downvote
Esquared, I tried you first and had the same result (hours with no or 0 data not being included in the output table).

SQLDenis (heeyyy... there's just one n in your name!!) - I tried yours second and got:

Invalid object name 'hours'.

I'm looking at the logic to see if I can figure it out.


Jamfool, yours looks very long and complicated and maybe even a little scary. I'll try it last if nothing else works! :)
 
Upvote 0 Downvote
A ha...

Got a table called hours, query runs fine. I'm getting back now four columns instead of three: the first is id, the second appears to be date, the third hours, and the fourth the needed data.

Sorting is the next big hurdle - it used to sort by Day 1, hours 8-19, Day2, Hours 8-19, etc. It appears to be sorted by that first column (id) which is not very helpful. I fiddled with the "order by" line with no success (and will next try finessing the line, then perhaps manipulating...) but would appreciate any sorting command suggestions.

Also, how can I label each column header (like the first, which says id, but the others all say "No column name")

And again, thanks so much for your input.
 
Upvote 0 Downvote
select h.id,coalesce(DayPart,0) as ColName1Here,coalesce(Hourpart,0) as ColName2Here ,coalesce(Countpart,0) as ColName3Here
from
(select datepart(dd,DateTime) as DayPart,
datepart(hh,DateTime) as Hourpart,count(datepart(hh,DateTime)) as Countpart
from Table1
where DateTime >= '2005-12-01 00:00:00.000'
and DateTime < '2006-01-01 00:00:00.000'
and datepart(hh,DateTime) between 8 and 19
and datepart(dw,DateTime) BETWEEN 2 and 6
and ((Variable4 = 'V4') OR (TID = 1234))
and PID = 5678
and Variable6 IN ('Group1','Group2','Group3')
group by datepart(dd,DateTime),datepart(hh,DateTime)) d
right join hours h on d.Hourpart = h.id
order by 2,1

Denis The SQL Menace
SQL blog:
Personal Blog:
 
Upvote 0 Downvote
Tricky, tricky.... The sorting is now back in order, although it starts out with 13 rows of 0s except for the id column that appear to be sorted by the number in the id column. Those rows do not list a date or time, just 0s. Where I would need a 0, particularly for the 19th hour, is nothing. The row stops with the 18th hour and continues next with the 8th hour of the next day. Perhaps the needed 0s (at the top of the table?) are not being inserted in the correct locations?
 
Upvote 0 Downvote
Here are the first few rows of the results:

1 0 0 0
2 0 0 0
3 0 0 0
4 0 0 0
5 0 0 0
6 0 0 0
7 0 0 0
20 0 0 0
21 0 0 0
22 0 0 0
23 0 0 0
24 0 0 0
8 1 8 85
9 1 9 227
10 1 10 266
11 1 11 254
12 1 12 195
13 1 13 233
14 1 14 217
15 1 15 213
16 1 16 164
17 1 17 56
18 1 18 28
8 2 8 66
9 2 9 180
10 2 10 196
11 2 11 199
12 2 12 174
13 2 13 185
14 2 14 190
15 2 15 191
16 2 16 124
17 2 17 44
18 2 18 30
19 2 19 2
8 5 8 101
9 5 9 194
10 5 10 273
 
Upvote 0 Downvote
Aha it's turning into a mess
It's because it's ordered by day since day is 0 for not existing values it is displayed first
You will have to create a datetable and do the same join as with the hours table and use that day in the display

Denis The SQL Menace
SQL blog:
Personal Blog:
 
Upvote 0 Downvote
Okay, I used your earlier script to create a table of dates called "days" from 1 - 31. The following is at the beginning of the query:

select h.id,coalesce(DayPart,0) as Date,coalesce(Hourpart,0) as Hour ,coalesce(Countpart,0) as Data
from (select datepart(dd,DateTime) as DayPart,
datepart(hh,DateTime) as Hourpart,count(datepart(hh,DateTime)) as Countpart

The middle remains unchanged throughout all this.
And the following is the end:

group by datepart(dd,DateTime),datepart(hh,DateTime)) d
right join hours h on d.Hourpart = h.id
right join days d on d.Daypart = d.id
order by 2,1

and get the following error:
The correlation name 'd' is specified multiple times in a FROM clause.

I don't understand fully the language or contest of some of the commands, obviously. I also tried changing the h.id to d.id and received the same error.
 
Upvote 0 Downvote
you have aliased 2 tables with d
change this
group by datepart(dd,DateTime),datepart(hh,DateTime)) d
right join hours h on d.Hourpart = h.id
right join days d on d.Daypart = d.id
order by 2,1


to this

group by datepart(dd,DateTime),datepart(hh,DateTime)) d
right join hours h on d.Hourpart = h.id
right join days d2 on d.Daypart = d2.id
order by 2,1


Denis The SQL Menace
SQL blog:
Personal Blog:
 
Upvote 0 Downvote
Still not quite there, same problem as before. Here's the output:

NULL 0 0 0
NULL 0 0 0
NULL 0 0 0
NULL 0 0 0
NULL 0 0 0
NULL 0 0 0
NULL 0 0 0
NULL 0 0 0
NULL 0 0 0
NULL 0 0 0
8 1 8 85
9 1 9 227
10 1 10 266
11 1 11 254
12 1 12 195
13 1 13 233
14 1 14 217
15 1 15 213
16 1 16 164
17 1 17 56
18 1 18 28
8 2 8 66
9 2 9 180
10 2 10 196
11 2 11 199
12 2 12 174
13 2 13 185
14 2 14 190
15 2 15 191
16 2 16 124
17 2 17 44
18 2 18 30
19 2 19 2
8 5 8 101
9 5 9 194
10 5 10 273
11 5 11 305
12 5 12 217
13 5 13 252
14 5 14 83
15 5 15 98
16 5 16 66
17 5 17 22
18 5 18 3
8 6 8 23
9 6 9 51
10 6 10 98

Query beginning:
select h.id,coalesce(DayPart,0) as Date,coalesce(Hourpart,0) as Hour ,coalesce(Countpart,0) as Data
from (select datepart(dd,DateTime) as DayPart,datepart(hh,DateTime) as Hourpart,count(datepart(hh,DateTime)) as Countpart

and query end:
group by datepart(dd,DateTime),datepart(hh,DateTime)) d
right join hours h on d.Hourpart = h.id
right join days d2 on d.Daypart = d2.id
order by 2,1

I'm sure I've missed something or screwed something up.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bharons
  • Locked
  • Question
Pick up value from combobox
Replies
0
Views
250
bharons
bharons
gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
606
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question
Error in Select query
Replies
14
Views
281
Andrzejek
Andrzejek
peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
375
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
SELECT TOP 1 of Each of a Group
Replies
5
Views
170
grnzbra
grnzbra

Part and Inventory Search

Sponsor

Back
Top