April 21, 2009 at 4:20 pm
I want to update the overbook_flag = 'Y' for all the overbooked appointments.
for example: For the doctor's who have appt_count=5 and appt_limit=2, based on the apptcreatedate
I want to update the overbook_flag for the ones whose appointment have been booked after reaching limit of 2.
So the following rows(apptcount:5, appt_limit:2) overbook_flag needs to be updated with 'Y'.
1. apptcreatedate='2009-03-02 17:06:19.370', 'Patient1','doctor1'
2. apptcreatedate='2009-02-27 09:05:20.887', 'Patient2','doctor1'
3. apptcreatedate='2009-02-27 09:03:18.543', 'Patient3','doctor1'
Below is the code to be used for creating test data:
Create table #temp1
(appt_count int,
appt_limit int,
apptcreatedate datetime,
Patient varchar(100),
doctor varchar(100),
overbook_flag char(1)
)
Insert into #temp1
select 5,2,'2009-03-02 17:06:19.370','Patient1','doctor1',null
union
select 5,2,'2009-02-27 09:05:20.887', 'Patient2','doctor1',null
union
select 5,2,'2009-02-27 09:03:18.543', 'Patient3','doctor1',null
union
select 5,2,'2009-02-27 09:03:02.277', 'Patient4','doctor1',null
union
select 5,2,'2009-02-19 18:11:16.367', 'Patient5','doctor1',null
union
select 4,1,'2009-03-01 17:06:19.370','Patient1','doctor2',null
union
select 4,1,'2009-02-27 09:05:20.887', 'Patient2','doctor2',null
union
select 4,1,'2009-02-27 09:03:18.543', 'Patient3','doctor2',null
union
select 4,1,'2009-02-10 09:03:02.277', 'Patient4','doctor2',null
union
select 1,1,'2009-02-1 09:03:02.277', 'Patient1','doctor3',null
union
select 2,2,'2009-01-1 09:03:02.277', 'Patient1','doctor4',null
union
select 2,2,'2008-12-28 09:10:02.277', 'Patient2','doctor4',null
SELECT * FROM #temp1
Thanks.
April 22, 2009 at 12:33 am
check the below code i make it generic based on appointment limit it will set the flag 'Y'
select ROW_NUMBER() OVER(ORDER BY doctor,apptcreatedate) AS ROW_ID,*
INTO #tempdr
from #temp1
DECLARE @Dr VARCHAR(10),
@Dr1 VARCHAR(10),
@COUNT INT,
@outerloopstart int,
@@outerloopend int,
@innerloopstart int,
@innerloopend int,
@limit int,
@chk int
SELECT @Dr = doctor,@limit=appt_limit from #tempdr where ROW_ID = 1
SELECT @Dr1 = doctor,@limit=appt_limit from #tempdr where ROW_ID = 2
SELECT @@outerloopend = MAX(ROW_ID) from #tempdr
SET @outerloopstart = 1
while (@outerloopstart <=@@outerloopend)
BEGIN
IF( @Dr = @Dr1)
BEGIN
SELECT @innerloopend = COUNT(*)+@outerloopstart from #tempdr WHERE doctor = @dr
SET @chk = 1
SET @innerloopstart = @outerloopstart
WHILE (@innerloopstart @limit)
BEGIN
update #temp1
SET overbook_flag = 'Y'
from #temp1
JOIN #tempdr ON #temp1.doctor = #tempdr.doctor AND #temp1.Patient = #tempdr.Patient
where ROW_ID = @innerloopstart
END
SET @CHK = @CHK + 1
SET @innerloopstart = @innerloopstart + 1
END
END
ELSE
BEGIN
SET @innerloopstart = @innerloopstart + 1
END
SET @outerloopstart = @innerloopstart
SELECT @Dr = doctor,@limit=appt_limit from #tempdr where ROW_ID = @outerloopstart
SELECT @Dr1 = doctor,@limit=appt_limit from #tempdr where ROW_ID = @outerloopstart+1
--SELECT @outerloopstart,@Dr,@Dr1
END
April 22, 2009 at 12:58 am
UPDATE t SET overbook_flag = 'Y'
FROM #temp1 t
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY doctor ORDER BY apptcreatedate) AS Appt_Number,
appt_count, appt_limit, apptcreatedate, doctor
FROM #temp1) d
ON d.doctor = t.doctor AND d.apptcreatedate = t.apptcreatedate
WHERE d.Appt_Number > t.appt_limit
SELECT * FROM #temp1 ORDER BY doctor, apptcreatedate
Results:
appt_count appt_limit apptcreatedate Patient doctor overbook_flag
----------- ----------- ----------------------- -------- -------- -------------
5 2 2009-02-19 18:11:16.367 Patient5 doctor1 NULL
5 2 2009-02-27 09:03:02.277 Patient4 doctor1 NULL
5 2 2009-02-27 09:03:18.543 Patient3 doctor1 Y
5 2 2009-02-27 09:05:20.887 Patient2 doctor1 Y
5 2 2009-03-02 17:06:19.370 Patient1 doctor1 Y
4 1 2009-02-10 09:03:02.277 Patient4 doctor2 NULL
4 1 2009-02-27 09:03:18.543 Patient3 doctor2 Y
4 1 2009-02-27 09:05:20.887 Patient2 doctor2 Y
4 1 2009-03-01 17:06:19.370 Patient1 doctor2 Y
1 1 2009-02-01 09:03:02.277 Patient1 doctor3 NULL
2 2 2008-12-28 09:10:02.277 Patient2 doctor4 NULL
2 2 2009-01-01 09:03:02.277 Patient1 doctor4 NULL
Many thanks for providing sample data.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply