Census Commodity Report Template for Distribution
Census Commodity Report Template for Distribution
A few things to note about this query:
– it was developed to provide commodity reporting to the U.S Census bureau
– it combined data from the GP company DB and our WMS DB
– certain data points are hard-coded to the report as all of the data is either the same or does not pertain to our industry
– for the dollar amount reported SUBTOTAL is used as to not include freight charges
Variables in the query:
– X.000000 is used in a case statement for shipping weight when the shipping weight equals 0.00 pounds. The X.000000 is replaced with our average shipment weight.
– XXX needs to be changed to the correct commodity code for your industry. If you have multiple commodity codes you will have to replace the XXX with a CASE statement to dictate the codes.
– XXX for commodity description will also need to be addressed for your industry. If you have multiple commodity descriptions you will have to replace the XXX with a CASE statement to dictate the descriptions to match your commodity codes.
– XXX for Temperature Control and Hazardous Materials will need to be replaced with CASE statement based on the SKU’s shipped. Ours are hard-coded because of the type of SKU’s we ship.
– XXX for Mode of Transport needs to be replaced with any local delivery methods your company uses.
– the XXXX-XX-XX for the GLPOSTDT date range needs to be replaced with the dates requested by the Census bureau.
– XXX in the NOT IN portion of the query should be replaced with any local counter business. Or you can comment it out if you do not do counter business.
– XXX in LOCNCODE needs to be set to the SITE ID of the location you are reporting on.
select distinct(a.sopnumbe) [Invoice ID Number],
case
when month(a.glpostdt) = ‘1’ then ‘January’
when month(a.glpostdt) = ‘2’ then ‘February’
when month(a.glpostdt) = ‘3’ then ‘March’
when month(a.glpostdt) = ‘4’ then ‘April’
when month(a.glpostdt) = ‘5’ then ‘May’
when month(a.glpostdt) = ‘6’ then ‘June’
when month(a.glpostdt) = ‘7’ then ‘July’
when month(a.glpostdt) = ‘8’ then ‘August’
when month(a.glpostdt) = ‘9’ then ‘September’
when month(a.glpostdt) = ’10’ then ‘October’
when month(a.glpostdt) = ’11’ then ‘November’
when month(a.glpostdt) = ’12’ then ‘December’
end [Shipment Month],
day(a.glpostdt) [Shipment Day], a.SUBTOTAL [Shipment Value],
case
when b.ship_wght = ‘0.0000000’ then ‘X.000000’
when isnull(b.ship_wght,0) = ‘0.0000000’ then ‘X.000000’
else b.ship_wght
end [Net Shipment Weight], ‘XXX’ [SGTG Commodity Code],
‘XXX’ [Commodity Description], ‘XXX’ [Temperature Control Y/N],
‘XXX’ [Hazardous Material], a.city [US City], a.state [US State], a.ZIPCODE [Zip Code],
case
when SHIPMTHD = ‘XXX’ then ‘2’
else ‘1’
end [Mode of Transport],
case
when a.ccode like ‘US’ Then ‘N’
when a.ccode like ‘USA’ then ‘N’
when a.ccode like ‘ ‘ then ‘N’
else ‘Y’
end [Export Y/N],
case
when a.ccode like ‘US’ Then ‘N/A’
when a.ccode like ‘USA’ then ‘N/A’
when a.ccode like ‘ ‘ then ‘N/A’
else a.city
end [Foreign Destination-City],
case
when a.ccode like ‘US’ Then ‘N/A’
when a.ccode like ‘USA’ then ‘N/A’
when a.ccode like ‘ ‘ then ‘N/A’
else a.country
end [Foreign Destination-Country],
case
when a.ccode like ‘US’ Then ‘N/A’
when a.ccode like ‘USA’ then ‘N/A’
when a.ccode like ‘ ‘ then ‘N/A’
else ‘1’
end [Export Mode]
from sop30200 a
left outer join [XXX-db].[XXX].[dbo].[shiphist] b
on a.ORIGNUMB = b.order_num
where a.GLPOSTDT between ‘XXXX-XX-XX’ and ‘XXXX-XX-XX’
and a.soptype = ‘3’
and a.VOIDSTTS = ‘0’
and a.SHIPMTHD not in (‘XXX’, ‘XXX’)
and a.locncode = ‘XXX’