Avatar of BobRosas
BobRosas
 asked on

Optimize my code

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
Microsoft SQL Server 2008SSRSMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
BobRosas

8/22/2022 - Mon
DcpKing

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.
BobRosas

ASKER
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.
DcpKing

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!
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
BobRosas

ASKER
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?
BobRosas

ASKER
Thank you for your help.
DcpKing

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
BobRosas

ASKER
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)
DcpKing

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
BobRosas

ASKER
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)
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
BobRosas

ASKER
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?
BobRosas

ASKER
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
BobRosas

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
DcpKing

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
BobRosas

ASKER
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
DcpKing

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
BobRosas

ASKER
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!
ASKER CERTIFIED SOLUTION
DcpKing

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
BobRosas

ASKER
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
DcpKing

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
BobRosas

ASKER
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
BobRosas

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

ASKER
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!
Your help has saved me hundreds of hours of internet surfing.
fblack61