SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

Stevan23
Starting Member

11 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?

visakh16
Very Important crosS Applying yaK Herder

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

Go to Top of Page

Stevan23
Starting Member

11 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  
 New 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.06 seconds. Powered By: Snitz Forums 2000