Changing time boundaries for a natural day

Transact-SQL
1

Over the years I set up a rather complex system for the evaluation of presence time and productivity in our workshop. Now the manager has extended the timetable of the late shift to 2am. If the workers register now for any task, check-in or check-out after midnight, then this event is assigned to the next day and everything gets messed up.

As I don't want to change the entire concept of the application with a significant amount of queries, I thought of a more tricky workaround to solve this timeshift.

I collect the relevant data from the time-register-database according to the "usual concept" for one day:

DATEADD(day, DATEDIFF(day, 0, TransactionTime), 0) = DATEADD(day, DATEDIFF(day, 0, @CheckDay), 0)
month(TransactionTime) = @CheckMonth and year(TransactionTime) = @CheckYear
Datepart(ISO_WEEK,TransactionTime) = @IsoWeek and year(TransactionTime) = @CheckYear

Then when I group, I group by day, month or week.

So, what I though of was "moving" the data to a different time zone (if this is possible), for gathering, evaluation and analisis, while for display of some data in the reports I would need to treat the data as if the belong to the local time standard.

Does anybody have an idea how to interpret that data as if it belonged to a different time zone?
Any other hint or suggestion is highly apreciated.

Martin

2

Can you supply some DDL and sample data? If the data is stored in Check-in/Check-out, then it should be pretty easy to determine which goes with which. I wouldn't mess around with time-zones because you'd just be prolonging the inevitable

1 Like
3

Subtract 2 hours from all the original times (so 1:59:59AM becomes 11:59:59PM) , then treat them as ordinary "calendar" days for grouping/totalling purposes.

4

@mike01: I don't know how to turn this query into something that you could execute together with added sample Data. The query covers almost 200 lines!

@ScottPletcher: this is what I'm trying now, although I expect a dramatic drop in performance as i have about 80'000 records per year.

5

Nah, 80,000 rows should be peanuts for SQL Server. If it doesn't perform well enough, post the actual SQL here and we can tune it up.

1 Like
6

Agree with @mike01: DDL and sample data is always most helpful and is often absolutely required.