locked
Summing UP Individual Calculation RRS feed

  • Question

  • Hi Guys,

    I have 2 Tables Namely and Item Master & Item Ledger. The relationship is Item.No = Item Master.Item No.

    And for an Item There can be many records in Item Ledger. 

    In Item Ledger, there are columns called Item No, Transaction Date, Cost Amount(USD), Cost Amount(SGD), Sales Amount(USD)

    What i want is for an Item my query goes to Item Ledger based on relationship and transaction date filters and sum up the value based on the calculation as Sales Amount(USD) * Cost Amount(SGD) / Cost Amount(USD).



    ALI

    Tuesday, March 3, 2015 1:58 PM

Answers

  • Hi Visakh,

    Yes this works but the query isquite complex. Can't I simply use

    Select Item.No,Item.Description,
      (Select SUM(IL.Sales Amount(USD) * IL.Cost Amount(SGD) *1.0/NULLIF(IL.COST Amount(USD),0)) From Item Ledger Table As IL Where Filters...)
    
    From Item Table As Item

    And Thanks for at least giving the idea how this can be done.

    Thank you.


    ALI

    If a nested subquery is preferable, you may reference the below.

    DECLARE @Item_Master TABLE(Item_No CHAR(1),Description VARCHAR(99))
    INSERT INTO @Item_Master VALUES('A','This is A item')
    INSERT INTO @Item_Master VALUES('B','This is B item')
    
    DECLARE @Item_Ledger TABLE(Item_No CHAR(1),Transaction_Date DATE, [Cost_Amount(USD)] DECIMAL(10,6) ,
    [Cost_Amount(SGD)] DECIMAL(10,6), [Sales_Amount(USD)] DECIMAL(10,6) );
     
    INSERT INTO @Item_Ledger VALUES('A','12-mar-2014',100,126.8457,200);
    INSERT INTO @Item_Ledger VALUES('A','19-mar-2014',102,130.509,201);
    INSERT INTO @Item_Ledger VALUES('A','14-apr-2014',105,131.922,210);
    INSERT INTO @Item_Ledger VALUES('A','15-jun-2014',99,123.741486,200);
    INSERT INTO @Item_Ledger VALUES('A','16-jun-2014',99.02,123.745294,200);
    INSERT INTO @Item_Ledger VALUES('A','17-jun-2014',100,124.97,201);
    
    
    
    DECLARE @StartDate DATE,@EndDate DATE
    SET @StartDate='20140101'
    SET @EndDate='20140401'
    Select Item.ITEM_No,Item.Description,
     (Select SUM(IL.[Sales_Amount(USD)] * IL.[Cost_Amount(SGD)] *1.0/NULLIF(IL.[COST_Amount(USD)],0)) From @Item_Ledger As IL
      WHERE Item_No = item.Item_No
    AND Transaction_Date >= @StartDate
    AND Transaction_Date < DATEADD(dd,1,@EndDate))
    From @Item_Master  As item


    If you have any question, feel free to let me know.


    Eric Zhang
    TechNet Community Support


    • Edited by Eric__Zhang Thursday, March 5, 2015 9:56 AM
    • Marked as answer by alisag Thursday, March 5, 2015 10:34 AM
    Thursday, March 5, 2015 9:54 AM

All replies

  • Sorry cannot test it right now..

    SELECT ItemNo,

    (SELECT SUM(calc) FROM tbl WHERE dt >='20140301'  AND dt <='20140301') sgd,

    (SELECT SUM(calc) FROM tbl WHERE dt >='20140401'  AND dt <='2014071') sgd1

    FROM tbl 

    GROUP BY ItemNo


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, March 3, 2015 2:17 PM
  • SELECT m.itemNo,
    COALESCE(SalesAmount,0) AS SalesAmount
    FROM ItemMaster m
    OUTER APPLY (SELECT SUM(([Sales Amount(USD)] * [Cost Amount (SGD)]) * 1.0/NULLIF([Cost Amount (USD)],0)) AS SalesAmount
    FROM ItemLedger 
    WHERE ItemNo = m.ItemNo
    AND TransactionDate >= @StartDate
    AND TransactionDate < DATEADD(dd,1,@EndDate)
    )l
    Where you pass daterange through @StartDate and @EndDate parameters ie 20140301 and 20141231 for Mar 2014 etc

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, March 3, 2015 2:47 PM
  • Hi Uri,

    The Calc is not a column in the Item Ledger Table. I showed that just to clarify that how the value is calculated.


    ALI

    Wednesday, March 4, 2015 1:49 AM
  • Hi Visakh,

    Yes this works but the query isquite complex. Can't I simply use

    Select Item.No,Item.Description,
      (Select SUM(IL.Sales Amount(USD) * IL.Cost Amount(SGD) *1.0/NULLIF(IL.COST Amount(USD),0)) From Item Ledger Table As IL Where Filters...)
    
    From Item Table As Item

    And Thanks for at least giving the idea how this can be done.

    Thank you.


    ALI

    Wednesday, March 4, 2015 1:55 AM
  • Hi Visakh,

    Yes this works but the query isquite complex. Can't I simply use

    Select Item.No,Item.Description,
      (Select SUM(IL.Sales Amount(USD) * IL.Cost Amount(SGD) *1.0/NULLIF(IL.COST Amount(USD),0)) From Item Ledger Table As IL Where Filters...)
    
    From Item Table As Item

    And Thanks for at least giving the idea how this can be done.

    Thank you.


    ALI

    If a nested subquery is preferable, you may reference the below.

    DECLARE @Item_Master TABLE(Item_No CHAR(1),Description VARCHAR(99))
    INSERT INTO @Item_Master VALUES('A','This is A item')
    INSERT INTO @Item_Master VALUES('B','This is B item')
    
    DECLARE @Item_Ledger TABLE(Item_No CHAR(1),Transaction_Date DATE, [Cost_Amount(USD)] DECIMAL(10,6) ,
    [Cost_Amount(SGD)] DECIMAL(10,6), [Sales_Amount(USD)] DECIMAL(10,6) );
     
    INSERT INTO @Item_Ledger VALUES('A','12-mar-2014',100,126.8457,200);
    INSERT INTO @Item_Ledger VALUES('A','19-mar-2014',102,130.509,201);
    INSERT INTO @Item_Ledger VALUES('A','14-apr-2014',105,131.922,210);
    INSERT INTO @Item_Ledger VALUES('A','15-jun-2014',99,123.741486,200);
    INSERT INTO @Item_Ledger VALUES('A','16-jun-2014',99.02,123.745294,200);
    INSERT INTO @Item_Ledger VALUES('A','17-jun-2014',100,124.97,201);
    
    
    
    DECLARE @StartDate DATE,@EndDate DATE
    SET @StartDate='20140101'
    SET @EndDate='20140401'
    Select Item.ITEM_No,Item.Description,
     (Select SUM(IL.[Sales_Amount(USD)] * IL.[Cost_Amount(SGD)] *1.0/NULLIF(IL.[COST_Amount(USD)],0)) From @Item_Ledger As IL
      WHERE Item_No = item.Item_No
    AND Transaction_Date >= @StartDate
    AND Transaction_Date < DATEADD(dd,1,@EndDate))
    From @Item_Master  As item


    If you have any question, feel free to let me know.


    Eric Zhang
    TechNet Community Support


    • Edited by Eric__Zhang Thursday, March 5, 2015 9:56 AM
    • Marked as answer by alisag Thursday, March 5, 2015 10:34 AM
    Thursday, March 5, 2015 9:54 AM