April 15, 2010 at 8:02 am
Hi there,
Can you guys help - I'm useless at TSQL.
I have the following statment -
SELECT dbo.[IH_RE-TENANCY].[tenancy-ref]
,dbo.[IH_IH-LOCATION].[place-ref]
,dbo.[IH_RE-TENANCY].[tncy-start]
from dbo.[IH_RE-TENANCY]
INNER JOIN
dbo.[IH_RE-TNCY-PLACE]
ON dbo.[IH_RE-TENANCY].[tncy-sys-ref] = dbo.[IH_RE-TNCY-PLACE].[tncy-sys-ref]
INNER JOIN
dbo.[IH_IH-LOCATION]
ON dbo.[IH_RE-TNCY-PLACE].[place-ref] = dbo.[IH_IH-LOCATION].[place-ref]
where dbo.[IH_IH-LOCATION].[place-ref] = '2010020080'
ORDER BY dbo.[IH_IH-LOCATION].[place-ref]
It pulls me back the following results -
tenancy-ref place-ref tncy-start
201002008004620100200802006-09-04 00:00:00.000
201002008004720100200802008-05-19 00:00:00.000
201002008004820100200802009-11-16 00:00:00.000
20100200804520100200801998-05-18 00:00:00.000
This is pulling me back 4 results, however I only want it to pull back 1 result for each place-ref.
So I would only want to see -
201002008004820100200802009-11-16 00:00:00.000
as it is the date that is the most recent.
Please help.
Once I have this part right - I need to incoperate it into an exsisting query
April 15, 2010 at 8:04 am
and the question is.....?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 15, 2010 at 8:09 am
You're pretty close. Check out GROUP BY in BOL.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 15, 2010 at 9:06 am
SELECT dbo.[IH_RE-TENANCY].[tenancy-ref]
,dbo.[IH_IH-LOCATION].[place-ref]
,MAX(dbo.[IH_RE-TENANCY].[tncy-start])
from dbo.[IH_RE-TENANCY]
INNER JOIN
dbo.[IH_RE-TNCY-PLACE]
ON dbo.[IH_RE-TENANCY].[tncy-sys-ref] = dbo.[IH_RE-TNCY-PLACE].[tncy-sys-ref]
INNER JOIN
dbo.[IH_IH-LOCATION]
ON dbo.[IH_RE-TNCY-PLACE].[place-ref] = dbo.[IH_IH-LOCATION].[place-ref]
where dbo.[IH_IH-LOCATION].[place-ref] = '2010020080'
ORDER BY dbo.[IH_IH-LOCATION].[place-ref]
GROUP BY dbo.[IH_IH-LOCATION].[place-ref]
Throwing back error message -
Server: Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'GROUP'.
April 15, 2010 at 10:30 am
The GROUP BY clause goes before the ORDER BY clause.
Edit: You'll also need to add dbo.[IH_RE-TENANCY].[tenancy-ref] to the GROUP BY clause... try it without it and you'll get a new error.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 16, 2010 at 3:40 am
I have now got the query working how I would want it -
SELECT MAX (dbo.[IH_RE-TENANCY].[tncy-start])
,dbo.[IH_IH-LOCATION].[place-ref]
--,dbo.[IH_RE-TENANCY].[gross-rent]
from dbo.[IH_RE-TENANCY]
INNER JOIN
dbo.[IH_RE-TNCY-PLACE]
ON dbo.[IH_RE-TENANCY].[tncy-sys-ref] = dbo.[IH_RE-TNCY-PLACE].[tncy-sys-ref]
INNER JOIN
dbo.[IH_IH-LOCATION]
ON dbo.[IH_RE-TNCY-PLACE].[place-ref] = dbo.[IH_IH-LOCATION].[place-ref]
Where dbo.[IH_IH-LOCATION].[location-sts] not in ('X','D')
AND dbo.[IH_IH-LOCATION].[Location-Type] not in ('GARAGE', 'UNIT', 'OFFICE', 'RENTONLY','COMHALL','FOYER','STUDENT','KENTAUST')
AND dbo.[IH_IH-LOCATION].[scheme] not in ('LEASED','MARKET','MEDWAY','RENTBUY','SHARED')
GROUP BY dbo.[IH_IH-LOCATION].[place-ref]
--,dbo.[IH_RE-TENANCY].[gross-rent]
ORDER BY dbo.[IH_IH-LOCATION].[place-ref]
It appears to do what I want it to - however I am trying to incorparte the top query into this query -
SELECT PLACE.[place-ref] AS 'Corr. Property Name'
,PLACE.[number] AS 'Corr. Street number'
,CAST(CASE
WHEN PLACE.[saon] IS NULL
THEN PLACE.[name]
ELSE PLACE.[saon] + PLACE.[name] END AS VARCHAR(45))AS 'Corr. SubStreet'
,PLACE.[street-name]AS 'Corr. Street'
,PLACE.[address3]AS 'Corr. Address 2'
,PLACE.[address4]AS 'Corr. Address 3'
,PLACE.[address5]AS 'Corr. Address 4'
,PLACE.[post-code] AS 'Corr. Postcode'
,LOC.[max-occupants] AS 'Bedspaces'
,LOC.[bedrooms] AS 'Bedrooms'
,LOC.[floor]AS 'Floor'
,CAST(CASE
WHEN loc.[location-type] IN ('HOUSE', 'HOUSEGAR') THEN 'H'
WHEN loc.[location-type] IN ('BUNGALOW','BUNGSHEL') THEN 'B'
WHEN loc.[location-type] IN ('FLAT', 'FLATGAR','FLATSHEL') THEN 'F'
WHEN loc.[location-type] = 'MAISON' THEN 'M'
WHEN loc.[location-type] IN ('STUDIO','STUDSHEL') THEN 'S'
ELSE '1' END AS VARCHAR(3))AS 'Property Type'
,CAST(CASE
WHEN loc.[location-type] IN ('BUNGSHEL', 'FLATSHEL','STUDSHEL') THEN 'SHL'
ELSE '' END AS VARCHAR(3))AS 'Sheltered'
FROM dbo.[CORE_CO-PLACE] AS PLACE
LEFT OUTER JOIN
dbo.[IH_IH-LOCATION] AS LOC
ON
PLACE.[place-ref] = LOC.[place-ref]
/* LEFT OUTER JOIN
dbo.[IH_RE-TNCY-PLACE] AS TENPLA
ON
LOC.[place-ref] = TENPLA.[place-ref]
LEFT OUTER JOIN
dbo.[IH_RE-TENANCY] AS TEN
ON
TENPLA.[tncy-sys-ref] = TEN.[tncy-sys-ref] */
WHERE PLACE.[internal] = 'yes'
AND
LOC.[location-sts] not in ('X','D')
AND LOC.[Location-Type] not in ('GARAGE', 'UNIT', 'OFFICE', 'RENTONLY','COMHALL','FOYER','STUDENT','KENTAUST')
ANDPLACE.[PLACE-REF] <> '2DUMMY'
AND LOC.[scheme] not in ('LEASED','MARKET','MEDWAY','RENTBUY','SHARED')
ORDER BYPLACE.[place-ref]
Basically the second TSQL (call it SQL2) is pulling back a list of all our property data.
What I want to do is from the property tables -
dbo.[IH_IH-LOCATION] AS LOC and dbo.[CORE_CO-PLACE] AS PLACE
is link off to the tenancy tables in order to report on the current rent.
/* LEFT OUTER JOIN
dbo.[IH_RE-TNCY-PLACE] AS TENPLA
ON
LOC.[place-ref] = TENPLA.[place-ref]
LEFT OUTER JOIN
dbo.[IH_RE-TENANCY] AS TEN
ON
TENPLA.[tncy-sys-ref] = TEN.[tncy-sys-ref] */
My Property query pulls back 7142 rows.
However when I link to the tenancy table it nearly double my results.
This is due to many tenancies being linked to one place.
I therefore want to link my 7142 properties with the most recent tenancy for that location.
I have no idea how to do this - my attempt below failed miserably -
SELECT PLACE.[place-ref] AS 'Corr. Property Name'
,PLACE.[number] AS 'Corr. Street number'
,CAST(CASE
WHEN PLACE.[saon] IS NULL
THEN PLACE.[name]
ELSE PLACE.[saon] + PLACE.[name] END AS VARCHAR(45))AS 'Corr. SubStreet'
,PLACE.[street-name]AS 'Corr. Street'
,PLACE.[address3]AS 'Corr. Address 2'
,PLACE.[address4]AS 'Corr. Address 3'
,PLACE.[address5]AS 'Corr. Address 4'
,PLACE.[post-code] AS 'Corr. Postcode'
,LOC.[max-occupants] AS 'Bedspaces'
,LOC.[bedrooms] AS 'Bedrooms'
,LOC.[floor]AS 'Floor'
,CAST(CASE
WHEN loc.[location-type] IN ('HOUSE', 'HOUSEGAR') THEN 'H'
WHEN loc.[location-type] IN ('BUNGALOW','BUNGSHEL') THEN 'B'
WHEN loc.[location-type] IN ('FLAT', 'FLATGAR','FLATSHEL') THEN 'F'
WHEN loc.[location-type] = 'MAISON' THEN 'M'
WHEN loc.[location-type] IN ('STUDIO','STUDSHEL') THEN 'S'
ELSE '1' END AS VARCHAR(3))AS 'Property Type'
,CAST(CASE
WHEN loc.[location-type] IN ('BUNGSHEL', 'FLATSHEL','STUDSHEL') THEN 'SHL'
ELSE '' END AS VARCHAR(3))AS 'Sheltered'
,(SELECT MAX (dbo.[IH_RE-TENANCY].[tncy-start])
,dbo.[IH_IH-LOCATION].[place-ref]
--,dbo.[IH_RE-TENANCY].[gross-rent]
from dbo.[IH_RE-TENANCY]
INNER JOIN
dbo.[IH_RE-TNCY-PLACE]
ON dbo.[IH_RE-TENANCY].[tncy-sys-ref] = dbo.[IH_RE-TNCY-PLACE].[tncy-sys-ref]
INNER JOIN
dbo.[IH_IH-LOCATION]
ON dbo.[IH_RE-TNCY-PLACE].[place-ref] = dbo.[IH_IH-LOCATION].[place-ref]
Where dbo.[IH_IH-LOCATION].[location-sts] not in ('X','D')
AND dbo.[IH_IH-LOCATION].[Location-Type] not in ('GARAGE', 'UNIT', 'OFFICE', 'RENTONLY','COMHALL','FOYER','STUDENT','KENTAUST')
AND dbo.[IH_IH-LOCATION].[scheme] not in ('LEASED','MARKET','MEDWAY','RENTBUY','SHARED')
GROUP BY dbo.[IH_IH-LOCATION].[place-ref])
--,dbo.[IH_RE-TENANCY].[gross-rent]
as test
FROM dbo.[CORE_CO-PLACE] AS PLACE
LEFT OUTER JOIN
dbo.[IH_IH-LOCATION] AS LOC
ON
PLACE.[place-ref] = LOC.[place-ref]
/* LEFT OUTER JOIN
dbo.[IH_RE-TNCY-PLACE] AS TENPLA
ON
LOC.[place-ref] = TENPLA.[place-ref]
LEFT OUTER JOIN
dbo.[IH_RE-TENANCY] AS TEN
ON
TENPLA.[tncy-sys-ref] = TEN.[tncy-sys-ref] */
WHERE PLACE.[internal] = 'yes'
AND
LOC.[location-sts] not in ('X','D')
AND LOC.[Location-Type] not in ('GARAGE', 'UNIT', 'OFFICE', 'RENTONLY','COMHALL','FOYER','STUDENT','KENTAUST')
ANDPLACE.[PLACE-REF] <> '2DUMMY'
AND LOC.[scheme] not in ('LEASED','MARKET','MEDWAY','RENTBUY','SHARED')
ORDER BYPLACE.[place-ref]
I got this error - Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
I really don't know how to achieve what I want? Please help.
Thanks
April 16, 2010 at 5:09 am
Move the sub-query from the select's column list, and make it a derived table in the from clause, with the appropriate join condition to the [place-ref] column. Name the aggregrated field, and use that in the select's column list.
That looks like it would handle it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 16, 2010 at 5:29 am
Hi Wayne, your help is appreaciated, but I have no idea how to go about doing what you suggest.
April 16, 2010 at 5:32 am
What I just posted still doesn't work.
Can someone please explain how I in corporate a subquery into the FROM syntax or create a derived table
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply