CODEWITHSUNDEEP
sql-server-2008
Krunal Shah
Krunal Shah

Reputation: 864

Fetch Value from Above Row in SQL Table and Calculate on the basis of that in next row

I want to Calculate Balance Quantity on the basis of top records fetched in my query.

My query is like as follows -

    SELECT ItemLedger.Code as Code,                                                                                                     
    ISNULL(MstOrganization.Name,'') AS OrganizationName,                                                                  
    ISNULL(stmVoucherType.Name,'') AS VoucherTypeName,                                                                                                         
    ISNULL(stmItem.Name,'') AS ItemName,                                                                                           
     ISNULL(stmItem.IsProductTag,0) AS IsProductTag,                                                                                                         
     ItemLedger.InOut AS [InOut],
     ItemLedger.ItemCode,                                                                                        
     ItemLedger.Quantity As Quantity,                                                                                                   
     CASE WHEN ItemLedger.Inout = 0    
     THEN ItemLedger.Quantity    
     ELse 0    
     End as InwardQuantity,    
      CASE WHEN ItemLedger.Inout = 1    
     THEN ItemLedger.Quantity * (-1)    
     ELse 0    
     End as OutwardQuantity,
     SUM((CASE WHEN ItemLedger.Inout = 0    
     THEN ItemLedger.Quantity    
     ELse 0    
     End) -    
      (CASE WHEN ItemLedger.Inout = 1    
     THEN ItemLedger.Quantity     
     ELse 0    
     End)) as BalanceQuantity

     FROM VIEW_ITEM_STOCK_LEDGER as ItemLedger                                                                 
     LEFT JOIN MstGroup ON MstGroup.Code = ItemLedger.GroupCode           
     LEFT JOIN MstOrganization ON MstOrganization.Code = ItemLedger.OrganizationCode                                                                       
     LEFT JOIN MstCurrency As BaseCurrency On BaseCurrency.Code = MstOrganization.CurrencyCode                                                                      
     LEFT JOIN stmVoucherType ON stmVoucherType.Code = ItemLedger.VoucherTypeCode                                                                      
     LEFT JOIN MstCurrency As DocumentCurrency ON DocumentCurrency.Code = ItemLedger.DocumentCurrencyCode                                                                      
     LEFT JOIN MstUser ON MstUser.Code = ItemLedger.UserCode                                       
     LEFT JOIN MstUOM As DetailUOM ON DetailUOM.Code = ItemLedger.DetailUOMCode                                                                      
     LEFT JOIN stmItem ON stmItem.Code = ItemLedger.ItemCode                                                                      
     LEFT JOIN MstUOM As ItemUOM ON ItemUOM.Code = stmItem.UOMCode                                                                      
     LEFT JOIN mstProductCategory ON mstProductCategory.Code = stmItem.ProductCategoryCode                                                                      
     LEFT JOIN mstTax ON mstTax.Code = ItemLedger.TaxCode                                                                                                                                                                          
     LEFT JOIN MstStockPoint ON MstStockPoint.Code = ItemLedger.StockPointCode     
     LEFT JOIN dbo.SplitCode('110,112,145,162,163,164,165,166,167,226,323,324,326,354,376') As OrgCode ON OrgCode.Code = ItemLedger.OrganizationCode                                            
     LEFT JOIN TranProductTagging ON TranProductTagging.Code = ItemLedger.ItemCode                                                                                                                  
      WHERE ((CASE WHEN 1 = 0                             
         THEN (CASE WHEN ISNULL((ItemLedger.Quantity),0) <> 0                              
           THEN 1                            
           ELSE 0                             
         END                                                                                                
           )                                                                                             
        ELSE 1                                                                                     
       END                                                                                                
      ) = 1) AND (ItemLedger.FilterDocumentDate <= '2018-10-25' AND ItemLedger.TransactionType = 0)                          
      AND ItemLedger.VoucherTypeCode not in (107,  --@AXS_VOUCHERTYPE_STOCKPOINTTRANSFER                                                          
                402, --@AXS_VOUCHERTYPE_STOCKIN                                                            
                403) --@AXS_VOUCHERTYPE_STOCKOUT                                                            
        AND StmItem.IsProductTag = 0 -- Tag Items                                                            
        AND ItemLedger.OrganizationKey Like ('' + 'AABA' + '%') AND                                 
      ((Len(ISNULL('10046406','')) > 0 AND ItemLedger.itemcode in (select * from dbo.SplitCode('10046406')))                                 
     OR Len(ISNULL('10046406','')) = 0 AND 1=1)
     group by ItemLedger.Code,MstOrganization.Name,stmVoucherType.Name,stmItem.Name,stmItem.IsProductTag,
     ItemLedger.InOut,ItemLedger.ItemCode,ItemLedger.Quantity

This Query is producing Output as Follows -

enter image description here

But, field Balance Quantity is fetching wrong,

My Required O/P for Balance Quantity Field -

BalanceQuantity
      12
      10

Any help would be appreciated.

Upvotes: 1

Views: 82

Answers (2)

Zhorov
Zhorov

Reputation: 29943

Explanation:

You may try with SUM(..) OVER (PARTITTION BY ...). I'm not able to reproduce and test this without actual data, but you may try with this:

WITH ResultValues (
    Code, 
    OrganizationName, 
    VoucherTypeName, 
    ItemName, 
    IsProductTag, 
    InOut, 
    ItemCode, 
    Quantity, 
    InwardQuantity, 
    OutwardQuantity
) AS (
    -- Your statement here
)
SELECT 
    *,  
    SUM(InwardQuantity + OutwardQuantity) OVER (PARTITION BY ItemCode ORDER BY ItemCode) AS BalanceQuantity
FROM ResultValues

Working example:

Below is working example that demonstrates this approach:

DECLARE @ItemTable TABLE (
    ItemID varchar(100), 
    ItemDate date, 
    ItemIn numeric(10, 2),
    ItemOut numeric(10, 2)
);
INSERT INTO @Itemtable VALUES
('001', '2018-01-10', 10.01, 0),
('001', '2018-02-10', 10.02, 0),
('001', '2018-03-10', 10.03, 0),
('001', '2018-04-10', 0, -10.04),
('001', '2018-05-10', 10.05, 0),
('002', '2018-01-10', 20.01, 0),
('002', '2018-02-10', 20.02, 0),
('002', '2018-03-10', 20.03, 0),
('002', '2018-04-10', 20.04, 0),
('002', '2018-05-10', 20.05, 0),
('003', '2018-01-10', 30.01, 0),
('003', '2018-02-10', 0, -30.02);

SELECT 
    ItemID, 
    ItemDate, 
    ItemIn, 
    ItemOut, 
    SUM(ItemIn + ItemOut) OVER (PARTITION BY ItemID ORDER BY ItemDate) AS ItemBalance
FROM @ItemTable

Output:

ItemID  ItemDate    ItemIn  ItemOut ItemBalance
001 2018-01-10  10.01   0.00    10.01
001 2018-02-10  10.02   0.00    20.03
001 2018-03-10  10.03   0.00    30.06
001 2018-04-10  0.00    -10.04  20.02
001 2018-05-10  10.05   0.00    30.07
002 2018-01-10  20.01   0.00    20.01
002 2018-02-10  20.02   0.00    40.03
002 2018-03-10  20.03   0.00    60.06
002 2018-04-10  20.04   0.00    80.10
002 2018-05-10  20.05   0.00    100.15
003 2018-01-10  30.01   0.00    30.01
003 2018-02-10  0.00    -30.02  -0.01

Update:

For SQL Server versions, that do not fully support SUM(...) OVER (...), try with this: 

SELECT 
    i.ItemID, 
    i.ItemDate, 
    i.ItemIn, 
    i.ItemOut, 
    (
    SELECT SUM(s.ItemIn + s.ItemOut) 
    FROM @ItemTable s 
    WHERE (i.ItemID = s.ItemId) AND (i.ItemDate >= s.ItemDate)
    ) AS ItemBalance
FROM @ItemTable i
ORDER BY i.ItemID, i.ItemDate

Upvotes: 1

Thom A
Thom A

Reputation: 95561

You would probably be better putting the entire query in a CTE and then using LAG. Note that I've put comments around the PARTITION BY as there isn't enough information for me to infer if it is needed, or what it should be if it is:

WITH CTE AS(
    SELECT ItemLedger.Code AS Code,
           ISNULL(MstOrganization.Name, '') AS OrganizationName,
           ISNULL(stmVoucherType.Name, '') AS VoucherTypeName,
           ISNULL(stmItem.Name, '') AS ItemName,
           ISNULL(stmItem.IsProductTag, 0) AS IsProductTag,
           ItemLedger.InOut AS [InOut],
           ItemLedger.ItemCode,
           ItemLedger.Quantity AS Quantity,
           CASE WHEN ItemLedger.Inout = 0 THEN ItemLedger.Quantity ELSE 0 END AS InwardQuantity,
           CASE WHEN ItemLedger.Inout = 1 THEN ItemLedger.Quantity * (-1) ELSE 0 END AS OutwardQuantity,
           SUM((CASE WHEN ItemLedger.Inout = 0 THEN ItemLedger.Quantity ELSE 0 END) - (CASE WHEN ItemLedger.Inout = 1 THEN ItemLedger.Quantity ELSE 0 END)) AS BalanceQuantity
    FROM VIEW_ITEM_STOCK_LEDGER ItemLedger
         LEFT JOIN MstGroup ON MstGroup.Code = ItemLedger.GroupCode
         LEFT JOIN MstOrganization ON MstOrganization.Code = ItemLedger.OrganizationCode
         LEFT JOIN MstCurrency BaseCurrency ON BaseCurrency.Code = MstOrganization.CurrencyCode
         LEFT JOIN stmVoucherType ON stmVoucherType.Code = ItemLedger.VoucherTypeCode
         LEFT JOIN MstCurrency DocumentCurrency ON DocumentCurrency.Code = ItemLedger.DocumentCurrencyCode
         LEFT JOIN MstUser ON MstUser.Code = ItemLedger.UserCode
         LEFT JOIN MstUOM DetailUOM ON DetailUOM.Code = ItemLedger.DetailUOMCode
         LEFT JOIN stmItem ON stmItem.Code = ItemLedger.ItemCode
         LEFT JOIN MstUOM ItemUOM ON ItemUOM.Code = stmItem.UOMCode
         LEFT JOIN mstProductCategory ON mstProductCategory.Code = stmItem.ProductCategoryCode
         LEFT JOIN mstTax ON mstTax.Code = ItemLedger.TaxCode
         LEFT JOIN MstStockPoint ON MstStockPoint.Code = ItemLedger.StockPointCode
         LEFT JOIN dbo.SplitCode('110,112,145,162,163,164,165,166,167,226,323,324,326,354,376') OrgCode ON OrgCode.Code = ItemLedger.OrganizationCode
         LEFT JOIN TranProductTagging ON TranProductTagging.Code = ItemLedger.ItemCode
    WHERE ((CASE
                 WHEN 1 = 0 THEN (CASE WHEN ISNULL((ItemLedger.Quantity), 0) <> 0 THEN 1 ELSE 0 END)
                 ELSE 1
            END) = 1)
      AND (ItemLedger.FilterDocumentDate <= '2018-10-25'
       AND ItemLedger.TransactionType = 0)
      AND ItemLedger.VoucherTypeCode NOT IN (107, --@AXS_VOUCHERTYPE_STOCKPOINTTRANSFER                                                          
                                             402, --@AXS_VOUCHERTYPE_STOCKIN                                                            
                                             403) --@AXS_VOUCHERTYPE_STOCKOUT                                                            
      AND stmItem.IsProductTag = 0 -- Tag Items                                                            
      AND ItemLedger.OrganizationKey LIKE ('' + 'AABA' + '%')
      AND ((LEN(ISNULL('10046406', '')) > 0
        AND ItemLedger.itemcode IN (SELECT * FROM dbo.SplitCode('10046406') ))
        OR LEN(ISNULL('10046406', '')) = 0
       AND 1 = 1)
    GROUP BY ItemLedger.Code,
             MstOrganization.Name,
             stmVoucherType.Name,
             stmItem.Name,
             stmItem.IsProductTag,
             ItemLedger.InOut,
             ItemLedger.ItemCode,
             ItemLedger.Quantity)
SELECT CTE.OrganizationName,
       CTE.VoucherTypeName,
       CTE.ItemName,
       CTE.IsProductTag,
       CTE.InwardQuantity,
       CTE.OutwardQuantity,
       CTE.BalanceQuantity + LAG(CTE.BalanceQuantity,1,0) OVER (/*PARTITION BY ??? */ ORDER BY [Code]) AS BalanceQuantity
FROM CTE;

Note: The OP has only given use a 2 row sample, I wonder is this is actually meant to be a running total, if there are 3 or more rows. Impossible to know with the limited information as well.

Upvotes: 0

Related Questions