Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 707
  • Last Modified:

Microsoft SQL 2005 Query

Hi guys, so I have another quetion out there at http://www.experts-exchange.com/Microsoft/Development/Q_27849588.html#a38355220 

But here I can just cut to the chase. Here is the Query I have thus far.


SELECT C.Last_Name ,C.First_Name ,C.Date_of_Birth, P.End_Date
From pm.Contacts as C
INNER JOIN pm.Patients as P
ON P.Patient_ID = C.Contact_ID
 LEFT JOIN pm.Policies AS O
 ON O.Contact_ID = C.Contact_ID
WHERE P.END_DATE IS NOT NULL


And here is what I need to do. I need to detect the duplicate records based on the Criteria in the Select Statement, with 1 exception. EXAMPLE: If there are 2 John Smiths with same DOB, BUT 1 has an end date and the other has NO end date then I DONT want them, as far as Im concerned there is only 1 because there is an end date. So I need any record(s) that are the same person that has END_DATE of NULL Greater than 1
0
dmanisit
Asked:
dmanisit
  • 5
  • 4
  • 2
  • +1
3 Solutions
 
Mez4343Commented:
So if John Smith is only on Contacts table and on Patients table the End_Date is Null, you still want John  Smith in resultset right?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
;With res as (
SELECT rn = ROW_NUMBER() OVER( partition by  C.Last_Name ,C.First_Name ,C.Date_of_Birth ORDER BY P.End_Date ) ,
     C.Last_Name ,C.First_Name ,C.Date_of_Birth, P.End_Date
From pm.Contacts as C
INNER JOIN pm.Patients as P
ON P.Patient_ID = C.Contact_ID
 LEFT JOIN pm.Policies AS O
 ON O.Contact_ID = C.Contact_ID
WHERE P.END_DATE IS NOT NULL
)

SELECT * FROM res where rn = 1
0
 
Mez4343Commented:
DECLARE @Contacts table (Contact_ID int, Last_Name varchar(100), First_Name varchar(100), Date_of_Birth datetime,End_Date datetime)
DECLARE @Patients table (Patient_ID int, Last_Name varchar(100), First_Name varchar(100), Date_of_Birth datetime,End_Date datetime)
DECLARE @Policies table (Contact_ID int)

INSERT INTO @Contacts Values(1,'john', 'smith', '01-01-2000', '01-01-2000');
INSERT INTO @Patients Values(1,'john', 'smith', '01-01-2000', null);
INSERT INTO @Policies Values(1);
INSERT INTO @Contacts Values(2,'Fred', 'smith', '01-01-2000', '01-01-2000');
INSERT INTO @Patients Values(2,'Fred', 'smith', '01-01-2000', '01-1-2000');
INSERT INTO @Policies Values(2);

-- your original query
-- excludes john smith because Patients row has no End Date
SELECT C.Last_Name ,C.First_Name ,C.Date_of_Birth, P.End_Date
From @Contacts as C
INNER JOIN @Patients as P ON P.Patient_ID = C.Contact_ID
 LEFT JOIN @Policies AS O ON O.Contact_ID = C.Contact_ID
WHERE P.END_DATE IS NOT NULL

-- So I need any record(s) that are the same person that has END_DATE of NULL Greater than 1
SELECT C.Last_Name ,C.First_Name ,C.Date_of_Birth, P.End_Date
From @Contacts as C
INNER JOIN @Patients as P ON P.Patient_ID = C.Contact_ID
 LEFT JOIN @Policies AS O ON O.Contact_ID = C.Contact_ID
WHERE P.END_DATE IS NULL OR C.End_date IS NULL
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
dmanisitAuthor Commented:
Thank you Mez, I think I mis communicated (as usual). When I said greater than 1. i am thinking of maybe counting the records, the dob is there. So we will match on last name first name and dob for duplicated. However the end date the user can enter. In other words if they find a duplicate they can manually deactivate by adding a date. Therefore the value is no longer NULL. So upon doing the query If I only match on name and dob is not good enough. I need ONLY the records to display that have more than 1 end date of Null
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
seems I missed few things here

;With res as (
SELECT rn =ROW_NUMBER() OVER( partition by  C.Last_Name ,C.First_Name ,C.Date_of_Birth ORDER BY CASE WHEN P.End_Date IS NULL THEN '1900-01-01' else P.End_Date end DESC ) ,
     C.Last_Name ,C.First_Name ,C.Date_of_Birth, P.End_Date
From pm.Contacts as C
INNER JOIN pm.Patients as P
ON P.Patient_ID = C.Contact_ID
 LEFT JOIN pm.Policies AS O
 ON O.Contact_ID = C.Contact_ID
)

SELECT * FROM res where rn = 1
0
 
dmanisitAuthor Commented:
Thanks Aneeshattingal,

Except, None of these people on the Query have duplicate records?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Its unclear, would you pasting a sample structure and expected results.
0
 
dmanisitAuthor Commented:
LOL, if I knew how to post a sample structure I could write the Query.
If there are 2 John smiths in the DB, matching on First_Name, Last_Name, DOB then we would consider that as being a duplicate record.

BUT

We need to include End_Date.

So if the patient has 2 records AND the end date on BOTH records are NULL then I want to display those records.

BUT

If there are 2 Records and 1 record has an End_Date but the other doesnt have an End_date I DONT want to display them, I dont care about those.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT C.Last_Name ,C.First_Name ,C.Date_of_Birth
From pm.Contacts as C
INNER JOIN pm.Patients as P
ON P.Patient_ID = C.Contact_ID
WHERE NOT EXISTS (SELECT 1 FROM pm.Policies AS O WHERE O.Contact_ID = C.Contact_ID AND P.END_DATE IS NOT NULL  )
0
 
Scott PletcherSenior DBACommented:
Currently you don't really need to return any columns from the Patients table (End_Date will always be NULL anyway),  so code below should do it.
Don't see any use of the Policies table either, but I'm guessing you removed those columns as they weren't needed for the core requirements.


SELECT C.Last_Name ,C.First_Name ,C.Date_of_Birth, CAST(NULL AS datetime) AS End_Date
FROM pm.Contacts AS C
INNER JOIN (
    SELECT Patient_ID
    FROM pm.Patients
    GROUP BY P.Patient_ID
    HAVING
        SUM(CASE WHEN End_Date IS NULL THEN 1 ELSE 0 END) > 1 AND
        SUM(CASE WHEN End_Date IS NOT NULL THEN 1 ELSE 0 END) = 0
) AS P
  ON P.Patient_ID = C.Contact_ID
LEFT JOIN pm.Policies AS O --not referenced
  ON O.Contact_ID = C.Contact_ID
0
 
dmanisitAuthor Commented:
So as all of you helped somewhat, here is the final query that I built on after talking to all of you. Thank you for your help. This worked for me.

select c.last_name, c.first_name, c.date_Of_Birth, count(*) as countP
from pm.contacts c
inner join pm.patients p on c.contact_id = p.patient_id
inner join Pm.Policies po on c.contact_id = po.contact_id

where p.end_date is null
and C.Is_Patient = '1'
and po.carrier_id = 196

group by c.last_name, c.first_name, c.date_of_birth
having count(*) > 1
0
 
dmanisitAuthor Commented:
Thank you
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now