Optimize my code

BobRosas
BobRosas used Ask the Experts™
on
I have a report with an access front end and SQLServer backend.  The report is linked to the SQL Server tables but all the code is access.  When I run a specific report for all supervisors it takes about 3 seconds.  EE helped me with some very complex SQL code so that I could run a similiar report in Report Services.  The code works,,,the problem is, when I initially got help, Supervisor was a required dropdown.  Now it needs to be like the access report so you can run it for all Supervisors.  I changed the code, but when I run it in SQL server it takes 45 seconds (with access it's 3 seconds).  When I tried it in Report Services it won't even run, it times out.  All I added was an OR statement to the WHERE statement to run when Supervisor is Null.  Did I do it wrong?  The code is complex enough that I'm afraid to ask what can be done to speed things up but I'm hoping someone can suggest something that a novice SQL programmer can do.

Here is the code...
declare @From datetime, @To datetime, @Supervisor int
SET @From = '01/01/12';
SET @To = '01/05/12';
SET @Supervisor = 212;

SELECT CONVERT(varchar(10), TS_Day, 101) AS Date,
       EmployeeId,
       Name,
       UserName AS Supervisor,
       CONVERT(varchar, DATEDIFF(hh, 0, DATEADD(ss,SUM(ts_seconds + ts_manual_seconds + ts_approved_break_seconds), 0))) + SUBSTRING
      (CONVERT(varchar(8),DATEADD(ss,SUM(ts_seconds + ts_manual_seconds + ts_approved_break_seconds), 0), 114), 3, 3) AS HrsMin
FROM (SELECT times.TS_Day,
      L.EmployeeId,
      L.LastName + ', ' + L.FirstName AS Name,
      U.UserName,
      SUM(times.TS_Seconds) AS ts_seconds,
      SUM(times.TS_Manual_Seconds) AS ts_manual_seconds,
      SUM(times.TS_Break_Seconds) AS ts_break_seconds,
      CASE
        WHEN SUM([TS_Break_Seconds]) < 1800
        THEN SUM([TS_Break_Seconds])
        ELSE 1800
        END AS ts_approved_break_seconds
            FROM (SELECT Company,
                  EmployeeId,
                  CONVERT(datetime, CONVERT(varchar(8), TimeOut, 112)) AS TS_Day,
                  TimeIn,
                  TimeOut,
                  BreakFlag,
                  DATEDIFF(ss,TimeIn, TimeOut) AS TS_Seconds,
                  ISNULL(TimeSheetMinutes, 0) * 60 AS TS_Manual_Seconds,
                  ISNULL ((SELECT TOP (1)
                              CASE
                              WHEN t1.Breakflag = 1
                              THEN
                               CASE
                               WHEN datediff(ss, t1.timeout, isnull(t2.timein, t1.timeout)) BETWEEN 900 AND 1500
                               THEN 900
                               WHEN datediff(ss, t1.timeout, isnull(t2.timein, t1.timeout)) < 901
                               THEN datediff(ss, t1.timeout, isnull(t2.timein, t1.timeout))
                               ELSE 0
                               END
                              ELSE 0
                              END AS Expr1
                        FROM dbo.EmployeeHours AS t2
                        WHERE (EmployeeId = t1.EmployeeId) AND
                          (Company = t1.Company) AND
                          (CONVERT(varchar(8), TimeIn) = CONVERT(varchar(8), t1.TimeIn)) AND
                          (TimeIn > t1.TimeIn) OR
                          (EmployeeId = t1.EmployeeId) AND
                          (Company = t1.Company) AND
                          (CONVERT(varchar(8), TimeIn) = CONVERT(varchar(8), t1.TimeIn)) AND
                          (TimeIn = t1.TimeIn) AND
                          (TimeOut > t1.TimeOut)
                        ORDER BY TimeIn, TimeOut, RecordId), 0
                        ) AS TS_Break_Seconds
                  FROM dbo.EmployeeHours AS t1
                ) AS times
       INNER JOIN
       dbo.EmployeeList AS L ON
       times.EmployeeId = L.EmployeeId AND
       times.Company = L.Company
       LEFT OUTER JOIN
       dbo.UserList AS U ON L.ManagerUID = U.UID AND
       L.Company = U.Company
--I added from HERE to...
       WHERE (CONVERT(datetime, CONVERT(char(8), times.TimeIn, 112)) BETWEEN @FROM AND @TO) AND
       (L.Suspend = 0) AND
       (times.Company = 1) AND
       (@Supervisor IS NULL)
       OR
--HERE
       (CONVERT(datetime, CONVERT(char(8), times.TimeIn, 112)) BETWEEN @FROM AND @TO) AND
       (L.Suspend = 0) AND
       (times.Company = 1) AND
       (U.UID = @Supervisor)
       GROUP BY times.TS_Day,
                times.EmployeeId,
                times.TS_Day,
                L.EmployeeId,
                L.LastName + ', ' + L.FirstName,
                U.UserName
    ) AS daily
GROUP BY TS_Day,
         EmployeeId,
         Name,
         UserName
HAVING (SUM(ts_seconds + ts_manual_seconds + ts_approved_break_seconds) / 60 > 540)

ORDER BY TS_Day, Name, EmployeeId
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Can you run EITHER the version selecting a supervisor OR the version for all supervisors? If so, split the thing and see if the all-supervisors is faster than 45 seconds.

Author

Commented:
Thanks for you quick response!  
I commented out my code and ran it for just 1 supervisor and it took 2 seconds.
Then I commented out the existing code and used just the code I added for All Supervisors and it took 44 seconds...the results was only 63 records (which looks right) and the time span I used was just for 5 days (which is less than what it's normally run for).  If my code gathers all the records for the specific date range first (which I think it does) then there should not be that many records even if it's run for all Supervisors.

Commented:
At this point you get lazy and look at how often the data changes. If it only changes monthly, for example, then run it on the first of the month into a table and query from that whenever people want it. Spending 45 seconds a month once is much better than spending it every time someone gets curious and asks for it. Then you can get on with other interesting stuff!
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
Thank you for your input.  Unfortunately this is a report to verify data.  So it gets run, corrections are made, then it's run again.  So having static data doesn't work.  Plus I have a number of other reports that will need similiar code.  What I really don't understand is why Access is what...15x faster.  Because it's run by all supervisors and for the same time span takes only 3 seconds.  We are trying to get rid of access so I need to get these reports transferred.  Can you make any other suggestions?

Author

Commented:
Thank you for your help.

Commented:
Bob,

From the info we have, I can't tell why Access is so much faster either. Normally it isn't, because it pulls all the data across the net in order to make joins (unless they're pass-through queries), which is way slower than using SQL.

In your code you're using a number of queries in the place of tables. Try taking each one of these queries separately and testing it against the data with one and with all supervisors (if applicable). Hopefully you'll find that there's one that suddenly gets slow - i.e. is the main cause of the whole thing being slow. Then concentrate your brain-power on that one alone.

hth

Mike

Author

Commented:
Mike,
Thanks for your help.  I tried running an inner query and noticed it was gathering days I didn't need so I moved the code to narrow the search to the inner query.  If I limit the scope of an inner query shouldn't it take less time to run?  Yet with the following code it now takes 52 seconds (longer) to run.


declare @From datetime, @To datetime, @Supervisor int
SET @From = '01/01/12';
SET @To = '01/05/12';
SET @Supervisor = 212;

SELECT t1.Company,
                  t1.EmployeeId,
                  CONVERT(datetime, CONVERT(varchar(8), TimeOut, 112)) AS TS_Day,
                  TimeIn,
                  TimeOut,
                  BreakFlag,
                  DATEDIFF(ss,TimeIn, TimeOut) AS TS_Seconds,
                  ISNULL(TimeSheetMinutes, 0) * 60 AS TS_Manual_Seconds,
                  ISNULL ((SELECT TOP (1)
                              CASE
                              WHEN t1.Breakflag = 1
                              THEN
                               CASE
                               WHEN datediff(ss, t1.timeout, isnull(t2.timein, t1.timeout)) BETWEEN 900 AND 1500
                               THEN 900
                               WHEN datediff(ss, t1.timeout, isnull(t2.timein, t1.timeout)) < 901
                               THEN datediff(ss, t1.timeout, isnull(t2.timein, t1.timeout))
                               ELSE 0
                               END
                              ELSE 0
                              END AS Expr1
                        FROM dbo.EmployeeHours AS t2
                        WHERE (EmployeeId = t1.EmployeeId) AND
                          (Company = t1.Company) AND
                          (CONVERT(datetime, CONVERT(char(8), t1.TimeIn, 112)) BETWEEN @FROM AND @TO) AND
                          (L.Suspend = 0) AND
                                      (t1.Company = 1) AND
                          (CONVERT(varchar(8), TimeIn) = CONVERT(varchar(8), t1.TimeIn)) AND
                          (TimeIn > t1.TimeIn) OR
                          (EmployeeId = t1.EmployeeId) AND
                          (Company = t1.Company) AND
                          (CONVERT(varchar(8), TimeIn) = CONVERT(varchar(8), t1.TimeIn)) AND
                          (TimeIn = t1.TimeIn) AND
                          (TimeOut > t1.TimeOut)
                        ORDER BY TimeIn, TimeOut, RecordId), 0
                        ) AS TS_Break_Seconds
                  FROM dbo.EmployeeHours AS t1
                  INNER JOIN
       dbo.EmployeeList AS L ON
       t1.EmployeeId = L.EmployeeId AND
       t1.Company = L.Company
       LEFT OUTER JOIN
       dbo.UserList AS U ON L.ManagerUID = U.UID AND
       L.Company = U.Company
                        WHERE      (CONVERT(datetime, CONVERT(char(8), t1.TimeIn, 112)) BETWEEN @FROM AND @TO)

Commented:
You have this:
(CONVERT(datetime, CONVERT(char(8), t1.TimeIn, 112)) BETWEEN @FROM AND @TO)

which will do a couple of converts on every single row of the table. Then you do it again on the next line.

Try doing the conversions on the @TO and @FROM variables so that they are the same type as the TimeIn and TimeOut fields. That should save your poor cpu a heap of work!

Then you have
WHEN datediff(ss, t1.timeout, isnull(t2.timein, t1.timeout)) BETWEEN 900 AND 1500
THEN 900
WHEN datediff(ss, t1.timeout, isnull(t2.timein, t1.timeout)) < 901
THEN datediff(ss, t1.timeout, isnull(t2.timein, t1.timeout))
ELSE 0

Which might be better as something like
case
when t2.timein is null then 0
when datediff(ss, t1.timeout, t2.timein) > 900 then 900
else datediff(ss, t1.timeout, t2.timein)
end
(assuming I didn't miss something !)

BTW, your two datetimes will have zero seconds, just dates, so try this code:
select ((datediff(minute, @From, @To)) / 4) --gives 1440, the nr. of mins in a day.

if you're looking for "between Monday and Friday", for instance, including Friday, and Monday is 1/1 and Friday is 1/5, then I think you're going to need to go from the very first instant of Monday to just before the very first instant of Saturday!

hth

Mike

Author

Commented:
Thank you so much for your help.  What you are saying makes sense.  I tried to make changes accordingly.  I even took out some needed "converts" just to try and speed things up.  Even with all that it still takes 43 seconds (which is only 2 seconds off the original run time).  I went ahead and posted the code again...maybe I missed something or didn't do it right?  I still can't find the bottle neck.  Are there any other suggestions you would offer?

declare @From datetime, @To datetime, @Supervisor int
SET @From = '01/01/12';
SET @To = '01/05/12';
SET @Supervisor = 212;

SELECT t1.Company,
       t1.EmployeeId,
       CONVERT(datetime, CONVERT(varchar(8), TimeOut, 112)) AS TS_Day,
       TimeIn,
       TimeOut,
       BreakFlag,
       DATEDIFF(ss,TimeIn, TimeOut) AS TS_Seconds,
       ISNULL(TimeSheetMinutes, 0) * 60 AS TS_Manual_Seconds,
       ISNULL ((SELECT TOP (1)
         CASE
           WHEN t1.Breakflag = 1
           THEN
             CASE
               WHEN t2.timein is null
               THEN 0
                     WHEN datediff(ss, t1.timeout, t2.timein) > 900
                   THEN 900
                   ELSE datediff(ss, t1.timeout, t2.timein)
             END
         ELSE 0
         END AS Expr1
       FROM dbo.EmployeeHours AS t2
       WHERE (EmployeeId = t1.EmployeeId)
       AND (Company = t1.Company)
       AND (L.Suspend = 0)
       AND (t1.Company = 1)
       AND (TimeIn > t1.TimeIn)
       OR (EmployeeId = t1.EmployeeId)
       AND (Company = t1.Company)
       AND (TimeIn = t1.TimeIn)
       AND (TimeOut > t1.TimeOut)
       ORDER BY Employeeid, TimeIn, TimeOut, RecordId), 0) AS TS_Break_Seconds
       FROM dbo.EmployeeHours AS t1
       INNER JOIN dbo.EmployeeList AS L
       ON t1.EmployeeId = L.EmployeeId
       AND t1.Company = L.Company
         WHERE      (CONVERT(datetime, CONVERT(char(8), t1.TimeIn, 112)) BETWEEN @FROM AND @TO)

Author

Commented:
I forgot an important part...
You mentioned...
   Try doing the conversions on the @TO and @FROM variables so that they are the same...

I have @From and @To defined as datetime with format 01/01/12 and I need to convert it to
2012-01-01 00:00:00 format.  

So instead of...
   (CONVERT(datetime, CONVERT(char(8), t1.TimeIn, 112)) BETWEEN @FROM AND @TO)
I tried…
   WHERE (t1.TimeIn between CONVERT(char(14), @FROM, 120) AND  
   CONVERT(char(14), @TO, 120))
And got the error
    conversion failed when converting character string to smalldatetime data type.

So I tried...
    WHERE (t1.TimeIn between CONVERT(datetime, CONVERT(char(14), @FROM, 120)) AND  
    CONVERT(datetime, CONVERT(char(14), @TO, 120)))
And got the error…
      conversion failed when converting date and /or time from character string
So what am I doing wrong on this CONVERT?

Author

Commented:
Tried both of the below.  The result appears to be the same and it still takes 47 to 54 seconds.

WHERE (t1.TimeIn between CONVERT(char(8), @FROM, 112) AND  
CONVERT(char(8), @TO, 112))     --47  to 51 seconds
   
WHERE (t1.TimeIn between CONVERT(datetime, CONVERT(char(8), @FROM, 112)) AND  
CONVERT(datetime, CONVERT(char(8), @TO, 112)))    --47 to 54 seconds

Author

Commented:
I noticed something else that is odd.
In my initial post I showed code that I added that caused the slow down.  I thought the reason was because I need to run the report by all supervisors not just one.  So why is it when I run my code WITH a supervisor it is still really slow?  I tried commenting out the lines I added and it runs in about 3 seconds.  I add them back in without changing parameter values and it takes 43 seconds to run.  

Here is my initial post in part...
--I added from HERE to...
       (CONVERT(datetime, CONVERT(char(8), times.TimeIn, 112)) BETWEEN @FROM AND @TO) AND
       (L.Suspend = 0) AND
       (times.Company = 1) AND
       (@Supervisor IS NULL)
       OR
--HERE
       (CONVERT(datetime, CONVERT(char(8), times.TimeIn, 112)) BETWEEN @FROM AND @TO) AND
       (L.Suspend = 0) AND
       (times.Company = 1) AND
       (U.UID = @Supervisor)

So I changed all the above to
 (CONVERT(datetime, CONVERT(char(8), times.TimeIn, 112)) BETWEEN @FROM AND @TO) AND
       (L.Suspend = 0) AND
       (times.Company = 1) AND
       ((U.UID = @Supervisor) OR (@Supervisor Is Null))

but  it's doing the same thing.  If I comment out the OR it's 3 seconds.  If I leave it in it's 43.  Since I have a paramter for supervisor there has to be something wrong with that line of code...right?  I can't figure it out.

Commented:
Are TimeIn and TimeOut both DateTime fields? In that case just leave out the converts completely! (do "print @FROM" just after setting it in your current code to see what's in there).

Next, this bit is in both parts of the WHERE clause:
       (L.Suspend = 0) AND
       (times.Company = 1) 

Open in new window

so factor it out to appear once - it simplifies things for you.

...

Mike

Author

Commented:
I took the changes you suggested and incorporated them into the code from my original post.   In doing so, I found a "convert" line of code that REALLY speeds things up.  Like now it takes 1 second to run.  But it calcualtes the HrsMin field wrong.  In some cases it's off by 15 minutes because it's not converting correctly.  

I posted all my original code, plus changes, plus comments on the one line of code that really speeds things up.  Maybe I could do the convert somewhere else or just change that line of code?  But I'm not seeing how.  I do appreciate all you've done and I've increased points!  I'm hoping that fixing this one line of code is the solution but I really need your help...please!


declare @From datetime, @To datetime, @Supervisor int

SET @From = '01/01/12';
SET @To = '01/05/12';
--SET @Supervisor = 212;
SET @Supervisor = Null;

SELECT CONVERT(varchar(10), TS_Day, 101) AS Date,
       EmployeeId,
       Name,
       UserName AS Supervisor,
       CONVERT(varchar, DATEDIFF(hh, 0, DATEADD(ss,SUM(ts_seconds + ts_manual_seconds + ts_approved_break_seconds), 0))) + SUBSTRING
      (CONVERT(varchar(8),DATEADD(ss,SUM(ts_seconds + ts_manual_seconds + ts_approved_break_seconds), 0), 114), 3, 3) AS HrsMin
FROM (SELECT times.TS_Day,
      L.EmployeeId,
      L.LastName + ', ' + L.FirstName AS Name,
      U.UserName,
      SUM(times.TS_Seconds) AS ts_seconds,
      SUM(times.TS_Manual_Seconds) AS ts_manual_seconds,
      SUM(times.TS_Break_Seconds) AS ts_break_seconds,
      CASE
        WHEN SUM([TS_Break_Seconds]) < 1800
        THEN SUM([TS_Break_Seconds])
        ELSE 1800
        END AS ts_approved_break_seconds
            FROM (SELECT Company,
                  EmployeeId,
                  CONVERT(datetime, CONVERT(varchar(8), TimeOut, 112)) AS TS_Day,
                  --TimeOut AS TS_Day,   --this way not quicker and don't get all data (got 11 out of 60)
                  TimeIn,
                  TimeOut,
                  BreakFlag,
                  DATEDIFF(ss,TimeIn, TimeOut) AS TS_Seconds,
                  ISNULL(TimeSheetMinutes, 0) * 60 AS TS_Manual_Seconds,
                  ISNULL ((SELECT TOP (1)
                              CASE
                                             WHEN t1.Breakflag = 1
                               THEN
                               CASE
                                                WHEN t2.timein is null
                                                THEN 0
                                                WHEN datediff(ss, t1.timeout, t2.timein) > 900
                                                THEN 900
                               ELSE datediff(ss, t1.timeout, t2.timein)
                                             END
                              ELSE 0
                              END AS Expr1
                        FROM dbo.EmployeeHours AS t2
                        WHERE (EmployeeId = t1.EmployeeId) AND
                          (Company = t1.Company) AND
                         -- (CONVERT(varchar(8), TimeIn) = CONVERT(varchar(8), t1.TimeIn)) AND  --works but very slow
                          t2.TimeIn = t1.TimeIn AND   --this quicker (1 sec) but don't get all data cause HrsMin calculates wrong.
                         --  TimeIn = CONVERT(varchar(8), t1.TimeIn) AND  --fast but still wrong data
                          -- (CONVERT(varchar(8), TimeIn) = t1.TimeIn)AND   --get error "conversion of varchar to smalldatetime out of range
                          ((TimeIn > t1.TimeIn)
                          OR
                          (TimeIn = t1.TimeIn) AND
                          (TimeOut > t1.TimeOut))
                        ORDER BY TimeIn, TimeOut, RecordId), 0
                        ) AS TS_Break_Seconds
                  FROM dbo.EmployeeHours AS t1
                ) AS times
       INNER JOIN
       dbo.EmployeeList AS L ON
       times.EmployeeId = L.EmployeeId AND
       times.Company = L.Company
       LEFT OUTER JOIN
       dbo.UserList AS U ON L.ManagerUID = U.UID AND
       L.Company = U.Company
       WHERE
        times.TimeIn BETWEEN @FROM AND @TO AND
       (L.Suspend = 0) AND
       (times.Company = 1) AND
       ((U.UID = @Supervisor) OR (@Supervisor Is Null))
       GROUP BY times.TS_Day,
                times.EmployeeId,
                times.TS_Day,
                L.EmployeeId,
                L.LastName + ', ' + L.FirstName,
                U.UserName
    ) AS daily
GROUP BY TS_Day,
         EmployeeId,
         Name,
         UserName
HAVING (SUM(ts_seconds + ts_manual_seconds + ts_approved_break_seconds) / 60 > 540)

ORDER BY TS_Day, Name, EmployeeId

Commented:
What types are TimeIn and TimeOut? Varchar? datetime?   Could you also give some (2 or 3) examples of what is in them please?

Thx

Mike

Author

Commented:
TimeIn and TimeOut are both smalldatetime
Examples are...
TimeIn                                                  TimeOut
2005-08-05 07:28:00      2005-08-05 13:00:00
2005-08-05 07:45:00      2005-08-05 09:34:00
2005-08-05 07:48:00      2005-08-05 14:55:00
2005-08-05 08:01:00      2005-08-05 10:24:00
2005-08-05 08:27:00      2005-08-05 09:17:00
Thank you!
Commented:
So try declaring @TO and @FROM as smalldatetime and doing any comparisons like that. You've been converting datetimes into varchars and then back into smalldatetimes, when having the whole thing in smalldatetimes would save lots of work!

You also say:

 t2.TimeIn = t1.TimeIn AND   --this doesn't get all data cause HrsMin calculates wrong.

can you possibly give an example, please? I'm not too sure I understand how it will get an equality test incorrect ... except that what is your criterion for exactitude. IOW, how near do two times have to be to be considered "equal"? My reason for asking is that digital computers only store things to a limited exactitude. For example, 8 bits can represent 512 different values. If you are representing numbers here then you might say that that is every integer from 1 to 512, but then you have no way of representing some value between, say, 25 and 26. Instead of using a straight equality test, you could probably better say something like this:

abs (t2.TimeIn - t1.TimeIn) < '00:00:01'   --two times less than 1 second apart

hth

Mike

Author

Commented:
I changed @TO and @FROM to smalldatetime and I tried to take out as many converts as I could and still have it work.  I also figured out why the HrsMin is off but I can't figure out how to fix it.  When I use the "Convert" line of code (the one that runs really slow) then the HrsMin includes breaks (TS_Break_Seconds).  If I use the code without "Convert" then Breaks are no longer  included in HrsMin which means the total is off by up to 30 minutes (two 15 minute breaks).  I've included my code to show the changes as well as comments.  Your continued help is really appreciated!  Thank you so much!

declare @From smalldatetime, @To smalldatetime, @Supervisor int

SET @From = '01/01/12';
SET @To = '01/05/12';
--SET @Supervisor = 212;
SET @Supervisor = Null;

SELECT TS_Day AS Date,
       EmployeeId,
       Name,
       UserName AS Supervisor,
       DATEDIFF(hh, 0, DATEADD(ss,SUM(ts_seconds + ts_manual_seconds + ts_approved_break_seconds),0)) +  
       DATEADD(ss,SUM(ts_seconds + ts_manual_seconds + ts_approved_break_seconds), 0) AS HrsMin

FROM (SELECT times.TS_Day,
      L.EmployeeId,
      L.LastName + ', ' + L.FirstName AS Name,
      U.UserName,
      SUM(times.TS_Seconds) AS ts_seconds,
      SUM(times.TS_Manual_Seconds) AS ts_manual_seconds,
      SUM(times.TS_Break_Seconds) AS ts_break_seconds,
      CASE
        WHEN SUM([TS_Break_Seconds]) < 1800
        THEN SUM([TS_Break_Seconds])
        ELSE 1800
        END AS ts_approved_break_seconds
            FROM (SELECT Company,
                  EmployeeId,
                  CONVERT(varchar(8), TimeOut, 112) AS TS_Day,  
                  TimeIn,
                  TimeOut,
                  BreakFlag,
                  DATEDIFF(ss,TimeIn, TimeOut) AS TS_Seconds,
                  ISNULL(TimeSheetMinutes, 0) * 60 AS TS_Manual_Seconds,
                  ISNULL ((SELECT TOP (1)
                              CASE
                                             WHEN t1.Breakflag = 1
                               THEN
                               CASE
                                                WHEN t2.timein is null
                                                THEN 0
                                                WHEN datediff(ss, t1.timeout, t2.timein) > 900
                                                THEN 900
                               ELSE datediff(ss, t1.timeout, t2.timein)
                                             END
                              ELSE 0
                              END AS Expr1
                        FROM dbo.EmployeeHours AS t2
                        WHERE (EmployeeId = t1.EmployeeId) AND
                          (Company = t1.Company) AND
                      --   TimeIn = t1.TimeIn AND  --fast but does not include breaks in HrsMin total
                      --     abs (t2.TimeIn - t1.TimeIn) < '00:00:01' AND  --err msg Implicit conversion from data type smalldatetime to float not allowed.
                          (CONVERT(varchar(8), TimeIn) = CONVERT(varchar(8), t1.TimeIn)) AND  --Orig includes breaks but very slow
                         ((TimeIn > t1.TimeIn)
                          OR
                          (TimeIn = t1.TimeIn AND
                          TimeOut > t1.TimeOut))
                        ORDER BY TimeIn, TimeOut, RecordId), 0
                        ) AS TS_Break_Seconds
                  FROM dbo.EmployeeHours AS t1
                ) AS times
       INNER JOIN
       dbo.EmployeeList AS L ON
       times.EmployeeId = L.EmployeeId AND
       times.Company = L.Company
       LEFT OUTER JOIN
       dbo.UserList AS U ON L.ManagerUID = U.UID AND
       L.Company = U.Company
       WHERE
        times.TS_Day BETWEEN @FROM AND @TO AND
       (L.Suspend = 0) AND
       (times.Company = 1) AND
       ((U.UID = @Supervisor) OR (@Supervisor Is Null))
       GROUP BY times.TS_Day,
                times.EmployeeId,
                times.TS_Day,
                L.EmployeeId,
                L.LastName + ', ' + L.FirstName,
                U.UserName
    ) AS daily
GROUP BY TS_Day,
         EmployeeId,
         Name,
         UserName
HAVING (SUM(ts_seconds + ts_manual_seconds + ts_approved_break_seconds) / 60 > 540)

ORDER BY TS_Day, Name, EmployeeId

Commented:
Hi Bob,

FYI, every time I pull your code into SSMS I get an imbalance of parens!

Aside from that, do you have an absolutely minimal dataset that you could post? For example, a few rows from EmployeeHours so I can see your problem with the time difference. I'd guess that it's just with the inner-most select statement (the one that ends up creating t2.

thanks

Mike

Author

Commented:
Mike,
Per your request I'm attaching some sample data.   I don't understand either why you are getting an "imbalance of paren" error.  I copied and pasted the code above right into SSMS and I didn't get any errors.

The difference between the 2 lines of code seems like only a format issue yet it chnages the speed and end result.  I still can't figure out the problem.  Again I REALLY appreciate your help!
TestData.xls

Author

Commented:
I do appreciate all your help!  Did you by any chance have any luck with the sample data?
Thanks!

Author

Commented:
I really apprecaite all the time you spent helping me.  I believe we've narrowed down the problem and I will continue to work on it.
Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial