Post
Ask Question
Preetham Thangamma

Preetham Thangamma

  • NA
  • 167
  • 16.6k

case in sql when in a single column there are rows with diff condition

Mar 28 2023 6:56 AM 
WITH good_data AS (
  SELECT 
    * 
  FROM 
    [dbo].[claims_production] 
--WHERE Coverage IN ('bi','bodily injury') ), q 
AS ( SELECT CASE WHEN FORMAT([ReportedDate], 'MM') = '01' THEN 'January' WHEN FORMAT([ReportedDate], 'MM') = '02' THEN 'February' END AS [Month Name], 
CASE WHEN FORMAT([ReportedDate], 'MM') = '01' THEN (SELECT COUNT([Suffix #]) FROM good_data WHERE [ReportedDate] <= '2023-01-31 00:00:00.000' AND ClaimStatus IN ('o', 'opened', 'open', 'reopen')) WHEN FORMAT([ReportedDate], 'MM') = '02' THEN (SELECT COUNT([Suffix #]) FROM good_data WHERE [ReportedDate] <= '2023-02-28 00:00:00.000' AND ClaimStatus IN ('o', 'opened', 'open', 'reopen')) END AS claims_opened, 
CASE WHEN ClaimStatus IN ('C', 'closed', 'close') AND MONTH([ReportedDate]) IN (01, 02) AND YEAR([ReportedDate]) IN ('2023') THEN COUNT([Suffix #]) ELSE 0 END AS claims_closed, 
CASE WHEN MONTH([ReportedDate]) IN (01, 02) AND YEAR([ReportedDate]) = '2023' THEN SUM([Indemnity Paid]) END AS [Indemnity Paid], CASE WHEN MONTH([ReportedDate]) IN (01, 02) AND YEAR([ReportedDate]) = '2023' THEN SUM([Expense Paid]) END AS [Expense Paid], 
CASE WHEN MONTH([ReportedDate]) IN (01, 02) AND YEAR([ReportedDate]) = '2023' THEN SUM([Expense Outstanding] + [Indemnity Outstanding]) END AS [Reserve], CASE WHEN ClaimStatus IN ('o', 'opened', 'open', 'reopen') AND MONTH([ReportedDate]) IN (01, 02) AND YEAR([ReportedDate]) <= '2023' THEN COUNT([Suffix #]) ELSE 0 END AS OPENED_CLAIMS,Coverage,ReportedDate FROM good_data group by ReportedDate,ClaimStatus,coverage ) 
SELECT [Month Name], claims_opened, SUM(claims_closed) AS claims_closed, SUM([Indemnity Paid]) AS [Indemnity Paid], SUM([Expense Paid]) AS [Expense Paid], SUM([Reserve]) AS [Reserve],sum(OPENED_CLAIMS) as OPENED_CLAIMS ,Coverage,ReportedDate 
--into Claims_Summary_report FROM q WHERE [Month Name] IN ('January', 'February') GROUP BY [Month Name] ,claims_opened ,Coverage,ReportedDate ORDER BY [Month Name];

this is my code and here claims_opened column is repeating the values that is for feb month,it is repeating 3843 and for january it is showing 3761 but i don't want to display like this.instead, i want to display the count(suffix) in each row and when i sum it up only then it should display3761 for jan and 3843 for feb


Answers (2)