SQL Syntax to Count and Sum by 26 weeks

I have been tasked with the job of coming up with a SSRS report that will count and sum quantities and costs in weekly increments starting from a specified date and then for the next 26 weeks. Each column will count and sum values for the 7 days that make up each week. So if the date entered is April 6, 2016 the first column is 04/06-04/12, the next is 04/13-04/19, etc.  My question is how do I structure the query so that it will automatically sum by week?

I have attached a copy of the actual dataset for 2015. What this report will do is allow the users to forecast what is expected to be received over that six month period.  Here is the query that produced this dataset. The PRMDATE field is the date the goods are expected to be received so the columns need to be based on this field value.

SELECT ITEMNMBR,QTYORDER,EXTDCOST,PRMDATE FROM RAD..POP10110 WHERE PRMDATE>='01/01/15' ORDER BY PRMDATE

There is no grouping on this report. In this case these values are what are expected to be received. So the end result for this data would be one row and in each column would be a total count and the total sum of the cost of the items due in each week. Something like:

Week One       Week Two      Week Three    Total
April 6              April 13           April 20
Count Cost      Count  Cost    Count  Cost     Count   Cost
2,000  10,000  3,000 15,000  2,500  12,500  7,500  37,500
SamplePOOrders.xlsx
LVL 1
rwheeler23Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
you are looking to aggregate data

declare startdate as datetime
select
sum (if ( prmdate between @startdate and dateadd ('d',@startdate,6)  then extdcost ) as week 1,
sum (if ( prmdate between dateadd('d',@startdate,7) and dateadd ('d',@startdate,13)  then extdcost ) as week 2,
.
.
.

sum (if ( prmdate between dateadd('d',@startdate,175) and dateadd ('d',@startdate,181)  then extdcost ) as week 26
from table where prmdate>=@startdate
PortletPaulEE Topic AdvisorCommented:
I would do this just a little differently, in particular I avoid using "between" when dealing with date ranges

Here I place the date calculations in a CROSS JOINed subquery which allows me to refer to those calculations by their alias
in the case expressions and these do not use "between" deliberately.

declare @startdate as datetime = '20150101'

select
    sum(case when prmdate >= w0 and prmdate < w1  then extdcost end) as costw1
  , sum(case when prmdate >= w1 and prmdate < w2  then extdcost end) as costw2
  , sum(case when prmdate >= w2 and prmdate < w3  then extdcost end) as costw3
  , sum(case when prmdate >= w3 and prmdate < w4  then extdcost end) as costw4
  , sum(case when prmdate >= w4 and prmdate < w5  then extdcost end) as costw5
  , sum(case when prmdate >= w5 and prmdate < w6  then extdcost end) as costw6
  , sum(case when prmdate >= w6 and prmdate < w7  then extdcost end) as costw7
  , sum(case when prmdate >= w7 and prmdate < w8  then extdcost end) as costw8
  , sum(case when prmdate >= w8 and prmdate < w9  then extdcost end) as costw9
  , sum(case when prmdate >= w9 and prmdate < w10 then extdcost end) as costw10
  , sum(case when prmdate >= w10 and prmdate < w11 then extdcost end) as costw11
  , sum(case when prmdate >= w11 and prmdate < w12 then extdcost end) as costw12
  , sum(case when prmdate >= w12 and prmdate < w13 then extdcost end) as costw13
  , sum(case when prmdate >= w13 and prmdate < w14 then extdcost end) as costw14
  , sum(case when prmdate >= w14 and prmdate < w15 then extdcost end) as costw15
  , sum(case when prmdate >= w15 and prmdate < w16 then extdcost end) as costw16
  , sum(case when prmdate >= w16 and prmdate < w17 then extdcost end) as costw17
  , sum(case when prmdate >= w17 and prmdate < w18 then extdcost end) as costw18
  , sum(case when prmdate >= w18 and prmdate < w19 then extdcost end) as costw19
  , sum(case when prmdate >= w19 and prmdate < w20 then extdcost end) as costw20
  , sum(case when prmdate >= w20 and prmdate < w21 then extdcost end) as costw21
  , sum(case when prmdate >= w21 and prmdate < w22 then extdcost end) as costw22
  , sum(case when prmdate >= w22 and prmdate < w23 then extdcost end) as costw23
  , sum(case when prmdate >= w23 and prmdate < w24 then extdcost end) as costw24
  , sum(case when prmdate >= w24 and prmdate < w25 then extdcost end) as costw25
  , sum(case when prmdate >= w25 and prmdate < w26 then extdcost end) as costw26
from POP10110
cross join (
  select
	   @startdate as w0
	  , dateadd(week,1,@startdate) as w1
	  , dateadd(week,2,@startdate) as w2
	  , dateadd(week,3,@startdate) as w3
	  , dateadd(week,4,@startdate) as w4
	  , dateadd(week,5,@startdate) as w5
	  , dateadd(week,6,@startdate) as w6
	  , dateadd(week,7,@startdate) as w7
	  , dateadd(week,8,@startdate) as w8
	  , dateadd(week,9,@startdate) as w9
	  , dateadd(week,10,@startdate) as w10
	  , dateadd(week,11,@startdate) as w11
	  , dateadd(week,12,@startdate) as w12
	  , dateadd(week,13,@startdate) as w13
	  , dateadd(week,14,@startdate) as w14
	  , dateadd(week,15,@startdate) as w15
	  , dateadd(week,16,@startdate) as w16
	  , dateadd(week,17,@startdate) as w17
	  , dateadd(week,18,@startdate) as w18
	  , dateadd(week,19,@startdate) as w19
	  , dateadd(week,20,@startdate) as w20
	  , dateadd(week,21,@startdate) as w21
	  , dateadd(week,22,@startdate) as w22
	  , dateadd(week,23,@startdate) as w23
	  , dateadd(week,24,@startdate) as w24
	  , dateadd(week,25,@startdate) as w25
	  , dateadd(week,26,@startdate) as w26
 ) wks
 
  

Open in new window


please see: "Beware of Between"

& see demo at: http://sqlfiddle.com/#!3/d5994/5

(+edit)
NOTE:
It isn't possible to provide column headings that include the date(s) UNLESS you also use "dynamic sql" (sql that is generated in a query then executed by the query).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
arnoldCommented:
Paul, point well made, though fortunately for me I accounted for the between (inclusive) that is part of the query.

you might be able to use ;with CTE and your initial select with the date variance to use the prior query and directly pass the answers to the 26 w handling query.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

PortletPaulEE Topic AdvisorCommented:
agreed, one could use a CTE instead of the cross join, but there is no performance benefit in doing so

I prefer to not use between for any date range operation, an approach that ensures there is never a doubt regarding the range boundaries and the data precision. While some may disagree with me, many others do agree on this e.g.

the best practice with date and time ranges is to avoid BETWEEN and to always use the form:

WHERE col >= '20120101' AND col < '20120201'
This form works with all types and all precisions, regardless of whether the time part is applicable.
http://sqlmag.com/t-sql/t-sql-best-practices-part-2
Itzik Ben-Gan
rwheeler23Author Commented:
Thank you for not only the answer but a thorough explanation of the proper way to query dates. I just remember way back when told not to use between and now I know why.
arnoldCommented:
one caveate. one might want to consider is to adjust the startdate to the either the first monday based on the date, i.e. you have to define the start of the week on whose basis you want the data.

i.e. using date function determine what day of week startdate is and then either subtract the day of week from the start date. or advance the date by the difference to the next day.
Which day of the week is the start of your data set.
For your dual query, you would need to duplicate the case/if one sums the amounts while the other sums the quantities.
week1 count, week1 cost, etc.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.