Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Loop for Different times of same day
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

15 Posts

Posted - 02/06/2013 :  00:37:25  Show Profile  Reply with Quote
Hi all,

Sorry if there is a topic like this, I have searched but was unable to find anything like what we are needing for this query.

My company is running MSSQL 2008 and we are trying to work out a method of obtaining some information for a day between two tables and the tables have a unique key only through the stop datetime field (but there might be a time difference of 5 minutes).

This is due to someone having to push a button after a shift ends (the shift is calculated automatically) and we are trying to get the two to marry up at around the same time.

We cannot adjust any of the SQL database tables or views as the external supply company has locked it down (but we have read-only access).

I have created the following query:
SELECT sum(t1.EndEggs/12) As Dozens, sum(t1.EndWeight/10000) As Kgs
FROM dbo.Tbl_PeriodCounter t0
INNER JOIN dbo.Tbl_PeriodCounterGrade t1 ON t0.PeriodID = t1.PeriodID
INNER JOIN dbo.Tbl_Supply t3 ON (DATEDIFF(minute, t3.StopDateTime, t0.StopTime) <= '912' AND DATEDIFF(minute, t3.StopDateTime, t0.StopTime) >= '5')
WHERE t3.StopDateTime = '20130205 07:30:31:000'

Which gives the following results:
Dozens Kgs
9039.33333333333 6678.8446

We have yet to test out the change every shift, so that's why the <= '912' as the manager only reset the count yesterday.

Now the above was only to test if the query would work and we could extract the data, but the problems are:
a) There are multiple shifts on a day and I need to be able to pick all of them up for a day
b) The input relies on a datetime field. There is no date type in this SQL database.

Is it possible to create a loop for this query to marry the time of the shift ending to the time the button was pressed (with anything up to 5 minutes to allow for the button to be pushed) for the one day over multiple datetimes?

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 02/06/2013 :  01:19:53  Show Profile  Reply with Quote
are you saying that you dont have date range to indicate start and end of a shift? then whats the field which recognizes a shift duration?

SQL Server MVP

Go to Top of Page

Starting Member

15 Posts

Posted - 02/06/2013 :  04:21:42  Show Profile  Reply with Quote
Hi visakh16,

Thanks for the quick response, I'll try to explain the process:

1) The Supply table holds the shift start and stop times and is updated every time there is a shift change (this happens automatically)
2) The Period Counter table only has a stop time recorded in the table when the reset button is pushed (the StartTime defaults to the previous time the button was pushed and we are trying to co-ordinate it so that the button is pushed at the end of every shift to match the Supply table times).

We have multiple shifts in one day and I was wondering if there was a type of loop query that would look throughout the one day (eg. today's date) and would look at the end of every shift +- 5 minutes to be able to link the two tables together for every supply shift change.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000