Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2008
    Posts
    1

    Unanswered: 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

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    I think here
    dateadd(wk, -8, @startDate)
    you should subtract 8*7 days instead

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    first of all, you don't need ROUND (especially not to 1 decimal place) since dividing a whole number of days by the whole number 7 yields a whole number

    secondly, you don't need a temp table

    your main problem is that you were matching week numbers in the join based on equality, and therefore any data older than 8 weeks was not being matched

    you have to match the older ones to week 8, as shown in red below, and then you need another GROUP BY to roll them all together

    finally, add the weeknumber to the SELECT and GROUP BY to get the results in the right order:
    Code:
    SELECT w.WeekNumber
         , w.Age
         , SUM(COALESCE(t.Total,0)) AS Total
      FROM ( SELECT 0 AS WeekNumber
                  , 'Up to 1wk old' as Age
             UNION ALL SELECT 1,'1wk to 2wks old'
             UNION ALL SELECT 2,'2wks to 3wks old'
             UNION ALL SELECT 3,'3wks to 4wks old'
             UNION ALL SELECT 4,'4wks to 5wks old'
             UNION ALL SELECT 5,'5wks to 6wks old'
             UNION ALL SELECT 6,'6wks to 7wks old'
             UNION ALL SELECT 7,'7wks to 8wks old'
             UNION ALL SELECT 8,'8wks old or older'
           ) AS w
    LEFT OUTER 
      JOIN ( SELECT DATEDIFF(DD,ReceivedDate,@startDate) / 7 AS NumWeeks
                  , COUNT(*) AS Total
               FROM Log
             GROUP 
                 BY DATEDIFF(DD,ReceivedDate,@startDate) / 7
           ) AS t
        ON t.NumWeeks = w.WeekNumber
        OR t.NumWeeks > w.WeekNumber AND w.WeekNumber = 8
    GROUP 
        BY w.WeekNumber
         , w.Age
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •