November 28, 2012 at 5:36 am
Hi All,
I have a table (unfortunately the front application is from a third party so the table cannot be modified) it is a timecard entry system which has multiple start time and stop times (stored as varchar) in a single row. Obviously this is not ideal so I am trying to normalise the data. The start and stop times do not have information which I want to add. There is a single date field which I can append to the first start time and figure out the subsequent dtaes using dateadd and the duration (difference between start and stop times) Simply concatenatingt the date to the start and stop times wont work because it doesnt account for day boundaries. I wrote the following recursive query which works but does not perform particularly well. Other than using a cursor etc is there a set base approach I could use
CORRECTEDTIMESHEET as (
SELECT
cast(ShiftDate as datetime) + cast(StartTime as datetime) [StartTime]
,DateAdd(mi, Duration, cast(ShiftDate as datetime) + cast(StartTime as datetime)) [FinishTime]
FROM TIMESHEET
WHERE Row = 1
UNION ALL
SELECT
CTS.[FinishTime] [StartTime]
,DateAdd(mi, TS.Duration, CTS.[FinishTime]) [EndTime]
FROM TIMESHEET TS
INNER JOIN CORRECTEDTIMESHEET CTS
ON TS.GUID = CTS.GUID AND TS.Row = CTS.Row + 1
)
November 28, 2012 at 6:58 am
Create a unique clustered index on column [row].
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 28, 2012 at 7:35 am
If you could post some sample data and ddl scripts as per this article --> http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D, then I'm sure lots of people would be happy to help. Otherwise, we're just guessing.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply