June 13, 2008 at 1:05 am
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
June 13, 2008 at 2:13 am
Hi James,
Here is what you need
declare @startDate datetime
set @startdate = '2008-06-13'
Select count(*) as total,
a.Numweeks
From (
select case when (round(datediff(dd, ReceivedDate, @startDate) / 7, 1)<= 8)
then (round(datediff(dd, ReceivedDate, @startDate) / 7, 1))
Else 8
end as Numweeks
from [log]
) a
group by Numweeks
order by 2
Regards
Shrikant Kulkarni
June 13, 2008 at 2:19 am
Thanks for responding mate, however the result should be:
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 102
and your query produces the following as a result:
totalNumweeks
2770
3311
4232
3803
3764
3505
3746
3177
690738
Any ideas?
June 13, 2008 at 2:22 am
Sorry about that, it did produce the correct result, when I added where Status ='Open' 🙂
How would you suggest I change the 1, 2, 3, 4, 5..... to 1 wks to 2 wks old, 2 wks to 3 wks old, etc.
June 13, 2008 at 3:38 am
I've just created a lookup table and done it that way 🙂
I'm needing to expand the query further now, and was wondering if you could help? Here are the details:
The query you gave me produced the following results:
totalNumweeks
600
271
212
153
94
65
26
17
1028
I need to be able to also include in there the total by department and finish up with a result set of:
age Total Department 1, Department 2, Department 3, Department 4, Department 5
Up to 1 wk old 60, 1, 2, 3, 4, 5
1 wks to 2 wks old 27, 1, 2, 3, 4, 5
2 wks to 3 wks old 21, 1, 2, 3, 4, 5
3 wks to 4 wks old 15, 1, 2, 3, 4, 5
4 wks to 5 wks old 9, 1, 2, 3, 4, 5
5 wks to 6 wks old 6, 1, 2, 3, 4, 5
6 wks to 7 wks old 2, 1, 2, 3, 4, 5
7 wks to 8 wks old 1, 1, 2, 3, 4, 5
8 wks and older 102, 1, 2, 3, 4, 5
The query I accepted in the above question is shown below.
To get the Department information, the Log table links to an Assignment Table which then links to a Department table which has the DepartmentName field.
The Assignment Table has many records related to the one Log record (as it passes through multiple deparments) and has a field called Order which is a sequential number that details the latest record for that Log item. To get the group name back, it needs to link through on the DepartmentId that is used for that particular latest record as specified by the Order field in Assignment.
How could this be done?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply