December 16, 2009 at 4:22 am
Oh my lord thats worked.
THANK YOU!!!!
I can understand the logic from this now.
Out of intrest at the very end of the SELECT statement you have a )d
What does that mean?
Many Thanks again
December 16, 2009 at 4:30 am
Ryan Keast (12/16/2009)
Oh my lord thats worked.THANK YOU!!!!
I can understand the logic from this now.
Out of intrest at the very end of the SELECT statement you have a )d
What does that mean?
Many Thanks again
Top work Ryan! Thanks for going to the trouble of trying out the code and putting some thought into how it works.
The whole of your original statement/query is wrapped up like this:
SELECT... FROM (...) d
so the d is just an alias, as you've used in your original query
FROM dbo.[IH_EM-COMMUNICATION] COMM
Incidentally, best practice is not to use SELECT *, but to resolve out to the columns which you need.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 16, 2009 at 4:49 am
Oh I see on the alias point of thing now.
Makes sense.
I'll remove the * and post my final statement once I have it working fully to the way I want it.
But I am now at a position to work with what you have given me.
Thanks again.
December 16, 2009 at 5:31 am
Final Code - Working exactly how I want it -
Thanks
SELECT [Communication Reference],
[Created Date],
[Main Code],
[Sub Code],
[Current Officer Code],
[Person Title],
[Forename],
[Surname],
[Date Of Birth],
[Age],
[Age Range] = CASE
WHEN [Age] < 18 THEN 'Under 18'
WHEN [Age] BETWEEN 18 AND 24 THEN '18 TO 24'
WHEN [Age] BETWEEN 25 AND 49 THEN '25 TO 49'
WHEN [Age] BETWEEN 50 AND 65 THEN '50 TO 65'
WHEN [Age] BETWEEN 66 AND 80 THEN '66 TO 80'
WHEN [Age] > 80 THEN 'Over 80'
ELSE 'No Age Range' END,
[Gender],
[Origin Code],
[Place Reference],
[Person Reference],
[Vulnerable]
FROM (
SELECT
COMM.[comm-reference] AS 'Communication Reference',
COMM.[crt-date] AS 'Created Date',
COMM.[main-code] AS 'Main Code',
COMM.[sub-code] AS 'Sub Code',
COMM.[current-officer-code] AS 'Current Officer Code',
PERSON.[PERSON-TITLE] AS 'Person Title',
PERSON.FORENAMES AS 'Forename',
PERSON.SURNAME AS 'Surname',
PERSON.[D-O-B] AS 'Date Of Birth',
[Age] = DATEDIFF(year, PERSON.[D-O-B],
GETDATE()) - CASE
WHEN MONTH(GETDATE()) > MONTH(PERSON.[D-O-B]) THEN 0
WHEN MONTH(GETDATE()) = MONTH(PERSON.[D-O-B]) AND DAY(GETDATE()) >= DAY(PERSON.[D-O-B]) THEN 0
ELSE 1 END,
PERSON.GENDER AS 'Gender' ,
PERSON.[ORIGIN-CODE] AS 'Origin Code',
ORIGIN.[PLACE-REF] AS 'Place Reference',
ORIGIN.[PERSON-REF] AS 'Person Reference',
CAST(
CASE WHEN EXTENSION.[POTENTIALLY-VULNERABLE] IS NULL THEN 'No' ELSE 'Yes' END
AS NVARCHAR(5)) AS 'Vulnerable'
FROM dbo.[IH_EM-COMMUNICATION] COMM
INNER JOIN dbo.[IH_EM-ORIGINATOR] ORIGIN
ON COMM.[comm-reference] = ORIGIN.[COMM-REFERENCE]
INNER JOIN dbo.[CORE_CO-PERSON] PERSON
ON ORIGIN.[PERSON-REF] = PERSON.[PERSON-REF]
LEFT OUTER JOIN dbo.[CORE_CO-PERSON-EXTENSION] EXTENSION
ON EXTENSION.[PERSON-REF] = PERSON.[PERSON-REF]
WHERE (COMM.[comm-type] = 'WB')
AND (ORIGIN.[PERSON-TYPE] = 'O')
) alias --alias = alias
ORDER BY [Created Date]
December 16, 2009 at 5:49 am
Nice work.
There are a number of different ways of achieving the same thing with TSQL, even in the version you are using. Here's one of them:
SELECT
COMM.[comm-reference] AS 'Communication Reference',
COMM.[crt-date] AS 'Created Date',
COMM.[main-code] AS 'Main Code',
COMM.[sub-code] AS 'Sub Code',
COMM.[current-officer-code] AS 'Current Officer Code',
PERSON.[PERSON-TITLE] AS 'Person Title',
PERSON.FORENAMES AS 'Forename',
PERSON.SURNAME AS 'Surname',
PERSON.[D-O-B] AS 'Date Of Birth',
PERSON.[Age],
[Age Range] = CASE
WHEN PERSON.[Age] < 18 THEN 'Under 18'
WHEN PERSON.[Age] BETWEEN 18 AND 24 THEN '18 TO 24'
WHEN PERSON.[Age] BETWEEN 25 AND 49 THEN '25 TO 49'
WHEN PERSON.[Age] BETWEEN 50 AND 65 THEN '50 TO 65'
WHEN PERSON.[Age] BETWEEN 66 AND 80 THEN '66 TO 80'
WHEN PERSON.[Age] > 80 THEN 'Over 80'
ELSE 'No Age Range' END,
PERSON.GENDER AS 'Gender' ,
PERSON.[ORIGIN-CODE] AS 'Origin Code',
ORIGIN.[PLACE-REF] AS 'Place Reference',
ORIGIN.[PERSON-REF] AS 'Person Reference',
CAST(
CASE WHEN EXTENSION.[POTENTIALLY-VULNERABLE] IS NULL THEN 'No' ELSE 'Yes' END
AS NVARCHAR(5)) AS 'Vulnerable'
FROM dbo.[IH_EM-COMMUNICATION] COMM
INNER JOIN dbo.[IH_EM-ORIGINATOR] ORIGIN
ON COMM.[comm-reference] = ORIGIN.[COMM-REFERENCE]
INNER JOIN (SELECT [PERSON-REF], [PERSON-TITLE], FORENAMES, SURNAME, [D-O-B], GENDER, [ORIGIN-CODE],
[Age] = DATEDIFF(year, PERSON.[D-O-B],
GETDATE()) - CASE
WHEN MONTH(GETDATE()) > MONTH(PERSON.[D-O-B]) THEN 0
WHEN MONTH(GETDATE()) = MONTH(PERSON.[D-O-B]) AND DAY(GETDATE()) >= DAY(PERSON.[D-O-B]) THEN 0
ELSE 1 END
FROM dbo.[CORE_CO-PERSON]) PERSON
ON ORIGIN.[PERSON-REF] = PERSON.[PERSON-REF]
LEFT OUTER JOIN dbo.[CORE_CO-PERSON-EXTENSION] EXTENSION
ON EXTENSION.[PERSON-REF] = PERSON.[PERSON-REF]
WHERE (COMM.[comm-type] = 'WB')
AND (ORIGIN.[PERSON-TYPE] = 'O')
ORDER BY COMM.[crt-date]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply