Create Field off Case Statement in Another Field

Question

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?

`[BusinessCalendarDayNum] - [BusinessCalendarDayNum]` -> `0` — Juan Carlos Oropeza, Oct 14 '16 at 14:36

Jaydip Jadhav · Accepted Answer · 2016-10-14T14:46:11.160

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

Is there a way to do it all in the select statement without having to create the from statement? Almost like using the same columns Start Date, End Date BusinessCalendarDayNum to create a CASE statement specifically for AgeBucket? — smul86, Oct 14 '16 at 14:39
You can put the subquery in the `WHEN BusinessAge ` but then you have to repeat the subquery 4 times. So would be unreadable, so this look simpler — Juan Carlos Oropeza, Oct 14 '16 at 14:43
Thank you for your help this is what I needed actually! Appreciate you. — smul86, Oct 14 '16 at 14:47

Create Field off Case Statement in Another Field

1 Answers1