date query question - 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 2005 Forums

Transact-SQL (2005)

date query question

Author

Topic

KiwiinAussie
Starting Member

5 Posts

Posted - 2008-06-13 : 03:08:25

Here's the query:

create table #weeks
(
WeekNumber int
)

insert into #weeks (WeekNumber) values (0)
insert into #weeks (WeekNumber) values (1)
insert into #weeks (WeekNumber) values (2)
insert into #weeks (WeekNumber) values (3)
insert into #weeks (WeekNumber) values (4)
insert into #weeks (WeekNumber) values (5)
insert into #weeks (WeekNumber) values (6)
insert into #weeks (WeekNumber) values (7)
insert into #weeks (WeekNumber) values (8)

declare @startDate datetime
set @startdate = '2008-06-13'

select case w.WeekNumber
when 0 then 'Up to 1wk old'
when 1 then '1wk to 2wks old'
when 2 then '2wks to 3wks old'
when 3 then '3wks to 4wks old'
when 4 then '4wks to 5wks old'
when 5 then '5wks to 6wks old'
when 6 then '6wks to 7wks old'
when 7 then '7wks to 8wks old'
when 8 then '8wks old'
end as Age,
isNull(t.Total, 0) as Total
from #weeks w
left outer join
(
select round(datediff(dd, ReceivedDate, @startDate) / 7, 1) as NumWeeks,
count(*) as Total
from Log
where ReceivedDate between dateadd(wk, -8, @startDate) and @startDate
and Status = 'Open'
group by round(datediff(dd, ReceivedDate, @startDate) / 7, 1)
) t
on t.NumWeeks = w.WeekNumber
order by w.WeekNumber

Current Results which are correct except for "8wks or more" which should return 102, but I can't seem to figure out a way of doing it. Can someone help me out please to do this, and/or a better way of doing it?

Age Total
Up to 1wk old 60
1wk to 2wks old 30
2wks to 3wks old 21
3wks to 4wks old 15
4wks to 5wks old 9
5wks to 6wks old 6
6wks to 7wks old 2
7wks to 8wks old 1
8wks or more 0

Data: (these are all the open Status records).

ReceivedDate
2006-05-03
2006-10-06
2006-10-16
2006-11-16
2006-12-15
2007-01-23
2007-03-12
2007-03-12
2007-04-03
2007-05-21
2007-06-19
2007-07-09
2007-08-01
2007-08-09
2007-08-14
2007-08-17
2007-08-20
2007-09-03
2007-09-12
2007-09-18
2007-10-09
2007-10-10
2007-10-17
2007-11-05
2007-11-16
2007-11-19
2007-11-23
2007-11-23
2007-11-28
2007-12-11
2007-12-11
2007-12-14
2007-12-17
2007-12-19
2007-12-21
2007-12-21
2007-12-24
2008-01-02
2008-01-08
2008-01-22
2008-01-23
2008-01-24
2008-01-25
2008-01-29
2008-01-29
2008-01-31
2008-01-31
2008-02-01
2008-02-01
2008-02-05
2008-02-05
2008-02-06
2008-02-06
2008-02-11
2008-02-19
2008-02-19
2008-02-20
2008-02-22
2008-02-26
2008-02-27
2008-02-29
2008-02-29
2008-03-03
2008-03-04
2008-03-05
2008-03-05
2008-03-07
2008-03-10
2008-03-10
2008-03-10
2008-03-12
2008-03-12
2008-03-13
2008-03-19
2008-03-25
2008-03-27
2008-03-27
2008-03-27
2008-04-01
2008-04-02
2008-04-02
2008-04-02
2008-04-03
2008-04-04
2008-04-07
2008-04-07
2008-04-07
2008-04-09
2008-04-10
2008-04-10
2008-04-11
2008-04-11
2008-04-14
2008-04-14
2008-04-14
2008-04-15
2008-04-15
2008-04-16
2008-04-16
2008-04-17
2008-04-17
2008-04-17
2008-04-21
2008-04-29
2008-05-02
2008-05-05
2008-05-06
2008-05-07
2008-05-09
2008-05-09
2008-05-09
2008-05-12
2008-05-12
2008-05-12
2008-05-13
2008-05-13
2008-05-14
2008-05-14
2008-05-15
2008-05-16
2008-05-19
2008-05-20
2008-05-20
2008-05-20
2008-05-20
2008-05-21
2008-05-21
2008-05-21
2008-05-21
2008-05-21
2008-05-21
2008-05-22
2008-05-22
2008-05-23
2008-05-23
2008-05-26
2008-05-26
2008-05-27
2008-05-27
2008-05-27
2008-05-27
2008-05-28
2008-05-28
2008-05-29
2008-05-29
2008-05-29
2008-05-29
2008-05-29
2008-05-29
2008-05-30
2008-05-30
2008-05-30
2008-05-30
2008-05-30
2008-05-30
2008-05-30
2008-06-02
2008-06-02
2008-06-02
2008-06-02
2008-06-02
2008-06-02
2008-06-02
2008-06-02
2008-06-03
2008-06-03
2008-06-03
2008-06-03
2008-06-03
2008-06-03
2008-06-03
2008-06-03
2008-06-04
2008-06-04
2008-06-04
2008-06-04
2008-06-04
2008-06-04
2008-06-05
2008-06-05
2008-06-05
2008-06-05
2008-06-06
2008-06-06
2008-06-06
2008-06-06
2008-06-07
2008-06-07
2008-06-10
2008-06-10
2008-06-10
2008-06-10
2008-06-10
2008-06-10
2008-06-10
2008-06-10
2008-06-10
2008-06-10
2008-06-10
2008-06-10
2008-06-11
2008-06-11
2008-06-11
2008-06-11
2008-06-11
2008-06-11
2008-06-11
2008-06-11
2008-06-11
2008-06-12
2008-06-12
2008-06-12
2008-06-12
2008-06-12
2008-06-12
2008-06-12
2008-06-12
2008-06-12
2008-06-12
2008-06-12
2008-06-12
2008-06-12
2008-06-12
2008-06-12
2008-06-12
2008-06-12
2008-06-12
2008-06-12
2008-06-12
2008-06-12
2008-06-12
2008-06-12
2008-06-12
2008-06-12
2008-06-12
2008-06-12
2008-06-13
2008-06-13
2008-06-13
2008-06-13
2008-06-13
2008-06-13
2008-06-13
2008-06-13
2008-06-13
2008-06-13

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 03:32:19

Its working as expected. Inside derived table you've added the condition
ReceivedDate between dateadd(wk, -8, @startDate) and @startDate

This will take data only from 18/04/2008 onwards. so first record picked will be one with date 21/04/2008. and when you take round(datediff(dd, ReceivedDate, @startDate) / 7, 1) for this it will be 7 so you wont have any records with NumWeeks 8 and so count value will return null for it which is set to 0 by isnull.
b/w i didnt understand the use of round() there as even without it you will get only integer result as both the operands involved are integers (datediff returns int & also 7 is int)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources