CODEWITHSUNDEEP
sqlsql-serverdateaddgetdate
Krittapat Somsiri
Krittapat Somsiri

Reputation: 9

SQL Using Case with date function

I'm trying to use case for date to run query in SSIS backward 1 month which is

After I run the query, it shows an error:

incorrect syntax near month

(see the screenshot).

Can someone please provide me assistance? Thank you in advance.

enter image description here

SELECT DISTINCT
    a.POLICY_NO, c.CLAIM_NO, c.ENTRY_DATE, 
    c.LOSS_DATE, c.LOSS_TIME, c.LOSS_DESC_CODE, c.LOSS_DESCRIPTION,
    c.CLAIM_STATUS, c.CLAIM_TYPE, c.PERSON_ENTRY_CODE,
    (SELECT p.person_full_name 
     FROM center_setup.dbo.setup_person_name_master p
     WHERE p.person_short_name = c.person_entry_code) AS Person_Name,
    c.loss_place_name
FROM
    (SELECT
         cm.policy_no, COUNT(cm1.claim_no) AS Count_,
         cm.LOSS_DATE, 
         CASE
             WHEN MONTH(cm.ENTRY_DATE) = '01' THEN DATEADD(year, -1, cm.ENTRY_DATE)
    month(cm.ENTRY_DATE) >= '02' AND year(cm.ENTRY_DATE)=YEAR(GETDATE()) then DATEADD(month, -1, cm.ENTRY_DATE) 
         END, 
         cm.LOSS_DESC_CODE
     FROM 
         cosl_master cm 
     INNER JOIN
         cosl_master cm1 ON cm.POLICY_NO = cm1.POLICY_NO 
                         AND cm.loss_date = cm1.loss_date 
                         AND cm.loss_desc_code = cm1.LOSS_DESC_CODE 
                         AND cm1.CLAIM_STATUS <> 'I' 
                         -- AND cm.LOSS_TIME = cm1.LOSS_TIME
     WHERE
         cm.claim_status IN ('O', 'R') 
         AND cm.CLAIM_STATUS <> 'I'
     GROUP BY
         cm.claim_no, cm.policy_no, cm.LOSS_DATE, cm.ENTRY_DATE, cm.LOSS_DESC_CODE
     HAVING
         COUNT(cm1.claim_no) > 1) A 
LEFT JOIN
    COSL_MASTER c ON A.POLICY_NO = c.POLICY_NO 
                  AND c.CLAIM_STATUS <> 'I' 
                  AND a.LOSS_DATE = c.LOSS_DATE 
                  AND c.LOSS_DESC_CODE = a.LOSS_DESC_CODE
ORDER BY
    a.policy_no ASC

Upvotes: -1

Views: 63

Answers (2)

Sumant Kumar Mandal
Sumant Kumar Mandal

Reputation: 242

You have missed WHEN keyword for second test case.

case
   when month(cm.ENTRY_DATE) = '01' then DATEADD(year, -1, cm.ENTRY_DATE)
   when month(cm.ENTRY_DATE) >= '02' AND year(cm.ENTRY_DATE)=YEAR(GETDATE()) then DATEADD(month, -1, cm.ENTRY_DATE) 
end

We can directly use DATEADD(MONTH,-1,cm.ENTRY_DATE) without any CASE statement. We don't need to worry about year.

SELECT DATEADD(MONTH,-1,CONVERT(DATETIME,'2023-10-01', 102)) AS PrevMonthDate

Output will be 2023-09-01 00:00:00.000.

SELECT DATEADD(MONTH,-1,CONVERT(DATETIME,'2023-01-01', 102)) AS PrevMonthDate

Output will be 2022-12-01 00:00:00.000.

Upvotes: 2

Craig
Craig

Reputation: 1226

There are two different approaches to the use of CASE, so you just need to understand the correct syntax, depending on how you are going to do your CASE comparison. (In the particular case of your error, your syntax is not quite right)

Either (a simple comparison of one value):

SELECT CASE month(cm.EntryDate)
    WHEN 1 THEN 'something'
    WHEN 2 THEN 'something else'
    WHEN 3 THEN 'another different thing'
END

or (different comparison conditions)

SELECT CASE
    WHEN month(cm.EntryDate) = 1 THEN 'this action'
    WHEN month(cm.EntryDate) >= 2 AND year(cm.EntryDate) = year(getdate()) THEN 'a different action'
END

Upvotes: 1

Related Questions