Need Help With an Update Script

[alvinstraight38@hotmail.com]

Hey guys,

I am stucking trying to create a SQL update statement that will do the
following:

Assign a discharge date = three days after the admit date.


The kicker is they want SQL to wait 3 days after the admit date
before it assigns the discharge date.

I thought about using this statement:

UPDATE Patients SET
Visit_Discharge Date=Visit_Admi tDate + 3

The problem here is it would assign the discharge date just whenever
the script is run. If a person was admitted today, it would give them
a discharge date of May 31st, but I don't want it to assign the
discharge date until May31th.

IS there anyway to do this?

Thanks!

[--CELKO--]

Re: Need Help With an Update Script

>IS there anyway to do this? <<

Put in the discharge date as soon as you know it, then put a VIEW on
the table that hides this date until the CURRENT_TIMESTA MP makes it
magically appear for the idiot that came up with this spec.

Remember, tables are not files; tables can be virtual (VIEWs, derived,
CTE, etc.) while files have to be materialized. There is no need to
write to disk storage over and over.

[Plamen Ratchev]

Re: Need Help With an Update Script

If the rules are that the discharge date is always 3 days after admit date
(doesn't sound right), then you can use a view that will dynamically
calculate 3 days if the date is not in the future (else the value will be
NULL):

CREATE VIEW PatientsDischar geDate
AS
SELECT admit_date,
CASE WHEN DATEDIFF(DAY, admit_date,
CURRENT_TIMESTA MP) >= 3
THEN DATEADD(DAY, 3, admit_date)
END AS dischage_date
FROM Patients;

If it is a must to persist the date, you could schedule a job to run around
midnight every day and update the column where discharge date is not
assigned yet (using the same logic to check if date is not in the future).

HTH,

Plamen Ratchev

http://www.SQLStudio.com

[alvinstraight38@hotmail.com]

Re: Need Help With an Update Script

On May 28, 11:38 am, --CELKO-- <jcelko...@eart hlink.netwrote:

IS there anyway to do this? <<

>
Put in the discharge date as soon as you know it, then put a VIEW on
the table that hides this date until the CURRENT_TIMESTA MP makes it
magically appear for the idiot that came up with this spec.
>
Remember, tables are not files; tables can be virtual (VIEWs, derived,
CTE, etc.) while files have to be materialized.  There is no need to
write to disk storage over and over.

That sounds pretty complicated. I came up with this script:

BEGIN TRANSACTION
update patients
set visit_discharge date = visit_admitdate + 3
where visit_discharge date = '1900-01-01 00:00:00.000'
and visit_admitdate = getdate() - 3

ROLLBACK TRANSACTION

Could this work? It would only excecute if the admit date equals the
current date - 3. Thefore, if the admit date was today, it would not
assign a discharge date until may 31th.

[Plamen Ratchev]

Re: Need Help With an Update Script

You can also define the column as computed column:

CREATE TABLE Patients (
keycol INT PRIMARY KEY,
admit_date DATETIME,
dischage_date AS
CASE WHEN DATEDIFF(DAY, admit_date,
CURRENT_TIMESTA MP) >= 3
THEN DATEADD(DAY, 3, admit_date)
END);

HTH,

Plamen Ratchev

http://www.SQLStudio.com

[--CELKO--]

Re: Need Help With an Update Script

>That sounds pretty complicated. <<

It is a lot easier than what you are trying to do. Why do you keep
thinking about procedural instead of declarative code, materialized
data instead of virtual? This is SQL, not COBOL. Build a base table
and make sure that it follows your business rules

CREATE TABLE Patients
( ..
admit_date DATETIME NOT NULL,
discharge_date DATETIME NOT NULL,
CHECK (discharge_date = DATEADD(DAY, 3, admit_date)),
,..);

Now just show this to the users:

CREATE VIEW CurrentPatients (., admit_date, discharge_date, ..)
AS
SELECT .., admit_date,
CASE WHEN discharge_date <= CURRENT_TIMESTA MP
THEN discharge_date ELSE NULL END AS discharge_date,
...
FROM Patients;

No scripts, triggers, proprietary transaction syntax, host language
procedures, etc.

[preet]

Re: Need Help With an Update Script

I agree with celko, creating views is the only way to solve this
problem, it will hide the discharge date till it is required on that
date.

provided you discharge date is a fixed 3 days after the admit date.





--------------------------

http://www.eecpindia.com

http://www.anchorfx.com

*** Sent via Developersdex http://www.developersdex.com ***

[Philipp Post]

Re: Need Help With an Update Script

From my point of view the business rule itself needs review instead of
the SQL (at least from the specifications given). I have problems to
imagine a hospital (assuming this is for one) where all patients stay
exactly 3 days - not more, not less. If this is an average time, it is
no good idea to persist such dummy date in the table as SQL has NULL
for "unknown" and you will most likely forget to correct some of the
dates lateron when you get the actual discharge date and you will have
difficulties in finding out what dates are actuals and what are dummy
dates. As the previous posters pointed out this could be masked in a
view (slightly amended version of the one Plamen posted):

CREATE VIEW MaskedDischarge Dates
AS
SELECT admit_date,
CASE WHEN dischage_date IS NULL
AND DATEDIFF(DAY, admit_date,
CURRENT_TIMESTA MP) >= 3
THEN DATEADD(DAY, 3, admit_date)
ELSE dischage_date
END AS masked_dischage _date
FROM Patients

I could rather make sense of a business rule which says: The
dischage_date will be entered once the actual discharge occurrs. If
the column contains NULL this indicates that the patient is still in.

brgds

Philipp Post

[--CELKO--]

Re: Need Help With an Update Script

>I could rather make sense of a business rule which says: The discharge_date will be entered once the actual discharge occurs. If the column contains NULL this indicates that the patient is still in. <<

Carry this thought one step further; add an "expected_disch arge_date"
and "actual_dischar ge_date" to the table. Fill the
"expected_disch arge_date" with a reasonable estimates based on other
data -- people with a hangnail should leave sooner than people who
lost limbs. You can do some planning and data mining with that
information.

[Eric Isaacs]

Re: Need Help With an Update Script

On May 29, 12:29 pm, --CELKO-- <jcelko...@eart hlink.netwrote:

data -- people with a hangnail should leave sooner than people who
lost limbs.  

Ah, but what about when the doc takes off a bit to much when they come
in with a hang nail and leave with a lost limb?

No really, it sounds like Celko is on the right track. You're asking
to store an estimated discharge date before the actual discharge
date. If the actual date is NULL, the person is still in the
hospital, dispite what the estimate says. That gives you more
information and more flexibility.

[Philipp Post]

Re: Need Help With an Update Script

add an "expected_disch arge_date" and "actual_dischar ge_date" to the table. Fill the
"expected_disch arge_date" with a reasonable estimates based on other
data <

Sounds to be the right way to me. Another approach (with the same
result) could be to maintain a table with ICD diagnostic codes
(assuming a hospitals database has the diagnoses for each patient
stored in another table) and their corresponding estimated stay days
based on the experience for this sickness. Those patients with multi-
sicknesses get at least the longest stay time of their sicknesses
assigned.

Then it becomes easy to run a statistic which shows the patiens which
needed longer for convalescence and those which convaleced quicker
than the average.

brgds

Philipp Post

[--CELKO--]

Re: Need Help With an Update Script

>Ah, but what about when the doc takes off a bit to much when they come in with a hang nail and leave with a lost limb? <<

And here I was, thinking it only happens to me!