locked
How to use a case statement in where clause RRS feed

  • Question

  • Hi All,

    I have a requirement which is to bring all the claims that are created in the last month.So, i wrote a query something like this

    select * from claims
    where
    (Month(ClaimOpenDate) = Month(Getdate())-1 and year(claimopendate) = year(getDate()))

    which would give me any new claims created in last month of current year, but this condition fails if we are in the first month of a new year( lets say if we are in 2016 jan then month(getdate())-1 would be 0 and year(getdate()) would be 2016 so we dont find any records where year is 2016 and month is 0 for claimopen).

    So, i would like to use a case statament or something which can help me get around this one.

    Can someone please help me with any suggestions?

    Thanks

    Wednesday, April 1, 2015 2:20 PM

Answers

  • Try it like this...

    SELECT
    	c.*
    FROM
    	claims c
    WHERE
    	c.OpenDate BETWEEN DATEADD(mm, DATEDIFF(mm, 0, CURRENT_TIMESTAMP) -1, 0) AND DATEADD(mm, DATEDIFF(mm, 0, CURRENT_TIMESTAMP), 0)


    Jason Long

    • Proposed as answer by Charlie Liao Monday, April 13, 2015 1:26 AM
    • Marked as answer by Charlie Liao Wednesday, April 15, 2015 1:31 AM
    Wednesday, April 1, 2015 2:27 PM
  • It's all going to depend on you indexes. Do you have any indexes that have ClaimOpenDate in the 1st key position? If so, does it include the proper included columns to cover the query? What is the execution plan telling you?

    I'd be willing to bet that DESCRIPTION like '%Denial Letter%' is causing a greater expense than anything else. Leading wild cards preclude the use of any index on that column (non-sargable), and should be avoided when possible. 


    Jason Long

    • Proposed as answer by Charlie Liao Monday, April 13, 2015 1:26 AM
    • Marked as answer by Charlie Liao Wednesday, April 15, 2015 1:31 AM
    Wednesday, April 1, 2015 3:35 PM

All replies

  • You may need to use DATEADD function instead...something like below.

    Select Month(Dateadd(month,-1,Getdate()))


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]

    Wednesday, April 1, 2015 2:27 PM
  • Try it like this...

    SELECT
    	c.*
    FROM
    	claims c
    WHERE
    	c.OpenDate BETWEEN DATEADD(mm, DATEDIFF(mm, 0, CURRENT_TIMESTAMP) -1, 0) AND DATEADD(mm, DATEDIFF(mm, 0, CURRENT_TIMESTAMP), 0)


    Jason Long

    • Proposed as answer by Charlie Liao Monday, April 13, 2015 1:26 AM
    • Marked as answer by Charlie Liao Wednesday, April 15, 2015 1:31 AM
    Wednesday, April 1, 2015 2:27 PM
  • Please try:

    DECLARE @StartDate DATETIME
    	,@EndDate DATETIME
    
    SET @StartDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
    SET @EndDate   = DATEADD(MONTH, DATEDIFF(MONTH, - 1, GETDATE()) - 1, - 1)
    
    --SELECT @StartDate, @EndDate
    
    SELECT *
    FROM claims
    WHERE ClaimOpenDate BETWEEN @StartDate AND @EndDate


    Cheers,
    Vaibhav Chaudhari
    [MCTS], [MCP]

    Wednesday, April 1, 2015 2:35 PM
  • Hi Latheesh,

    Thanks for your help, but your query would give me just the last month but i man have many years with just month 3 data, i also want to restrict the year too. Any suggestions please?

    Thanks

    Wednesday, April 1, 2015 2:46 PM
  • Hi Jason,

    Thanks a lot for your help. This is what exactly i am looking for but i just gave a sample query above below is my original query 

    select
    row_number() over (order by [ClaimNumber]) as DataElementName
    ,c.PolicyNumber  as PolicyNum
    , c.FirstName as CustNameF
    ,c.LastName as CustNameL
    ,c.PolicyForm as PolType
    ,'Homesite' as Company
    ,[ClaimNumber] as ClaimNum
    ,E.office as  Ofc
    ,e.Supervisior_FullName as Team
    , RIGHT(e.adjuster_Name ,LEN(e.adjuster_Name)- charindex(',' ,e.adjuster_Name))     as FORepF
    ,  case when  charindex(',' ,e.adjuster_Name) <> 0 then left(e.adjuster_Name,charindex(',' ,e.adjuster_Name)-1)  else e.adjuster_Name end as FORepL
    ,e.AdjusterID as RepC -- not sure 
    ,CONVERT ( varchar,c.LossDate ,101) as DOL
    ,convert (varchar,c.ClaimOpenDate,101) as DOR
    ,rtrim(c.Loss_State) as LossSt
    ,c.Loss_ZipCode as LossZIP
    ,c.Loss_City as LossCity
    ,c.LossType as FOL
    ,''  as PR
    ,''  as PRNum
    ,1  as FeaNum
    ,'HO' as FeaType
    ,case when rtrim(c.claimStatus)= 'Closed' then 'Closed' else 'Open' end as FeaStat
    ,'' as FeaOpen
    ,'' as FeaClosed
    ,s.PaymentIndemnityAmount as PaidAmt
    ,s.ReserveIndemnityAmount as Reserve
    ,'' as Sub
    ,'' as Sal
    ,'' as FeatOwnOfc
    ,e.Supervisior_FullName as FeatOwnTeam
    ,RIGHT(e.adjuster_Name ,LEN(e.adjuster_Name)- charindex(',' ,e.adjuster_Name))   as FeatOwnRepF
    ,case when  charindex(',' ,e.adjuster_Name) <> 0 then left(e.adjuster_Name,charindex(',' ,e.adjuster_Name)-1)  else e.adjuster_Name end as FeatOwnRepL
    ,e.AdjusterID as FeatOwnRepCode
    ,NULL AS Description  --not sure
    from [Stg].[HS_DW_RV_Claims] c
    inner join [dbo].[Claims_Primary_Adjuster] a on a.CLAIM_NUMBER = c.ClaimNumber
    inner join [dbo].[vw_Adjuster] e on e.adjuster_Name  = a.primary_ADJUSTER
    left outer join [Stg].[HS_DW_LossClaimSummary] s on c.ClaimKey=s.ClaimKey 
    where   c.LoadSource = 'CMS'      
            and 
    		(s.PaymentIndemnityAmount <>0 or s.PaymentExpenseAmount <>0)  
    		and
    		ClaimOpenDate BETWEEN DATEADD(mm, DATEDIFF(mm, 0, CURRENT_TIMESTAMP) -1, 0) AND DATEADD(mm, DATEDIFF(mm, 0, CURRENT_TIMESTAMP), 0)
    
    
    UNION ALL
    
    
    select 
    row_number() over (order by [ClaimNumber]) as DataElementName
    ,c.PolicyNumber  as PolicyNum
    , c.FirstName as CustNameF
    ,c.LastName as CustNameL
    ,c.PolicyForm as PolType
    ,'Homesite' as Company
    ,[ClaimNumber] as ClaimNum
    ,E.office as  Ofc
    ,e.Supervisior_FullName as Team
    , RIGHT(e.adjuster_Name ,LEN(e.adjuster_Name)- charindex(',' ,e.adjuster_Name))     as FORepF
    ,  case when  charindex(',' ,e.adjuster_Name) <> 0 then left(e.adjuster_Name,charindex(',' ,e.adjuster_Name)-1)  else e.adjuster_Name end as FORepL
    ,e.AdjusterID as RepC -- not sure 
    ,CONVERT ( varchar,c.LossDate ,101) as DOL
    ,convert (varchar,c.ClaimOpenDate,101) as DOR
    ,rtrim(c.Loss_State) as LossSt
    ,c.Loss_ZipCode as LossZIP
    ,c.Loss_City as LossCity
    ,c.LossType as FOL
    ,''  as PR
    ,''  as PRNum
    ,1  as FeaNum
    ,'HO' as FeaType
    ,case when rtrim(c.claimStatus)= 'Closed' then 'Closed' else 'Open' end as FeaStat
    ,'' as FeaOpen
    ,'' as FeaClosed
    ,s.PaymentIndemnityAmount as PaidAmt
    ,s.ReserveIndemnityAmount as Reserve
    ,'' as Sub
    ,'' as Sal
    ,'' as FeatOwnOfc
    ,e.Supervisior_FullName as FeatOwnTeam
    ,RIGHT(e.adjuster_Name ,LEN(e.adjuster_Name)- charindex(',' ,e.adjuster_Name))   as FeatOwnRepF
    ,case when  charindex(',' ,e.adjuster_Name) <> 0 then left(e.adjuster_Name,charindex(',' ,e.adjuster_Name)-1)  else e.adjuster_Name end as FeatOwnRepL
    ,e.AdjusterID as FeatOwnRepCode 
    ,DESCRIPTION --not sure
    from Stg.IG_Document D
    inner join [Stg].[HS_DW_RV_Claims] c on D.PARENTREF = C.ClaimNumber
    inner join [dbo].[Claims_Primary_Adjuster] a on a.CLAIM_NUMBER = c.ClaimNumber
    inner join [dbo].[vw_Adjuster] e on e.adjuster_Name  = a.primary_ADJUSTER
    left outer join [Stg].[HS_DW_LossClaimSummary] s on c.ClaimKey=s.ClaimKey   
    where   c.LoadSource = 'CMS'      
            and 
    		DESCRIPTION like '%Denial Letter%'
    		and
    		ClaimOpenDate BETWEEN DATEADD(mm, DATEDIFF(mm, 0, CURRENT_TIMESTAMP) -1, 0) AND DATEADD(mm, DATEDIFF(mm, 0, CURRENT_TIMESTAMP), 0)

    So if i use your logic in the end for both the where clauses its been more than 10 minutes and the query is still running however if i use my old method it doesnt even take a second. Looks like its affecting the execution plan. Any suggestions to get around this one please?

    Thanks

    Wednesday, April 1, 2015 2:48 PM
  • It's all going to depend on you indexes. Do you have any indexes that have ClaimOpenDate in the 1st key position? If so, does it include the proper included columns to cover the query? What is the execution plan telling you?

    I'd be willing to bet that DESCRIPTION like '%Denial Letter%' is causing a greater expense than anything else. Leading wild cards preclude the use of any index on that column (non-sargable), and should be avoided when possible. 


    Jason Long

    • Proposed as answer by Charlie Liao Monday, April 13, 2015 1:26 AM
    • Marked as answer by Charlie Liao Wednesday, April 15, 2015 1:31 AM
    Wednesday, April 1, 2015 3:35 PM