September 20, 2012 at 3:06 pm
Not real sure how to set up an update from one table to another..
I have the following select
SELECT TOP (100) PERCENT dbo.PROGRESS_NOTE.LastModified, dbo.PROGRESS_NOTE.SUPERVISOR_SIGNED_DATE, dbo.USER_DEFINED_DATA.LastModified AS lastmodified,
dbo.USER_DEFINED_DATA.CAD701, dbo.PROGRESS_NOTE.PROGRESS_NOTE_TEMPLATE_MONIKER, dbo.RECORDED_SERVICE.STARTTIME,
dbo.RECORDED_SERVICE.ENDTIME, GETDATE() - 2 AS Expr2
FROM dbo.RECORDED_SERVICE
LEFT OUTER JOIN dbo.RECORDED_SERVICE_TO_PROGRESS_NOTE_COLLECTION ON
dbo.RECORDED_SERVICE.OID = dbo.RECORDED_SERVICE_TO_PROGRESS_NOTE_COLLECTION.OID
LEFT OUTER JOIN
dbo.PROGRESS_NOTE ON dbo.RECORDED_SERVICE_TO_PROGRESS_NOTE_COLLECTION.OID_LINK = dbo.PROGRESS_NOTE.OID
LEFT OUTER JOIN
dbo.USER_DEFINED_DATA ON dbo.PROGRESS_NOTE.USER_DEFINED_DATA_MONIKER = dbo.USER_DEFINED_DATA.OID
WHERE (dbo.PROGRESS_NOTE.SUPERVISOR_SIGNED_DATE >= GETDATE() - 2) AND
(dbo.PROGRESS_NOTE.PROGRESS_NOTE_TEMPLATE_MONIKER = '5F9300229956433190E845581F2D366A')
ORDER BY dbo.PROGRESS_NOTE.LastModified DESC
I want to update dbo.RECORDED_SERVICE.STARTTIME with lastmodified
and
update dbo.RECORDED_SERVICE.ENDTIME with lastmodified + 10 minutes...
Hope its kind of clear!!!
Thanks in Advance
Joe
September 20, 2012 at 3:12 pm
Joe,
Try this:
declare
@Nowsmalldatetime,
@NowPlus10Minssmalldatetime
set @Now = getdate()
set @NowPlus10Mins = DATEADD(MI, 10, getdate())
update your first table using @Now
Update your second table using @NowPlus10Mins
Mark
September 20, 2012 at 3:15 pm
Hey Mark, thanks for the quick response
I was a bit confusing..
lastmodified is a date on the table
can I use
declare
@Now smalldatetime,
@NowPlus10Mins smalldatetime
set @Now = lastmodified
set @NowPlus10Mins = DATEADD(MI, 10, lastmodified)
update your first table using @Now
Update your second table using @NowPlus10Mins
Thanks
September 20, 2012 at 3:21 pm
If that's a field in one of your tables, yes, like this:
select @Now = lastmodified <or whatever it's called>
from <table>
where <fill in your where clause here>
set @NowPlus10Minutes = dateadd(MI, 10, @Now)
Update your tables.
Mark
September 20, 2012 at 3:31 pm
Hi Mark,
Sorry I really s*ck at this...
I have this:
declare
@Now smalldatetime,
@NowPlus10Mins smalldatetime
set @NowPlus10Mins = dateadd(MI, 10, @Now)
select @Now = user_defined_data.LastModified
FROM dbo.RECORDED_SERVICE
LEFT OUTER JOIN dbo.RECORDED_SERVICE_TO_PROGRESS_NOTE_COLLECTION ON
dbo.RECORDED_SERVICE.OID = dbo.RECORDED_SERVICE_TO_PROGRESS_NOTE_COLLECTION.OID
LEFT OUTER JOIN
dbo.PROGRESS_NOTE ON dbo.RECORDED_SERVICE_TO_PROGRESS_NOTE_COLLECTION.OID_LINK = dbo.PROGRESS_NOTE.OID
LEFT OUTER JOIN
dbo.USER_DEFINED_DATA ON dbo.PROGRESS_NOTE.USER_DEFINED_DATA_MONIKER = dbo.USER_DEFINED_DATA.OID
WHERE (dbo.PROGRESS_NOTE.SUPERVISOR_SIGNED_DATE >= GETDATE() - 2) AND
(dbo.PROGRESS_NOTE.PROGRESS_NOTE_TEMPLATE_MONIKER = '5F9300229956433190E845581F2D366A')
ORDER BY dbo.PROGRESS_NOTE.LastModified DESC
but where and how do I format my updates?
Thx again...
September 20, 2012 at 3:46 pm
It's hard to say without knowing more about the tables. Unless you can post the schema, you may have to take this and run with it.
First, move you select statement populating @Now above the line populating @NowPlus10Minutes.
After that, you should have the 2 values. Run an update statement against both tables using them:
update dbo.RECORDED_SERVICE
set STARTTIME = @Now
where <some logic goes here>
and this:
update dbo.RECORDED_SERVICE
set ENDTIME = @NowPlus10Minutes
where <some logic goes here>
Make sure you have a back up of your database before running these updates in case something goes wrong.
Mark
September 20, 2012 at 4:11 pm
Mark.. do u hate me yet !! 🙂
Ok so this is what I have and What I "Want" to do....
Want: I want to update recorded_service.starttime with user_defined_data.lastmodifed and recorded_service.endtime with user_defined_data.lastmodifed + 10 minutes
I HAve:
declare
@Now smalldatetime,
@NowPlus10Mins smalldatetime
update dbo.RECORDED_SERVICE
set RECORDED_SERVICE.STARTTIME = @Now, dbo.RECORDED_SERVICE.endtime = dateadd(MI, 10, @Now)
select @Now = user_defined_data.LastModified
FROM dbo.RECORDED_SERVICE
LEFT OUTER JOIN dbo.RECORDED_SERVICE_TO_PROGRESS_NOTE_COLLECTION ON
dbo.RECORDED_SERVICE.OID = dbo.RECORDED_SERVICE_TO_PROGRESS_NOTE_COLLECTION.OID
LEFT OUTER JOIN
dbo.PROGRESS_NOTE ON dbo.RECORDED_SERVICE_TO_PROGRESS_NOTE_COLLECTION.OID_LINK = dbo.PROGRESS_NOTE.OID
LEFT OUTER JOIN
dbo.USER_DEFINED_DATA ON dbo.PROGRESS_NOTE.USER_DEFINED_DATA_MONIKER = dbo.USER_DEFINED_DATA.OID
WHERE (dbo.PROGRESS_NOTE.SUPERVISOR_SIGNED_DATE >= GETDATE() - 2) AND
(dbo.PROGRESS_NOTE.PROGRESS_NOTE_TEMPLATE_MONIKER = '5F9300229956433190E845581F2D366A')
ORDER BY dbo.PROGRESS_NOTE.LastModified DESC
Just wondering if this looks right?
Thanks
Joe
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply