We help IT Professionals succeed at work.

If record appears more than once select

156 Views
Last Modified: 2010-03-20
I would like to only select the records where the lngPatientid occurs more than once.   How can I do this, I tried count but it does not appear to work.  Thanks

select strfname, strlname, p.lngPatientid, dtmdate,  bytresult , lngitemid from tblsbline as sb inner join tblPatient as p on sb.lngPatientid = p.lngPatientid  inner join tblsblinedata as hbl on sb.lngsuperitemid = hbl.lngsuperitemid where  dtmdate between '01/01/2002' and '12/31/2002' and (lngitemid = '156' or lngitemid = '43')
Comment
Watch Question

select
      strfname,
      strlname,
      p.lngPatientid,
      count(p.lngPatientid) idcnt,
      dtmdate,  
      bytresult ,
      lngitemid
from
      tblsbline as sb
      inner join tblPatient as p on sb.lngPatientid = p.lngPatientid  
      inner join tblsblinedata as hbl on sb.lngsuperitemid = hbl.lngsuperitemid
where  
      dtmdate between '01/01/2002' and '12/31/2002' and (lngitemid = '156' or lngitemid = '43')
group by
      strfname,
      strlname,
      p.lngPatientid,
      dtmdate,  
      bytresult ,
      lngitemid
HAVING count(p.lngPatientid)>1

Author

Commented:
This does not apear to give me the duplicates, then done manually there are about 250 but when i run the script above I only get 3.

thanks
select
      p.lngPatientid,
      count(p.lngPatientid) idcnt,
from
      tblsbline as sb
      inner join tblPatient as p on sb.lngPatientid = p.lngPatientid  
      inner join tblsblinedata as hbl on sb.lngsuperitemid = hbl.lngsuperitemid
where  
      dtmdate between '01/01/2002' and '12/31/2002' and (lngitemid = '156' or lngitemid = '43')
group by
           p.lngPatientid
HAVING count(p.lngPatientid)>1

Author

Commented:
Thanks that works for the Patientid but I need to also get the other fields as stated above

Author

Commented:
As soon as I add the dtmdate or bytresults it get the problem.   The dtmdate and bytresults will not be the same so I know this is why I am getting the error.

Thanks
CONVERT(VARCHAR, DTMDATE, 101)

Author

Commented:
What would that do please?   I would need to not group the date or result field.  How could I do that.

Thank you
yes
select
      strfname,
      strlname,
      p.lngPatientid,
      count(p.lngPatientid) idcnt,
      convert(varchar, dtmdate, 101),  
      bytresult ,
      lngitemid
from
      tblsbline as sb
      inner join tblPatient as p on sb.lngPatientid = p.lngPatientid  
      inner join tblsblinedata as hbl on sb.lngsuperitemid = hbl.lngsuperitemid
where  
      convert(varchar, dtmdate, 101) between '01/01/2002' and '12/31/2002' and (lngitemid = '156' or lngitemid = '43')
group by
      strfname,
      strlname,
      p.lngPatientid,
      convert(varchar, dtmdate, 101),  
      bytresult ,
      lngitemid
HAVING count(p.lngPatientid)>1
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.