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];