Post reply

Query returning last 13 weeks

  • January 26, 2004 at 6:02 am

    #107717

    I want to be able to count for each country, the number of sales reports during the last 13 weeks. Row headers = Country Code, column headers 13,12,11 to represent current week, prior week etc.

  • January 26, 2004 at 6:42 am

    #491589

    To get the number of sales reports for each of the countries, by week, is not too difficult...

    SELECT Country, DatePart(w, SalesReportDate) As Week, Count(SalesReport) As NumReports
    FROM TableSalesReports
    GROUP BY Country, Datepart(w, SalesReportDate)

    Use any typical 'CrossTab query' technique to format the results to your liking.

  • January 26, 2004 at 6:59 am

    #491594

    I hope this gets you on track :

    SELECT country_code

    , sum(case when datepart(ww,TsCrea) = datepart(ww,dateadd(ww,-13,getdate())) then 1 else 0 end) as count_13

    , sum(case when datepart(ww,TsCrea) = datepart(ww,dateadd(ww,-12,getdate())) then 1 else 0 end) as count_12

    , sum(case when datepart(ww,TsCrea) = datepart(ww,dateadd(ww,-11,getdate())) then 1 else 0 end) as count_11

    , sum(case when datepart(ww,TsCrea) = datepart(ww,dateadd(ww,-10,getdate())) then 1 else 0 end) as count_10

    , sum(case when datepart(ww,TsCrea) = datepart(ww,dateadd(ww,-09,getdate())) then 1 else 0 end) as count_09

    , sum(case when datepart(ww,TsCrea) = datepart(ww,dateadd(ww,-08,getdate())) then 1 else 0 end) as count_08

    , sum(case when datepart(ww,TsCrea) = datepart(ww,dateadd(ww,-07,getdate())) then 1 else 0 end) as count_07

    , sum(case when datepart(ww,TsCrea) = datepart(ww,dateadd(ww,-06,getdate())) then 1 else 0 end) as count_06

    , sum(case when datepart(ww,TsCrea) = datepart(ww,dateadd(ww,-05,getdate())) then 1 else 0 end) as count_05

    , sum(case when datepart(ww,TsCrea) = datepart(ww,dateadd(ww,-04,getdate())) then 1 else 0 end) as count_04

    , sum(case when datepart(ww,TsCrea) = datepart(ww,dateadd(ww,-03,getdate())) then 1 else 0 end) as count_03

    , sum(case when datepart(ww,TsCrea) = datepart(ww,dateadd(ww,-02,getdate())) then 1 else 0 end) as count_02

    , sum(case when datepart(ww,TsCrea) = datepart(ww,dateadd(ww,-01,getdate())) then 1 else 0 end) as count_01

    , sum(case when datepart(ww,TsCrea) = datepart(ww,dateadd(ww,-00,getdate())) then 1 else 0 end) as count_00

    FROM dbo.Table

    where tscrea > dateadd(ww,-13,getdate())

    group by country_code

    order by country_code

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • January 26, 2004 at 7:15 am

    #491600

    Thanks for the replies, the code above is exactly what I was looking for. Great!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply