FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE [dbo].[Claim](
	[claimId] [int] NOT NULL,
	[payerId] [nvarchar](50) NOT NULL,
	[CreatedDate] [datetime] NULL,
	[source] [nvarchar](3) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Claim] ADD PRIMARY KEY CLUSTERED 
(
	[claimId] ASC
)
GO
ALTER TABLE [dbo].[Claim] ADD  DEFAULT (getdate()) FOR [CreatedDate]
GO

CREATE TABLE [dbo].[conditions](
	[condId] [int] NOT NULL,
	[ruleId] [int] NULL,
	[conditionalType] [nvarchar](25) NOT NULL,
	[conditionalOperator] [nvarchar](25) NOT NULL,
	[conditionalValue] [nvarchar](25) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[conditions] ADD PRIMARY KEY CLUSTERED 
(
	[condId] ASC
)
GO

CREATE TABLE [dbo].[rules](
	[ruleId] [int] NOT NULL,
	[systemType] [nvarchar](5) NOT NULL,
	[enabled] [varchar](1) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[rules] ADD PRIMARY KEY CLUSTERED 
(
	[ruleId] ASC
)
GO

insert into claim values (101, '12345', GETDATE(),'AAA')
insert into claim values (102, '54321', '2025-03-01 18:22:27.687','AAA')
insert into claim values (103, '12345', GETDATE(),'PPP')
insert into claim values (104, '54321', GETDATE(),'PPP')
insert into claim values (105, '12345', '2025-03-01 18:22:27.687','PPP')
insert into claim values (106, '55555', '2025-02-01 18:22:27.687','PPP')
insert into claim values (107, '12345', '2025-02-01 18:22:27.687','PPP')
insert into claim values (108, '12345', '2025-02-01 18:22:27.687','AAA')
insert into claim values (109, '12345', '2025-02-01 18:22:27.687','PPP')
insert into claim values (110, '22222', '2025-02-01 18:22:27.687','AAA')
insert into claim values (111, '11111', '2025-02-01 18:22:27.687','AAA')

insert into rules values (1, 'AAA', 'Y')
insert into rules values (2, 'AAA', 'Y')
insert into rules values (3, 'PPP', 'Y')
insert into rules values (4, 'AAA', 'Y')
insert into rules values (5, 'PPP', 'Y')
insert into rules values (6, 'AAA', 'Y')

insert into conditions values (1, 1, 'payerId', 'equals', '12345')
insert into conditions values (2, 2, 'payerId', 'equals', '54321')
insert into conditions values (3, 2, 'createdDate', 'greater_than_or_equal', '2025-03-01')
insert into conditions values (4, 3, 'payerId', 'equals', '54321')
insert into conditions values (5, 4, 'createdDate', 'less_than_or_equal', '2025-03-06')
insert into conditions values (6, 5, 'createdDate', 'equals', '2025-02-01')
insert into conditions values (7, 6, 'payerId', 'equals', '22222')
insert into conditions values (8, 6, 'payerId', 'equals', '11111')

WITH condition_checks AS (
    SELECT
        c.claimId,
        r.ruleId,
        r.systemType,
        cond.condId,
        cond.conditionalType,
        cond.conditionalOperator,
        cond.conditionalValue,
        CASE WHEN cond.conditionalType = 'payerId' AND cond.conditionalOperator = 'equals' AND c.payerId = cond.conditionalValue THEN 1
            ELSE
                CASE
                    WHEN cond.conditionalType = 'payerId' AND cond.conditionalOperator = 'equals' AND c.payerId = cond.conditionalValue THEN 1
                    WHEN cond.conditionalType = 'createdDate' AND cond.conditionalOperator = 'equals' AND CONVERT(date, c.CreatedDate) = CONVERT(date, cond.conditionalValue) THEN 1
                    WHEN cond.conditionalType = 'createdDate' AND cond.conditionalOperator = 'greater_than_or_equal' AND CONVERT(date, c.CreatedDate) >= CONVERT(date, cond.conditionalValue) THEN 1
  					WHEN cond.conditionalType = 'createdDate' AND cond.conditionalOperator = 'less_than_or_equal' AND CONVERT(date, c.CreatedDate) <= CONVERT(date, cond.conditionalValue) THEN 1
                    ELSE 0 END
            END AS is_condition_met
        
    FROM
        claim c
    JOIN
        rules r ON c.source = r.systemType AND r.enabled = 'Y'
    JOIN
        conditions cond ON r.ruleId = cond.ruleId
),
matched_claims AS (
    SELECT
        claimId,
        ruleId,
        systemType,
        COUNT(*) AS total_conditions,
        SUM(is_condition_met) AS conditions_met
    FROM
        condition_checks
    GROUP BY
        claimId,
        ruleId,
        systemType
)
SELECT
    ruleId,
    COUNT(DISTINCT claimId) AS claimsCount,
    systemType
FROM
    matched_claims
WHERE
    total_conditions = conditions_met
GROUP BY
    ruleId,
    systemType
ORDER BY
    ruleId

ruleId	claimsCount	systemType
1	2	AAA
2	1	AAA
3	1	PPP
4	4	AAA
5	3	PPP

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(5 rows affected)