SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Stored procedure for monthly sales with discount
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Lebowski
Starting Member

17 Posts

Posted - 03/06/2014 :  23:27:32  Show Profile  Reply with Quote
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

Edited by - Lebowski on 03/06/2014 23:37:29

khtan
In (Som, Ni, Yak)

Singapore
17635 Posts

Posted - 03/07/2014 :  00:23:46  Show Profile  Reply with Quote
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
Time is always against us


Edited by - khtan on 03/07/2014 00:24:21
Go to Top of Page

Lebowski
Starting Member

17 Posts

Posted - 03/07/2014 :  00:49:09  Show Profile  Reply with Quote
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
Time is always against us





http://mnmt.no
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17635 Posts

Posted - 03/07/2014 :  01:08:41  Show Profile  Reply with Quote
just put a CREATE PROCEDURE ... infront and it will be a stored procedure


KH
Time is always against us

Go to Top of Page

Lebowski
Starting Member

17 Posts

Posted - 03/07/2014 :  01:14:49  Show Profile  Reply with Quote
That didnt work, And also i need monthly reports..

http://mnmt.no
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17635 Posts

Posted - 03/07/2014 :  01:18:37  Show Profile  Reply with Quote
what didn't work ?


KH
Time is always against us

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17635 Posts

Posted - 03/07/2014 :  01:20:56  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000