Created
May 13, 2025 18:54
-
-
Save HarryMcCarney/5731e2c9f505d54a3677ea46c7a2f769 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| create table #locationInfo | |
| ( | |
| LocationId nvarchar(50) not null primary key, | |
| LocationType nvarchar(50) not null, | |
| Longitude float not null, | |
| Latitude float not null | |
| ) | |
| insert #locationInfo | |
| select [Global] as LocationID, | |
| case | |
| when upper([Type]) = 'P' then 'Plant' | |
| when upper([Type]) = 'M' then 'Manufacturer' | |
| when upper([Type]) = 'R' then 'Retailer' | |
| when upper([Type]) = 'T' then 'TPM' | |
| when upper([Type]) = 'O' then 'Other' | |
| when upper([Type]) = 'H' then 'Heat Treatment Facility' | |
| else 'Unknown' | |
| end as LocationType, | |
| Lon, | |
| Lat | |
| from | |
| ( | |
| select *, | |
| ROW_NUMBER() OVER (PARTITION BY loc.Global ORDER BY loc.Type DESC) AS rn | |
| from [Import].[CY24_Origin_UKI_data MD] loc | |
| where Lat is not null and lon is not null | |
| )x | |
| where rn = 1 | |
| -- Quantity to Locations missing Lat/Long | |
| select sum(edge.Qty) | |
| from [Import].[CY24_Origin_UKI_data_Export_v2] edge | |
| where edge.DGLID in | |
| ( | |
| select [global] | |
| from [Import].[CY24_Origin_UKI_data MD] d | |
| left join #locationInfo t | |
| on d.Global = t.LocationId | |
| where t.LocationId is null | |
| ) | |
| -- Zero cost counts | |
| select sum(edge.Qty) as TotalQuantity, | |
| sum(case when edge.ABCTransType in ('CPU', 'Return') then edge.Qty else 0 end) as zeroCostQuantity, | |
| sum(case when edge.ABCTransType not in ('CPU', 'Return') then edge.Qty else 0 end) as nonZeroCostQuantity | |
| from [Import].[CY24_Origin_UKI_data_Export_v2] edge | |
| join #locationInfo oLoc | |
| on oLoc.LocationID = edge.OGLID | |
| join #locationInfo dLoc | |
| on dLoc.LocationID = edge.DGLID | |
| where edge.Batch not in ('NA UNDERLD', 'RU PC') |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment