Thank you for the link, it was very informative and answered a great many questions about the data flow. In regards, to my original question of how to report on or compare un-posted items to posted items during the day.
Currently we are using the SOP30200 and SOP 30300 tables to get sales data for reporting and those are the dates being used to filter.
Will the SOP10100 and SOP10200 be the equivalent un-posted tables? Should the SOPNUMBE and SOPTYPE combo be able to link to SOP300000s tables? Or should I compare them with the RM20101 (RM Open file) and look for the SOPNUMBE and SOPTYPE combos that are not in the SOP30000s?
Would it look like the following:
[code="sql"]
SELECT SOP30200.SLPRSNID, SOP30300.XTNDPRCE, SOP30300.SOPTYPE, SOP30200.DOCDATE,
SOP30300.NONINVEN, SOP30300.SOPNUMBE, SOP30300.ITEMDESC, SOP30300.EXTDCOST, SOP30200.VOIDSTTS,
SOP30200.CUSTNMBR, SOP30300.MRKDNAMT, SOP30300.QUANTITY, SOP30300.QTYFULFI,
SOP30300.ITEMNMBR
INTO #_Temp1
FROM SOP30200
INNER JOIN SOP30300
ON (SOP30200.SOPTYPE = SOP30300.SOPTYPE) AND (SOP30200.SOPNUMBE = SOP30300.SOPNUMBE)
WHERE
(DOCDATE >= '01/01/2014' AND DOCDATE<= GETDATE())
SELECT SOP10200.SLPRSNID, SOP10200.XTNDPRCE, SOP10100.SOPTYPE, SOP10100.DOCDATE,
SOP10200.NONINVEN, SOP10100.SOPNUMBE, SOP10200.ITEMDESC, SOP10100.EXTDCOST, SOP10100.VOIDSTTS,
SOP10100.CUSTNMBR, SOP10100.MRKDNAMT, SOP10200.QUANTITY, SOP10200.QTYFULFI,
SOP10200.ITEMNMBR
INTO #_Temp2
FROM SOP10200
INNER JOIN SOP10100
ON (SOP10200.SOPTYPE = SOP10100.SOPTYPE) AND (SOP10200.SOPNUMBE = SOP10100.SOPNUMBE)
WHERE
(DOCDATE >= '01/01/2014' AND DOCDATE<= GETDATE())
-- Posted
SELECT *
FROM #_Temp1
WHERE
(DOCDATE >= DATEADD(dd,-2,GetDate()) AND DOCDATE<= GETDATE())
ORDER BY DOCDATE Desc
-- UnPosted
SELECT *
FROM #_Temp2
WHERE
(DOCDATE >= DATEADD(dd,-2,GetDate()) AND DOCDATE<= GETDATE())
ORDER BY DOCDATE Desc
[/code]
