Solved

help with pivoting tabular dataset and prorating quantities by month

Posted on 2014-03-14
5
198 Views
Last Modified: 2014-03-20
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
0
Comment
Question by:metropia
  • 3
  • 2
5 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 39931766
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
0
 

Author Comment

by:metropia
ID: 39931800
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
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39932252
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
0
 

Author Comment

by:metropia
ID: 39933516
Hi David,

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

Thank you much.
0
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
ID: 39933551
Hi,

Have you run this piece of code?

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

Open in new window


Regards
  David
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
T-SQL 10 35
RAISERROR WITH NOWAIT 2 17
VB.NET Application Installation with sqlserver 8 30
SQL Recursion 6 18
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question