Consecutive Days - 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 2000 Forums

SQL Server Development (2000)

Consecutive Days

Author

Topic

Scott
Posting Yak Master

145 Posts

Posted - 2003-12-04 : 01:24:59

I have a database table that captures daily values:


create table DailyValues (
	CapDate smalldatetime NOT NULL,
	DayValue int NOT NULL
)

Some days are not captured at all so when I retrieve the results for the last seven days [


Select 	Left(DateName(dw, CapDate), 3) + ' ' + 
		DateName(day, CapDate) + ' ' + 
		Datename(month, CapDate) as DayofWeek,
	DayValue
from DailyValues
where CapDate between CAST(FLOOR(CAST(GetDate() AS float))AS smalldatetime) and DateAdd(dd, -7, GetDate())

] the recordset has days missing. I want the recordset to return all days consecutively with a DayValue of 0 if it is missing.

Thanks
Scott

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-12-04 : 01:52:47

Someone will probablly show you more elegant code, but the basic principle is to create a table containing all dates:

create table alldays (datecheck smalldatetime)

declare @dateindex smalldatetime
set @dateindex = convert(smalldatetime, '01-Nov-2003')
while (@dateindex < convert(smalldatetime, '01-Nov-2004'))
begin
	insert into alldays (datecheck) select @dateindex
	set @dateindex = dateadd(d, 1, @dateindex)
end
go

Then join this table to your dailyvalues table. I've also used the isnull function so that I get 0 for missing days rather than null.

select Left(DateName(dw, datecheck), 3) + ' ' + DateName(day, datecheck) + ' ' + Datename(month, datecheck), isnull(dayvalue,0)
from DailyValues full outer join alldays
	on capdate = datecheck
where datecheck between DateAdd(dd, -7, GetDate()) and GetDate() 
order by datecheck

enjoy

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-12-04 : 02:46:13

create table #t (val int, valdate smalldatetime)
insert into #t
select 444, '20031204' union all
select 333, '20031203' union all
select 111, '20031201' union all
select 280, '20031128'
go

select val, cast(getdate()-i as char(11)) from #t
RIGHT JOIN
(select i=1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7) tt
ON
cast(valdate as char(11))=cast(getdate()-i as char(11))
order by getdate()-i

drop table #t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources