Transfer/Allocate Sales Data based on Date ranges

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Transfer/Allocate Sales Data based on Date ranges

Author

Topic

ravishwor
Starting Member

20 Posts

Posted - 2008-07-24 : 21:25:02

I have a need to now review my current setup that Transfers Sales$ from a Repcode to another Repcode. Until now we had a setup that reduced the Sales for a Rep by a fixed% and then increased the sales for another Repcode by the same fixed% with no Start and End dates for the %.

The key joins are based on the Repcode and the CustomerType codes.

Now I need to add a date range (start and end dates) to the Transfer% as the % are being changed and we want the Old% to only affect the Old Sales data.

This is the Current Transfer% Table in which I think I will need to add the Start and End dates.

CREATE TABLE [dbo].[JDM10_Sales_Transfer_Rates](
[Cust_Type] [nvarchar](255) NULL,
[From_State] [nvarchar](255) NULL,
[From_Rep] [char](3) NULL,
[To_State] [nvarchar](255) NULL,
[Rep_code] [char](3) NULL,
[Transfer] [float] NULL
) ON [PRIMARY]

Sample Data from ].[JDM10_Sales_Transfer_Rates]
MTW VIC 16M Hobart MTV 0.094
MTW VIC 16M Melbourne MTT -0.094

Here, for Cust_Type MTW, Repcode 16M Sales are Increased for Rep MTV by 9.4% and decreased by 9.4% for Repcode MTT.

My Customer Table has the Cust_Type and Repcode field plus toher Customer related fields.

My InvoiceLines Table has these fields;
The ones required for the Transfer calculation are (so_rep_code (to get the From_Rep), so_cust_code (to get the Cust_Type), so_processing_date)

so_order_no,
so_bo_suffix,
so_cust_code,
so_order_status,
so_order_reason_code,
so_whse_code,
so_territory_code,
so_rep_code,
so_invoice_no,
so_credit_note_no,
so_processing_date,
stock_code,
sol_item_cost,
sol_shipped_sales_tax_amt,
sol_line_cost,
sol_ordered_qty,
sol_backorder_qty,
sol_shipped_qty,
sol_shipped_amount,
sol_line_amount,
sol_item_price,
sol_ordered_sales_tax_amt,
so_order_reason_code,
so_order_type_code

Any help or ideas on how to proceed.
Thx,
Ravi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 00:36:57

didnt get full idea. do you mean two dates which determine period when the percentage was changed for a rep?

ravishwor
Starting Member

20 Posts

Posted - 2008-07-25 : 01:08:59

Hi Visakh16,

Yes your are on the right path. Table [JDM10_Sales_Transfer_Rates] which holds the Transfer% will need to have 2 additional Fields (StartDate and EndDate) for the Transfer%. This is to allow for the Change in the Transfer% by the Cust_Type/Repcode which occurs from time to time.

EG; [JDM10_Sales_Transfer_Rates]
MTW VIC 16M Hobart MTV 0.094 01/10/2007 30/06/2008
MTW VIC 16M Melbourne MTT -0.094 01/10/2007 30/06/2008
MTW VIC 16M Hobart MTV 0.099 01/07/2008 31/07/2009
MTW VIC 16M Melbourne MTT -0.099 01/07/2008 31/07/2009

For this sample, any invoices lines with so_processing_date between (01/10/2007 30/06/2008) and Cust_Type = MTW and Repcode = 16M the Invoiceline amount (sol_line_amount) is Increased by 9.4% for Repcode = MTV.

The corresponding Transaction for Repcode = MTT is Decreased by 9.4%.

The same logic will apply for so_processing_date between (01/07/2008 31/07/2009). In this case the Transfer% that will be used is 9.9%.

Thx.

ravishwor
Starting Member

20 Posts

Posted - 2008-07-25 : 01:11:12

I am just looking for a Select SQL statement not Update as this needs to be part of my report extract.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 01:23:53

quote:
Originally posted by ravishwor

I am just looking for a Select SQL statement not Update as this needs to be part of my report extract.

so your select statement should extract records from invoice line along with the new price(after appying the increase/decrease % from main table)?

ravishwor
Starting Member

20 Posts

Posted - 2008-07-25 : 01:37:23

Hi Visakh16, Yes the Select sql should return the updated 'sol_line_amount'. It should also return oth other Invoices lines that do not have this logic applied.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 04:19:01

quote:
Originally posted by ravishwor

Hi Visakh16, Yes the Select sql should return the updated 'sol_line_amount'. It should also return oth other Invoices lines that do not have this logic applied.

select il.reqdfields,il.sol_line_amount +(il.sol_line_amount * coalesce(str.Rep_code,0)) as [new price]
from InvoiceLines il
LEFT JOIN JDM10_Sales_Transfer_Rates str
ON str.From_Rep=il.so_rep_code 
AND str.Cust_Type=il.so_rep_code 
AND il.so_processing_date>=str.StartDate 
AND il.so_processing_date<=str.EndDate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources