May 19, 2011 at 4:32 am
Hi there -
Getting the following error when I run this code -
SELECTTOP 100 PERCENT [PlaceRef]
,[TenantName]
,[Address1]
,[Address2]
,[Address3]
,[Address4]
,[Address5]
,[PostCode]
,[LocationType]
,[TenancyStatus]
,[ReviewDate]
,[ActualDate]
,[Days Under/Over Target]
,[Missed Target] = CASE
WHEN [Days Under/Over Target] > 0 OR [Outstanding] = 'OVERDUE' THEN 'YES'
ELSE 'NO' END
,[Outstanding] = CASE
WHEN [ReviewDate] <= GETDATE() AND [ActualDate]IS NULL THEN 'OVERDUE'
ELSE '' END
,[ReviewMONTH]
,[ReviewYEAR]
FROM
(
SELECT DISTINCT LOC.[place-ref] AS 'PlaceRef'
,TEN.[corr-name1]As 'TenantName'
,PLAC.[address1] AS 'Address1'
,PLAC.[address2] AS 'Address2'
,PLAC.[address3] AS 'Address3'
,PLAC.[address4] AS 'Address4'
,PLAC.[address5] AS 'Address5'
,PLAC.[post-code] AS 'PostCode'
,LOC.[location-type] AS 'LocationType'
,TEN.[tncy-status] AS 'TenancyStatus'
,SUPDET.[review-date]AS 'ReviewDate'
,SUPDET.[actual-review] AS 'ActualDate'
,DATEDIFF (day, SUPDET.[review-date],SUPDET.[actual-review]) AS 'Days Under/Over Target'
,DATEPART(MM,SUPDET.[review-date])AS 'ReviewMONTH'
,DATEPART(YYYY,SUPDET.[review-date])AS 'ReviewYEAR'
FROM dbo.[IH_IH-LOCATION] AS LOC
INNER JOIN
dbo.[CORE_CO-PLACE]AS PLAC
ON PLAC.[place-ref] = LOC.[place-ref]
INNER JOIN
dbo.[IH_RE-TNCY-PLACE] AS TENPL
ON LOC.[place-ref] = TENPL.[place-ref]
INNER JOIN
dbo.[IH_RE-TENANCY]AS TEN
ON TENPL.[tncy-sys-ref] = TEN.[tncy-sys-ref]
INNER JOIN
dbo.[IH_RE-SP-SUP-PLAN-HDR] AS SUPHEAD
ON TEN.[tncy-sys-ref] = SUPHEAD.[reference]
INNER JOIN
dbo.[IH_RE-SP-SUP-PLAN-DTL] AS SUPDET
ON SUPHEAD.[reference] = SUPDET.[reference]
WHERE LOC.[location-type] IN ('FLATSHEL','BUNGSHEL','STUDSHEL')
AND SUPDET.[review-date] >= CONVERT(DATETIME, '2011-04-01 00:00:00', 102)
) ONION
It's definatley to do with the following bit of code -
,[Missed Target] = CASE
WHEN [Days Under/Over Target] > 0 OR [Outstanding] = 'OVERDUE' THEN 'YES'
ELSE 'NO' END
But I need to say that when the Days Under/Over Target is over zero OR when the Outstanding column is Overdue to flag up as yes.
Can anyone help at all please?
Thanks
May 19, 2011 at 4:37 am
Sorry Guys -
Got it working.
SELECTTOP 100 PERCENT [PlaceRef]
,[TenantName]
,[Address1]
,[Address2]
,[Address3]
,[Address4]
,[Address5]
,[PostCode]
,[LocationType]
,[TenancyStatus]
,[ReviewDate]
,[ActualDate]
,[Days Under/Over Target]
,[Outstanding]
,[Missed Target] = CASE
WHEN [Days Under/Over Target] > 0 OR [Outstanding] = 'OVERDUE' THEN 'YES'
ELSE 'NO' END
,[ReviewMONTH]
,[ReviewYEAR]
FROM
(
SELECT DISTINCT LOC.[place-ref] AS 'PlaceRef'
,TEN.[corr-name1]As 'TenantName'
,PLAC.[address1] AS 'Address1'
,PLAC.[address2] AS 'Address2'
,PLAC.[address3] AS 'Address3'
,PLAC.[address4] AS 'Address4'
,PLAC.[address5] AS 'Address5'
,PLAC.[post-code] AS 'PostCode'
,LOC.[location-type] AS 'LocationType'
,TEN.[tncy-status] AS 'TenancyStatus'
,SUPDET.[review-date]AS 'ReviewDate'
,SUPDET.[actual-review] AS 'ActualDate'
,DATEDIFF (day, SUPDET.[review-date],SUPDET.[actual-review]) AS 'Days Under/Over Target'
,CASE WHEN SUPDET.[review-date] <= GETDATE() AND SUPDET.[actual-review]IS NULL
THEN 'OVERDUE'
ELSE '' END AS 'Outstanding'
,DATEPART(MM,SUPDET.[review-date])AS 'ReviewMONTH'
,DATEPART(YYYY,SUPDET.[review-date])AS 'ReviewYEAR'
FROM dbo.[IH_IH-LOCATION] AS LOC
INNER JOIN
dbo.[CORE_CO-PLACE]AS PLAC
ON PLAC.[place-ref] = LOC.[place-ref]
INNER JOIN
dbo.[IH_RE-TNCY-PLACE] AS TENPL
ON LOC.[place-ref] = TENPL.[place-ref]
INNER JOIN
dbo.[IH_RE-TENANCY]AS TEN
ON TENPL.[tncy-sys-ref] = TEN.[tncy-sys-ref]
INNER JOIN
dbo.[IH_RE-SP-SUP-PLAN-HDR] AS SUPHEAD
ON TEN.[tncy-sys-ref] = SUPHEAD.[reference]
INNER JOIN
dbo.[IH_RE-SP-SUP-PLAN-DTL] AS SUPDET
ON SUPHEAD.[reference] = SUPDET.[reference]
WHERE LOC.[location-type] IN ('FLATSHEL','BUNGSHEL','STUDSHEL')
AND SUPDET.[review-date] >= CONVERT(DATETIME, '2011-04-01 00:00:00', 102)
) ONION
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply