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
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
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Itzik Ben-Gan
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:http://sqlmag.com/t-sql/t-sql-best-practices-part-2
WHERE col >= '20120101' AND col < '20120201'
This form works with all types and all precisions, regardless of whether the time part is applicable.
Itzik Ben-Gan
ASKER
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.
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.
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.
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.