April 17, 2014 at 4:05 am
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?
April 17, 2014 at 4:28 am
SET DATEFORMAT mdy
DECLARE @test-2 TABLE (
Site_id int,
product_id int,
mode varchar(10),
quantity decimal(9,3),
date_of_issue_return date
)
INSERT INTO @test-2 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-2
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
April 18, 2014 at 1:54 am
You are a genious spaghettidba....thanks a ton for the help.....people like u make the world a better place to live.....
April 18, 2014 at 2:00 am
You're welcome.
Thanks for the kind words.
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply