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
Tuhin PaulPosted Mar 28, 2023, 8:09 PM
The query selects all data from the table and creates a temporary table called "good_data". Then it creates another temporary table called "q", which extracts data related to claims that were reported in January and February of 2023. The data includes the number of claims opened, claims closed, indemnity paid, expense paid, and reserve for each month and coverage type. Also the query selects and groups the data from table "q" based on the month name, opened and closed claims, coverage, and reported date. The results are sorted by the month name in ascending order.
Tuhin PaulPosted Mar 28, 2023, 8:06 PM
If you want to display the count of opened claims for each individual claim in the same row, you can modify your query to remove the aggregation in the subquery q, and also remove the GROUP BY clause at the end.