I work with some sharp people.
And I’ve been using SQL Server for a long time.. but even I sometimes have embarrassing gaps in my knowledge about SQL Server.
This is one of those situations.. I cannot believe that I didn’t realize (until just a few months ago) that we could use CROSS APPLY without a function.
I’m utterly amazed by this, it’s a better method that what I was previously doing.. For the record, I was previously in the habit of returning the input parameters in the function so that I could join on it.
(SELECT POMatQty = COALESCE(SUM(T.QTY),0),
POMatMBF = COALESCE(SUM(T.total_BF*0.001),0) — No Nulls…
FROM TRANSACT AS T
WHERE T.TRAN_CODE = ‘PO’
AND T.TYPE = ‘PORECPT’
AND T.LOC = L.LOC — Constrain sub-select to out record…..
AND [T].[ITEM_NO] = LEFT([L].[ITEM_NO],13)+’900′ — …. Loc and Item_No
AND T.TRAN_DATE BETWEEN CAST(DATEADD(MM,-@MonthsMAT,GETDATE()) AS DATE)
AND CAST(GETDATE() AS DATE) –NO FUTURE SALES