CODEWITHSUNDEEP
t-sqlcase
MTRXITGuy
MTRXITGuy

Reputation: 19

Nested Case in both When and Else

I am trying to create "Buckets" into my select. Basically I want to determine the aging of a record (in this case tickets). The Aging depends on 2 fields. If there is a required date then it is based on this field, if there is no require date, then is based on the date that the ticket was entered. What I want to accomplish is to have a Aging bucket for 120+, 90-120 Days, 60-90 Days, 30-60 Days, 15-30 Days, 5-15 days and 1-5 Days.
The following query is not working right (syntax error). I've been staring at it for too long now.

EDIT Formatting

SELECT     v_rpt_Service.TicketNbr, v_rpt_Service.Location, v_rpt_Service.Board_Name, v_rpt_Service.Status_Description, v_rpt_Service.ClosedDesc, 
                      v_rpt_Service.ServiceType, v_rpt_Service.SubType_RecID, v_rpt_Service.ServiceSubType, 
                      v_rpt_Service.company_name, v_rpt_Service.Summary, v_rpt_Service.team_name, v_rpt_Service.date_entered, v_rpt_Service.entered_by, 
                      v_rpt_Service.Date_Required, v_rpt_Service.hours_budget, v_rpt_Service.Hours_Scheduled, Member.Member_ID, v_rpt_Service.Territory,
                      Case
                      When Date_Required IS Null then round((DATEDIFF(DAY,date_entered,GETDATE())+1)*.69,0) else round((DATEDIFF(Day,date_required,getdate())+1)*.69,0) END as Age,
                      **case when 
                        (Case when Date_Required IS Null
                            then round((DATEDIFF(DAY,date_entered,GETDATE())+1)*.69,0) 
                            else round((DATEDIFF(Day,date_required,getdate())+1)*.69,0) 
                        end )>= 120 
                        then '120+' 
                        else 
                            (Case when Date_Required IS Null
                            then round((DATEDIFF(DAY,date_entered,GETDATE())+1)*.69,0) 
                            else round((DATEDIFF(Day,date_required,getdate())+1)*.69,0) 
                            end) < 120 AND 
                            case when Date_Required IS Null
                            then round((DATEDIFF(DAY,date_entered,GETDATE())+1)*.69,0) 
                            else round((DATEDIFF(Day,date_required,getdate())+1)*.69,0) 
                        end >= 90
                        then '90-120 Days'
                        else 'Newer' 
                        end as Aging**
FROM         Member AS Member INNER JOIN
                      SR_Team AS SR_Team ON Member.Member_RecID = SR_Team.Member_RecID INNER JOIN
                      v_rpt_Service AS v_rpt_Service ON SR_Team.Description = v_rpt_Service.team_name
WHERE     (v_rpt_Service.ClosedDesc = 'Open') AND (v_rpt_Service.Board_Name = 'Solution Design')

Upvotes: 0

Views: 133

Answers (1)

Alexander Sigachov
Alexander Sigachov

Reputation: 1551

Try to organize your code, something like

WITH source_date AS (
    SELECT
       v_rpt_Service.TicketNbr,
       v_rpt_Service.Location,
       v_rpt_Service.Board_Name,
       v_rpt_Service.Status_Description,
       v_rpt_Service.ClosedDesc,
       v_rpt_Service.ServiceType,
       v_rpt_Service.SubType_RecID,
       v_rpt_Service.ServiceSubType,
       v_rpt_Service.company_name,
       v_rpt_Service.Summary,
       v_rpt_Service.team_name,
       v_rpt_Service.date_entered,
       v_rpt_Service.entered_by,
       v_rpt_Service.Date_Required,
       v_rpt_Service.hours_budget,
       v_rpt_Service.Hours_Scheduled,
       Member.Member_ID,
       v_rpt_Service.Territory,
       CASE
          WHEN Date_Required IS NULL THEN round((DATEDIFF(DAY,date_entered,GETDATE())+1)*.69,0)
          ELSE round((DATEDIFF(DAY,date_required,getdate())+1)*.69,0)
       END AS Age
    FROM Member AS Member
    INNER JOIN SR_Team AS SR_Team ON Member.Member_RecID = SR_Team.Member_RecID
    INNER JOIN v_rpt_Service AS v_rpt_Service ON SR_Team.Description = v_rpt_Service.team_name
    WHERE (v_rpt_Service.ClosedDesc = 'Open') AND (v_rpt_Service.Board_Name = 'Solution Design')
)
SELECT
    sd.*,
    CASE
       WHEN sd.Age >= 1 AND sd.Age<5 THEN '1-5'
       WHEN sd.Age >= 5 AND sd.Age<15 THEN '5-15'
       WHEN sd.Age >= 15 AND sd.Age<30 THEN '15-30'
       WHEN sd.Age >= 30 AND sd.Age<60 THEN '30-60'
       WHEN sd.Age >= 60 AND sd.Age<90 THEN '60-90'
       WHEN sd.Age >= 90 AND sd.Age<120 THEN '90-120'
       WHEN sd.Age >= 120 THEN '120+'
       ELSE 'Unknown'
    END AS Aging
FROM source_date AS sd

Upvotes: 1

Related Questions