Timestamp null value problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Timestamp null value problem

Author

Topic

savolol
Starting Member

2 Posts

Posted - 2011-06-23 : 05:51:04

Select dayofyear,hour,
max(case when A.RunNr=1 then COUNT_A else 0 end) as [RunNr=1],
max(case when A.RunNr!=1 then COUNT_A else 0 end) as [RunNr>1]
from
(
Select count(distinct IDNR)as [COUNT_A],RunNr,DATEPART (dayofyear,"When") as dayofyear,DATEPART (hh,"When") as "hour"

from table_x

where "When" >CURRENT_TIMESTAMP-7

Group by DATEPART (dayofyear,"When"),RunNr,DATEPART (hh,"When")) [A]

group by dayofyear,hour
order by dayofyear,hour

Responce:

dayofyear hour RunNr=1 RunNr>1
----------- ----------- ----------- -----------
171 8 1 0
171 9 2 0
173 9 2 0
173 12 0 1
173 13 2 1
173 14 0 1
174 8 3 1
174 9 0 1
174 11 1 0

How can I plot null lines also, so that time line continues?
Like:
dayofyear hour RunNr=1 RunNr>1
----------- ----------- ----------- -----------
171 23 1 0
171 24 2 0
172 1 0 0
172 2 0 1
172 3 0 0
172 4 1 10
....

How can I ask timestamps from database and join them to this?
Or better solution???

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-06-23 : 10:22:42

Since we know nothing about your database it's hard to say. In general you'll need to do an [LEFT | RIGHT] OUTER JOIN to the set of vaules that you want to display (DayOfYear?) and join that to your result set to see the NULL values.

If you still need more help you might want to check out the link below. It will tell you how to prove DDL, DML and expected output in a consumable format so we cna help you better.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources