Rolling query count and sum

Do you have a representation of your data you could share?

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.

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

Please show how you expect the results to look?

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

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.

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.

Just to clarify, the sum of what is in this question is...

This is easily more than a typical single EE question.

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)?

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.

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.

>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.

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?

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.

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.