December 8, 2016 at 1:40 am
Originally I had this query:
Original value for starttime is 1800001 (Clarion time figures based upon 1/100 of a sec since 00:00 +1)
Update dbo.timeaccountmovement set
DATO = Getdate()
,starttime = 2520001
,endtime = 5040001
,minutes = CASE WHEN days = 1 THEN (24*60*60*100)+(endtime-starttime) ELSE endtime-starttime END
,duration = cast((CASE WHEN days = 1 THEN (24*60*60*100)-(starttime-endtime) ELSE endtime-starttime END )/100/60/60 as varchar)
+ ':' + left( cast (abs(((CASE WHEN days = 1 THEN (24*60*60*100)-(endtime-starttime) ELSE endtime-starttime END )/100/60)-
(round((CASE WHEN days = 1 THEN (24*60*60*100)-(endtime-starttime) ELSE endtime-starttime END )/100/60/60,0)*60))as varchar) +'0',2)
where ownertype = 1 and ownerid = 2778017 and timeaccountid = 8
You will see, that i first set Starttime to a new value, and later uses starttime in the formulas.
Same for endtime.
what happens is, that the SQL server uses the original starttime 1800001, and not 2520001 as I expected in the formulas.
So, simple solution was to make the update in two steps.
Question:
Why did the original query not work, and which general rule have i overlooked?
Trying to get better...
Best regards
Edvard Korsbæk
December 8, 2016 at 2:12 am
Edvard
Yes, that's how it works. Try declaring @starttime and @endtime variables and setting them to the new values. You can then use them in the update query instead of the starttime and endtime columns.
John
December 8, 2016 at 2:17 am
So, general rule is:
Al values are read at start of (update) query from DB, and does not change before theye are read back again.
Right?
Best regards
Edvard
December 8, 2016 at 2:22 am
Edvard
Yes. Can you imagine the confusion if it were any other way? You wouldn't know whether you were getting old values or new values!
John
December 13, 2016 at 9:02 pm
John Mitchell-245523 (12/8/2016)
EdvardYes, that's how it works. Try declaring @starttime and @endtime variables and setting them to the new values. You can then use them in the update query instead of the starttime and endtime columns.
John
Should be looking like this:
Update dbo.timeaccountmovement set
DATO = Getdate()
,@starttime = 2520001
,starttime = @starttime
,@endtime = 5040001
,endtime = @endtime
,@minutes = CASE WHEN days = 1 THEN (24*60*60*100)+(@endtime-@starttime) ELSE @endtime-@starttime END
,minutes = @minutes
,duration = cast(@minutes /100/60/60 as varchar) + ':' + left( cast (abs((@minutes/100/60)-
(round(@minutes/100/60/60,0)*60))as varchar) +'0',2)
where ownertype = 1 and ownerid = 2778017 and timeaccountid = 8
_____________
Code for TallyGenerator
December 13, 2016 at 9:36 pm
BTW,
you've got an error in your duration calculations.
Here is the testing script:
SELECT *,
duration_datetime = DATEADD(ss, T2.minutes/100, 0),
duration_orig = cast(minutes /100/60/60 as varchar) + ':' + left( cast (abs((minutes/100/60)- (round(minutes/100/60/60,0)*60))as varchar) +'0',2),
duration_correct = cast(minutes /100/60/60 as varchar) + ':' + REPLACE(STR(DATEPART(MINUTE, DATEADD(ss, T2.minutes/100, 0)), 2), ' ', '0')
FROM (
SELECT minutes = days * 24*60*60*100 + endtime-starttime , *
FROM (
SELECT 1 Days, 2520001 starttime, 5040001 endtime
UNION
SELECT 0 Days, 2520001 starttime, 5040001 endtime
UNION
SELECT 0 Days, 2520001 starttime, 5046501 endtime
) T
)T2
_____________
Code for TallyGenerator
December 13, 2016 at 11:46 pm
Sergiy (12/13/2016)
BTW,you've got an error in your duration calculations.
You are totally right.
Wonder a bit about how many calculations it has done wrong - I am using it in a lot of places.
Thank you - add for yourself some words of gratitude - Neither SQL or english is my natural language!
Best regards
Edvard
December 18, 2016 at 5:17 pm
Edvard Korsbæk (12/8/2016)
Originally I had this query:Original value for starttime is 1800001 (Clarion time figures based upon 1/100 of a sec since 00:00 +1)
Update dbo.timeaccountmovement set
DATO = Getdate()
,starttime = 2520001
,endtime = 5040001
,minutes = CASE WHEN days = 1 THEN (24*60*60*100)+(endtime-starttime) ELSE endtime-starttime END
,duration = cast((CASE WHEN days = 1 THEN (24*60*60*100)-(starttime-endtime) ELSE endtime-starttime END )/100/60/60 as varchar)
+ ':' + left( cast (abs(((CASE WHEN days = 1 THEN (24*60*60*100)-(endtime-starttime) ELSE endtime-starttime END )/100/60)-
(round((CASE WHEN days = 1 THEN (24*60*60*100)-(endtime-starttime) ELSE endtime-starttime END )/100/60/60,0)*60))as varchar) +'0',2)
where ownertype = 1 and ownerid = 2778017 and timeaccountid = 8
You will see, that i first set Starttime to a new value, and later uses starttime in the formulas.
Same for endtime.
what happens is, that the SQL server uses the original starttime 1800001, and not 2520001 as I expected in the formulas.
So, simple solution was to make the update in two steps.
Question:
Why did the original query not work, and which general rule have i overlooked?
Trying to get better...
Best regards
Edvard Korsbæk
Where did "days" come from and how is it used?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2016 at 11:02 pm
Days (tinyint) is a field in timeaccountmovement, and is used for 'Over midnight'
IF Days = o, then starttime and endtime is the same day
if days = 1, then endtime is the day after starttime.
If you have a duty from today 16:00 until tomorrow midnight, its something different from today 16:00 to midnight today.
Best regards
Edvard Korsbæk
December 26, 2016 at 9:03 pm
John Mitchell-245523 (12/8/2016)
EdvardYes. Can you imagine the confusion if it were any other way? You wouldn't know whether you were getting old values or new values!
John
No confusion at all if you know what you're doing. 😉 Don't forget the 3 part update in BOL. Wouldn't be a "Quirky Update" because it wouldn't be using an ordered previous row.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 26, 2016 at 9:08 pm
Edvard Korsbæk (12/8/2016)
Originally I had this query:Original value for starttime is 1800001 (Clarion time figures based upon 1/100 of a sec since 00:00 +1)
Update dbo.timeaccountmovement set
DATO = Getdate()
,starttime = 2520001
,endtime = 5040001
,minutes = CASE WHEN days = 1 THEN (24*60*60*100)+(endtime-starttime) ELSE endtime-starttime END
,duration = cast((CASE WHEN days = 1 THEN (24*60*60*100)-(starttime-endtime) ELSE endtime-starttime END )/100/60/60 as varchar)
+ ':' + left( cast (abs(((CASE WHEN days = 1 THEN (24*60*60*100)-(endtime-starttime) ELSE endtime-starttime END )/100/60)-
(round((CASE WHEN days = 1 THEN (24*60*60*100)-(endtime-starttime) ELSE endtime-starttime END )/100/60/60,0)*60))as varchar) +'0',2)
where ownertype = 1 and ownerid = 2778017 and timeaccountid = 8
You will see, that i first set Starttime to a new value, and later uses starttime in the formulas.
Same for endtime.
what happens is, that the SQL server uses the original starttime 1800001, and not 2520001 as I expected in the formulas.
So, simple solution was to make the update in two steps.
Question:
Why did the original query not work, and which general rule have i overlooked?
Trying to get better...
Best regards
Edvard Korsbæk
I guess my question here is, why aren't you simply using variables called @StartTime and @EndTime if you don't want to use the values from the table? Sergiy's answer takes care of that.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 26, 2016 at 10:32 pm
Edvard Korsbæk (12/18/2016)
Days (tinyint) is a field in timeaccountmovement, and is used for 'Over midnight'IF Days = o, then starttime and endtime is the same day
if days = 1, then endtime is the day after starttime.
If you have a duty from today 16:00 until tomorrow midnight, its something different from today 16:00 to midnight today.
Best regards
Edvard Korsbæk
Seems strange that someone would pluralize the name of a column if it could only contain 0 or 1. Have you checked to make sure that it doesn't contain more than those two values? It's important because, so far, everyone is testing the Days column for "1". If it's not 0 or 1, then big surprise with wrong answers unless they test for both values and fail and equation on the "ELSE".
--Jeff Moden
Change is inevitable... Change for the better is not.
December 26, 2016 at 11:28 pm
With the idea that UPDATE in SQL Server can do both variable and column assignments in the same statement and with the understanding that I've not tested this particular bit of code against your table and following Sergiy's lead of using cascading variables in both SELECTs and UPDATEs, here's what I'd do unless the formulas that Sergiy used proved to be faster.
Also, if they ever make it so "Days" can be > 1, this will still handle it up to 248 days before the INT variable is overrun. You could change @cDuration from INT to BIGINT if you want to extend that by quite a bit.
--===== These variables would become parameters for a stored procedure
DECLARE @cStartTime INT = 2520001 --These are "Clarion" times of # of 1/100ths of a second past midnight + 1.
,@cEndTime INT = 5040001
,@OwnerType INT = 1
,@OwnerID INT = 2778017
,@TimeAccountID INT = 8
;
--===== These variables would be local variables
DECLARE @cDuration INT
,@dtDuration DATETIME
;
UPDATE tgt
SET DATO = GETDATE()
,StartTime = @cStartTime
,EndTime = @cEndTime
,@cDuration = [Days]*8640000+@cEndTime-@cStartTime+3000 --3000 is 30 seconds for rounding
,@dtDuration = DATEADD(ms,@cDuration%100-1,DATEADD(ss,@cDuration/100,0)) --As a DATETIME
,[Minutes] = DATEDIFF(mi,0,@dtDuration)
,Duration = CONVERT(VARCHAR(10),DATEDIFF(hh,0,@dtDuration)) --Hours, can be > 24
+ SUBSTRING(CONVERT(CHAR(8),@dtDuration,108),3,3) --The rest as :mi
FROM dbo.timeaccountmovement tgt
WHERE tgt.ownertype = @OwnerType
AND tgt.ownerid = @OwnerID
AND tgt.timeaccountid = @TimeAccountID
;
[EDIT] Forgot about [Minutes] and added that calculation.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 27, 2016 at 4:40 am
I guess my question here is, why aren't you simply using variables called @StartTime and @EndTime if you don't want to use the values from the table? Sergiy's answer takes care of that.
[SNIP]
Wil do in the future based upon my present knowledge.
December 27, 2016 at 4:43 am
Seems strange that someone would pluralize the name of a column if it could only contain 0 or 1. Have you checked to make sure that it doesn't contain more than those two values? It's important because, so far, everyone is testing the Days column for "1". If it's not 0 or 1, then big surprise with wrong answers unless they test for both values and fail and equation on the "ELSE".
[SNIP]
Its quite some years ago the DB was new - As i remember it, we used Days as description to tell, that a duty was over more than one day in contrast to be on the same day.
I know 100% sure, that i have never supported duties over two midnights. They exists, byt i do not support them
Best regards
Edvard Korsbæk
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply