My query is a bit long so I'm going to try to keep things short and only talk about relevant information. I have a column called Total_Returned_Value. I want to sum all the individual return values to get the final total return value of all returns. Because of the structure of the table, that column has duplicates so I can't just sum that column. I want to sum that column based on another "id" type column called Customer_Purchase_Order_Number where it only sums for one of each Purchase Order Number .
This is how I'm doing it.
,rn = ROW_NUMBER() OVER (PARTITION BY Customer_Purchase_Order_Number ORDER BY Customer_Purchase_Order_Number)
,SUM(CASE WHEN rn = 1 then rh.Total_Returned_Value ELSE 0 END) OVER (PARTITION BY rh.Customer_Number) as Total_Returned_Values
So it organizes it by Customer_Purchase_Order_Number, assigns each set of equal numbers a row number, and only sums the ones where row number = 1.
Seems pretty straight forward. However, I get the error "Invalid column name 'rn'." If I try to put the row number select statement directly into the case statement, it tells me that I cannot have an aggregate within an aggregate. Does anyone know what to do?
This is my full code.
DECLARE @LocalCompanyCode VARCHAR(5)
SET @LocalCompanyCode = '03'
DECLARE @LocalDivisionCode VARCHAR(5)
SET @LocalDivisionCode = '001'
DECLARE @LocalFROMDate DATETIME
SET @LocalFROMDate = '2/04/2019'
DECLARE @LocalToDate DATETIME
SET @LocalToDate = '2/08/2019'
SET NOCOUNT ON;
WITH rh AS
(SELECT rh.Company_Code
,rh.Division_Code
,rh.Date_Created
,rh.Customer_Number
,rh.Customer_Purchase_Order_Number
,rh.Return_Number
,(rh.Total_Value-rh.Freight_Charges) as Returned_Value
,rh.Freight_Charges
,rh.Remarks
,SUM(rh.Total_Value-rh.Freight_Charges)
OVER (PARTITION BY rh.Customer_Purchase_Order_Number) as Total_Returned_Value
FROM [JMNYC-AMTDB].[AMTPLUS].[dbo].Returns_Header rh (nolock)
)
SELECT rh.Company_Code
,rh.Division_Code
,rh.Date_Created
,rh.Customer_Number
,rh.Customer_Purchase_Order_Number
,rh.Return_Number
,rd.Item_Number
,Sum(rd.Quantity_Returned) OVER (PARTITION BY rd.Item_Number) as Total_Items_Returned_Number
,rd.Color_Code
,Sum(rd.Quantity_Returned) OVER (PARTITION BY rd.Item_Number, rd.Color_Code) as Total_Items_Returned_NumberColor
,rd.Quantity_Returned
,rh.Returned_Value
,rh.Freight_Charges
,rh.Remarks
,rh.Total_Returned_Value
/*,rn = ROW_NUMBER() OVER (PARTITION BY Customer_Purchase_Order_Number ORDER BY Customer_Purchase_Order_Number)
,SUM(CASE WHEN rn = 1 then rh.Total_Returned_Value ELSE 0 END) OVER (PARTITION BY rh.Customer_Number) as Total_Returned_Values*/
,DENSE_RANK() OVER (PARTITION BY rh.Customer_Number ORDER BY rh.Customer_Purchase_Order_Number ASC)
+ DENSE_RANK() OVER (PARTITION BY rh.Customer_Number ORDER BY rh.Customer_Purchase_Order_Number DESC)
- 1 AS Total_Returns_Count
,SUM(CASE WHEN rh.Remarks LIKE '%lost%' then 1 ELSE 0 END) OVER (PARTITION BY rh.Customer_Number) as "Lost_Packages"
,SUM (Quantity_Returned) OVER () as "Total_ReturnedItems_Count"
FROM rh
LEFT JOIN
[JMNYC-AMTDB].[AMTPLUS].[dbo].Returns_Detail rd (nolock)
on rd.Return_Number = rh.Return_number
WHERE
(rh.Company_Code = @LocalCompanyCode OR @LocalCompanyCode IS NULL) AND
(rh.Division_Code = @LocalDivisionCode OR @LocalDivisionCode IS NULL) AND
rh.Customer_Number = 'ecom2x' AND
rh.Customer_Purchase_Order_Number not like '%bulk%' AND
rh.Date_Created BETWEEN @LocalFROMDate AND DATEADD(dayofyear, 1, @LocalToDate)
--AND rh.remarks like '%magic%'
ORDER BY Total_Items_Returned_Number desc, Total_Items_Returned_NumberColor desc
