none
Is there way i can track the total of all the other Items in the order? RRS feed

  • Question

  • Hello,

    Is there way I can track the total of all the other Items in the one order or Order? 

    EXTPRICE Is the column
    Price customer paid for the items (not including shipping) on the invoice with the DISP-LTPWR item. I want to track the total of all the OTHER items on the order. The DISP-LTPWR is only being used to flag the order.

    Please let me know if more information needed? 

    Thursday, December 5, 2019 6:39 PM

All replies

  • Please post your question with a table DDL,sample data and expected result. Thanks.
    Thursday, December 5, 2019 6:54 PM
    Moderator
  • Where is the DDL for this table, whose name we don't even know?  

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, December 5, 2019 7:09 PM
  • Here it is what I have wrote.

    Requirement:- EXTPRICE Price customer paid for the items (not including shipping) on the invoice with the DISP-LTPWR item. I want to track the total of all the OTHER items on the order. The DISP-LTPWR is only being used to flag the order.

    Total Light and Power Sales Since Display Purchase Calculation: total EXTPRICE for this customer for all Light & Power sales since INVDTE of the DISP-LTPWR item

    Create table #TotalLightAndPower

    ( CUSTNO char(6),

      TotalLightAndPower decimal(21,6)

    )

    Insert #TotalLightAndPower

    --Select C.CUSTNO,

    -- ISNULL(RTA.TotalLightAndPower, 0) 'Total Light & Power  Since'

    --From #CABSales C

    --Left Join (Select A.CUSTNO, SUM(A.INVAMT) as TotalLightAndPower FROM dbo.vwARDSLS A

    --Inner Join #CABSales C ON C.CUSTNO = A.CUSTNO

    --WHERE A.IARCLSS LIKE 'LT-ASC%'

    --AND A.INVDTE > C.INVDTE

    --AND A.ITEM != ' DISP-LTPWR''

    --GROUP BY A.CUSTNO)LT-ASC ON C.CUSTNO = LT-ASC.CUSTNO

    --Order by C.CUSTNO

    Select C.CUSTNO,

    ISNULL(SUM(A.INVAMT), 0) as 'Total Light & Power  Since'

    FROM #CABSALES C

    Left Join dbo.vwARDSLS A ON C.CUSTNO = A.CUSTNO

    WHERE A.IARCLSS LIKE 'LT-ASC%'

    AND A.ITEM != 'DISP-LTPWR'

    AND A.NATION = 0

    AND A.INVDTE > C.INVDTE

    GROUP BY C.CUSTNO

    Union all

    Select C.CUSTNO,

    ISNULL(SUM(A.INVAMT), 0) as 'Total Light & Power  Since'

    FROM #CABSALES C

    Left Join dbo.vwARDSLS A ON C.CUSTNO = A.CUSTNO AND C.CSHIPNO = A.CSHIPNO

    WHERE A.IARCLSS LIKE 'LT-ASC%'

    AND A.ITEM != 'DISP-LTPWR'

    AND A.NATION = 1

    AND A.INVDTE > C.INVDTE

    GROUP BY C.CUSTNO

    Order by C.CUSTNO

    Thursday, December 5, 2019 7:16 PM
  • Create table #TotalLightAndPower

    ( CUSTNO char(6), TotalLightAndPower decimal(21,6))

    Insert #TotalLightAndPower 
    (CUSTNO,TotalLightAndPower) Values(/*.You need to provide sample data..*/)

    And your expected result from your sample data.
    It will help to understand your requirement. 
    Thanks.

    The code you posted with insert... select does not work for us.
    We don't have your source table #CABSALES.
    Thursday, December 5, 2019 7:22 PM
    Moderator
  • Hi JinalContractor,

    Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …) along with your expected result? So that we’ll get a right direction and make some test.

     

    When you post your issue, please refer POSTING TIPS - Code, Images, Hyperlinks, Details.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 6, 2019 3:06 AM
  • Here I'm attaching the screen shot of how is the data should be look like it. 

    Not able to pull the correct data on fields called TotalLightAndPower  and TotalLPSales.

    When I pull the data for this customer PRO589 using  select top 100 * from  dbo.vwARDSLS where custno='PRO589' . 

    This is customer has multiple invoice number. 

    INVNO
    5655752
    5655752
    5655752
    5655752
    5655752
     
    ORNUM
    35186334
    35186334
    35186334
    35186334
    35186334
     



    Wednesday, December 11, 2019 4:14 PM
  • Hi , 

    Sorry for my poor understanding . 

    Could you please explain more? We need your table structure (CREATE TABLE …) and some sample data(INSERT INTO …) along with your expected result.  So that we’ll get a right direction and make some test. According to the information you provided above, I could not understand your actual needs.

    Thank you in advance.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 12, 2019 7:27 AM
  • Sorry I think I'm not much clear about this. But let me post whole scenario here. 

    I've created Stored procedure to calculate Rebate for the customer.  My calculation is coming wrong so not sure how's the data will look like. Sorry for this long description. 

    Data is not coming correct for table TotalLightAndPower,  TotalLPSales and Rebate. 

    If you can guide me.. Thank you

    Here is the code:-


    CREATE TABLE #CABSales
    (
    CUSTNO char(6),
    CSHIPNO char(6),
    INVNO char(10),
    INVDTE date,
    INVAMT decimal(21, 6),
    TotalLightAndPower decimal(21,6),
    TotalLPSales decimal(21,6),
    RebateApplied decimal(21,6)

    )
    Insert into #CABSales
    Select V.CUSTNO, 
    CASE WHEN C.NATION = 1 AND A.EMAIL LIKE '%@%' AND A.MISC6 !='' THEN V.CSHIPNO
    ELSE ''
    END AS CSHIPNO,
    V.INVNO, 
    V.INVDTE, 
    V.INVAMT,
    0,
    0,
    0
    From dbo.vwARDSLS V
    Inner Join tblARCUST C ON V.CUSTNO = C.CUSTNO
    Inner Join tblARCADR A ON A.CSHIPNO = V.CSHIPNO
    --Inner Join tblARMAST T ON V.INVAMT = T.INVAMT
    Where ITEM = 'DISP-LTPWR'
    AND C.GRP !='HRE'
    And C.GRP !='TKE'



    Create table #TotalLPSales
    ( CUSTNO char(6),
      TotalLPSales decimal(21,6)
    )

    Insert into #TotalLPSales
    Select C.CUSTNO,
    ISNULL(SUM(A.INVAMT), 0) as 'Total Light & Power '
    FROM #CABSALES C
    Left Join dbo.vwARDSLS A ON C.CUSTNO = A.CUSTNO
    WHERE A.IARCLSS LIKE 'LT-ASC%'
    --AND A.ITEM != 'DISP-LTPWR'
    AND A.NATION = 0
    GROUP BY C.CUSTNO
    Union
    Select C.CUSTNO,
    ISNULL(SUM(A.INVAMT), 0) as 'Total Light & Power '
    FROM #CABSALES C
    Left Join dbo.vwARDSLS A ON C.CUSTNO = A.CUSTNO AND C.CSHIPNO = A.CSHIPNO
    WHERE A.IARCLSS LIKE 'LT-ASC%'
    AND A.ITEM != 'DISP-LTPWR'
    AND A.NATION = 1
    GROUP BY C.CUSTNO
    Order by C.CUSTNO

    Create table #TotalLightAndPower 
    ( CUSTNO char(6),
      TotalLightAndPower decimal(21,6)
    )
    Insert #TotalLightAndPower
    --Select C.CUSTNO, 
    -- ISNULL(RTA.TotalLightAndPower, 0) 'Total Light & Power  Since'
    --From #CABSales C
    --Left Join (Select A.CUSTNO, SUM(A.INVAMT) as TotalLightAndPower FROM dbo.vwARDSLS A
    --Inner Join #CABSales C ON C.CUSTNO = A.CUSTNO 
    --WHERE A.IARCLSS LIKE 'RTA%'
    --AND A.INVDTE > C.INVDTE
    --AND A.ITEM != 'CAB-SAM-DOORKIT'
    --GROUP BY A.CUSTNO)RTA ON C.CUSTNO = RTA.CUSTNO
    --Order by C.CUSTNO
    Select C.CUSTNO,
    ISNULL(SUM(A.INVAMT), 0) as 'Total Light & Power  Since'
    FROM #CABSALES C
    Left Join dbo.vwARDSLS A ON C.CUSTNO = A.CUSTNO
    WHERE A.IARCLSS LIKE 'LT-ASC%'
    AND A.ITEM != 'DISP-LTPWR'
    AND A.NATION = 0
    AND A.INVDTE > C.INVDTE
    GROUP BY C.CUSTNO
    Union
    Select C.CUSTNO,
    ISNULL(SUM(A.INVAMT), 0) as 'Total Light & Power  Since'
    FROM #CABSALES C
    Left Join dbo.vwARDSLS A ON C.CUSTNO = A.CUSTNO AND C.CSHIPNO = A.CSHIPNO
    WHERE A.IARCLSS LIKE 'LT-ASC%'
    AND A.ITEM != 'DISP-LTPWR'
    AND A.NATION = 1
    AND A.INVDTE > C.INVDTE
    GROUP BY C.CUSTNO
    Order by C.CUSTNO


    Create table #Rebate
    ( CUSTNO char(6),
      INVNO char(10),
      RebateApplied decimal(21,6)
    )
    Insert into #Rebate 
    Select DISTINCT C.CUSTNO,
    A.INVNO,
    A.EXTPRICE
    FROM #CABSALES C
    Left Join dbo.vwARDSLS A ON C.CUSTNO = A.CUSTNO
    WHERE A.ITEM = 'REBATES-LTPWR' 
    AND datediff(day,C.INVDTE,getdate()) < 365
    AND A.NATION = 0
    GROUP BY C.CUSTNO, A.INVNO, A.EXTPRICE
    Union
    Select C.CUSTNO,
    A.INVNO,
    A.EXTPRICE 
    FROM #CABSALES C
    Left Join dbo.vwARDSLS A ON C.CUSTNO = A.CUSTNO AND C.CSHIPNO = A.CSHIPNO
    WHERE A.ITEM = 'REBATES-LTPWR'
    AND datediff(day,C.INVDTE,getdate()) < 365
    AND A.NATION = 1
    GROUP BY C.CUSTNO, A.INVNO, A.EXTPRICE
    Order by C.CUSTNO

    Update #CABSales
    SET TotalLightAndPower = R.TotalLightAndPower
    From #TotalLightAndPower R
    Inner Join #CABSales C ON C.CUSTNO = R.CUSTNO
    WHere C.CUSTNO = R.CUSTNO

    Update #CABSales
    SET TotalLPSales = R.TotalLPSales
    From #TotalLPSales R
    Inner Join #CABSales C ON C.CUSTNO = R.CUSTNO
    Where C.CUSTNO = R.CUSTNO

    Update #CABSales
    SET RebateApplied = R.RebateApplied
    From #Rebate R
    Inner join #CABSales C ON C.CUSTNO = R.CUSTNO
    Where C.CUSTNO = R.CUSTNO

    TRUNCATE table LightAndPowerRebateReport
    Insert into LightAndPowerRebateReport
    Select C.CUSTNO,
    CASE WHEN R.NATION = 1 AND A.EMAIL LIKE '%@%' AND A.MISC6 !='' THEN C.CSHIPNO
    ELSE ''
    END AS CSHIPNO,
    C.INVNO,
    C.INVDTE,
    C.INVAMT,
    ISNULL(C.TotalLightAndPower,0),
    ISNULL(C.TotalLPSales, 0),
    ISNULL(C.RebateApplied, 0)
    From #CABSales C
    Inner Join tblARCUST R ON C.CUSTNO = R.CUSTNO
    Inner Join tblARCADR A ON A.CSHIPNO = C.CSHIPNO
    --Inner Join tblARMAST T ON C.INVAMT = T.INVAMT

    Requirement:=-

    Pulled from vwARDSLS (sales history) for all customers (ignore customers who are have a tblARCUST.GRP value of HRE or TKE) who have purchased item number DISP-LTPWR, report should list the following values:

    CUSTNO
    Customer Number for Customer

    CSHIPNO
    Empty if tblARCUST.NATION=0. Pull if the CSHIPNO is set as a National Account Location (tblARCADR.EMAIL LIKE %@% tblARCADR.MISC6 !='')

    INVNO
    Invoice Number of the invoice for the DISP-LTPWR item

    INVDTE
    Invoice Date of the invoice for the DISP-LTPWR item

    EXTPRICE
    Price customer paid for the items (not including shipping) on the invoice with the DISP-LTPWR item. I want to track the total of all the OTHER items on the order. The DISP-LTPWR is only being used to flag the order.

    Total Light and Power Sales Since Display Purchase
    Calcuation: total EXTPRICE for this customer for all Light & Power sales since INVDTE of the DISP-LTPWR item

    Total Light & Power Sales
    Calculation: total EXTPRICE for this customer for all Light & Power sales independent of date of Kit purchase

    Rebate Applied Already
    Calculation: total EXTPRICE for this customer for item REBATES-LTPWR, if the customer has not yet received the REBATES-LTPWR item, and it has been 1 calendar year since the Display DISP-LTPWR item purchase, insert "Not Eligible"



    Thursday, December 12, 2019 3:24 PM
  • Hi JinalContractor, 

    Thank you for detailed reply. 

    But I am sorry that you reply make me in a mess. I wanted to help you very much, but there was nothing I could do. 

    Maybe could you please simplify your code?

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 13, 2019 7:47 AM