CODEWITHSUNDEEP
sqlsql-servert-sql
DotnetSparrow
DotnetSparrow

Reputation: 27996

Getting grouped by record value in second row

I have the following query

Select 
   Date, Item_Code, 
   SUM(In_Quantity) as In_Quantity, 
   SUM(Issue_Quantity) as Issue_Quantity, 
   (SUM(In_Quantity) - SUM(issue_Quantity)) as BalanceQty    
from
   (select 
        tbl_add_product.Date as Date, tbl_add_product.Item_Code, 
        tbl_add_product.In_Quantity, 0 as Issue_Quantity 
    from 
        tbl_add_product
    where 
        Item_Code = 'pen' 

    union ALL

    select 
        tbl_issue_product.Date as Date, tbl_issue_product.Item_Code, 
        0 as In_Quantity, Issue_Quantity 
    from 
        tbl_issue_product
    where 
        Item_Code = 'pen') X 
group by 
    Item_Code, Date

which returns the following result:

Date         Item_Code  In_Quantity Issue_Quanitity BalanceQty
--------------------------------------------------------------
2014-12-02    pen           100         0             100
2014-12-03    pen           50         50               0

I want to 100 in second row. the logic is that balance Qty from first row should be added to In_Qty so that when Issue_Quantity is subtracted from it, it gives BalanceQty

Upvotes: 1

Views: 53

Answers (3)

Rob Farley
Rob Farley

Reputation: 15849

Wrap it in another subquery and then use:

SELECT *, SUM(BalanceQty) OVER (PARTITION BY Item_Code ORDER BY [Date])
FROM ( ... ) o

Upvotes: 1

Vasily
Vasily

Reputation: 5782

--variant using LAG() OVER () function

SELECT  [Date] ,
                Item_Code ,
                SUM(In_Quantity) AS In_Quantity ,
                SUM(Issue_Quantity) AS Issue_Quantity ,
                ( SUM(In_Quantity) - SUM(issue_Quantity) )
                + COALESCE(LAG(SUM(In_Quantity) - SUM(issue_Quantity)) OVER ( PARTITION BY item_code ORDER BY [date] ),
                           0) AS BalanceQty
        FROM    ( SELECT    tbl_add_product.Date AS Date ,
                            tbl_add_product.Item_Code ,
                            tbl_add_product.In_Quantity ,
                            0 AS Issue_Quantity
                  FROM      tbl_add_product
                  WHERE     Item_Code = 'pen'
                  UNION  ALL
                  SELECT    tbl_issue_product.Date AS Date ,
                            tbl_issue_product.Item_Code ,
                            0 AS In_Quantity ,
                            Issue_Quantity
                  FROM      tbl_issue_product
                  WHERE     Item_Code = 'pen'
                ) X
        GROUP BY Item_Code ,
                Date

Upvotes: 0

Sarath Subramanian
Sarath Subramanian

Reputation: 21311

Consider the following is the result from your first query after SUM and all

enter image description here

Here is you sample code

SELECT * INTO #TEMP
FROM
(
    SELECT '2014-12-02' [Date],    'pen' Item_Code,100  In_Quantity,0 Issue_Quanitity,100 BalanceQty
    UNION ALL
    SELECT '2014-12-03',    'pen',           50,         50,               0
)TAB

Use self join to get the previous record

;WITH CTE1 AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY  Item_Code,[DATE])RNO,* 
    FROM #TEMP T1
)
SELECT C1.[Date] ,C1.Item_Code,C1.In_Quantity,
C1.Issue_Quanitity,
ISNULL((C2.Issue_Quanitity+C1.In_QuantitY)+C1.Issue_Quanitity ,C1.In_Quantity)BalanceQty2
FROM CTE1 C1 
LEFT JOIN CTE1 C2 ON C1.RNO=C2.RNO+1

RESULT

enter image description here

Upvotes: 0

Related Questions