需要有关更新脚本的帮助 [英] Need Help With an Update Script
问题描述
嘿伙计们，
我一直在努力创建一个SQL更新语句，它将执行
跟随：
指定出院日期=在录取日期后三天。
踢球者是他们希望SQL在录取日期后等待3天
才能分配出院日期。
我想过用这句话：
更新患者套餐
Visit_DischargeDate = Visit_AdmitDate + 3
这里的问题是它会在脚本运行时每隔
指定放电日期。如果一个人今天被录取，它会给他们5月31日的出院日期，但是我不希望它在5月31日之前分配
的出院日期。 br />
无论如何都要这样做吗？
谢谢！
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_DischargeDate=Visit_AdmitDate + 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!
推荐答案
>无论如何都要这样做吗？ <<
一旦你知道它就放入出院日期，然后在隐藏这个日期的表格中将一个VIEW放在
上，直到CURRENT_TIMESTAMP使它成为这个规范的白痴神奇地出现了。$ / b
请记住，表格不是文件;表格可以是虚拟的（VIEW，派生，
CTE等），而文件必须具体化。没有必要一遍又一遍地写入磁盘存储器。
>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_TIMESTAMP 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.
如果规则是出院日期总是在承认后3天日期
（听起来不对），那么你可以使用一个动态的视图
如果日期不是将来计算3天（否则值）将是
NULL）：
CREATE VIEW PatientsDischargeDate
AS
SELECT admit_date，
CASE WHEN DATEDIFF（DAY，admit_date，
CURRENT_TIMESTAMP）> = 3
那么DATEADD（DAY，3，admit_date）
END AS dischage_date
患者;
如果必须坚持约会，你可以安排一份工作来运行每天午夜
并更新卸货日期尚未分配的栏目（使用相同的逻辑检查日期是否未来）。
HTH，
Plamen Ratchev
http://www.SQLStudio.com
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 PatientsDischargeDate
AS
SELECT admit_date,
CASE WHEN DATEDIFF(DAY, admit_date,
CURRENT_TIMESTAMP) >= 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
5月28日， 11:38 * am， - CelKO--< jcelko ... @ earthlink.netwrote：
On May 28, 11:38*am, --CELKO-- <jcelko...@earthlink.netwrote:
无论如何都要这样做吗？ <<
IS there anyway to do this? <<
一旦你知道就放入出院日期，然后把一个VIEW放在
隐藏这个的表上直到CURRENT_TIMESTAMP成功的日期
神奇地出现了这个规格的白痴。
请记住，表格不是文件;表格可以是虚拟的（VIEW，派生，
CTE等），而文件必须具体化。 *无需一遍又一遍地向磁盘存储写入
。
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_TIMESTAMP 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.
听起来很复杂。我想出了这个剧本：
BEGIN TRANSACTION
更新病人
set visit_dischargedate = visit_admitdate + 3
where visit_dischargedate ='''1900-01-01 00：00：00.000''
和visit_admitdate = getdate（） - 3
ROLLBACK TRANSACTION
这可行吗？如果承认日期等于
当前日期--3。如果承认日期是今天，它将不会
指定一个排放日期，直到5月31日。
That sounds pretty complicated. I came up with this script:
BEGIN TRANSACTION
update patients
set visit_dischargedate = visit_admitdate + 3
where visit_dischargedate = ''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.
这篇关于需要有关更新脚本的帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
