Sometimes requirements make things...difficult and therefore interesting.
So an integration customer wanted a daily transaction report formatted in a certain way to allow their ERP system to ingest the data in order to update their inventory numbers.
This would usually be a straightforward transaction dump, formatted to whatever format their system required. However, in the event that a transaction was updated after the fact, the customer did not want to see the new value for the transaction...they wanted the difference between the original transaction and the updated transaction. Simple enough, yeah?
Not really, because of the way our system handled transaction updates. When an update occurred, the quantity value of the original transaction was shifted to a separate XTRAN transaction and replaced with the new quantity value.
So in order to accomplish their desired output, I had to get a little crazy with WHILE loops and temp tables in order to shift values back to their original positions to then be able to calculate the difference. Things got even crazier when you considered the possibility of multiple updates on the same original transaction.
Excited for the challenge, I set about writing the solution...while working from 'home' over a few nice sunny days on 'vacation' at Lakeside.
BEGIN
SET NOCOUNT ON;
--*******************************************
-- TELERIK NOT SHOWING DATA SOURCE FIELDS IF STORED PROCEDURE USES TEMP TABLES FIX
-- source of solution: https://www.telerik.com/forums/temporary-table-in-stored-proc
IF 1=0
BEGIN
SET FMTONLY OFF
END
--*******************************************
DECLARE
@counter INT = 1
,@loopCounter INT = 0
,@rowCount INT = 0
,@loopRowCount INT = 0
,@currentTransNo NVARCHAR(35) = ''
--=========================================================
-- first get yesterday's transactions
SELECT *
INTO #YESTERDAY
FROM
(
SELECT
TRANS.transnumber
,TRANS.station
,TRANS.bin
,TRANS.Item
,TRANS.OldItem
,TRANS.employee
,TRANS.User1
,TRANS.User3
,TRANS.User2
,TRANS.User4
,TRANS.cost
,TRANS.quantity
,TRANS.Transdate
,TRANS.Transtime
,TRANS.[type]
,TRANS.TypeDescription
,TRANS.binqty
,TRANS.User5
,TRANS.SerialID
,TRANS.User6
,TRANS.RelatedKey
,TRANS.[Status]
,TRANS.CribBin
,TRANS.BatchId
,TRANS.IssuedTo
,TRANS.Consignment
,TRANS.OtherCribBin
,TRANS.WONo
,TRANS.TransUsage
,TRANS.RepairCalCycle
,TRANS.Crib
,TRANS.LotNo
,TRANS.UsageType
,TRANS.UsageCribBin
,TRANS.UsageItemNumber
,TRANS.ReservationNo
,TRANS.SubType
,TRANS.OtherSiteNo
,TRANS.CycleCountClassNo
,TRANS.ExpectedAccuracy
,TRANS.TransRFIDNo
,TRANS.LocalTransDate
,TRANSDETAIL.TransReasonCode
FROM
REDACTED_CLOUD_01.CM01.dbo.TRANS AS TRANS LEFT OUTER JOIN
REDACTED_CLOUD_01.CM01.dbo.Crib AS CRIB ON TRANS.Crib = CRIB.Crib LEFT OUTER JOIN
REDACTED_CLOUD_01.CM01.dbo.TRANSDETAIL AS TRANSDETAIL ON TRANS.transnumber = TRANSDETAIL.TransNo
WHERE
CAST(Transdate AS DATE) = CAST(DATEADD(day,-1,GETDATE()) AS DATE)
AND Crib.SiteID = 'REDACTED'
AND (
(
TRANSDETAIL.TransReasonCode <> 'NB'
AND TRANSDETAIL.TransReasonCode IS NOT NULL
)
OR (TRANSDETAIL.TransReasonCode IS NULL)
)
AND (
TRANS.bin <> 'CANDYBIN'
OR TRANS.Item <> 'REDACTEDCANDY'
)
) AS yesterday
--==========================================================================
-- Ok, first chunk of logic to "show the difference in values"
-- for the UPDATE/XTRAN tansactions
-- We'll do the one where the UPDATE takes place on a different day
--==========================================================================
-- get two whole months of transactions so that we definitely capture the
-- past month/30 days...many fields in case we need them.
SELECT *
INTO #WHOLEMONTH
FROM
(
SELECT
TRANS.transnumber
,TRANS.station
,TRANS.bin
,TRANS.Item
,TRANS.OldItem
,TRANS.employee
,TRANS.User1
,TRANS.User3
,TRANS.User2
,TRANS.User4
,TRANS.cost
,TRANS.quantity
,TRANS.Transdate
,TRANS.Transtime
,TRANS.type
,TRANS.TypeDescription
,TRANS.binqty
,TRANS.User5
,TRANS.SerialID
,TRANS.User6
,TRANS.RelatedKey
,TRANS.Status
,TRANS.CribBin
,TRANS.BatchId
,TRANS.IssuedTo
,TRANS.Consignment
,TRANS.OtherCribBin
,TRANS.WONo
,TRANS.TransUsage
,TRANS.RepairCalCycle
,TRANS.Crib
,TRANS.LotNo
,TRANS.UsageType
,TRANS.UsageCribBin
,TRANS.UsageItemNumber
,TRANS.ReservationNo
,TRANS.SubType
,TRANS.OtherSiteNo
,TRANS.CycleCountClassNo
,TRANS.ExpectedAccuracy
,TRANS.TransRFIDNo
,TRANS.LocalTransDate
FROM
REDACTED_CLOUD_01.CM01.dbo.TRANS AS TRANS LEFT OUTER JOIN
REDACTED_CLOUD_01.CM01.dbo.Crib AS CRIB ON TRANS.Crib = CRIB.Crib
WHERE
CAST(Transdate AS DATE) >= CAST(DATEADD(month,-2,GETDATE()) AS DATE)
AND Crib.SiteID = 'REDACTED'
AND (
TRANS.bin <> 'CANDYBIN'
OR TRANS.Item <> 'REDACTEDCANDY'
)
) AS wholemonth
-- ok, now get just the XTRANS
SELECT *
INTO #WHOLEMONTH_XTRAN_TRANSNO
FROM
(
SELECT DISTINCT BatchId
FROM #WHOLEMONTH AS blah
WHERE TypeDescription = 'XTRAN'
) AS wholemonth_xtran_transno
-- now get a list of the original transactions for XTRANS
SELECT *
INTO #WHOLEMONTH_XTRAN_OGTRANS
FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY transnumber) AS theRow
,transnumber
,Item
,quantity
,Transdate
,Transtime
,BatchId
,TypeDescription
,'OG' AS [Sequence]
FROM #WHOLEMONTH AS blah
WHERE transnumber IN (SELECT BatchId FROM #WHOLEMONTH_XTRAN_TRANSNO)
) AS wholemonth_xtran_ogtrans
-- now just the XTRANS where the items match (because the XTRAN BatchId
- can still be the same for ISSUES that are not the same item
SELECT *
INTO #WHOLEMONTH_XTRAN_JUSTRELEVANTXTRAN
FROM
(
SELECT
b.transnumber
,b.Item
,b.quantity
,b.Transdate
,b.Transtime
,b.BatchId
,b.TypeDescription
,ROW_NUMBER() OVER (PARTITION BY b.BatchId ORDER BY b.Transdate,b.Transtime) AS [Sequence]
FROM
#WHOLEMONTH_XTRAN_OGTRANS AS a INNER JOIN
#WHOLEMONTH AS b ON a.Item = b.Item AND a.transnumber = b.BatchId
WHERE b.TypeDescription = 'XTRAN'
) AS wholemonth_xtran_justrelevantxtran
-- ok, now a while loop to itterate through the OG trans rows, do some
-- stuff, and then dump that stuff into a temp table to use later.
SELECT @rowCount = COUNT(theRow) FROM #WHOLEMONTH_XTRAN_OGTRANS
DROP TABLE IF EXISTS #INSIDELOOP
CREATE TABLE #INSIDELOOP
(
transnumber NVARCHAR(30) NOT NULL
,Item NVARCHAR(30) NOT NULL
,ParentTransQty INT NULL
,OGTransQty INT NULL
,ShiftedTransQty INT NULL
,DifferenceQty INT NULL
,Transdate DATETIME NULL
,Transtime TIME NULL
,TypeDescription NVARCHAR(30) NULL
,OGTrans NVARCHAR(30) NULL
,[Sequence] NVARCHAR(10) NULL
)
WHILE @counter <= @rowCount
BEGIN
-- We need to get the max theRow number where the XTRAN batch id =
-- the OG transnumber and set a variable to that theRow number.
-- Then use that variable to WHILE through stuff and shuffle values
-- for the transactions.
SELECT @loopRowCount =
COUNT([Sequence])
FROM #WHOLEMONTH_XTRAN_JUSTRELEVANTXTRAN
WHERE BatchId IN (SELECT transnumber FROM #WHOLEMONTH_XTRAN_OGTRANS WHERE theRow = @counter)
SET @loopRowCount += 1 -- add 1 to account for the OG parent transaction
-- now the next while loop to do some stuff I don't know what yet
SET @loopCounter = 1
WHILE @loopCounter <= @loopRowCount
BEGIN
-- get the first row and 'correct' its value....
-- the 'original' value of the transaction will be
-- the first XTRAN(if multiple)
IF @loopCounter = 1 -- first run of next inside loop
BEGIN
INSERT INTO #INSIDELOOP (transnumber,Item,ParentTransQty,OGTransQty,ShiftedTransQty,DifferenceQty,Transdate,Transtime,TypeDescription,OGTrans,[Sequence])
SELECT
OG.transnumber
,OG.Item
,OG.quantity AS ParentTransQty
,OG.quantity AS OGTransQty
,XTRAN.quantity AS ShiftedTransQty
,0 AS DifferenceQty
,OG.Transdate
,OG.Transtime
,OG.TypeDescription
,OG.transnumber AS OGTrans
,OG.[Sequence]
FROM
(
SELECT
transnumber
,Item
,quantity
,Transdate
,Transtime
,BatchId
,TypeDescription
,[Sequence]
FROM #WHOLEMONTH_XTRAN_OGTRANS
WHERE theRow = @counter
) AS OG LEFT OUTER JOIN
(
SELECT
transnumber
,Item
,quantity
,Transdate
,Transtime
,BatchId
,TypeDescription
,[Sequence]
FROM #WHOLEMONTH_XTRAN_JUSTRELEVANTXTRAN
) AS XTRAN ON OG.transnumber = XTRAN.BatchId AND OG.Item = XTRAN.Item AND XTRAN.[Sequence] = @loopCounter -- 1
SELECT @currentTransNo = transnumber FROM #WHOLEMONTH_XTRAN_OGTRANS WHERE theRow = @counter
END
ELSE IF @loopCounter = @loopRowCount AND @loopCounter > 1 -- ELSE IF to handle the last run so we can compare last XTRAN to OG trans again
BEGIN
INSERT INTO #INSIDELOOP (transnumber,Item,ParentTransQty,OGTransQty,ShiftedTransQty,DifferenceQty,Transdate,Transtime,TypeDescription,OGTrans,[Sequence])
SELECT
XTRAN.transnumber
,XTRAN.Item
,OG.quantity AS ParentTransQty
,XTRAN.quantity AS OGTransQty
,OG.quantity AS ShiftedTransQty
,OG.quantity - XTRAN.quantity AS DifferenceQty
,XTRAN.Transdate
,XTRAN.Transtime
,XTRAN.TypeDescription
,XTRAN.BatchId AS OGTrans
,XTRAN.[Sequence]
FROM
(
SELECT
transnumber
,Item
,quantity
,Transdate
,Transtime
,BatchId
,TypeDescription
,[Sequence]
FROM #WHOLEMONTH_XTRAN_OGTRANS
WHERE theRow = @counter
) AS OG LEFT OUTER JOIN
(
SELECT
transnumber
,Item
,quantity
,Transdate
,Transtime
,BatchId
,TypeDescription
,[Sequence]
FROM #WHOLEMONTH_XTRAN_JUSTRELEVANTXTRAN
) AS XTRAN ON OG.transnumber = XTRAN.BatchId AND OG.Item = XTRAN.Item AND XTRAN.[Sequence] = @loopRowCount - 1 -- final XTRAN
END
ELSE IF @loopCounter < @loopRowCount AND @loopCounter > 1
BEGIN
-- ELSE sql statements here to handle any non-first,
-- non-last XTRANS, comparing XTRAN to XTRAN
INSERT INTO #INSIDELOOP (transnumber,Item,ParentTransQty,OGTransQty,ShiftedTransQty,DifferenceQty,Transdate,Transtime,TypeDescription,OGTrans,[Sequence])
SELECT
XTRAN.transnumber
,XTRAN.Item
,OG.quantity AS ParentTransQty
,XTRAN.quantity AS OGTransQty
,XTRAN2.quantity AS ShiftedTransQty
,XTRAN2.quantity - XTRAN.quantity AS DifferenceQty
,XTRAN.Transdate
,XTRAN.Transtime
,XTRAN.TypeDescription
,XTRAN.BatchId AS OGTrans
,XTRAN.[Sequence]
FROM
(
SELECT
transnumber
,Item
,quantity
,Transdate
,Transtime
,BatchId
,TypeDescription
,[Sequence]
FROM #WHOLEMONTH_XTRAN_JUSTRELEVANTXTRAN
WHERE BatchId = @currentTransNo AND [Sequence] = @loopCounter - 1
) AS XTRAN LEFT OUTER JOIN
(
SELECT
transnumber
,Item
,quantity
,Transdate
,Transtime
,BatchId
,TypeDescription
,[Sequence]
FROM #WHOLEMONTH_XTRAN_JUSTRELEVANTXTRAN
) AS XTRAN2 ON XTRAN.BatchId = XTRAN2.BatchId AND XTRAN.Item = XTRAN2.Item AND XTRAN2.[Sequence] = @loopCounter LEFT OUTER JOIN
(
SELECT
transnumber
,Item
,quantity
,Transdate
,Transtime
,BatchId
,TypeDescription
,[Sequence]
FROM #WHOLEMONTH_XTRAN_OGTRANS
WHERE Transnumber = @currentTransNo
) AS OG ON XTRAN.BatchId = OG.Transnumber AND XTRAN.Item = OG.Item
END
SET @loopCounter += 1
END
SET @counter += 1
END
-- now get just the OG row from the #INSIDELOOP dataset and add row numbers.
SELECT *
INTO #INSIDELOOP_OGROWS
FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY transnumber) AS theRow
,transnumber
,Item
,ParentTransQty
,OGTransQty
,ShiftedTransQty
,DifferenceQty
,Transdate
,Transtime
,TypeDescription
,OGTrans
,[Sequence]
FROM #INSIDELOOP AS blah
WHERE [Sequence] = 'OG'
) AS insidelooprows
-- ok, I think we're going to have to loop again to better determine which
-- of the scenarios these transaction updates might be:
-- 1. Same day
-- 2. Different Day
-- reset the rowCount and counter variable
SELECT @rowCount = COUNT(theRow) FROM #INSIDELOOP_OGROWS
SELECT @counter = 1
-- create another temp table to dump the results of the looping into
DROP TABLE IF EXISTS #SECONDINSIDELOOP
CREATE TABLE #SECONDINSIDELOOP
(
transnumber NVARCHAR(30) NULL
,Item NVARCHAR(30) NULL
,ParentTransQty INT NULL
,OGTransQty INT NULL
,ShiftedTransQty INT NULL
,DifferenceQty INT NULL
,Transdate DATETIME NULL
,Transtime TIME NULL
,TypeDescription NVARCHAR(30) NULL
,OGTrans NVARCHAR(30) NULL
,[Sequence] NVARCHAR(10) NULL
,Scenario INT NULL -- this will hold 1 for parent trans, 2 for same day, 3 for different day
)
--same structure as the last loops sort of
WHILE @counter <= @rowCount
BEGIN
SELECT @loopRowCount =
COUNT([Sequence])
FROM #INSIDELOOP
WHERE OGTrans IN (SELECT OGTrans FROM #INSIDELOOP_OGROWS WHERE theRow = @counter)
SET @loopCounter = 1
WHILE @loopCounter <= @loopRowCount
BEGIN
IF @loopCounter = 1 -- first row of the set is the original INSERT transaction
BEGIN
INSERT INTO #SECONDINSIDELOOP (transnumber,Item,ParentTransQty,OGTransQty,ShiftedTransQty,DifferenceQty,Transdate,Transtime,TypeDescription,OGTrans,[Sequence],Scenario)
SELECT
OG.transnumber
,OG.Item
,OG.ParentTransQty
,OG.OGTransQty
,OG.ShiftedTransQty
,OG.DifferenceQty
,OG.Transdate
,OG.Transtime
,OG.TypeDescription
,OG.OGTrans
,OG.[Sequence]
,1 AS Scenario
FROM
(
SELECT
transnumber
,Item
,ParentTransQty
,OGTransQty
,ShiftedTransQty
,DifferenceQty
,Transdate
,Transtime
,TypeDescription
,OGTrans
,[Sequence]
FROM #INSIDELOOP_OGROWS
WHERE theRow = @counter
) AS OG
SELECT @currentTransNo = transnumber FROM #INSIDELOOP_OGROWS WHERE theRow = @counter
END
ELSE
--IF @loopCounter = @loopRowCount AND @loopCounter > 1 -- get the last one
BEGIN
INSERT INTO #SECONDINSIDELOOP (transnumber,Item,ParentTransQty,OGTransQty,ShiftedTransQty,DifferenceQty,Transdate,Transtime,TypeDescription,OGTrans,[Sequence],Scenario)
SELECT
XTRAN.transnumber
,XTRAN.Item
,XTRAN.ParentTransQty
,XTRAN.OGTransQty
,XTRAN.ShiftedTransQty
,XTRAN.DifferenceQty
,XTRAN.Transdate
,XTRAN.Transtime
,XTRAN.TypeDescription
,XTRAN.OGTrans
,XTRAN.[Sequence]
,CASE
WHEN FORMAT(OG.Transdate,'yyyy-MM-dd') = FORMAT(XTRAN.Transdate,'yyyy-MM-dd')
THEN 2
ELSE 3 END AS Scenario
FROM
(
SELECT
transnumber
,Item
,ParentTransQty
,OGTransQty
,ShiftedTransQty
,DifferenceQty
,Transdate
,Transtime
,TypeDescription
,OGTrans
,[Sequence]
FROM #INSIDELOOP
WHERE OGTrans = @currentTransNo AND [Sequence] = 'OG'
) AS OG LEFT OUTER JOIN
(
SELECT
transnumber
,Item
,ParentTransQty
,OGTransQty
,ShiftedTransQty
,DifferenceQty
,Transdate
,Transtime
,TypeDescription
,OGTrans
,[Sequence]
FROM #INSIDELOOP
) AS XTRAN ON OG.OGTrans = XTRAN.OGTrans AND XTRAN.[Sequence] = CAST(@loopCounter - 1 AS NVARCHAR(MAX))
END
SET @loopCounter += 1
END
SET @counter += 1
END
-- ok, we have to filter out RECVE transactions....and I think the easiest
-- way to do that would be to just filter them and their associated XTRANs
-- all out at the end. So get the unique RECVE transnumbers to use later
-- to filter out.
SELECT *
INTO #JUSTRECVE
FROM
(
SELECT DISTINCT transnumber
FROM #WHOLEMONTH
WHERE TypeDescription = 'RECVE'
) AS justrecve
-- And now delete the RECVE trans and their
-- associated XTRANS from the looping stuff we just did
DELETE FROM #SECONDINSIDELOOP
WHERE OGTrans IN (SELECT transnumber FROM #JUSTRECVE)
--================================
-- Get the pertinent item info
--================================
SELECT *
INTO #THEITEMS
FROM
(
SELECT DISTINCT Item
FROM #YESTERDAY AS gettingtheitems
) AS theitems
-- now make an index
IF EXISTS
(
SELECT name
FROM sys.indexes
WHERE name = N'IX_THEITEMS'
)
DROP INDEX IX_THEITEMS ON #THEITEMS
CREATE NONCLUSTERED INDEX IX_THEITEMS
ON #THEITEMS (Item)
SELECT *
INTO #INV
FROM
(
SELECT
ItemNumber
,Description2
,Manufacturer
,MfrNumber
,UDFREDACTEDPartReference
FROM REDACTED_CLOUD_01.CM01.dbo.INVENTRY AS INVENTRY
WHERE ItemNumber IN (SELECT Item FROM #THEITEMS)
) AS inv
--======================================
-- Get the pertinent STATION info
--=======================================
SELECT *
INTO #THECRIBBINS
FROM
(
SELECT DISTINCT CribBin
FROM #YESTERDAY AS gettingthecribbins
) AS thecribbins
-- now make an index
IF EXISTS
(
SELECT name
FROM sys.indexes
WHERE name = N'IX_THECRIBBINS'
)
DROP INDEX IX_THECRIBBINS ON #THECRIBBINS
CREATE NONCLUSTERED INDEX IX_THECRIBBINS
ON #THECRIBBINS (CribBin)
SELECT *
INTO #STA
FROM
(
SELECT
CribBin
,Crib
,Bin
,Item
,Consignment
FROM REDACTED_CLOUD_01.CM01.dbo.STATION AS STATION
WHERE CribBin IN (SELECT CribBin FROM #THECRIBBINS)
) AS sta
--======================================
-- Get the pertinent EMPLOYEE info
--=======================================
SELECT *
INTO #EMPLOYEENAME
FROM
(
SELECT DISTINCT employee AS empid
FROM #YESTERDAY AS gettingthename
) AS employeename
-- now make an index
IF EXISTS
(
SELECT name
FROM sys.indexes
WHERE name = N'IX_THENAME'
)
DROP INDEX IX_THENAME ON #EMPLOYEENAME
CREATE NONCLUSTERED INDEX IX_THENAME
ON #EMPLOYEENAME (empid)
SELECT *
INTO #EMP
FROM
(
SELECT
ID
,LastName
,FirstName
,User1
FROM REDACTED_CLOUD_01.CM01.dbo.EMPLOYEE AS EMPLOYEE
WHERE ID IN (SELECT empid FROM #EMPLOYEENAME)
) AS emp
--===================================
-- Now put it together I suppose
--===================================
SELECT *
INTO #CHARGEOUTINTERFACE
FROM
(
SELECT
[COMPANY]
,[CHARGED_QTY]
,[WAREHOUSE]
,[BIN]
,[DEPARTMENT]
,[CHARGED_DATE]
,[REDACTED_PART_NUMBER]
,[OPERATOR_NAME]
,[CREATION_DATETIME]
,[TRANSACTION_ID]
,[TRANSACTION_TYPE]
,[CONSIGNMENT]
FROM
(
SELECT
'REDACTED' AS [COMPANY]
-- ok, need some logic to make it so we
-- report the DIFFERENCE in the original
-- value compared to the XTRAN
,CASE
WHEN YEST.transnumber IN (SELECT transnumber FROM #SECONDINSIDELOOP)
THEN
CASE
WHEN XTRANSORTED.Scenario = 1 THEN XTRANSORTED.ShiftedTransQty
ELSE XTRANSORTED.DifferenceQty
END
ELSE YEST.quantity
END AS [CHARGED_QTY]
,CASE WHEN STA.Crib = 3605 THEN 'JAN' ELSE 'MRO' END AS [WAREHOUSE]
,STA.Bin AS [BIN]
,CASE
WHEN (YEST.TypeDescription = 'ADJUS' OR YEST.TypeDescription = 'COUNT') THEN 'REDACTED'
WHEN YEST.User1 IS NULL THEN EMP.User1
WHEN YEST.User1 = '' THEN EMP.User1
ELSE YEST.User1
END AS [DEPARTMENT]
,FORMAT(CAST(YEST.Transdate AS DATE), 'yyyy-MM-dd') AS [CHARGED_DATE]
,INV.UDFREDACTEDPartReference AS [REDACTED_PART_NUMBER]
,UPPER(EMP.LastName) AS [OPERATOR_NAME]
,FORMAT(CAST(GETDATE() AS DATE), 'yyyy-MM-dd') AS [CREATION_DATETIME]
,CASE WHEN YEST.TypeDescription = 'XTRAN' THEN YEST.BatchId ELSE YEST.transnumber END AS [TRANSACTION_ID]
,CASE
WHEN YEST.TypeDescription = 'XTRAN' THEN 'U'
WHEN YEST.TypeDescription = 'ISSUE' THEN 'I'
WHEN YEST.TypeDescription = 'COUNT' THEN 'I'
WHEN YEST.TypeDescription = 'ADJUS' THEN 'I'
WHEN YEST.TypeDescription = 'CANCL' THEN 'D'
ELSE ''
END AS [TRANSACTION_TYPE]
,STA.Consignment AS [CONSIGNMENT]
FROM
#YESTERDAY AS YEST INNER JOIN
#INV AS INV ON YEST.Item = INV.ItemNumber INNER JOIN
#STA AS STA ON YEST.CribBin = STA.CribBin INNER JOIN
#EMP AS EMP ON YEST.employee = EMP.ID LEFT OUTER JOIN
#SECONDINSIDELOOP AS XTRANSORTED ON YEST.transnumber = XTRANSORTED.transnumber
) AS blah
WHERE
--case statement for a where clause?
CASE WHEN TRANSACTION_TYPE = 'U' AND CHARGED_QTY = 0 THEN 1 ELSE 0 END = 0 AND WAREHOUSE <> 'JAN'
) AS chargeoutinterface
-- now we remove any rows with anything to do with
-- any RECVE transactions...pretty sure we already
-- got them, but for good measure
DELETE FROM #CHARGEOUTINTERFACE
WHERE TRANSACTION_ID IN (SELECT transnumber FROM #JUSTRECVE)
SELECT *
FROM #CHARGEOUTINTERFACE
ENDA batch job scheduled in Windows Task Scheduler would run this daily to output a csv file that would be fed to a Bash script running on a Debian Linux VM to deposit the file on the customer's FTP server, along with its brother, the ReceiveInterface which followed the same general format.