October 14, 2013 at 5:46 am
Hi All!
I have this Query:
select * from dbo.dutyrostershift A
join dbo.timeaccountmovement B ON B.ownerid = A.id
where( A.fromtime <> B.starttime or A.totime <> B.endtime)
And B.timeaccountid = 12
AND A.std = 1
AND B.ownertype = 1
Basically, it finds records which has mismatched start and endtimes - they should be indentical in dbo.dutyrostershift and dbo.timeaccountmovement in the pairs where the ownerid in dbo.timeaccountmovement is equal to the ID in tdbo.dutyrostershift.
The Query finds the records correctly.
From here, it should be simple to update dbo.timeaccountmovement with the correct values from dbo.dutyrostershift, but I must admit, that iI have not found out how yet. Any WHO can/will help?
Best regards
Edvard Korsbæk
October 14, 2013 at 6:00 am
Check out first on which value is going to be updated with which value with the following query:
select B.starttime, case when A.fromtime <> B.starttime then A.fromtime end as 'Updatedstarttime',
B.endtime, case when A.totime <> B.endtime then A.totime end as 'Updatedendtime',
*
from dbo.dutyrostershift A
join dbo.timeaccountmovement B ON B.ownerid = A.id
where( A.fromtime <> B.starttime or A.totime <> B.endtime)
And B.timeaccountid = 12
AND A.std = 1
AND B.ownertype = 1
For example, B.starttime should be getting updated with Updatedstarttime value (wherever it is populated).
If all looks good then try with following query to update:
updateB
set B.starttime = case when A.fromtime <> B.starttime then A.fromtime end,
B.endtime = case when A.totime <> B.endtime then A.totime end
from dbo.dutyrostershift A
join dbo.timeaccountmovement B ON B.ownerid = A.id
where( A.fromtime <> B.starttime or A.totime <> B.endtime)
And B.timeaccountid = 12
AND A.std = 1
AND B.ownertype = 1
Check this out. You must check the update statement under transaction and check the result. Based on that perform commit or rollback.
Note: This query is written without checking the underlying data and based on the generalized understanding based on column names.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply