Query returning last 13 weeks

• StefanJ

  SSCarpal Tunnel

  Points: 4713

  More actions

  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.

• NPeeters

  SSChampion

  Points: 12557

  More actions

  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.

• Johan Bijnens

  SSC Guru

  Points: 135380

  More actions

  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

• StefanJ

  SSCarpal Tunnel

  Points: 4713

  More actions

  January 26, 2004 at 7:15 am

  #491600

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