Hello everyone,
I have such a situation. I have Bill of Lading that has multiple Pallet Tags. I need to count all distinct Pallet Tags per Bill of Lading.
Right now I am getting a number for example '771' which is wrong.
It counts all the rows that exist for the Bill of Lading and not distinct Pallet Tags for this Bill of Lading.
I basically need to get in the column 'Count of Pallet Tags per Bill of Lading' number 20 because that is how many distinct pallets are associated with this Bill of Lading.
SELECT [PALLET_TAG#]
,[BILL_OF_LADING#]
, COUNT ([PALLET_TAG#]) OVER (PARTITION BY [BILL_OF_LADING#]) AS 'Count of Pallet Tags per Bill of Lading'
,[UCC128_BARCODE]
FROM table
where BILL_OF_LADING#='6461470'
COUNT()
with an OVER()
clause does not support the DISTINCT
keyword, so you'll have to do this in two steps.
WITH New_Pallet_Tags AS
(
SELECT [PALLET_TAG#]
,[BILL_OF_LADING#]
, CASE WHEN [PALLET_TAG#] = LAG([PALLET_TAG#] OVER (PARTITION BY [BILL_OF_LADING#] ORDER BY [PALLET_TAG#, [UCC128_BARCODE]) THEN 0 ELSE 1 END AS Pallet_Tag_Change
,[UCC128_BARCODE]
FROM table
where BILL_OF_LADING#='6461470'
)
SELECT [PALLET_TAG#]
, [BILL_OF_LADING#]
, SUM(Pallet_Tag_Change) OVER(PARTITION BY [BILL_OF_LADING#]) AS Pallet_Tag_Count
, [UCC128_BARCODE]
FROM New_Pallet_Tags
Drew
NOTE: This code is completely untested due to lack of sample data.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 29, 2023 at 12:37 am
Thank you for your help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply