Update that compares two dates

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 2000 Forums

Transact-SQL (2000)

Update that compares two dates

Author

Topic

BDesigns
Starting Member

12 Posts

Posted - 2006-11-29 : 11:13:47

Hey guys, I'm trying to write an UPDATE statement that will compare two appointments and then update values, but I need to do a few things.

First, if another appointment exists and the scheduled date is less then 72 hours from the original appointment's end date, then populate this:

-Set the Apt_Code = 74
-Set the Apt_From_Date = to the previous appointments date
-Set the Apt_Through_Date = to the new appointments date

I can't wrap my head around how to compare the dates and then populate the Apt_From_Date and Apt_Through_Date accordingly.

I realize I could use a SELECT after my WHERE clause to say something like "and does not exist in" , but from there I'm lost.

The appointments are in the same table, say "Table_Apt" and they are updating the three codes in another table, say "Table_Codes".

Any help?

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-11-29 : 12:29:00

How does Table_Codes relate to Table_Apt? Can you post some DDL and expected results? It sounds like you need a self outer join on Table_Apt, but it's difficult to say for sure without some more info.

Mark

BDesigns
Starting Member

12 Posts

Posted - 2006-11-29 : 12:31:46

Table_Codes and Table_Apt can be joined using an patient_id (since this person can have more than one appointment). The results are still the same, if the criteria is met then:

-Set the Apt_Code = 74
-Set the Apt_From_Date = to the previous appointments date
-Set the Apt_Through_Date = to the new appointments date

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-11-29 : 13:36:53

follow the first link in my signature,and restate your question.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

BDesigns
Starting Member

12 Posts

Posted - 2006-11-29 : 14:00:31

Well one table houses the appointment information, that is Table_Apt:

Table_Apt has these columns:
-apt_id
-patient_id
-scheduled_date
-end_date

*This table can have the same patient_id repeated, but apt_id is unique (cannot be repeated).
-----------------------

Table_Codes has:
-patient_id
-apt_code (which will always be 74 when the criteria is met, other wise null)
-apt_from_date
-apt_through_date

This will typically only have one row, based off the criteria we are only pulling single values, so a single date for both date columns, apt_code, and patient_id, since this update is a part of a Power Builder data window (aka inside a larger stored procedure using temp tables) it only relates to one patient.

BDesigns
Starting Member

12 Posts

Posted - 2006-11-30 : 13:42:37

So here is how the data would look:

Table_Apt


apt_id |  patient_id |  scheduled_date |    end_date
1         000001         10/12/2006        11/15/2006
2         000001         10/10/2006        10/12/2006
3         000001         08/25/2006        09/05/2006

The Table_Codes table will be populated with the results.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources