I need help with a case statement. THANK YOU!

• jodiejean1

  Grasshopper

  Points: 16

  More actions

  December 6, 2018 at 10:55 pm

  #409364

  This is what I have. I know that he datediff work independent. I am looking at how long a support ticket has been open and group by category.

  Case When DATEDIFF(D,Defect.SubmittedDate,ISNULL(Defect.FixedDate,GETDATE()))>7
  THEN '7+ Days'
  When DATEDIFF(D,Defect.SubmittedDate,ISNULL(Defect.FixedDate,GETDATE())) >8 AND DATEDIFF(D,Defect.SubmittedDate,ISNULL(Defect.FixedDate,GETDATE())) < 12
  THEN '8 - 12 Days Old'
  When DATEDIFF(D,Defect.SubmittedDate,ISNULL(Defect.FixedDate,GETDATE())) >12
  else '12+ Days old'
  end

• pietlinden

  SSC Guru

  Points: 63597

  More actions

  December 6, 2018 at 11:42 pm

  #2015723

  Since you have a bunch of GETDATE()'s... why not just stuff that value into a variable, and then just compare to that?

  DECLARE @CompareDate DATE = ISNULL(Defect.FixedDate,GETDATE());
SELECT "Grouping" =
    CASE
        WHEN DATEDIFF(day,Defect.SubmittedDate,@CompareDate) >12 THEN '12+ Days old'
        WHEN DATEDIFF(day,Defect.SubmittedDate,@CompareDate) >8 AND DATEDIFF(D,Defect.SubmittedDate,@CompareDate) < 12 THEN '8 - 12 Days Old'
        WHEN DATEDIFF(day,Defect.SubmittedDate,@CompareDate)>7 THEN '7+ Days'
    END

  The problem with your code (and mine still a little, I think) is that you have to order the tests so that the first one that evaluates to true is the correct one. So in your case, you have to order the comparisons in the proper order ... >12,>8, >7  otherwise your case won't work right.

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  December 7, 2018 at 2:27 am

  #2015730

  Here is a quick example, which does not use a CASE statement.
  😎

  
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @DEFECT TABLE
(
  DF_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
 ,SubmittedDate DATE  NOT NULL
 ,FixedDate   DATE   NULL
);
INSERT INTO @DEFECT(SubmittedDate,FixedDate)
VALUES
(CONVERT(DATE,'20181120',112),CONVERT(DATE,'20181120',112))
,(CONVERT(DATE,'20181120',112),CONVERT(DATE,'20181121',112))
,(CONVERT(DATE,'20181120',112),CONVERT(DATE,'20181122',112))
,(CONVERT(DATE,'20181120',112),CONVERT(DATE,'20181123',112))
,(CONVERT(DATE,'20181120',112),CONVERT(DATE,'20181124',112))
,(CONVERT(DATE,'20181120',112),CONVERT(DATE,'20181125',112))
,(CONVERT(DATE,'20181120',112),CONVERT(DATE,'20181126',112))
,(CONVERT(DATE,'20181120',112),CONVERT(DATE,'20181127',112))
,(CONVERT(DATE,'20181120',112),CONVERT(DATE,'20181128',112))
,(CONVERT(DATE,'20181120',112),CONVERT(DATE,'20181129',112))
,(CONVERT(DATE,'20181120',112),CONVERT(DATE,'20181130',112))
,(CONVERT(DATE,'20181120',112),CONVERT(DATE,'20181201',112))
,(CONVERT(DATE,'20181120',112),CONVERT(DATE,'20181202',112))
,(CONVERT(DATE,'20181120',112),CONVERT(DATE,'20181203',112))
,(CONVERT(DATE,'20181120',112),CONVERT(DATE,'20181204',112))
,(CONVERT(DATE,'20181120',112),CONVERT(DATE,'20181205',112))
,(CONVERT(DATE,'20181120',112),CONVERT(DATE,'20181206',112))
,(CONVERT(DATE,'20181120',112),NULL)
;
;WITH AGE_CATEGORY(HIGH,LOW,CATNO) AS
(
  SELECT 6, 0, 1 UNION ALL
  SELECT 11, 7, 2 UNION ALL
  SELECT 99,12, 3
)
,AGE_CALC AS
(
  SELECT
   DF.DF_ID
   ,DF.SubmittedDate
   ,DF.FixedDate
   ,DATEDIFF(DAY,DF.SubmittedDate,ISNULL(DF.FixedDate,CONVERT(DATE,GETDATE(),0))) AS AGE
  FROM  @DEFECT  DF
)
SELECT
  AC.DF_ID
 ,AC.SubmittedDate
 ,AC.FixedDate
 ,AC.AGE
 ,ACAT.CATNO
FROM  AGE_CALC  AC
CROSS APPLY AGE_CATEGORY  ACAT
WHERE AC.AGE  BETWEEN ACAT.LOW  AND ACAT.HIGH;


• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  December 7, 2018 at 3:46 am

  #2015732

  pietlinden - Thursday, December 6, 2018 11:42 PM

  Since you have a bunch of GETDATE()'s... why not just stuff that value into a variable, and then just compare to that?

  DECLARE @CompareDate DATE = ISNULL(Defect.FixedDate,GETDATE());
SELECT "Grouping" =
    CASE
        WHEN DATEDIFF(day,Defect.SubmittedDate,@CompareDate) >12 THEN '12+ Days old'
        WHEN DATEDIFF(day,Defect.SubmittedDate,@CompareDate) >8 AND DATEDIFF(D,Defect.SubmittedDate,@CompareDate) < 12 THEN '8 - 12 Days Old'
        WHEN DATEDIFF(day,Defect.SubmittedDate,@CompareDate)>7 THEN '7+ Days'
    END

  The problem with your code (and mine still a little, I think) is that you have to order the tests so that the first one that evaluates to true is the correct one. So in your case, you have to order the comparisons in the proper order ... >12,>8, >7  otherwise your case won't work right.

  You don't need the AND on the second WHEN 
  Can have an ELSE to cover the rest of the cases.
   CASE
        WHEN DATEDIFF(day,Defect.SubmittedDate,@CompareDate) >12 THEN '12+ Days old'
        WHEN DATEDIFF(day,Defect.SubmittedDate,@CompareDate) >8 THEN '8 - 12 Days Old'
        WHEN DATEDIFF(day,Defect.SubmittedDate,@CompareDate)>7 THEN '7+ Days' 
        ELSE '7- Days old'
    END