Avatar of metropia
metropiaFlag for United States of America asked on

help with pivoting tabular dataset and prorating quantities by month

I have a stored procedure that prorates quantities based on either a contract start date, contract end date, or an order requested delivery date.

A sales contract is also referred to as a "blanket" type
A regular sales order is a "standard" type

Sales Contracts use Contract Start and End date to do prorate calculation
Regular Sales Orders use the Requested Delivery Date to do prorate calculation

Right now the results are in tabular format all quantities summed up

Bu I need a way to figure out how to split the pro-ration calculation and the quantities by month.

I am including a copy of my t-sql and also a copy of some sample data I am using to test.

I hope that by looking at these files, what I am trying to accomplish becomes clearer than my description of my problem.

I hope also, someone can have the time to offer a hand on working on this, because I am clueless how to do this type of pivot, with calculation.

All the formulas are included, any question, please shoot.

Thank you much.
stored-procedure-tabular.txt
salesdemand-proratedforecast.xlsx
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
David Todd

8/22/2022 - Mon
David Todd

Hi,

Without looking too long at your code, there are a couple of dates there. Which date do you want to group by?

Anyway, I find it easy to group by months using something like this:

select getdate(), dateadd( month, datediff( month, 0, getdate()), 0 )

That is, its a development of the common way to remove the time from a datetime, or in other words, group by day. This works for most things other than week and second.

HTH
  David
ASKER
metropia

There is also another column I did not include at the time I uploaded the file, that is ItemNumber, I think I can group by that column.

I was not considering to group by any of the date fields, because regular sales orders use request delivery date,  and sales contracts used contract start and contract end date, and I do not understand how to group by using all these 3 cols.

Thank you
David Todd

Hi,

The bottom line for all those dates - there is ONE date which is when the transaction is realised by the accounting system. That is, if you put an order in now for delivery in a week, then often the order date is now and the accounting system shows the debt against the debtors account as of now. That is the date to use I think.

What I showed above will get grouping by month. I don't think that grouping by item number is meaningful.

HTH
  David
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
metropia

Hi David,

What do you mean by what you showed above? perhaps I am missing something from your post?

Thank you much.
ASKER CERTIFIED SOLUTION
David Todd

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question