If record appears more than once select

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')
running32Asked:
Who is Participating?
 
SQL_SERVER_DBAConnect With a Mentor Commented:
max(convert(varchar, dtmdate, 101))
put this in the select....take out of group by
0
 
SQL_SERVER_DBACommented:
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
0
 
running32Author 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
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
SQL_SERVER_DBACommented:
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
0
 
running32Author Commented:
Thanks that works for the Patientid but I need to also get the other fields as stated above
0
 
running32Author 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
0
 
SQL_SERVER_DBACommented:
CONVERT(VARCHAR, DTMDATE, 101)
0
 
running32Author Commented:
What would that do please?   I would need to not group the date or result field.  How could I do that.

Thank you
0
 
SQL_SERVER_DBACommented:
yes
0
 
SQL_SERVER_DBACommented:
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
0
 
running32Author Commented:
Thanks
0
All Courses

From novice to tech pro — start learning today.