SQL Loop for Different times of same day

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

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Loop for Different times of same day

Author

Topic

Stevan23
Starting Member

15 Posts

Posted - 2013-02-06 : 00:37:25

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-06 : 01:19:53

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
http://visakhm.blogspot.com/

Stevan23
Starting Member

15 Posts

Posted - 2013-02-06 : 04:21:42

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources