smul86 smul86 - 1 month ago 9
SQL Question

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?

Answer

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