date query question

  • 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

  • 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

  • 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?

  • 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.

  • 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