Is there a better way?

Question

Post reply

Is there a better way?

TriggerMe

SSChampion

Points: 11752
More actions
May 13, 2006 at 8:48 pm

#113019

I have the following Stored Procedure that basically perfroms an Insert and then 4 Updates to the same table that the Insert occurred on.
My question is, can the 4 Updates be done in a better way like 1 Update? Perhaps a Function or something to that exetent?
USP_InsertLogTime @EmployeeID INT, @CompanyID INT, @login DATETIME, @Logout DATETIME
AS
DECLARE @LogTimeID INT
IF @login > @Logout
BEGIN
RAISERROR('StartDate MUST be less than EndDate', 16, 1)
RETURN
END
IF @login IS NULL OR @Logout IS NULL
BEGIN
RAISERROR('Must enter both StartDate & EndDate', 16, 1)
RETURN
END
INSERT LogTime (EmployeeID, CompanyID, LogIn, LogOut)
VALUES (@EmployeeID, @CompanyID, @login, @Logout)
SET @LogTimeID = SCOPE_IDENTITY()
UPDATE LogTime
SET DaysTotal = X.Sec / (24 * 60 * 60)
FROM
(SELECT DATEDIFF(ss, @login, @Logout) AS 'Sec') X
WHERE LogTimeID = @LogTimeID
UPDATE LogTime
SET HoursTotal = X.Sec / (60 * 60) % 24
FROM
(SELECT DATEDIFF(ss, @login, @Logout) AS 'Sec') X
WHERE LogTimeID = @LogTimeID
UPDATE LogTime
SET MinutesTotal = (X.Sec / 60) % 60
FROM
(SELECT DATEDIFF(ss, @login, @Logout) AS 'Sec') X
WHERE LogTimeID = @LogTimeID
UPDATE LogTime
SET SecondsTotal = X.Sec % 60
FROM
(SELECT DATEDIFF(ss, @login, @Logout) AS 'Sec') X
WHERE LogTimeID = @LogTimeID
Kindest Regards,

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

Using your current table structure, you could do this...

INSERT LogTime (EmployeeID, CompanyID, LogIn, LogOut,

DaysTotal,HoursTotal,MinutesTotal,SecondsTotal)

SELECT @EmployeeID, @CompanyID, @login, @Logout,

DaysTotal = X.Sec / (24 * 60 * 60),

HoursTotal = X.Sec / (60 * 60) % 24,

MinutesTotal = (X.Sec / 60) % 60,

SecondsTotal = X.Sec % 60

FROM (SELECT DATEDIFF(ss, @login, @Logout) AS 'Sec') X

... to get rid of the 4 "expensive" updates.

However, I'd like to suggest that this is not a very good way to total durations for many reasons including the fact that you'll have to do still more base 24 and base 60 math if you ever attempt to aggragate the durations using a Group By or other mechanism...

If you absolutely insist on storing a "Duration" field, I'd get rid of the other columns having to do with time parts and do it like this...

INSERT LogTime (EmployeeID, CompanyID, LogIn, LogOut,

Duration)

SELECT @EmployeeID, @CompanyID, @login, @Logout,

@Logout-@Login

...because DURATION would be a DATETIME FIELD, you can do all sorts of very simple aggragations using Group By's, etc.

I believe your only problem then would be to display the results in the days, hours, minutes, seconds format that I suspect you are looking for. There's lot's of different ways to do that type of formatting posted on this forum... here's one (if the Duration column contains @Logout-@Login) that produces a "justified" column...

SELECT STR(DATEDIFF(dd,0,Duration)) + ' Days '

+ CONVERT(CHAR(8),Duration,108)

Many will tell you that type of formatting should be done in an application... I don't always agree with that especially when there's no application (GUI)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TriggerMe SSChampion Points: 11752 More actions · Answer 2

Jeff,

Thanks for your solution to the question however, thanks for suggesting a better way of storing the data. I was never keen on the Days, Hours Minutes & Seconds Columns. Somhow something was trying to tell me that this was not a good idea!

However, thats why this forum is available! Not just for answers but for suggestions on how to do things much more efficiently!

I will adopt your suggestion of the Duration field.

Well done Jeff.

Kindest Regards,

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

If I may make an additional suggestion... it is not necessary to store the data in a Duration column... you could simply make a calculated column consisting of the the formula Logout-Login. Everything else, save the Insert, would be identical.

And, thank you for feedback... I didn't know if you were allowed to change the table or not.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TriggerMe SSChampion Points: 11752 More actions · Answer 4

The duration field along with the others were purely for reporting puposes! I think your correct there is probably no need to have the duration field as well.

I'm aloud to change it as this is a newly designed Database and its still currently in the Conceptual stage!

Thanks.

Kindest Regards,

Marshall Smith SSCrazy Points: 2041 More actions · Answer 5

Depending on the amount and type of reporting you are doing, storing the Duration as a computed column may be beneficial to you. It has some cost to it, but it also has some benefit. Given that you seem to be focusing on that duration for reporting, and I doubt that your Logout and Login values will be updated, I think you have a good scenario for a computed column. Check it out in BOL.