help me write sql

  • 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?

  • 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

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

  • 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