CROSS APPLY without a function

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.

CROSS APPLY

(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

) iTVF3