CODEWITHSUNDEEP
sqlsql-servert-sqlcasedatediff
Joe
Joe

Reputation: 11

Using CASE logic after DATEDIFF

I'm using DATEDIFF logic successfully to calcuate Age. I need to then be able to take Age and use it as a column in CASE logic. When I attempt to do so, SMSS underlines Age in red and indicates it's an invalid column. How can I fix this?

USE DataWarehouse
SELECT DISTINCT
M.[Account Name Birth Date] as 'Birth Date',
M.[Mailing Address State] as 'State',
E.[ANSVIES2] as 'Income',
E.[FICO] as 'FICO Score',

DATEDIFF(DD,M.[Account Name Birth Date],GETDATE())/365 as [Age],

CASE WHEN [Age] >= 18 AND [Age] <= 34 AND E.[ANSVIES2] >= 50 THEN 'Credit 
Driven'
WHEN [Age] < 45 AND E.[ANSVIES2] < 50 THEN 'Fee Driven'
WHEN [Age] >= 35 AND [Age] <= 54 AND E.[ANSVIES2] >= 50 AND E.[ANSVIES2] <= 
125 THEN 'Middle Market'
WHEN [Age] >= 45 AND E.[ANSVIES2] < 50 THEN 'Low Income Depositor'
WHEN [Age] >= 55 AND E.[ANSVIES2] >= 50 AND E.[ANSVIES2] <= 125 THEN 'Middle 
Income Depositor'
WHEN [Age] >= 35 AND E.[ANSVIES2] >= 125 THEN 'Upscale'
ELSE 'Unclassified' END as 'Consumer Segment'

Upvotes: 1

Views: 100

Answers (4)

Joe
Joe

Reputation: 11

The code posted above didn't work for some reason. I was able to successfully implement this solution though - 

USE DataWarehouse
SELECT DISTINCT
M.[Symitar Account Id] as 'Symitar ID',
M.[Account Name Birth Date] as 'Birth Date',
M.[Account Name Death Date] as 'Death Date',
M.[Mailing Address State] as 'State',
E.[ANSVIES2] as 'Income',
E.[FICO] as 'FICO Score',
M.[Loan Type] as 'Loan Description',
M.[Loan Type Code] as 'Loan Type',
M.[Share Type] as 'Share Description',
M.[Share Type Code] as 'Share Type',

DATEDIFF(DD,M.[Account Name Birth Date],GETDATE())/365 as [Age],

CASE WHEN DATEDIFF(DD,M.[Account Name Birth Date],GETDATE())/365 >= 18 AND 
DATEDIFF(DD,M.[Account Name Birth Date],
GETDATE())/365 <= 34 AND E.[ANSVIES2] >= 50 THEN 'Credit Driven'
WHEN DATEDIFF(DD,M.[Account Name Birth Date],GETDATE())/365 < 45 AND E. 
[ANSVIES2] < 50 THEN 'Fee Driven'
WHEN DATEDIFF(DD,M.[Account Name Birth Date],GETDATE())/365 >= 35 AND 
DATEDIFF(DD,M.[Account Name Birth Date],
GETDATE())/365 <= 54 AND E.[ANSVIES2] >= 50 AND E.[ANSVIES2] <= 125 THEN 
'Middle Market'
WHEN DATEDIFF(DD,M.[Account Name Birth Date],GETDATE())/365 >= 45 AND E. 
[ANSVIES2] < 50 THEN 'Low Income Depositor'
WHEN DATEDIFF(DD,M.[Account Name Birth Date],GETDATE())/365 >= 55 AND E. 
[ANSVIES2] >= 50 AND E.[ANSVIES2] <= 125 
THEN 'Middle Income Depositor'
WHEN DATEDIFF(DD,M.[Account Name Birth Date],GETDATE())/365 >= 35 AND E. 
[ANSVIES2] >= 125 THEN 'Upscale'
ELSE 'Unclassified' END as 'Consumer Segment'

FROM Accounts.ExperianData E
INNER JOIN Accounts.MemberCube M
ON E.[Symitar Account Id] = M.[Symitar Account Id]
WHERE M.[Account Name Type] = 'Primary' AND /* Primary acct holders */
M.[Account Status] = 'Open' AND 
M.[Account Type] IN ('General Membership', 'Indirect Account', 'Employee Acc 
ount') AND 
/* Remove businesses and internal accounts */
M.[Account Name Birth Date] <= '05/18/1997' /* Older than 21 */ AND
M.[Mailing Address State] IN('UT', 'ID', 'AZ', 'NV', 'NM')

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You don't need to use subquery you can directly express this as by using value construct :

 SELECT  . . . ,
        (CASE WHEN tt.Age >= 18 AND tt.Age <= 34 AND t.[ANSVIES2] >= 50 
              THEN 'Credit Driven'
              WHEN [Age] < 45 AND E.[ANSVIES2] < 50 THEN 'Fee Driven'
              WHEN [Age] >= 35 AND [Age] <= 54 AND 
                   E.[ANSVIES2] >= 50 AND E.[ANSVIES2] <= 125 
              THEN 'Middle Market'
              WHEN [Age] >= 45 AND E.[ANSVIES2] < 50 
              THEN 'Low Income Depositor'
              WHEN [Age] >= 55 AND E.[ANSVIES2] >= 50 AND E.[ANSVIES2] <= 125 
              THEN 'Middle Income Depositor'
              WHEN [Age] >= 35 AND E.[ANSVIES2] >= 125 THEN 'Upscale'
              ELSE 'Unclassified'
         END) AS [Consumer Segment]
FROM table t 
CROSS APPLY (VALUES 
                (DATEDIFF(DD, t.[Account Name Birth Date],GETDATE())/365)
            ) tt(Age);

Upvotes: 0

Tolu
Tolu

Reputation: 175

The reason SSMS highlights your syntax is because of the logical-processing order of SQL.

Simply put, the alias "Age" used in the select phase, cannot be referenced in the same phase - it can be referenced in subsequent phases- (e.g. the next phase - ORDER BY).

So, a work arround would be to create a CTE or Nested table.

Logical step sequence of a Query:

  1. FROM / JOIN/ APPLY/ PIVOT/ UNPIVOT
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT list/ DISTINCT
  6. ORDER BY
  7. TOP/ OFFSET-FETCH

Upvotes: 1

Brad
Brad

Reputation: 3601

Or use a sub select (not the best way depending on your database size and indexes, temp tables or staging tables may be better depending on your situtation but this should work):

   Select CASE WHEN [Age] >= 18 AND [Age] <= 34 AND E.[ANSVIES2] >= 50 THEN 'Credit 
Driven'
WHEN [Age] < 45 AND E.[ANSVIES2] < 50 THEN 'Fee Driven'
WHEN [Age] >= 35 AND [Age] <= 54 AND E.[ANSVIES2] >= 50 AND E.[ANSVIES2] <= 
125 THEN 'Middle Market'
WHEN [Age] >= 45 AND E.[ANSVIES2] < 50 THEN 'Low Income Depositor'
WHEN [Age] >= 55 AND E.[ANSVIES2] >= 50 AND E.[ANSVIES2] <= 125 THEN 'Middle 
Income Depositor'
WHEN [Age] >= 35 AND E.[ANSVIES2] >= 125 THEN 'Upscale'
ELSE 'Unclassified' END as 'Consumer Segment'
, OtherColumnsHere
FROM (
     SELECT DISTINCT
     M.[Account Name Birth Date] as 'Birth Date',
     M.[Mailing Address State] as 'State',
     E.[ANSVIES2] as 'Income',
     E.[FICO] as 'FICO Score',    
     DATEDIFF(DD,M.[Account Name Birth Date],GETDATE())/365 as [Age],
     FROM RestOfYourSelectStatementHere
) SubSelect

Upvotes: 1

Related Questions