Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

help me write sql Expand / Collapse
Author
Message
Posted Thursday, April 17, 2014 4:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 2, 2014 5:04 AM
Points: 6, Visits: 14
I have a table (with data) like the following :


Site-id product_id mode quantity date_of_issue_return

3 14 Issue 75 02/10/2014
6 14 Issue 100.5 02/15/2014
7 14 Return 120 01/14/2014
1 12 Issue 400 01/04/2014
1 12 Issue 27.5 03/05/2014
4 9 Return 159 03/28/2014
4 9 Return 20 03/19/2014
3 14 Return 30 01/25/2014
4 9 Issue 101 01/01/2014

I want a report like the following within a given period

site_id ---product_id---total issue qty---total return qty---total consumption(total issue- total return)

please help me how can I write a sql query to achieve this?
Post #1562575
Posted Thursday, April 17, 2014 4:28 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:15 AM
Points: 4,467, Visits: 10,808
SET DATEFORMAT mdy

DECLARE @test TABLE (
Site_id int,
product_id int,
mode varchar(10),
quantity decimal(9,3),
date_of_issue_return date
)



INSERT INTO @test VALUES
(3, 14 ,'Issue' ,75 ,'02/10/2014')
,(6, 14 ,'Issue' ,100.5 ,'02/15/2014')
,(7, 14 ,'Return' ,120 ,'01/14/2014')
,(1, 12 ,'Issue' ,400 ,'01/04/2014')
,(1, 12 ,'Issue' ,27.5 ,'03/05/2014')
,(4, 9 ,'Return' ,159 ,'03/28/2014')
,(4, 9 ,'Return' ,20 ,'03/19/2014')
,(3, 14 ,'Return' ,30 ,'01/25/2014')
,(4, 9 ,'Issue' ,101 ,'01/01/2014')


SELECT site_id,
product_id,
SUM(CASE mode WHEN 'Issue' THEN quantity ELSE 0 END) AS total_issue_qty,
SUM(CASE mode WHEN 'Return' THEN quantity ELSE 0 END) AS total_return_qty,
SUM(CASE mode WHEN 'Issue' THEN quantity ELSE 0 END)
- SUM(CASE mode WHEN 'Return' THEN quantity ELSE 0 END) AS total_consumption
FROM @test
WHERE date_of_issue_return >= '01/01/2014'
AND date_of_issue_return < '02/01/2014'
GROUP BY site_id,
product_id

Hope this helps


--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1562585
Posted Friday, April 18, 2014 1:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 2, 2014 5:04 AM
Points: 6, Visits: 14
You are a genious spaghettidba....thanks a ton for the help.....people like u make the world a better place to live.....
Post #1562936
Posted Friday, April 18, 2014 2:00 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:15 AM
Points: 4,467, Visits: 10,808
You're welcome.
Thanks for the kind words.


--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1562938
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse