help me write sql

Question

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

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

tirthadip SSC Rookie Points: 34 More actions · Answer 2

You are a genious spaghettidba....thanks a ton for the help.....people like u make the world a better place to live.....

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

You're welcome.

Thanks for the kind words.

-- Gianluca Sartori

Cookies on SQLServerCentral