t-sql question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

t-sql question

Author

Topic

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2009-04-21 : 18:23:31

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.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-04-21 : 19:05:22

I may not be understanding the problem correctly, but can't you just do this?
[CODE]
UPDATE #temp1
SET overbook_flag = 'Y'
WHERE appt_count > appt_limit[/CODE]

Some days you're the dog, and some days you're the fire hydrant.

Purvi
Starting Member

3 Posts

Posted - 2009-04-22 : 01:55:38

I think you need to describe your problem/requirement in more detail. I cannot understand what you want.
If you do this -

UPDATE #temp1
SET overbook_flag = 'Y'
WHERE appt_count > appt_limit

it will update all the rows with 'Y' where appt_count>appt_limit and i dont think that is your requirement right?

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-04-22 : 02:31:44

Hi,
as per my understanding this may solve yr problem

update #temp1 set overbook_flag='Y' where appt_count>appt_limit

kunal

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-22 : 06:32:24

Hi Kunal,
What is difference between ur post and previous post
before posting see the other posts too...........

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-04-22 : 07:14:52

I should but i dont have time to look at all responses
Kunal

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-22 : 08:13:28

You have time to respond, but not time to read the entire thread to see if it has already been answered, or to check any of the suggestions of others, or to see if the OP has clarified his question?
*boggle*

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources