Rolling query count and sum

I have a table called POLINE and it has these fields
PONUMBER
LINENUMBER
ITEMNUMBER
ITEMDESC
QUANTITY
COST
PODATE

I have been asked to write a query that will compute rolling sums of the counts and costs. They need this to look at today's date and then to compute these values for the next 26 weeks. So if today was April 6th, this would be the first column and then there would be 25 more columns ending on the 15th of September with each column being one week later than the previous column. The rows would be the item numbers. So I am grouping by item number but then how to I get the 26 rolling column totals?
LVL 1
rwheeler23Asked:
Who is Participating?
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.

dsackerContract ERP Admin/ConsultantCommented:
Do you have a representation of your data you could share?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>and then to compute these values for the next 26 weeks.
Define 'week'.  Beginning on Saturday, Sunday, ...

The below can be used to get the week number based on the SET DATEFIRST setting in SQL Server, but it really can't be used in your situation as it won't handle 'the next 26 weeks' when that would break over a calendar year.
SELECT DATEPART(week, GETDATE())

Open in new window


Check out my article on Build your own SQL calendar table to perform complex date expressions, which has downloadable code to create your own calendar table.  Then you can JOIN this table to the one you're talking about, then group by the continuous_week column for the next 26 weeks.

>PODATE
Ok if this is just date, but if it's datetime then you'll have to CAST(PODATE as date) to filter out the time component to pull off the above JOIN.

Hope this helps.
Jim
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Also, the above comment speaks to returning grouped weeks as rows.  
To pull this off as columns that is going to be a PIVOT statement, but it looks like you have enough to keep you busy for awhile.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

rwheeler23Author Commented:
Here is a small sample for the real table and fields. YOU can use any of the date fields.  It appears they define a week as starting on a Monday. I will check out your article.
POSample.xlsx
0
dsackerContract ERP Admin/ConsultantCommented:
Please show how you expect the results to look?
0
rwheeler23Author Commented:
Here is a screenshot of the end result. I need to convert this spreadsheet into an SSRS report. I can handle all the values but need help setting up the weekly columns. So if I could see how one value is done I can apply it to the rest.
ShippingForecast.png
0
dsackerContract ERP Admin/ConsultantCommented:
Did you mention anywhere before now that this is a SSRS question? I may have missed that point.

That makes quite a difference in how we were approaching this, as we interpreted you saying you had "been asked to write a query" as only that.

From the looks of  your data and expected results, this seems more like a full-blown project and not just an EE question. Perhaps you can break this out into multiple EE requirements, OR better yet, find a contractor to work this for a fee. There are some good ones here on EE.
0
rwheeler23Author Commented:
Once I have the query building the SSRS report will fall into place. So let's just concentrate on the query.  I imagine that this report will ask them for a starting date which should be a Monday. The query then has to look at all the dates on existing POs and perform counts and sums on the dates grouping them by 26 weeks going forward from the starting date. This is a seasonal business. They will enter POs for the next year in the fall. For example, by November of 2015 POs will be entered for 2016 with 2016 dates on them. It is these dates upon which I need to group.

My apologies if my question was misleading. Since all SSRS reports start with a query my natural assumption was to start there and we all know what can happen when you assume.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Just to clarify, the sum of what is in this question is...
Create a set that is grouped by custom week, for the next 26 weeks, with aggregate totals
PIVOT that set so that the weeks are columns and not rows, and
Use that set as an SSRS data source, which likely means that the date is a parameter

This is easily more than a typical single EE question.
0
dsackerContract ERP Admin/ConsultantCommented:
RWheeler23, to recap your last post, are you saying the essence would be to count and sum by each date for an ITEMNBR, then to "change" those dates to look like successive dates, each date a week later than the previous, starting from when (the earliest date for that item)?
0
rwheeler23Author Commented:
From what they tell me each date is the beginning of the period. So the first column is April 6-12 and then second column is April 13 - 19, and so on.  Their POs will have dates within these periods and they are looking to report on what is expected within each week. So they will specify the first Monday of the report, in this case April 6th, and then for the next 26 weeks they want to know the counts and sums within each week not for a specific date in that week.
0
rwheeler23Author Commented:
For my own edification and to properly allocate points, what would have been the proper way to submit this question? Should I have broken it up into sections? I would have no problem doing that right now.
0
dsackerContract ERP Admin/ConsultantCommented:
Probably avoiding the word SSRS in a subsequent post wouldn't have raised the scope creep alarm (laughing).

Your post #40810800 (two posts above) seems to be getting to what you really want to accomplish, so no foul. We can stay with that. If you need to expand later, perhaps award points here first, then open a follow-up (or two). At the least that recognizes the value of people's time. :)

I created a working set of code to load a temp table with the data you provided (see below).  I see the earliest PRMDATE is 2014-04-10, then I see 2014-07-13, 2015-05-08, etc. I do not readily see values that would fit into each week's bucket. Can you connect the dots between the data and your spreadsheet example? Are you trending, filling in missing gaps with some formula, etc?

DECLARE @PoLine TABLE (
      PONUMBER          varchar(6)        NOT NULL,
      ORD               int               NOT NULL,
      ITEMNMBR          varchar(16)       NOT NULL,
      ITEMDESC          varchar(32)       NOT NULL,
      QTYORDER          decimal(11, 2)    NOT NULL,
      COST              AS CONVERT(decimal(11, 2), EXTDCOST / QTYORDER),
      EXTDCOST          decimal(11, 2)    NOT NULL,
      REQDATE           date              NOT NULL,
      PRMDATE           date              NOT NULL,
      PRMSHPDTE         date              NOT NULL    )
 
SET NOCOUNT ON
 
INSERT INTO @PoLine VALUES ('PO0997', 16384, 'PHON-ATT-53BK', 'Cordless-Attractive 5352-Black', 1, 90.25, '2014-04-10', '2014-04-10', '2014-04-10')
INSERT INTO @PoLine VALUES ('PO0999', 16384, 'ACCS-CRD-12WH', 'Phone Cord - 12'' White', 1, 3.29, '2014-04-10', '2014-04-10', '2014-04-10')
INSERT INTO @PoLine VALUES ('PO0999', 32768, 'ANSW-PAN-1450', 'Panache KX-T1450 answer', 2, 100.5, '2014-04-10', '2014-04-10', '2014-04-10')
INSERT INTO @PoLine VALUES ('PO1002', 16384, 'PHON-GTE-5043', 'Cordless-Grand S5043', 10, 812.5, '2014-04-01', '2014-04-01', '2014-04-01')
INSERT INTO @PoLine VALUES ('PO1002', 32768, 'PHON-GTE-3458', 'Memory-Grand M3458', 6, 450, '2014-04-01', '2014-04-01', '2014-04-01')
INSERT INTO @PoLine VALUES ('PO1004', 16384, 'HDWR-SWM-0100', 'Switching Module (<100)', 1, 7780.25, '2014-04-01', '2014-04-01', '2014-04-01')
INSERT INTO @PoLine VALUES ('PO1004', 32768, 'HDWR-SRG-0001', 'Surge Protector Panel', 6, 111.9, '2014-04-01', '2014-04-01', '2014-04-01')
INSERT INTO @PoLine VALUES ('PO1005', 16384, 'ACCS-HDS-1EAR', 'Headset-Single Ear', 15, 578.85, '2014-04-01', '2014-04-01', '2014-04-01')
INSERT INTO @PoLine VALUES ('PO1005', 32768, 'ACCS-CRD-25BK', 'Phone Cord - 25'' Black', 20, 119.6, '2014-04-01', '2014-04-01', '2014-04-01')
INSERT INTO @PoLine VALUES ('PO1006', 16384, 'PHON-ATT-53BL', 'Cordless-Attractive 5352-Blue', 10, 935.5, '2014-04-01', '2014-04-01', '2014-04-01')
INSERT INTO @PoLine VALUES ('PO1006', 32768, 'PHON-ATT-53BK', 'Cordless-Attractive 5352-Black', 10, 902.5, '2014-04-01', '2014-04-01', '2014-04-01')
INSERT INTO @PoLine VALUES ('PO1007', 16384, 'HDWR-LDS-0001', 'Network LDS/Card', 2, 5300, '2014-04-01', '2014-04-01', '2014-04-01')
INSERT INTO @PoLine VALUES ('PO1007', 32768, 'HDWR-RNG-0001', 'Ring Generator', 1, 648.5, '2014-04-01', '2014-04-01', '2014-04-01')
INSERT INTO @PoLine VALUES ('PO1007', 49152, 'HDWR-PRO-4862', 'Processor 486/25MHz', 2, 5996.3, '2014-04-01', '2014-04-01', '2014-04-01')
INSERT INTO @PoLine VALUES ('PO1008', 16384, 'HDWR-T1I-0001', 'T1 Interface Kit', 2, 2990, '2014-04-01', '2014-04-01', '2014-04-01')
INSERT INTO @PoLine VALUES ('PO1008', 32768, 'HDWR-TPS-0001', 'Tape Unit/Mass Storage', 1, 1224.64, '2014-04-01', '2014-04-01', '2014-04-01')
INSERT INTO @PoLine VALUES ('PO1008', 49152, 'HDWR-PRO-4866', 'Processor 486/66 MHz', 2, 6758.5, '2014-04-01', '2014-04-01', '2014-04-01')
INSERT INTO @PoLine VALUES ('PO1009', 16384, 'HDWR-ACC-0100', 'Acclaimed Call Center System 100', 1, 35000, '2014-04-01', '2014-04-01', '2014-04-01')
INSERT INTO @PoLine VALUES ('PO1009', 32768, 'FAXX-CAN-9800', 'Cantata FaxPhone 9800', 25, 35000, '2014-04-01', '2014-04-01', '2014-04-01')
INSERT INTO @PoLine VALUES ('PO1012', 16384, 'ACCS-CRD-25BK', 'Phone Cord - 25'' Black', 2, 11.96, '2014-07-13', '2014-07-13', '2014-07-13')
INSERT INTO @PoLine VALUES ('PO1013', 16384, 'ACCS-HDS-2EAR', 'Headset - Dual Ear', 10, 419.8, '2015-05-08', '2015-05-08', '2015-05-08')
INSERT INTO @PoLine VALUES ('PO1014', 16384, 'PHON-GTE-3458', 'Memory-Grand M3458', 4, 0, '2015-05-08', '2015-05-08', '2015-05-08')
INSERT INTO @PoLine VALUES ('PO1015', 16384, 'FAXX-SLK-2100', 'Sleek UX-2100 fax', 5, 4395.25, '2015-05-08', '2015-05-08', '2015-05-08')
INSERT INTO @PoLine VALUES ('PO1016', 16384, 'ACCS-RST-DXWH', 'Shoulder Rest - Deluxe White', 5, 22.75, '2015-05-08', '2015-05-08', '2015-05-08')
INSERT INTO @PoLine VALUES ('PO2020', 16384, 'PHON-ATT-53BK', 'Cordless-Attractive 5352-Black', 2, 180.5, '2016-02-26', '2016-02-26', '2016-02-26')
INSERT INTO @PoLine VALUES ('PO2046', 16384, 'ACCS-CRD-12WH', 'Phone Cord - 12'' White', 6, 19.74, '2017-01-19', '2017-01-19', '2017-01-19')
INSERT INTO @PoLine VALUES ('PO2046', 32768, 'ACCS-HDS-2EAR', 'Headset - Dual Ear', 6, 251.88, '2017-01-19', '2017-01-19', '2017-01-19')
INSERT INTO @PoLine VALUES ('PO2047', 16384, 'WIRE-MCD-0001', 'Multi conductor wire', 1000, 540, '2017-01-21', '2017-01-21', '2017-01-21')
INSERT INTO @PoLine VALUES ('PO2047', 32768, 'WIRE-SCD-0001', 'Single conductor wire', 500, 80, '2017-01-21', '2017-01-21', '2017-01-21')
INSERT INTO @PoLine VALUES ('PO2056', 16384, 'ACCS-CRD-25BK', 'Phone Cord - 25'' Black', 10, 59.8, '2017-04-11', '2017-04-11', '2017-04-11')
INSERT INTO @PoLine VALUES ('PO2056', 32768, 'ACCS-HDS-1EAR', 'Headset-Single Ear', 8, 308.72, '2017-04-11', '2017-04-11', '2017-04-11')
INSERT INTO @PoLine VALUES ('PO2056', 49152, 'ACCS-CRD-25BK', 'Phone Cord - 25'' Black', 2, 11.96, '2017-04-11', '2017-04-11', '2017-04-11')
INSERT INTO @PoLine VALUES ('PO2058', 16384, 'PHON-GTE-3458', 'Memory-Grand M3458', 4, 300, '2017-04-11', '2017-04-11', '2017-04-11')
INSERT INTO @PoLine VALUES ('PO2071', 16384, '100XLG', 'Green Phone', 1200, 34152, '2017-04-12', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2071', 32768, '100XLG', 'Green Phone', 100, 2846, '2017-04-12', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2071', 49152, '100XLG', 'Green Phone', 100, 2846, '2017-05-01', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2071', 65536, '100XLG', 'Green Phone', 100, 2846, '2017-06-01', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2071', 81920, '100XLG', 'Green Phone', 100, 2846, '2017-07-01', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2071', 98304, '100XLG', 'Green Phone', 100, 2846, '2017-08-01', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2071', 114688, '100XLG', 'Green Phone', 100, 2846, '2017-09-01', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2071', 131072, '100XLG', 'Green Phone', 100, 2846, '2017-10-01', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2071', 147456, '100XLG', 'Green Phone', 100, 2846, '2017-11-01', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2071', 163840, '100XLG', 'Green Phone', 100, 2846, '2017-12-01', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2071', 180224, '100XLG', 'Green Phone', 100, 2846, '2018-01-01', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2071', 196608, '100XLG', 'Green Phone', 100, 2846, '2018-02-01', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2071', 212992, '100XLG', 'Green Phone', 100, 2846, '2018-03-01', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2072', 16384, 'ACCS-CRD-12WH', 'Phone Cord - 12'' White', 12000, 39480, '2017-04-12', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2072', 32768, 'ACCS-CRD-12WH', 'Phone Cord - 12'' White', 1000, 3290, '2017-04-12', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2072', 49152, 'ACCS-CRD-12WH', 'Phone Cord - 12'' White', 1000, 3290, '2017-05-01', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2072', 65536, 'ACCS-CRD-12WH', 'Phone Cord - 12'' White', 1000, 3290, '2017-06-01', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2072', 81920, 'ACCS-CRD-12WH', 'Phone Cord - 12'' White', 1000, 3290, '2017-07-01', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2072', 98304, 'ACCS-CRD-12WH', 'Phone Cord - 12'' White', 1000, 3290, '2017-08-01', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2072', 114688, 'ACCS-CRD-12WH', 'Phone Cord - 12'' White', 1000, 3290, '2017-09-01', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2072', 131072, 'ACCS-CRD-12WH', 'Phone Cord - 12'' White', 1000, 3290, '2017-10-01', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2072', 147456, 'ACCS-CRD-12WH', 'Phone Cord - 12'' White', 1000, 3290, '2017-11-01', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2072', 163840, 'ACCS-CRD-12WH', 'Phone Cord - 12'' White', 1000, 3290, '2017-12-01', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2072', 180224, 'ACCS-CRD-12WH', 'Phone Cord - 12'' White', 1000, 3290, '2018-01-01', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2072', 196608, 'ACCS-CRD-12WH', 'Phone Cord - 12'' White', 1000, 3290, '2018-02-01', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2072', 212992, 'ACCS-CRD-12WH', 'Phone Cord - 12'' White', 1000, 3290, '2018-03-01', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2074', 16384, '100XLG', 'Green Phone', 1, 28.46, '2017-04-12', '2017-04-12', '2017-04-12')
INSERT INTO @PoLine VALUES ('PO2075', 16384, '1-A3261A', 'Multi-Core Processor', 12, 192000, '2017-04-12', '2017-04-12', '2017-04-12')
 
SET NOCOUNT OFF
 
SELECT * FROM @PoLine
ORDER BY REQDATE

Open in new window

0

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
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>For my own edification and to properly allocate points,
Looks like dsacker is doing most of the work here, so any split where he gets the bulk of the points is fine.
Do me a favor though and if you're going to award me some points, then do so on my first comment with the article link in it, as article authors get bonus points when links to EE articles are used in solutions.

>Should I have broken it up into sections?
No one's going to fault you for starting with a single question, just be prepared to be flexible as far as breaking things up.  Experts here are suckers for, er I mean compete for points, and aren't likely to write a book for a single question.

You wouldn't believe the number of people that people ask us to do their homework / do their entire project in a single question.

That, and I'm kind of a busy guy with a marginally acceptable social life, so if you put all of your requirements eggs into a single question basket and one expert responds, that may prevent other experts from commenting, causing you to wait longer to get answers.
0
Scott PletcherSenior DBACommented:
I suspect you'll want the column names/labels to be the actual dates.  For that, I'd suggest having SSRS use a stored proc as a dataset rather than trying to use a query directly in SSRS.  Other than that, the query looks straightforward to me.  Is that OK or do you need to use a query directly in SSRS?
0
rwheeler23Author Commented:
I need to now read through 5 sheets of documentation on the rest of this report. The sp idea sounds good for column headings. The want to use this report as a forecasting tool.  Hopefully in about an hour I can return to this and give it a try.
0
rwheeler23Author Commented:
Thanks for everyone's help. Having read the notes with this data I have decided to award these point because you have put a lot of time in on this and I will open a new case and hopefully phrase my question better. This report is more complex than I originally thought however, if I can get down the part about breaking out the 26 weeks  I should be able to take it from there.
0
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 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.