Stored procedure for monthly sales with discount

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

General SQL Server Forums

New to SQL Server Programming

Stored procedure for monthly sales with discount

Author

Topic

Lebowski
Starting Member

17 Posts

Posted - 2014-03-06 : 23:27:32

I need advice on how to create a stored procedure, or many stored procedures for generating a monthly sales report in our company.

We have two tables:

ITEM_SALES which consists of: 
Item_ID
Name
Store
Sales_Date
Sales_Price
Quantity

And then

ITEM_DISCOUNT which consists of: 
Item_ID
Name
Store
Sales_Price
Date_From
Date_To

Explanation: After each month, our different stores will send us a report on which discounts they had. These discounts can vary from one, to many days and therefor we have the Date_From, Date_to.

To make this sales report, i need a procedure that first fetches all sales in ITEM_SALES, then checks all the discounts in ITEM_DISCOUNT and overwrites the ones that have similar Item_ID, Name, and Store for a selected period.

Example: So if a item originally had a sales_price on 99,- and then had a discount sales_price to 79,- for 2014-01-02 to 2014-01-10 it has to be overwritten for that period so the report shows the right numbers.

Is this understandable? And can anyone help me or give me some tips on the way? Is temporary tables good for this?

If more info is needed, i will provide!

http://mnmt.no

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-07 : 00:23:46

[code]SELECT s.Item_ID
s.Name
s.Store
s.Sales_Date
Sales_Price = COALESCE(d.Sales_Price, s.Sales_Price)
s.Quantity
FROM ITEM_SALES s
LEFT JOIN ITEM_DISCOUNT d ON s.Store = d.Store
and s.Item_ID = d.Item_ID
and s.Sales_Date between d.Date_From and d.Date_to
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Lebowski
Starting Member

17 Posts

Posted - 2014-03-07 : 00:49:09

Thanks for replying, but a query wont help me much. I need a stored procedure, that generates a temporary report.

quote:

Originally posted by khtan

SELECT s.Item_ID
       s.Name
       s.Store
       s.Sales_Date
       Sales_Price = COALESCE(d.Sales_Price, s.Sales_Price)
       s.Quantity
FROM   ITEM_SALES s
       LEFT JOIN ITEM_DISCOUNT d ON  s.Store = d.Store
                                 and s.Item_ID = d.Item_ID
                                 and s.Sales_Date between d.Date_From and d.Date_to

KH
[spoiler]Time is always against us[/spoiler]

http://mnmt.no

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-07 : 01:08:41

just put a CREATE PROCEDURE ... infront and it will be a stored procedure

KH
[spoiler]Time is always against us[/spoiler]

Lebowski
Starting Member

17 Posts

Posted - 2014-03-07 : 01:14:49

That didnt work, And also i need monthly reports..

http://mnmt.no

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-07 : 01:18:37

what didn't work ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-07 : 01:20:56

this will sum up the Quantity by month

SELECT Item_ID, Name, Store, dateadd(month, datediff(month, 0, Sales_Date), 0), 
       Quantity = SUM(Quantity),
       Amount   = SUM(Quantity *  COALESCE(d.Sales_Price, s.Sales_Price))
FROM
(
SELECT s.Item_ID
       s.Name
       s.Store
       s.Sales_Date
       Sales_Price = COALESCE(d.Sales_Price, s.Sales_Price)
       s.Quantity
FROM   ITEM_SALES s
       LEFT JOIN ITEM_DISCOUNT d ON  s.Store = d.Store
                                 and s.Item_ID = d.Item_ID
                                 and s.Sales_Date between d.Date_From and d.Date_to
) s
GROUP BY  Item_ID, Name, Store, dateadd(month, datediff(month, 0, Sales_Date), 0)

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources