Actually John, that was a fortunate coincidence, since it was actually part of the requirements to ignore any extra hours at the start or end of the day. I was...

Yeah that will work provided your input query isn't anything complex.

But if you start having more complex queries in your execute SQL statement, you may encounter an error because the...

Are you trying to write an Execute SQL task and using @ProductionBeforeCount directly?

If so, it won't work, because it doesn't exist within the scope of that task.

What you can do...

As a possible suggestion, maybe try putting in a few Execute SQL tasks instead of trying to do it all in one?

Have three execute SQL Tasks:

SELECT COUNT(*) AS 'ProdCount' FROM...

Indeed - it's for some special calculations at the company I'm working at.

In the end I got the system working, using a similar query to the one that I posted...

You might want to consider doing it in two steps then. First, set up the table schema on your new system, and then use a bulk load to transfer the...

Excellent, tested it with a second user, and it still works, after a few minor modifications (in bold):

CREATE TABLE #PlannedHours

(

ID INT IDENTITY PRIMARY KEY,

UserID INT,

[TimeStart] SMALLDATETIME,

[TimeEnd] SMALLDATETIME,

[Type] TINYINT

)

CREATE TABLE #ActualHours

(

ID...

Ah good call - forgot I had modified my data set when I was experimenting with different options. Perfect, that works! Thanks!

That one unfortunately gives me a result of 92 minutes, while the actual result should be 62 minutes.

That's pretty much the exact problem scenario I just painted in my last post drew 😛

Still thinking about how I can handle that. Haven't come up with an answer yet.

Mm.. yeah I'll worry about tackling the multiple UserIDs after.

Discovered a problem though:

Let's say I change the data to this:

INSERT INTO #ActualHours([UserID], [TimeStart], [TimeEnd])

VALUES (1, '2011-09-27 08:00:00', '2011-09-27 10:30:00') --...

Yeah I know what you mean Sean. Unfortunately, no, I don't have anything like that.

But I wonder if this solution would work:

WITH cte AS

(

SELECT

#ActualHours.UserID,

#ActualHours.TimeStart,

#ActualHours.TimeEnd,

ABS(DATEDIFF(mi, #PlannedHours.TimeStart, #ActualHours.TimeStart)) AS StartDiff,

ABS(DATEDIFF(mi,...

Yeah it would be a sum of the differences - the minutes which are out of sync with the schedule that the person should be working. The 15 minutes over...

In this case Drew, it's supposed to be a sum of both the times that the person worked when they were not supposed to, and also didn't work when they...

Yeah sorry looking at the data I realise that I made a mistake on that record. It should be:

INSERT INTO #PlannedHours ([UserID], [TimeStart], [TimeEnd], [Type])

VALUES (1, '2011-09-27 08:00:00', '2011-09-27 09:45:00',...