Return data for all days even when null entries

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

SQL Server 2005 Forums

Transact-SQL (2005)

Return data for all days even when null entries

Author

Topic

turprob
Starting Member

2 Posts

Posted - 2011-05-04 : 06:48:16

Hi
I am trying to get the maximum 'hourly average' value for specific tags for each day of the month. The 'hourly average' values are entries in a table called 'Cond_Hourly_Avg' in the column 'Average_value' and the dates/time for all entries in the table are in the column 'time_date'. I have been using the below query, which gets me the maxuimum values for all the days where data exists, but I would still like the day of month & value of 0 for days where there is no data in the table. I'm fairly inexperienced SQL wise, so if you could advise me on the best way forward that would be most appreciated

SELECT
DATEPART(dd, time_date) as [Day],
MAX(Average_value) as [Max_Average_Value]
FROM Cond_Hourly_Avg
where
Tag LIKE '1A_TRANS_NOX'
and
time_date >= '04/01/2011 00:00:00'
AND
time_date < '04/30/2011 23:00:00'
GROUP BY
DATEPART(dd, time_date)
order by
datepart(dd,time_date)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-04 : 06:57:03

something like

with cte as
(
select dte = CONVERT(datetime,'20110401')
union all
select DATEADD(dd,1,dte) from cte where dte < '20110430')
)
SELECT DATEPART(dd, cte.dte) as [Day],
MAX(coalesce(Average_value,0)) as [Max_Average_Value]
FROM cte
left join Cond_Hourly_Avg h
on datediff(dd,cte.dte,h.time_date) = 0
and Tag LIKE '1A_TRANS_NOX'
GROUP BY cte.dte
order by cte.dte

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

turprob
Starting Member

2 Posts

Posted - 2011-05-04 : 07:06:19

Thanks Nigel. There was an extra bracket in your code but once removed it works a treat. Thanks for your help

quote:
Originally posted by nigelrivett

something like

with cte as
(
select dte = CONVERT(datetime,'20110401')
union all
select DATEADD(dd,1,dte) from cte where dte < '20110430')
)
SELECT DATEPART(dd, cte.dte) as [Day],
MAX(coalesce(Average_value,0)) as [Max_Average_Value]
FROM cte
left join Cond_Hourly_Avg h
on datediff(dd,cte.dte,h.time_date) = 0
and Tag LIKE '1A_TRANS_NOX'
GROUP BY cte.dte
order by cte.dte

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources