# Is there way i can track the total of all the other Items in the order?

• ### 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.

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
• 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..*/)

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

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.

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

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,

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.