I need to create report for optical care whose agreement type is like optical care. I need to show active member and allow only one person per membership to be selected that has claimed any dental treatment with a calendar month.
I also need state wise data for the same. I need to avoid repeating by excluding any membership that have been selected in the previous months. The age of person should be greater than 18 years of age. I need to select specific item members for the achieving the end result.
Please help me to write a query.
Can you add PRIMARY KEY, UNIQUE KEY & FOREIGN KEY definitions to the DDL? Thanks.
Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012
Although you haven't provided enough information, I have written a query which should get you closer to your goal by at least pointing you in the right direction for your specified reporting needs. I had to make some assumptions on what fields are used for your filters. You can adjust as necessary.
SELECT * FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY CLM.Membership_ID ORDER BY CLM.service_date) AS Row, MBSHP.membership_id, PRSN.person_id FROM claim_line as CLM INNER JOIN provider_claim as PR_CLM ON PR_CLM.provider_number_id = CLM.provider_number_id INNER JOIN Agreement as AGR ON AGR.agreement_type = PR_CLM.Agreement_Type INNER JOIN person_membership AS MBSHP ON CLM.Membership_ID = MBSHP.membership_id INNER JOIN Person as PRSN ON PRSN.person_id = MBSHP.person_id WHERE AGR.description LIKE '%optical care%' AND MBSHP.[status_flag] = 'A' --assumption this is active status AND CLM.claim_type = 'D' --assumption that this is dental treatment AND PRSN.date_of_birth < DATEADD(year, -18, CAST(GETDATE() as DATE)) --person is > 18 years old AND CLM.service_date BETWEEN '2013-09-01' AND '2013-09-30' -- For September 2013 ) as T1 WHERE Row = 1 --This will retrieve only the first person in the membership who has claimed dental in the time period selected --This will accomplish the goal of not repeating in part 2 for the state wide report as wellThe only part I could not accomodate is the "need to select specific item members for the achieving the end result." I cannot see how the Item table relates to the other tables. I hope this helps you.
- Proposed as answer by MajaS Saturday, October 26, 2013 2:56 AM