March 1, 2010 at 7:50 am
Hi there,
The following code is something I have put together that details certain information agaist tenants and occupants of our properties -
SELECT TEN.[tenancy-ref] AS 'Tenancy Reference'
,LOC.[place-ref]AS 'PlaceRef'
,PER.[PERSON-TITLE] + ' ' + PER.FORENAMES + ' '+ PER.SURNAME 'Person Name'
,TEN.[corr-name1] AS 'NameOnTenancy'
,CASE WHEN TENPER.[on-tncy] = 'yes'
THEN 'Tenant'
ELSE 'Occupant'
END AS 'TenantOrOccupant'
,TEN.[tncy-type] AS 'TenancyType'
,TEN.[tncy-cat] AS 'TenancyCategory'
,TEN.[tncy-start]AS 'TenancyStartDate'
,TEN.[curr-balance]AS 'Current Balance'
,LOC.[estate-code]AS 'EstateCode'
,LOC.[location-type]AS 'LocationType'
,LOC.[building-type]AS 'BuildingType'
,LOC.[mgt-area]AS 'ManagementType'
,LOC.[scheme] AS 'Scheme'
,LOC.[arrears-ofcr]AS 'Patch / Arrears Officer'
,LOC.[bedrooms] AS 'NumberOfBedrooms'
,PLA.[address1] + ',' + PLA.[address2] + ',' + PLA.[address3] AS 'Address'
,PLA.[post-code] AS 'PostCode'
,PER.[D-O-B] AS 'DateOfBirth'
,DATEDIFF(year, PER.[D-O-B], GETDATE()) - CASE
WHEN MONTH(GETDATE()) > MONTH(PER.[D-O-B])
THEN 0
WHEN MONTH(GETDATE()) = MONTH(PER.[D-O-B])
AND DAY(GETDATE()) >= DAY(PER.[D-O-B])
THEN 0
ELSE 1
END AS 'Age'
FROM dbo.[IH_RE-TENANCY] AS TEN
INNER JOIN
dbo.[IH_RE-TNCY-PLACE] AS TENPL
ON
TEN.[tncy-sys-ref] = TENPL.[tncy-sys-ref]
INNER JOIN
dbo.[IH_IH-LOCATION] AS LOC
ON
TENPL.[place-ref]= LOC.[place-ref]
INNER JOIN
dbo.[CORE_CO-PLACE] AS PLA
ON
LOC.[place-ref] = PLA.[place-ref]
INNER JOIN
dbo.[IH_RE-TNCY-PERSON] AS TENPER
ON
TEN.[tncy-sys-ref] = TENPER.[tncy-sys-ref]
INNER JOIN
dbo.[CORE_CO-PERSON] AS PER
ON
TENPER.[person-ref] = PER.[person-ref]
LEFT OUTER JOIN
dbo.[IH_EM-ORIGINATOR]AS ORIG
ON
LOC.[place-ref]= ORIG.[place-ref]
WHERE TEN.[tncy-status] = 'CUR'
ANDTEN.[tenancy-ref] not like 'FS%'
ANDTENPL.[prime-place] = 'yes'
ANDTENPER.[end-date] IS NULL
ORDER BY 1
What I have also been asked to do is to flag whether any of these tenants have raised a complaint in the last financial year (01-04-2009 - 31-03-2010)
The code for that is -
(SELECT DISTINCTCOM.[comm-reference],
ORIG.[PLACE-REF]
FROM dbo.[IH_EM-COMMUNICATION]AS COM
INNER JOIN
dbo.[IH_EM-ORIGINATOR]AS ORIG
ON
COM.[comm-reference] = ORIG.[comm-reference]
WHERE COM.[comm-type] = 'COMP'
AND CONVERT(DATETIME,COM.[crt-date] ) >= '01/04/2009'
AND ORIG.[PERSON-TYPE] = 'O')
I want to incorporate this in my original code. So basically if they have logged a complaint - pull back Logged Complaint but leave it NULL if they haven't. I am having great difficulty adding this as a sub query to my exsisting query. Please advise.
Thanks
Thanks
March 1, 2010 at 8:46 am
Ryan i think you'll want to left join the whole sub query and use a calculated column to return whether there was a complaint or not; i did not see a specific field that looked obvious to me that should be returned as the "Logged Complaint " itself; would that be the .[comm-reference] field in the subquery?
LEFT OUTER JOIN (
(SELECT DISTINCT COM.[comm-reference],
ORIG.[PLACE-REF]
FROM dbo.[IH_EM-COMMUNICATION]AS COM
INNER JOIN
dbo.[IH_EM-ORIGINATOR]AS ORIG
ON
COM.[comm-reference] = ORIG.[comm-reference]
WHERE COM.[comm-type] = 'COMP'
AND CONVERT(DATETIME,COM.[crt-date] ) >= '01/04/2009'
AND ORIG.[PERSON-TYPE] = 'O'
) COMPLAINTS
ON LOC.[place-ref] = COMPLAINTS.[PLACE-REF]
you know your data better than i do, I thought you'd join to teh table aliased LOC, but you would know which is the more appropriate table.
this is my final version of your SQL:
SELECT CASE WHEN COMPLAINTS.[PLACE-REF] IS NULL THEN 'N' ELSE 'Y' END AS 'RaisedComplaint',
TEN.[tenancy-ref] AS 'Tenancy Reference'
,LOC.[place-ref]AS 'PlaceRef'
,PER.[PERSON-TITLE] + ' ' + PER.FORENAMES + ' '+ PER.SURNAME 'Person Name'
,TEN.[corr-name1] AS 'NameOnTenancy'
,CASE WHEN TENPER.[on-tncy] = 'yes'
THEN 'Tenant'
ELSE 'Occupant'
END AS 'TenantOrOccupant'
,TEN.[tncy-type] AS 'TenancyType'
,TEN.[tncy-cat] AS 'TenancyCategory'
,TEN.[tncy-start]AS 'TenancyStartDate'
,TEN.[curr-balance]AS 'Current Balance'
,LOC.[estate-code]AS 'EstateCode'
,LOC.[location-type]AS 'LocationType'
,LOC.[building-type]AS 'BuildingType'
,LOC.[mgt-area]AS 'ManagementType'
,LOC.[scheme] AS 'Scheme'
,LOC.[arrears-ofcr]AS 'Patch / Arrears Officer'
,LOC.[bedrooms] AS 'NumberOfBedrooms'
,PLA.[address1] + ',' + PLA.[address2] + ',' + PLA.[address3] AS 'Address'
,PLA.[post-code] AS 'PostCode'
,PER.[D-O-B] AS 'DateOfBirth'
,DATEDIFF(year, PER.[D-O-B], GETDATE()) - CASE
WHEN MONTH(GETDATE()) > MONTH(PER.[D-O-B])
THEN 0
WHEN MONTH(GETDATE()) = MONTH(PER.[D-O-B])
AND DAY(GETDATE()) >= DAY(PER.[D-O-B])
THEN 0
ELSE 1
END AS 'Age'
FROM dbo.[IH_RE-TENANCY] AS TEN
INNER JOIN
dbo.[IH_RE-TNCY-PLACE] AS TENPL
ON
TEN.[tncy-sys-ref] = TENPL.[tncy-sys-ref]
INNER JOIN
dbo.[IH_IH-LOCATION] AS LOC
ON
TENPL.[place-ref]= LOC.[place-ref]
INNER JOIN
dbo.[CORE_CO-PLACE] AS PLA
ON
LOC.[place-ref] = PLA.[place-ref]
INNER JOIN
dbo.[IH_RE-TNCY-PERSON] AS TENPER
ON
TEN.[tncy-sys-ref] = TENPER.[tncy-sys-ref]
INNER JOIN
dbo.[CORE_CO-PERSON] AS PER
ON
TENPER.[person-ref] = PER.[person-ref]
LEFT OUTER JOIN
dbo.[IH_EM-ORIGINATOR]AS ORIG
ON
LOC.[place-ref]= ORIG.[place-ref]
LEFT OUTER JOIN
(SELECT DISTINCT COM.[comm-reference],
ORIG.[PLACE-REF]
FROM dbo.[IH_EM-COMMUNICATION]AS COM
INNER JOIN
dbo.[IH_EM-ORIGINATOR]AS ORIG
ON
COM.[comm-reference] = ORIG.[comm-reference]
WHERE COM.[comm-type] = 'COMP'
AND CONVERT(DATETIME,COM.[crt-date] ) >= '01/04/2009'
AND ORIG.[PERSON-TYPE] = 'O'
) COMPLAINTS
ON LOC.[place-ref] = COMPLAINTS.[PLACE-REF]
WHERE TEN.[tncy-status] = 'CUR'
AND TEN.[tenancy-ref] not like 'FS%'
AND TENPL.[prime-place] = 'yes'
AND TENPER.[end-date] IS NULL
ORDER BY 1
Lowell
March 2, 2010 at 7:24 am
Many Thanks.
I have played about with the code and this is now working.
Final Code as below -
SELECT TEN.[tenancy-ref] AS TenancyReference
, LOC.[place-ref] AS PlaceReference
, PER.[PERSON-REF] AS PersonReference
, PER.[PERSON-TITLE] + ' ' + PER.FORENAMES + ' ' + PER.SURNAME AS [Person Name]
, TEN.[corr-name1] AS NameOnTenancy
, CASE WHEN TENPER.[on-tncy] = 'yes' THEN 'Tenant' ELSE 'Occupant' END AS TenantOrOccupant
, TEN.[tncy-type] AS TenancyType
, TEN.[tncy-cat] AS TenancyCategory
, TEN.[tncy-start] AS TenancyStartDate
, TEN.[curr-balance] AS [Current Balance]
, LOC.[estate-code] AS EstateCode
, LOC.[location-type] AS LocationType
, LOC.[building-type] AS BuildingType
, LOC.[mgt-area] AS ManagementType
, LOC.scheme AS Scheme
, LOC.[arrears-ofcr] AS [Patch / Arrears Officer]
, LOC.bedrooms AS NumberOfBedrooms
, PLA.address1 + ',' + PLA.address2 + ',' + PLA.address3 AS Address, PLA.[post-code] AS PostCode
, PER.[D-O-B] AS DateOfBirth, DATEDIFF([year]
, PER.[D-O-B], GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(PER.[D-O-B])
THEN 0 WHEN MONTH(GETDATE()) = MONTH(PER.[D-O-B]) AND
DAY(GETDATE()) >= DAY(PER.[D-O-B])
THEN 0 ELSE 1 END AS Age
, PER.GENDER AS Gender
, PER.[ORIGIN-CODE] AS OriginCode
, CASE WHEN EXTEN.[POTENTIALLY-VULNERABLE] IS NULL
THEN 'No' ELSE 'Yes'
END AS Vulnerable
,CASE WHEN COMPLAINTS.[PERSON-REF] IS NULL
THEN 'No' ELSE 'Yes'
END AS LoggedComplaint
FROM
dbo.[IH_RE-TENANCY] TEN
INNER JOIN
dbo.[IH_RE-TNCY-PLACE] TENPL
ON
TEN.[tncy-sys-ref] = TENPL.[tncy-sys-ref]
INNER JOIN
dbo.[IH_IH-LOCATION] LOC
ON
TENPL.[place-ref] = LOC.[place-ref]
INNER JOIN
dbo.[CORE_CO-PLACE] PLA
ON
LOC.[place-ref] = PLA.[place-ref]
INNER JOIN
dbo.[IH_RE-TNCY-PERSON] TENPER
ON
TEN.[tncy-sys-ref] = TENPER.[TNCY-SYS-REF]
INNER JOIN
dbo.[CORE_CO-PERSON] PER
ON
TENPER.[PERSON-REF] = PER.[PERSON-REF]
LEFT OUTER JOIN
dbo.[CORE_CO-PERSON-EXTENSION] EXTEN
ON
EXTEN.[PERSON-REF] = PER.[PERSON-REF]
LEFT OUTER JOIN
(SELECT DISTINCTORIG.[PERSON-REF]
FROM dbo.[IH_EM-COMMUNICATION]AS COM
INNER JOIN
dbo.[IH_EM-ORIGINATOR]AS ORIG
ON
COM.[comm-reference] = ORIG.[comm-reference]
WHERE COM.[comm-type] = 'COMP'
AND CONVERT(DATETIME,COM.[crt-date] ) >= '01/04/2009'
AND ORIG.[PERSON-TYPE] = 'O'
AND ORIG.[PLACE-REF] not like '*%') COMPLAINTS
ON PER.[PERSON-REF] = COMPLAINTS.[PERSON-REF]
WHERE (TEN.[tncy-status] = 'CUR')
AND (TEN.[tenancy-ref] NOT LIKE 'FS%')
AND (TENPL.[prime-place] = 'yes')
AND (TENPER.[END-DATE] IS NULL)
ORDER BY TEN.[tenancy-ref]
March 2, 2010 at 7:38 am
Following on from this I was wondering if you could do the following -
I am set to save this SQL Code as a View.
This View will be attached to Excel in order for some of our staff to open and refresh the data.
Once they have the base data, they can then go about creating the views from theirselves.
Can you include parameters in the TSQL?
In other words if they were to open the excel file - hit the refresh, could they be prompted to pick a specific Location Type for example.
So if they entered Garage - it would only pull back garages.
The most use I can think of this is maybe the user entering a date range?
Thanks
March 3, 2010 at 2:29 pm
You can't link Excel to a stored procedure, so no parameters are allowed coming from SQL Server. However, you can use VB in Excel to build your query based on some values you have coded into some combo boxes at the top of your sheet.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
March 3, 2010 at 3:44 pm
I know you can use parameters in Excel (or at least you could in older versions, not sure about the newer ones), but I can't remember quite how to do it.
There was a query builder involved and I know I used to have excel prompt me for parameters to send in as filters when I did a refresh. That said, I do believe it was building the query and passing it in, but I know I didn't use any VB to do it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply