Time Functions - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Time Functions

Author

Topic

BCullenward
Starting Member

28 Posts

Posted - 2005-07-15 : 13:05:45

I am trying to get the number of minutes worked up to / between a specified amount of time and am having a bitch of a time doing it.

here is my query


SELECT
	dbo.[Names].PersonID,
	dbo.[Names].Person,
	SUM
	(
		
		DATEDIFF
		(
			hh, 
			TD.TimeIn,
			MIN
			(
				ISNULL
				(
					TD.TimeOut, 
					datePart(mm, Getdate()) + '/' + 
					datePart(dd, Getdate()) + '/' + 
					datePart(yyyy, Getdate()) + ' ' + 
					datePart(hh, '8') + ':' + 
					datePart(mi, '00') + ':' + 
					datePart(ss, '00')
				)
			)
		)
		* 60 + 
		DATEDIFF
		(
			mi, 
			TD.TimeIn, 
			MIN
			(
				ISNULL
				(
					TD.TimeOut, 
					datePart(mm, Getdate()) + '/' + 
					datePart(dd, Getdate()) + '/' + 
					datePart(yyyy, Getdate()) + ' ' + 
					datePart(hh, '8') + ':' + 
					datePart(mi, '00') + ':' + 
					datePart(ss, '00')
				)
			)	
		)	
	)	
	AS MinutesWorked

FROM         OrdersData.dbo.newTimeData TD INNER JOIN
                      dbo.[Names] ON OrdersData.dbo.newTimeData.EmployeeID = dbo.[Names].EmployeeID
WHERE     
	(OrdersData.dbo.newTimeData.Description = 'Work') 
AND 
	(DATEPART(yyyy, OrdersData.dbo.newTimeData.TimeIn) = DATEPART(yyyy, GETDATE())) 
AND 
        (DATEPART(dd, OrdersData.dbo.newTimeData.TimeIn) = DATEPART(dd, GETDATE())) 
AND 
	(DATEPART(mm, OrdersData.dbo.newTimeData.TimeIn) = DATEPART(mm, GETDATE()))
AND
	(DatePart(hh, OrdersData.dbo.newTimeData.TimeIn) < '8')

GROUP BY OrdersData.dbo.newTimeData.TimeIn, OrdersData.dbo.newTimeData.TimeOut, dbo.[Names].PersonID, dbo.[Names].Person

basically, I want to take the minutes between when they clocked in and 8am. if they were not in before 8, it will return nothing, if they have not yet clocked out or have clocked out after 8am I want to return the time as 8am, in order to calculate the minutes worked.

After this, I will make the changes so it will specify how many minutes they worked between 8am and 10am, and will adjust accordingly for the hours that I need.

My Min function should return the lowest of the clock out time, or 8am (or 10am for the second query or whatever).

The code is a mess and confusing and not working. There has to be an easier way to do this in SQL Server 2000.

SamC
White Water Yakist

3467 Posts

Posted - 2005-07-15 : 13:48:24

Could you post something about the table structure and maybe some simple sample data?

BCullenward
Starting Member

28 Posts

Posted - 2005-07-15 : 13:58:30

quote:
Originally posted by SamC

Could you post something about the table structure and maybe some simple sample data?


Names                         TimeDataNew
------                        -------------
PersonID (int)                PersonID (int)
Person   (char50)             TimeIn (timeDate)
                              TimeOut (timeDate)


Names                         TimeDataNew
------                        -----------
1  Joe                        1 2005-07-15 7:31:12.000 <null>
2  Bob                        2 2005-07-15 8:25:54.234 <null>
3  Dick                       3 2005-07-15 10:01:24.111 2005-07-15 10:02:24.532

I fail to see why sample data is needed, as it's pretty self explainitory and all I need is assistance with the date/time functionality.

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-15 : 17:18:39

I'm not sure if I have your logic down correctly, but I think you want something along the lines of this:

SELECT A.person, B.personid,
CASE WHEN DATEPART(hh, B.timein) >= 8 THEN NULL
ELSE CONVERT(char(8), DATEADD(hh, 8, 0), 108) END
FROM names A JOIN timedatanew B ON A.personid = B.personid

SamC
White Water Yakist

3467 Posts

Posted - 2005-07-15 : 18:49:01

The Red below returns a DATETIME value of 8AM, the same day as TimeIn

SELECT  N.PersonID, Person, TimeIn, TimeOut,
  DATEDIFF(mm, TimeIn, DATEADD(hh, 8, CONVERT(VARCHAR, TimeIn, 112))) As MinutesBeforeEight
  FROM Names N
  INNER JOIN TimeDataNew T ON T.PersonID = N.PersonID

  -- Only return records from users with TimeIn before 8AM
  WHERE DatePart(hh, OrdersData.dbo.newTimeData.TimeIn) < 8 -- Don't quote this INT value.

BCullenward
Starting Member

28 Posts

Posted - 2005-07-18 : 13:41:27

Thanks, that worked like a charm, now I need to figure out how to how many minutes were worked between 8am and 10am

jhermiz

3564 Posts

Posted - 2005-07-18 : 14:48:43

All you will need to do is use the BETWEEN clause or you can use < > for this as well.
SELECT N.PersonID, Person, TimeIn, TimeOut,
DATEDIFF(mm, TimeIn, DATEADD(hh, 8, CONVERT(VARCHAR, TimeIn, 112))) As MinutesBeforeEight
FROM Names N
INNER JOIN TimeDataNew T ON T.PersonID = N.PersonID

-- Only return records from users with TimeIn before 8AM
WHERE DatePart(hh, OrdersData.dbo.newTimeData.TimeIn) > 8 -- Don't quote this INT value.
AND
DatePart(hh, OrdersData.dbo.newTimeData.TimeIn) < 10 -- Don't quote this INT value.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources