Tuesday, March 31, 2015

Dynamic Value for Column

Hi, I have the following piece of code which produces this output:



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


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)


For reporting purposes How can I add a dynamic column so that it shows the latest no_referrals for the latest month and hospital chosen. In this case the latest month is March 2015 and hospital is FSH, so I would want my additional column to show the value 2592 on each row.


if the user was to select a date in January and two hospitals then I would want januarys value copied into each row for each respective hospital.


No comments:

Post a Comment