CODEWITHSUNDEEP
sqlsql-server-2012case-statement
smul86
smul86

Reputation: 411

Create Field off Case Statement in Another Field

I'm trying to figure out a query that will help me to create an AgeBucket field based of the source code I'm pulling. Currently, I made a field called BusinessAge that basically calculates the number of business days from a start date of a given request and tried to explain code below. 

CASE WHEN [EndDate] > [StartDate] THEN [BusinessCalendarDayNum] - [BusinessCalendarDayNum]  --End Date - Start Date
     WHEN [EndDate] IS NULL and [StartDate] IS NOT NULL THEN [BusinessCalendarDayNum] - [BusinessCalendarDayNum] -- GetDate - StartDate
     WHEN [EndDate] = [StartDate] THEN 1
     END AS BusinessAge,

Based off the number it returns, I want to make an AgeBucket field...say,for instance...

CASE WHEN BusinessAge >=0 and BusinessAge <6 THEN '0-5' 
     WHEN BusinessAge >5 and BusinessAge <11 THEN '6-10'

and so on, with multiple conditions.

Is there a way to manipulate the above query and make a more complex multiple case statement which I can use to create an Age Bucket field? Or what is the best way to do this?

Upvotes: 1

Views: 486

Answers (1)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try some think like this

SELECT  BusinessAge ,
        CASE WHEN BusinessAge >= 0 AND BusinessAge < 6 THEN '0-5'
             WHEN BusinessAge >= 6 AND BusinessAge <11 THEN '6-10'
        END AS AgeBucket, 

FROM (
       SELECT CASE WHEN [EndDate] > [StartDate] THEN [BusinessCalendarDayNum] - [BusinessCalendarDayNum]  --End Date - Start Date
                   WHEN [EndDate] IS NULL and [StartDate] IS NOT NULL THEN [BusinessCalendarDayNum] - [BusinessCalendarDayNum] -- GetDate - StartDate
                   WHEN [EndDate] = [StartDate] THEN 1
              END AS BusinessAge
       FROM TableName
 )t

Updated :

SELECT CASE WHEN [EndDate] > [StartDate] THEN [BusinessCalendarDayNum] - [BusinessCalendarDayNum]  --End Date - Start Date
                   WHEN [EndDate] IS NULL and [StartDate] IS NOT NULL THEN [BusinessCalendarDayNum] - [BusinessCalendarDayNum] -- GetDate - StartDate
                   WHEN [EndDate] = [StartDate] THEN 1
              END AS BusinessAge,
       CASE WHEN 
                CASE WHEN [EndDate] > [StartDate] THEN [BusinessCalendarDayNum] - [BusinessCalendarDayNum]  --End Date - Start Date
                   WHEN [EndDate] IS NULL and [StartDate] IS NOT NULL THEN [BusinessCalendarDayNum] - [BusinessCalendarDayNum] -- GetDate - StartDate
                   WHEN [EndDate] = [StartDate] THEN 1
                END BETWEEN 0 AND 6 
            THEN '0-5'
            WHEN 
                CASE WHEN [EndDate] > [StartDate] THEN [BusinessCalendarDayNum] - [BusinessCalendarDayNum]  --End Date - Start Date
                   WHEN [EndDate] IS NULL and [StartDate] IS NOT NULL THEN [BusinessCalendarDayNum] - [BusinessCalendarDayNum] -- GetDate - StartDate
                   WHEN [EndDate] = [StartDate] THEN 1
                END BETWEEN 6 AND 11 
            THEN '6-11'
        END AS AgeBucket

FROM TableName

Upvotes: 1

Related Questions