fill missing date range - 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

SQL Server 2008 Forums

Transact-SQL (2008)

fill missing date range

Author

Topic

hai
Yak Posting Veteran

84 Posts

Posted - 2012-05-18 : 12:05:35

Hi all,

For reporting purpose, I need to pull the whole calendar date with default value fill-in, even no data exist. Below is sample, expect for the report, which was from my simple approach. I need help to put this in a better coding.

thanks


DECLARE @EndDate datetime
DECLARE @StartDate datetime
declare @tbl1 table(id int, reading float, readdate datetime) 

SET @StartDate  = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101)
SET @EndDate    = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),101)  

insert into @tbl1
select 1, 999, '05/01/2012'
union all 
select 2, 999, '05/02/2012'
union all
select 1, 999, '05/05/2012'
union all 
select 2, 999, '05/05/2012'
union all
select 1, 999, '05/015/2012'
union all 
select 2, 999, '05/13/2012'




;WITH Dates(DATEPARAM) AS
(
    SELECT @StartDate AS datetime
        UNION ALL
    SELECT DATEADD(DAY, 1, DATEPARAM)
        FROM Dates
        WHERE DATEPARAM < @EndDate
)
--out put expect
select DATEPARAM as ReadDate, isnull(ID,1)as id, isnull(reading ,800) as reading from 
Dates left join
@tbl1 on  Dates.DATEPARAM=readdate
and id=1

union all 
select DATEPARAM, isnull(ID,2), isnull(reading ,800) from 
Dates left join
@tbl1 on  Dates.DATEPARAM=readdate
and id=2

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-05-18 : 16:14:04

What is the problem that you are trying to solve? Are you not getting the results desired? Are you getting an error message? Is it performing poorly?

=================================================
There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources