May 12, 2011 at 4:51 am
Hello there -
I have been working on the following query -
SELECTTOP 100 PERCENT [ID]
,[Alias]
,[Title]
,[Surname]
,[Forename]
,[FormerName]
,[Initials]
,[Address1]
,[Address2]
,[Address3]
,[Address4]
,[PostCode]
,[Town]
,[County]
,[Country]
,[DaytimePhone]
,[EveningPhone]
,[WorkPhone]
,[Mobile]
,[Fax]
,[Email]
,[NINumber]
,[DOB]
,[AgeRangeID] = CASE
WHEN [AGE] <10 THEN '1'
WHEN [AGE] BETWEEN 11 AND 15 THEN '2'
WHEN [AGE] BETWEEN 16 AND 17 THEN '3'
WHEN [AGE] BETWEEN 18 AND 25 THEN '4'
WHEN [AGE] BETWEEN 26 AND 30 THEN '5'
WHEN [AGE] BETWEEN 31 AND 40 THEN '6'
WHEN [AGE] BETWEEN 41 AND 50 THEN '7'
WHEN [AGE] BETWEEN 51 AND 60 THEN '8'
WHEN [AGE] BETWEEN 61 AND 70 THEN '9'
WHEN [AGE] BETWEEN 71 AND 80 THEN '10'
WHEN [AGE] >80 THEN '11'
ELSE '0' END
,[BirthPlace]
,[Gender]
,[EthnicOrigin] = CASE
WHEN [EthnicOrigin] = 'W1' THEN '1'
WHEN [EthnicOrigin] = 'W2' THEN '2'
WHEN [EthnicOrigin] = 'W3' THEN '3'
WHEN [EthnicOrigin] = 'M1' THEN '4'
WHEN [EthnicOrigin] = 'M2' THEN '5'
WHEN [EthnicOrigin] = 'M3' THEN '6'
WHEN [EthnicOrigin] = 'M4' THEN '7'
WHEN [EthnicOrigin] = 'A3' THEN '8'
WHEN [EthnicOrigin] = 'A4' THEN '9'
WHEN [EthnicOrigin] = 'A1' THEN '10'
WHEN [EthnicOrigin] = 'A2' THEN '11'
WHEN [EthnicOrigin] = 'B1' THEN '12'
WHEN [EthnicOrigin] = 'B2' THEN '13'
WHEN [EthnicOrigin] = 'B3' THEN '14'
WHEN [EthnicOrigin] = 'C1' THEN '15'
WHEN [EthnicOrigin] = 'C2' THEN '16'
WHEN [EthnicOrigin] = 'T1' THEN '17'
WHEN [EthnicOrigin] = 'R' THEN '18'
WHEN [EthnicOrigin] = 'T2' THEN '19'
WHEN [EthnicOrigin] = 'T3' THEN '20'
WHEN [EthnicOrigin] = 'T4' THEN '21'
ELSE '0' END
,[ReligionID] = CASE
WHEN [ReligionID] = 'BUD' THEN '1'
WHEN [ReligionID] = 'ROM' THEN '2'
WHEN [ReligionID] = 'CHR' THEN '3'
WHEN [ReligionID] = 'HIN' THEN '4'
WHEN [ReligionID] = 'MUS' THEN '5'
WHEN [ReligionID] = 'JEW' THEN '6'
WHEN [ReligionID] = 'NOR' THEN '7'
WHEN [ReligionID] = 'NOT' THEN '8'
WHEN [ReligionID] = 'OTH' THEN '9'
WHEN [ReligionID] = 'SIK' THEN '10'
WHEN [ReligionID] = 'PNA' THEN '11'
ELSE '0' END
,[SexualOrientationID] = CASE
WHEN [SexualOrientationID] = 'BIS' THEN '1'
WHEN [SexualOrientationID] = 'GAY' THEN '2'
WHEN [SexualOrientationID] = 'HET' THEN '3'
WHEN [SexualOrientationID] = 'LES' THEN '6'
WHEN [SexualOrientationID] = 'PNA' THEN '7'
ELSE '0' END
,[DisabilityID]
,[DisabilityDetails]
,[IsTenant]
,[TenancyNo]
,[TenancyTypeID] = CASE
WHEN [TenancyTypeID] IN ('***','DASS','GFAS','LKA') THEN '1'
WHEN [TenancyTypeID] IN ('ASTT','AST','DAST','GRAT','GFAT','STAF') THEN '2'
WHEN [TenancyTypeID] IN ('PROT','DPRO','GFPR') THEN '3'
--WHEN [TenancyTypeID] IN ('***','DASS','GFAS') THEN '4'
WHEN [TenancyTypeID] IN ('LEA','DFRE','SOWN','CMSL','RSLH','RTBF') THEN '5'
--WHEN [TenancyTypeID] IN ('***','DASS','GFAS') THEN '6'
WHEN [TenancyTypeID] = 'FOYE' THEN '7'
WHEN [TenancyTypeID] IN ('GARL','GARP','GARF','GARM') THEN '8'
WHEN [TenancyTypeID] = 'BASS' THEN '9'
WHEN [TenancyTypeID] = 'GF!N' THEN '10'
WHEN [TenancyTypeID] IN ('RTH','MRFT') THEN '11'
ELSE '0' END
,[TenancyCommenced]
,[IsOrderNone]
,[IsOrderForRent]
,[IsOrderForOther]
,[IsApproachable] = CASE
WHEN [IsApproachable] = 'yes' THEN '0'
ELSE '1' END
,[UnApproachableReason]
FROM
(
SELECT TEN.[tncy-sys-ref] + PER.[GENDER] AS 'ID'
,'' AS 'Alias'
,PER.[PERSON-TITLE] AS 'Title'
,PER.[Surname] AS 'Surname'
,PER.[FORENAMES] AS 'Forename'
,'' AS 'FormerName'
,PER.[INITIALS] AS 'Initials'
,PLA.address1 AS 'Address1'
,PLA.address2 AS 'Address2'
,PLA.address3 AS 'Address3'
,'' AS 'Address4'
,PLA.[post-code] AS 'PostCode'
,PLA.address4 AS 'Town'
,PLA.address5 AS 'County'
,'United Kingdom' AS 'Country'
,TELE.[CONTACT-DETAILS] AS 'DaytimePhone'
,TELE.[CONTACT-DETAILS] AS 'EveningPhone'
,WORKTELE.[CONTACT-DETAILS] AS 'WorkPhone'
,MOB.[CONTACT-DETAILS]AS 'Mobile'
,FAX.[CONTACT-DETAILS]AS 'Fax'
,EMAIL.[CONTACT-DETAILS]AS 'Email'
,PER.[NHI-NO] AS 'NINumber'
,PER.[D-O-B] AS 'DOB'
,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
,'' AS 'BirthPlace'
,CASE WHEN PER.[GENDER] = 'M' THEN '1'
WHEN PER.[GENDER] = 'F' THEN '2'
ELSE '0'END AS 'Gender'
,PER.[ORIGIN-CODE] AS 'EthnicOrigin'
,REL.[SUB-ATTRIB-CODE] AS 'ReligionID'
,SEX.[SUB-ATTRIB-CODE] AS 'SexualOrientationID'
,'' AS DisabilityID
,'' AS DisabilityDetails
,'1' AS 'IsTenant'
,TEN.[tenancy-ref] AS 'TenancyNo'
,TEN.[tncy-type] AS 'TenancyTypeID'
,TEN.[tncy-start] AS 'TenancyCommenced'
,'1' AS 'IsOrderNone'
,'0' AS 'IsOrderForRent'
,'0' AS 'IsOrderForOther'
,EXTEN.[POTENTIALLY-VIOLENT] AS 'IsApproachable'
,CASE WHEN EXTEN.[POTENTIALLY-VIOLENT] = 'yes' THEN 'Potentially Violent'
ELSE ''END AS 'UnApproachableReason'
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]
/* In order to get the lowest priority of the TEL*/
LEFT OUTER JOIN
(
SELECT PERS.[PERSON-REF]
,PERSCONT.[CONTACT-DETAILS]
,PERSCONT.[PRIORITY] AS Priority
FROM dbo.[CORE_CO-PERSON] AS PERS
INNER JOIN dbo.[CORE_CO-PERSON-CONTACT] AS PERSCONT
ON PERS.[PERSON-REF] = PERSCONT.[PERSON-REF]
INNER JOIN (
SELECT [PERSON-REF]
,MIN([PRIORITY]) AS [PRIORITY]
FROM dbo.[CORE_CO-PERSON-CONTACT]
WHERE [CONTACT-TYPE]= 'TEL'
GROUP BY [PERSON-REF]
) AS x
ON PERSCONT.[PERSON-REF] = x.[PERSON-REF]
AND PERSCONT.[PRIORITY] = x.[PRIORITY]
GROUP BY PERS.[PERSON-REF], PERSCONT.[CONTACT-DETAILS],PERSCONT.PRIORITY
) AS TELE
ONPER.[PERSON-REF] = TELE.[PERSON-REF]
/* In order to get the lowest priority of the WORK TELEPHONE */
LEFT OUTER JOIN
(
SELECT PERS.[PERSON-REF]
,PERSCONT.[CONTACT-DETAILS]
,PERSCONT.[PRIORITY] AS Priority
FROM dbo.[CORE_CO-PERSON] AS PERS
INNER JOIN dbo.[CORE_CO-PERSON-CONTACT] AS PERSCONT
ON PERS.[PERSON-REF] = PERSCONT.[PERSON-REF]
INNER JOIN (
SELECT [PERSON-REF]
,MIN([PRIORITY]) AS [PRIORITY]
FROM dbo.[CORE_CO-PERSON-CONTACT]
WHERE [CONTACT-TYPE]= 'WTL'
GROUP BY [PERSON-REF]
) AS x
ON PERSCONT.[PERSON-REF] = x.[PERSON-REF]
AND PERSCONT.[PRIORITY] = x.[PRIORITY]
GROUP BY PERS.[PERSON-REF], PERSCONT.[CONTACT-DETAILS],PERSCONT.PRIORITY ) AS WORKTELE
ONPER.[PERSON-REF] = WORKTELE.[PERSON-REF]
/* In order to get the lowest priority of the MOBILE */
LEFT OUTER JOIN
(
SELECT PERS.[PERSON-REF]
,PERSCONT.[CONTACT-DETAILS]
,PERSCONT.[PRIORITY] AS Priority
FROM dbo.[CORE_CO-PERSON] AS PERS
INNER JOIN dbo.[CORE_CO-PERSON-CONTACT] AS PERSCONT
ON PERS.[PERSON-REF] = PERSCONT.[PERSON-REF]
INNER JOIN (
SELECT [PERSON-REF]
,MIN([PRIORITY]) AS [PRIORITY]
FROM dbo.[CORE_CO-PERSON-CONTACT]
WHERE [CONTACT-TYPE]= 'MBL'
GROUP BY [PERSON-REF]
) AS x
ON PERSCONT.[PERSON-REF] = x.[PERSON-REF]
AND PERSCONT.[PRIORITY] = x.[PRIORITY]
GROUP BY PERS.[PERSON-REF], PERSCONT.[CONTACT-DETAILS],PERSCONT.PRIORITY
) AS MOB
ONPER.[PERSON-REF] = MOB.[PERSON-REF]
/* In order to get the lowest priority of the FAX*/
LEFT OUTER JOIN
(
SELECT PERS.[PERSON-REF]
,PERSCONT.[CONTACT-DETAILS]
,PERSCONT.[PRIORITY] AS Priority
FROM dbo.[CORE_CO-PERSON] AS PERS
INNER JOIN dbo.[CORE_CO-PERSON-CONTACT] AS PERSCONT
ON PERS.[PERSON-REF] = PERSCONT.[PERSON-REF]
INNER JOIN (
SELECT [PERSON-REF]
,MIN([PRIORITY]) AS [PRIORITY]
FROM dbo.[CORE_CO-PERSON-CONTACT]
WHERE [CONTACT-TYPE]= 'FAX'
GROUP BY [PERSON-REF]
) AS x
ON PERSCONT.[PERSON-REF] = x.[PERSON-REF]
AND PERSCONT.[PRIORITY] = x.[PRIORITY]
GROUP BY PERS.[PERSON-REF], PERSCONT.[CONTACT-DETAILS],PERSCONT.PRIORITY ) AS FAX
ONPER.[PERSON-REF] = FAX.[PERSON-REF]
/* In order to get the lowest priority of the Email*/
LEFT OUTER JOIN
(
SELECT PERS.[PERSON-REF]
,PERSCONT.[CONTACT-DETAILS]
,PERSCONT.[PRIORITY] AS Priority
FROM dbo.[CORE_CO-PERSON] AS PERS
INNER JOIN dbo.[CORE_CO-PERSON-CONTACT] AS PERSCONT
ON PERS.[PERSON-REF] = PERSCONT.[PERSON-REF]
INNER JOIN (
SELECT [PERSON-REF]
,MIN([PRIORITY]) AS [PRIORITY]
FROM dbo.[CORE_CO-PERSON-CONTACT]
WHERE [CONTACT-TYPE]= 'EML'
GROUP BY [PERSON-REF]
) AS x
ON PERSCONT.[PERSON-REF] = x.[PERSON-REF]
AND PERSCONT.[PRIORITY] = x.[PRIORITY]
GROUP BY PERS.[PERSON-REF], PERSCONT.[CONTACT-DETAILS],PERSCONT.PRIORITY
) AS EMAIL
ONPER.[PERSON-REF] = EMAIL.[PERSON-REF]
/* In order to get the Religion ID*/
LEFT OUTER JOIN
(
SELECT DES.[PERSON-REF]
,DES.[SUB-ATTRIB-CODE]
FROM dbo.[CORE.CO-PERSON-DESC-ATTR]AS DES
WHERE DES.[ATTRIB-CODE] = 'REL'
) AS REL
ON PER.[PERSON-REF] = REL.[PERSON-REF]
/* In order to get the SexualOrientation ID*/
LEFT OUTER JOIN
(
SELECT DES.[PERSON-REF]
,DES.[SUB-ATTRIB-CODE]
FROM dbo.[CORE.CO-PERSON-DESC-ATTR]AS DES
WHERE DES.[ATTRIB-CODE] = 'SEO'
) AS SEX
ON PER.[PERSON-REF] = SEX.[PERSON-REF]
WHERE (TEN.[tncy-status] = 'CUR') --Tenant Is Current--
AND (TEN.[tenancy-ref] NOT LIKE 'FS%') --Excludes Tenants that start with FS--
AND (TENPL.[prime-place] = 'yes') --The location is the primary place
AND (TENPER.[END-DATE] IS NULL) -- The tenants is still marked on the tenancy--
AND(TENPER.[ON-TNCY] = 'yes') --Only Tenants are exported. Occupants are not--
AND(PER.[SURNAME] IS NOT NULL) --In order to only extract geniune tenants
AND(PER.[PERSON-TITLE] IS NOT NULL) --In order to only extract geniune tenants
AND(TEN.[tncy-sys-ref] = '13989')
--ORDER BY PLA.[place-ref]
) ReACT
Order By [TenancyNo]
This has been modified to just pull back tncy-sys-ref 13989.
In my select statement I have TEN.[tncy-sys-ref] + PER.[GENDER]
This pulls back the following results -
ID Alias Title Surname Forename FormerName Initials Address1 Address2 Address3 Address4 PostCode Town County Country DaytimePhone EveningPhone WorkPhone Mobile Fax Email NINumber DOB AgeRangeID BirthPlace Gender EthnicOrigin ReligionID SexualOrientationID DisabilityID DisabilityDetails IsTenant TenancyNo TenancyTypeID TenancyCommenced IsOrderNone IsOrderForRent IsOrderForOther IsApproachable UnApproachableReason
------------------------------ ----- ---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------- ---------- -------------------------------------------------- -------------------------------------------------- -------------------- -------- --------------- -------------------- -------------------- -------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ------------------------------------------------------ ---------- ---------- ------ ------------ ---------- ------------------- ------------ ----------------- -------- -------------------- ------------- ------------------------------------------------------ ----------- -------------- --------------- -------------- --------------------
13989F Mrs Jabbour NULL J 1 Cazeneuve Street ME1 1XU Rochester Kent United Kingdom NULL NULL NULL NULL NULL NULL NULL NULL 0 2 0 0 0 1 109901 5 2003-04-01 00:00:00.000 1 0 0 1
13989M Mr Jabbour NULL J 1 Cazeneuve Street ME1 1XU Rochester Kent United Kingdom 01634 812 959 01634 812 959 NULL NULL NULL NULL NULL NULL 0 1 0 0 0 1 109901 5 2003-04-01 00:00:00.000 1 0 0 1
(2 row(s) affected)
As you can see from my results I am getting the ID (TEN.[tncy-sys-ref] + PER.[GENDER]) of -
13989F and 13989M?
What I want pull back is 139892 for 13989F and 139891 for 13989M.
So if the gender is M = 1 and F = 2.
How would I achieve that please?
Thanks
May 12, 2011 at 7:37 am
WOW!!! That's some query. Can you pare it down to include only the columns you are concerned about? Also, how about some table layouts and sample data?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 12, 2011 at 8:10 am
Ryan, can you confirm which version of SQL Server you've been working on please?
Cheers
ChrisM
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
May 12, 2011 at 8:46 am
It's for SQL2000.
I've Simplified the query below -
SELECTTOP 100 PERCENT [ID]
,[Gender]
FROM
(
SELECT TEN.[tncy-sys-ref] + PER.[GENDER] AS 'ID'
,CASE WHEN PER.[GENDER] = 'M' THEN '1'
WHEN PER.[GENDER] = 'F' THEN '2'
ELSE '0'END AS 'Gender'
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]
WHERE (TEN.[tncy-status] = 'CUR') --Tenant Is Current--
AND (TEN.[tenancy-ref] NOT LIKE 'FS%') --Excludes Tenants that start with FS--
AND (TENPL.[prime-place] = 'yes') --The location is the primary place
AND (TENPER.[END-DATE] IS NULL) -- The tenants is still marked on the tenancy--
AND(TENPER.[ON-TNCY] = 'yes') --Only Tenants are exported. Occupants are not--
AND(PER.[SURNAME] IS NOT NULL) --In order to only extract geniune tenants
AND(PER.[PERSON-TITLE] IS NOT NULL) --In order to only extract geniune tenants
AND(TEN.[tncy-sys-ref] = '13989')
--ORDER BY PLA.[place-ref]
) ReACT
The Results are -
ID Gender
------------------------------ ------
13989M 1
13989F 2
(2 row(s) affected)
What I am trying to achieve with the ID column is instead of pulling back an F or an M at the end of the 13989 is if it is an M, pull back 1 and if it's a F pull back 2.
The Gender data item in the dbo.[CORE_CO-PERSON] table is stored as either F and M, but like I have done in my Select statement I have used a CASE statement to either pull back 2 or 1.
How would I do this? Thanks
May 12, 2011 at 8:55 am
Got it now. You can use the case statement that you originally used. This will work as long as they are both strings, if not then you will have to do some casting.
SELECT TEN.[tncy-sys-ref] +
CASE WHEN PER.[GENDER] = 'M' THEN '1'
WHEN PER.[GENDER] = 'F' THEN '2'
ELSE '0'END AS 'Gender'
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 12, 2011 at 8:56 am
SELECT
TEN.[tncy-sys-ref] + CASE
WHEN PER.[GENDER] = 'M' THEN '1'
WHEN PER.[GENDER] = 'F' THEN '2'
ELSE '0'END AS 'ID',
CASE
WHEN PER.[GENDER] = 'M' THEN '1'
WHEN PER.[GENDER] = 'F' THEN '2'
ELSE '0'END AS 'Gender',
...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
May 12, 2011 at 9:10 am
Thanks Guys.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply