asked on

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')

SQL_SERVER_DBA

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

running32

ASKER

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

SQL_SERVER_DBA

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

running32

ASKER

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

running32

ASKER

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

SQL_SERVER_DBA

CONVERT(VARCHAR, DTMDATE, 101)

running32

ASKER

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

Thank you

SQL_SERVER_DBA

yes

SQL_SERVER_DBA

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

ASKER CERTIFIED SOLUTION

SQL_SERVER_DBA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

running32

ASKER

Thanks