Some Old SQL Snippets

--Visibar_Test

SELECT STK_ROOM, BIN, INV_CODE, LOC_DESC
FROM LOCATION_DESCRIPTION
WHERE (STK_ROOM IN (@Stock)) AND (BIN IN (@Bins))

-- Query to fix POUID

select cast(POUID as binary(7)), POUID, VCPOUID
from Mfg_DFSFNSF
where substring(POUID,6,1) = char(0)
and
substring(POUID,1,1) != char(0)


update Mfg_DFSFNSF
set POUID = left(POUID, 5) + char(32) + char(32)
where substring(POUID,6,1) = char(0)
and
substring(POUID,1,1) != char(0)

--Sales_2004

SELECT TOP (100) PERCENT dbo.Z_SAAM_CustomerClassValues_D_I.CustomerClassValue, dbo.Z_SAAM_GroupDesc.GroupDesc, SUM(slt.ShippedQuantity) 
AS ShippedQuantity, SUM(slt.ShippedQuantity * cl.ItemLocalNetUnitPrice) AS ShippedAmount, ISNULL(SUM(slt.StandardTotalShippedCost), 0.0) 
- ISNULL(SUM(ia.StandardTotalAdjustmentCost), 0.0) AS StandardTotalGLAmount, ISNULL(SUM(slt.SATotalShippedCost), 0.0) 
- ISNULL(SUM(ia.SATotalAdjustmentCost), 0.0) AS SATotalGLAmount, i.ItemReference4, fp.FiscalYear, fp.FiscalPeriod, i.ItemNumber
FROM dbo.SA_ShipmentCOLineTransaction AS slt INNER JOIN
dbo.SA_ShipmentCOLine AS sh ON sh.ShipmentCOLineKey = slt.ShipmentCOLineKey INNER JOIN
dbo.SA_COLine AS cl ON cl.COLineKey = sh.COLineKey INNER JOIN
dbo.SA_Item AS i ON i.ItemKey = sh.ItemKey INNER JOIN
dbo.SA_COHeader AS ch ON ch.COHeaderKey = cl.COHeaderKey LEFT OUTER JOIN
dbo.SA_CommissionCode AS cc ON cc.CommissionCodeKey = ch.CommissionCodeKey LEFT OUTER JOIN
dbo.SA_Region AS r ON r.RegionKey = ch.RegionKey INNER JOIN
dbo.SA_Customer AS c ON c.CustomerKey = ch.ShipToCustomerKey INNER JOIN
dbo.SA_ShipToDeliveryLocation AS st ON st.ShipToDeliveryLocationKey = ch.ShipToDeliveryLocationKey INNER JOIN
dbo.SA_FiscalPeriod AS fp ON slt.ShippedDate BETWEEN fp.PeriodStartDate AND fp.PeriodEndDate INNER JOIN
dbo.Z_SAAM_CustomerClassValues_D_I ON 
c.CustomerClassDefinition1Key = dbo.Z_SAAM_CustomerClassValues_D_I.CustomerClassDefinitionKey RIGHT OUTER JOIN
dbo.Z_SAAM_GroupDesc ON i.ItemReference4 = dbo.Z_SAAM_GroupDesc.GroupCode LEFT OUTER JOIN
dbo.SA_InventoryAdjustment AS ia ON ia.ItemKey = i.ItemKey AND ia.CustomerKey = c.CustomerKey AND 
ia.ShipToDeliveryLocationKey = st.ShipToDeliveryLocationKey AND ia.RegionKey = r.RegionKey AND 
ia.CommissionCodeKey = cc.CommissionCodeKey AND ia.FiscalPeriodKey = fp.FiscalPeriodKey AND ia.IsApplied = 1
GROUP BY fp.FiscalYear, fp.FiscalPeriod, dbo.Z_SAAM_GroupDesc.GroupDesc, dbo.Z_SAAM_CustomerClassValues_D_I.CustomerClassValue, i.ItemReference4, 
i.ItemNumber
HAVING (fp.FiscalYear = 2007) AND (NOT (i.ItemNumber = '21465P') AND NOT (i.ItemNumber = '20390P') AND NOT (i.ItemNumber = '20441') AND 
NOT (i.ItemNumber = '21266P') AND NOT (i.ItemNumber = '21444-1P') AND NOT (i.ItemNumber = '21464P') AND NOT (i.ItemNumber = '21465P') AND 
NOT (i.ItemNumber = '21487P') AND NOT (i.ItemNumber = '21500') AND NOT (i.ItemNumber = '21530') AND NOT (i.ItemNumber = '74025') AND 
NOT (i.ItemNumber = '74029') AND NOT (i.ItemNumber = '74030') AND NOT (i.ItemNumber = '74039') AND NOT (i.ItemNumber = '74050') AND 
NOT (i.ItemNumber = 'CREDIT-OVERHAUL') AND NOT (i.ItemNumber = 'HB800P') AND NOT (i.ItemNumber = 'HB900') AND 
NOT (i.ItemNumber = 'HB900-01') AND NOT (i.ItemNumber = 'HB900-02'))
ORDER BY fp.FiscalPeriod, i.ItemReference4