quote:

---

Originally posted by mfemenel

here's a quick example. basically what you want to look for is the hospital id with the lowest count in your table. The code below gives you the right answer but you don't want to have to recount your entire visit history every time you make a decision on where to assign a patient so you'll need to fine tune your where clause a bit or you'll have some perf problems when you're counting a million records.

Create table #test(visitID tinyint, hospitalid tinyint, patientid tinyint, visitingdate datetime default getdate())
insert into #test values(1,1,1,getdate())
waitfor delay '00:00:03'
insert into #test values(2,2,2,getdate())


Create table #hospitals (hospitalid tinyint, hospitalname varchar(30))
insert into #hospitals
select 1,'Test1'
UNION
select 2, 'Test2'
UNION
select 3,'Test3'
UNION
select 4, 'Test4'

select top 1 h.hospitalid,count(t.hospitalid) over( partition by t.hospitalid) as Visits
from #hospitals h
left join #test t
on h.hospitalid=t.hospitalid
GROUP BY h.hospitalid,t.hospitalid
ORDER BY COUNT(*) ASC

Mike
"oh, that monkey is going to pay"

---

quote:

---

Originally posted by khtan

select	(hospitalID + 1) % 4 + 1
from
(
	select	hospitalID, row_no = row_number() over (order by visitID desc)
	from	OPD
) a

---

KH
[spoiler]Time is always against us[/spoiler]

---