July 9, 2014 at 8:37 am
Hi there,
I have the following script -
SELECTDISTINCTLOC.[place-ref] AS 'Place Ref'
,PLA.address1 AS 'Address1'
,PLA.address2 AS 'Address2'
,PLA.address3 AS 'Address3'
,PLA.address4 AS 'Address4'
,PLA.address5 AS 'Address5'
,PLA.[post-code] AS 'Postcode'
,LOC.[location-sts] AS 'Location Status'
,LOC.[location-type] AS 'Location Type'
,LOC.scheme AS 'Scheme'
,LOC.[mgt-area] AS 'Managment Area'
,LOC.[accounts-company] AS 'Company'
,LOC.[rent-cst] AS 'Cost Centre'
,TEN.[tncy-status]
,MAX (TEN.[tenancy-ref]) AS 'Tenancy Ref'
FROM[dbo].[IH_IH-LOCATION] AS LOC
INNER JOIN
[dbo].[CORE_CO-PLACE] AS PLA
ONLOC.[place-ref] = PLA.[place-ref]
LEFT OUTER JOIN
[dbo].[IH_RE-TNCY-PLACE] AS TENP
ONPLA.[place-ref] = TENP.[place-ref]
LEFT OUTER JOIN
[dbo].[IH_RE-TENANCY] AS TEN
ONTENP.[tncy-sys-ref] = TEN.[tncy-sys-ref]
WHERELOC.[location-sts] NOT IN ('X','D')
ANDLOC.[place-ref] NOT LIKE 'FS%'
GROUP BYLOC.[place-ref]
,PLA.address1
,PLA.address2
,PLA.address3
,PLA.address4
,PLA.address5
,PLA.[post-code]
,LOC.[location-sts]
,LOC.[location-type]
,LOC.scheme
,LOC.[mgt-area]
,LOC.[accounts-company]
,LOC.[rent-cst]
,TEN.[tncy-status]
This pulls back all our location data along with all the tenancies that have been attached the the location -
Place Ref Address1 Address2 Address3 Address4 Address5 Postcode Location Status Location Type Scheme Managment Area Company Cost Centre tncy-status Tenancy Ref
-------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------- ---------------- ---------------- -------------------- ---------------- ---------------- ---------------------------------------- ---------------- --------------------------------------
2010010050 5 Alamein Avenue Chatham Kent ME5 0HZ O HOUSE MHSHOMES MHS 100 05/1216 CUR 201001005023
2010010100 10 Alamein Avenue Chatham Kent ME5 0HZ O HOUSE MHSHOMES MHS 100 05/1216 CUR 201001010034
2010010130 13 Alamein Avenue Chatham Kent ME5 0HZ O HOUSE MHSHOMES MHS 100 05/1216 CUR 2010010130004
2010010130 13 Alamein Avenue Chatham Kent ME5 0HZ O HOUSE MHSHOMES MHS 100 05/1216 FOR 201001013002
2010010160 16 Alamein Avenue Chatham Kent ME5 0HZ O HOUSE MHSHOMES MHS 100 05/1216 CUR 201001016009
2010010170 17 Alamein Avenue Chatham Kent ME5 0HZ V HOUSE MHSHOMES MHS 100 05/1216 FOR 201001017008
2010010390 39 Alamein Avenue Chatham Kent ME5 0HZ O HOUSE MHSHOMES MHS 100 05/1216 CUR 2010010390004
Above is a small example.
As you can see 13 Alamein Avenue is listed twice. This is because it pulls back the former and the current tenancy. I don't want to exclude former tenancies - so I want to pull back the most recent Tenancy Reference for each Place Reference.
I have a brain freeze on how I achieve this.
July 9, 2014 at 8:55 am
Something like this?
WITH Tenancies AS (
SELECTLOC.[place-ref] AS 'Place Ref'
,PLA.address1 AS 'Address1'
,PLA.address2 AS 'Address2'
,PLA.address3 AS 'Address3'
,PLA.address4 AS 'Address4'
,PLA.address5 AS 'Address5'
,PLA.[post-code] AS 'Postcode'
,LOC.[location-sts] AS 'Location Status'
,LOC.[location-type] AS 'Location Type'
,LOC.scheme AS 'Scheme'
,LOC.[mgt-area] AS 'Managment Area'
,LOC.[accounts-company] AS 'Company'
,LOC.[rent-cst] AS 'Cost Centre'
,TEN.[tncy-status]
,MAX (TEN.[tenancy-ref]) AS 'Tenancy Ref'
,ROW_NUMBER() OVER (PARTITION BY LOC.place_ref ORDER BY TEN.[tenancy-ref] DESC) RowNo
FROM[dbo].[IH_IH-LOCATION] AS LOC
INNER JOIN
[dbo].[CORE_CO-PLACE] AS PLA
ONLOC.[place-ref] = PLA.[place-ref]
LEFT OUTER JOIN
[dbo].[IH_RE-TNCY-PLACE] AS TENP
ONPLA.[place-ref] = TENP.[place-ref]
LEFT OUTER JOIN
[dbo].[IH_RE-TENANCY] AS TEN
ONTENP.[tncy-sys-ref] = TEN.[tncy-sys-ref]
WHERELOC.[location-sts] NOT IN ('X','D')
ANDLOC.[place-ref] NOT LIKE 'FS%'
)
SELECT * FROM Tenancies
WHERE RowNo = 1
John
July 9, 2014 at 8:57 am
Quick question, do you have some sample data?
😎
July 9, 2014 at 9:13 am
Thanks John,
Definately on the right track now -
changed it slightly to -
ROW_NUMBER() OVER (PARTITION BY LOC.[place-ref] ORDER BY TEN.[TNCY-START]) RowNo
It will now increment the RowNo if there are duplicate place-refs.
What I want though is just the Maximum row number for each place ref.
WITH Tenancies
AS
(
SELECTLOC.[place-ref] AS 'Place Ref'
,PLA.address1 AS 'Address1'
,PLA.address2 AS 'Address2'
,PLA.address3 AS 'Address3'
,PLA.address4 AS 'Address4'
,PLA.address5 AS 'Address5'
,PLA.[post-code] AS 'Postcode'
,LOC.[location-sts] AS 'Location Status'
,LOC.[location-type] AS 'Location Type'
,LOC.scheme AS 'Scheme'
,LOC.[mgt-area] AS 'Managment Area'
,LOC.[accounts-company] AS 'Company'
,LOC.[rent-cst] AS 'Cost Centre'
,TEN.[tncy-status]
,TEN.[tenancy-ref] AS 'Tenancy Ref'
,ROW_NUMBER() OVER (PARTITION BY LOC.[place-ref] ORDER BY TEN.[TNCY-START]) RowNo
FROM[dbo].[IH_IH-LOCATION] AS LOC
INNER JOIN
[dbo].[CORE_CO-PLACE] AS PLA
ONLOC.[place-ref] = PLA.[place-ref]
LEFT OUTER JOIN
[dbo].[IH_RE-TNCY-PLACE] AS TENP
ONPLA.[place-ref] = TENP.[place-ref]
LEFT OUTER JOIN
[dbo].[IH_RE-TENANCY] AS TEN
ONTENP.[tncy-sys-ref] = TEN.[tncy-sys-ref]
WHERELOC.[location-sts] NOT IN ('X','D')
ANDLOC.[place-ref] NOT LIKE 'FS%'
--ANDLOC.[place-ref] = '2010010130'
)
SELECT * FROM Tenancies
July 9, 2014 at 9:18 am
What I want though is just the Maximum row number for each place ref.
Do it like I showed you. Change the ORDER BY to DESC instead of ASC, so that you're looking for the minimum rather than the maximum. Since the minimum is always 1, you just [font="Courier New"]AND RowNo = 1[/font] to your WHERE clause.
John
July 9, 2014 at 9:21 am
Sorry John.
Thank you very much.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply