January 5, 2011 at 5:56 am
Hi there getting the following error when I run the SELECT STATEMENT.
Server: Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'as'.
SELECT dbo.[CORE_CO-PLACE].[place-ref],
dbo.[CORE_CO-PLACE].[address1],
dbo.[CORE_CO-PLACE].[address2],
dbo.[CORE_CO-PLACE].[address3],
dbo.[IH_IH-LOCATION].[location-type]
,dbo.[IH_RE-TENANCY].[tncy-end]AS 'Tenancy End'
,MAX(dbo.[IH_IH-LOCATION-STAT-HIST].[RECEIVED-KEYS]) AS 'RecievedKeysDate'
,MAX(dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET]) AS 'ReadyToLetDate'
,dbo.[IH_IH-LOCATION-HIST].[START-DATE] AS 'VoidFromDate'
,DATEDIFF(day, MAX(dbo.[IH_IH-LOCATION-STAT-HIST].[RECEIVED-KEYS]),
MAX(dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET]) as Days_with_Voids
FROM dbo.[CORE_CO-PLACE]
LEFT OUTER JOIN dbo.[IH_IH-LOCATION]
ON dbo.[CORE_CO-PLACE].[PLACE-REF] = dbo.[IH_IH-LOCATION].[PLACE-REF]
LEFT OUTER JOIN dbo.[IH_RE-TNCY-PLACE]
ON dbo.[IH_IH-LOCATION].[PLACE-REF] = dbo.[IH_RE-TNCY-PLACE].[PLACE-REF]
LEFT OUTER JOIN dbo.[IH_RE-TENANCY]
ON dbo.[IH_RE-TNCY-PLACE].[TNCY-SYS-REF] = dbo.[IH_RE-TENANCY].[TNCY-SYS-REF]
LEFT OUTER JOIN dbo.[IH_IH-LOCATION-STAT-HIST]
ON dbo.[IH_IH-LOCATION-STAT-HIST].[PLACE-REF] = dbo.[IH_IH-LOCATION].[PLACE-REF]
LEFT OUTER JOIN dbo.[IH_IH-PLACE-CHG]
ON dbo.[CORE_CO-PLACE].[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
LEFT OUTER JOIN dbo.[IH_IH-LOCATION-HIST]
ON dbo.[IH_IH-LOCATION-HIST].[PLACE-REF] = dbo.[IH_IH-LOCATION].[PLACE-REF]
WHERE dbo.[IH_IH-LOCATION-STAT-HIST].[RECEIVED-KEYS] IS NOT NULL
AND CONVERT(DATETIME, dbo.[IH_RE-TENANCY].[tncy-end]) between '30/11/2010' and '01/01/2011'
AND CONVERT(DATETIME, dbo.[IH_RE-TENANCY].[tncy-end]) < dbo.[IH_IH-LOCATION-HIST].[START-DATE]
AND dbo.[IH_IH-LOCATION-HIST].[LOCATION-STS] = 'V'
AND dbo.[IH_IH-LOCATION].[location-type] <> 'GARAGE'
GROUP BY dbo.[CORE_CO-PLACE].[place-ref],
dbo.[CORE_CO-PLACE].[address1],
dbo.[CORE_CO-PLACE].[address2],
dbo.[CORE_CO-PLACE].[address3],
dbo.[IH_IH-LOCATION].[location-type]
,dbo.[IH_RE-TENANCY].[tncy-end]
,dbo.[IH_IH-LOCATION-HIST].[START-DATE]
It's definatley linked to the ,DATEDIFF(day, MAX(dbo.[IH_IH-LOCATION-STAT-HIST].[RECEIVED-KEYS]), MAX(dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET]) as Days_with_Voids
Any Ideas?
Thanks
January 5, 2011 at 5:59 am
think you need an extra closing bracket before Days_with_Voids. currently you just have one.
)) as Days_with_Voids
January 5, 2011 at 6:15 am
Yes, As davidandrews13 mentioned closing parenthesis was missing for datediff function
January 5, 2011 at 6:40 am
Thanks Guys.
Done the trick
January 5, 2011 at 6:51 am
Messed up this post - let me explain it better what I am trying to achieve
January 7, 2011 at 4:19 am
Morning Guys,
I'm trying to do the following.
In my SELECT Statement I am using DATEDIFF to calculate some difference in dates. As below -
SELECT DATEDIFF(day, MAX(dbo.[IH_IH-LOCATION-STAT-HIST].[RECEIVED-KEYS])
,MAX(dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET])) AS 'Days_with_Voids'
,DATEDIFF(day, dbo.[IH_RE-TENANCY].[tncy-end]
,MAX(dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET])) AS 'Total_Days_to_RTL'
I now want to get the difference between these two fields.
What I have done is place ,'Days_with_Voids' - 'Total_Days_to_RTL' AS 'Difference'
However I get the following error -
Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals subtract, type equals varchar.
Can you please advise how I go about correcting this?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply