Dynamic Value for Column

Hi lrj1985,



Regading your description, you are trying to add one more column which valueing the lastest Month for HOSP_CODE specifically, right? If my understanding is correct, you can reference the below code.

SELECT

crs.HOSP_CODE,
DATEADD(DAY,1-DATEPART(DAY,crs.MONTH),CONVERT(date,crs.MONTH)) AS [MONTH],
crs.NO_REFERRALS,
crsx.NO_REFERRALS AS LAST_MONTH,
crs.NO_REFERRALS - crsx.NO_REFERRALS AS DIFF,
@LATEST_MONTH AS LATEST_MONTH,
@PREVIOUS_MONTH AS PREVIOUS_MONTH,
cat.LatestMonth AS LatestMonth


FROM #CRS_REFERRALS1 AS crs
FULL OUTER JOIN #CRS_REFERRALS1 AS crsx ON crs.HOSP_CODE = crsx.HOSP_CODE AND crs.MONTH = DATEADD(MONTH,1,crsx.MONTH)
CROSS APPLY
(
SELECT MAX(DATEADD(DAY,1-DATEPART(DAY,[MONTH]),CONVERT(date,[MONTH]))) AS LatestMonth FROM #CRS_REFERRALS1 c WHERE c.HOSP_CODE = csr.HOSP_CODE
) AS cat

If you have any question, feel free to let me know.

---

Eric Zhang

TechNet Community Support