Date Query - 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 2005 Forums

Transact-SQL (2005)

Date Query

Author

Topic

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-10-28 : 10:47:46

I have the below date query that should give me the number of hours by month/year and userid. The result gives me 811 hours for october which is impossible!

Doing the calculation in Excel with the data below gives me about 10.5 hours, can anyone help?

SELECT USERID,DATEPART(month,TimeAllocated) as "Month",
DATEPART(year,TimeAllocated) as "Year",
SUM(datediff(ss,TimeAllocated,TimeLastAccessed)/360) as "NumberOfHours"
FROM SESSIONS
where timeallocated > '01 october 2008' and userid = '3'
GROUP BY USERID,DATEPART(month,TimeAllocated),DATEPART(year,TimeAllocated)
order by 3,2 desc

TimeAllocated UserID TimeLastAccessed
2008-10-16 11:46:28.910 3 2008-10-16 16:29:30.937
2008-10-07 13:40:57.803 3 2008-10-07 16:59:14.063
2008-10-09 18:07:36.310 3 2008-10-09 19:10:53.450
2008-10-09 10:25:42.460 3 2008-10-09 16:14:30.900
2008-10-13 16:49:29.437 3 2008-10-13 17:43:10.063
2008-10-28 10:28:26.630 3 2008-10-28 10:28:26.630
2008-10-14 13:10:06.690 3 2008-10-14 16:07:33.907
2008-10-09 14:12:26.683 3 2008-10-09 14:14:43.623
2008-10-24 18:23:33.820 3 2008-10-24 19:57:55.247
2008-10-08 12:27:20.000 3 2008-10-08 13:20:57.060
2008-10-13 09:33:18.080 3 2008-10-13 11:02:34.523
2008-10-27 15:18:18.567 3 2008-10-27 15:18:18.567
2008-10-21 19:52:35.743 3 2008-10-21 19:52:35.743
2008-10-06 13:39:38.773 3 2008-10-06 14:59:52.030
2008-10-06 15:19:09.617 3 2008-10-06 17:32:04.577
2008-10-27 12:31:38.223 3 2008-10-27 12:31:38.223
2008-10-08 10:07:25.020 3 2008-10-08 11:49:38.070
2008-10-21 14:37:57.180 3 2008-10-21 14:38:01.103
2008-10-07 17:25:41.363 3 2008-10-07 17:59:59.590
2008-10-14 10:41:49.557 3 2008-10-14 15:39:58.720
2008-10-10 08:31:55.483 3 2008-10-10 08:32:04.970
2008-10-24 11:58:56.780 3 2008-10-24 18:03:11.233
2008-10-15 10:03:00.143 3 2008-10-15 13:34:16.007
2008-10-17 12:22:15.383 3 2008-10-17 15:05:16.583
2008-10-15 13:41:59.423 3 2008-10-15 14:25:24.687
2008-10-08 09:20:52.827 3 2008-10-08 12:07:04.010
2008-10-22 12:46:34.877 3 2008-10-22 13:01:00.730
2008-10-07 09:05:00.367 3 2008-10-07 14:12:56.010
2008-10-22 09:33:36.633 3 2008-10-22 17:30:39.107
2008-10-17 09:50:49.710 3 2008-10-17 10:15:58.450
2008-10-07 13:20:46.153 3 2008-10-07 13:40:46.803
2008-10-02 11:15:50.327 3 2008-10-02 12:14:11.270
2008-10-24 20:14:00.987 3 2008-10-24 21:14:33.800
2008-10-09 19:09:56.853 3 2008-10-09 19:09:58.853
2008-10-07 11:33:07.070 3 2008-10-07 12:43:06.537
2008-10-10 17:22:10.650 3 2008-10-10 19:52:04.173
2008-10-10 12:25:33.967 3 2008-10-10 17:12:17.937
2008-10-21 14:22:28.553 3 2008-10-21 18:09:38.897
2008-10-24 09:56:40.023 3 2008-10-24 11:34:18.690
2008-10-10 08:45:33.907 3 2008-10-10 12:12:27.500
2008-10-10 11:28:01.520 3 2008-10-10 11:28:10.317
2008-10-28 11:13:20.817 3 2008-10-28 11:13:20.817

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-28 : 10:54:01

FYI: 3600 seconds = 1 hour

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-10-28 : 10:55:01

My first idea is that an hour has 3600 seconds. But that would only improve by a factor of ten ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 11:05:58

quote:
Originally posted by bjoerns

My first idea is that an hour has 3600 seconds. But that would only improve by a factor of ten ...

not exactly. you've applying sum() over it...so result might change more. Try running changed query and see.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-28 : 11:07:06

A factor of ten is ok, if you also think about integer rounding.

DECLARE	@Sample TABLE
	(
		timeAllocated DATETIME,
		userID INT,
		timeLastAccessed DATETIME
	)

INSERT	@Sample
SELECT	'2008-10-16 11:46:28.910', 3, '2008-10-16 16:29:30.937' UNION ALL
SELECT	'2008-10-07 13:40:57.803', 3, '2008-10-07 16:59:14.063' UNION ALL
SELECT	'2008-10-09 18:07:36.310', 3, '2008-10-09 19:10:53.450' UNION ALL
SELECT	'2008-10-09 10:25:42.460', 3, '2008-10-09 16:14:30.900' UNION ALL
SELECT	'2008-10-13 16:49:29.437', 3, '2008-10-13 17:43:10.063' UNION ALL
SELECT	'2008-10-28 10:28:26.630', 3, '2008-10-28 10:28:26.630' UNION ALL
SELECT	'2008-10-14 13:10:06.690', 3, '2008-10-14 16:07:33.907' UNION ALL
SELECT	'2008-10-09 14:12:26.683', 3, '2008-10-09 14:14:43.623' UNION ALL
SELECT	'2008-10-24 18:23:33.820', 3, '2008-10-24 19:57:55.247' UNION ALL
SELECT	'2008-10-08 12:27:20.000', 3, '2008-10-08 13:20:57.060' UNION ALL
SELECT	'2008-10-13 09:33:18.080', 3, '2008-10-13 11:02:34.523' UNION ALL
SELECT	'2008-10-27 15:18:18.567', 3, '2008-10-27 15:18:18.567' UNION ALL
SELECT	'2008-10-21 19:52:35.743', 3, '2008-10-21 19:52:35.743' UNION ALL
SELECT	'2008-10-06 13:39:38.773', 3, '2008-10-06 14:59:52.030' UNION ALL
SELECT	'2008-10-06 15:19:09.617', 3, '2008-10-06 17:32:04.577' UNION ALL
SELECT	'2008-10-27 12:31:38.223', 3, '2008-10-27 12:31:38.223' UNION ALL
SELECT	'2008-10-08 10:07:25.020', 3, '2008-10-08 11:49:38.070' UNION ALL
SELECT	'2008-10-21 14:37:57.180', 3, '2008-10-21 14:38:01.103' UNION ALL
SELECT	'2008-10-07 17:25:41.363', 3, '2008-10-07 17:59:59.590' UNION ALL
SELECT	'2008-10-14 10:41:49.557', 3, '2008-10-14 15:39:58.720' UNION ALL
SELECT	'2008-10-10 08:31:55.483', 3, '2008-10-10 08:32:04.970' UNION ALL
SELECT	'2008-10-24 11:58:56.780', 3, '2008-10-24 18:03:11.233' UNION ALL
SELECT	'2008-10-15 10:03:00.143', 3, '2008-10-15 13:34:16.007' UNION ALL
SELECT	'2008-10-17 12:22:15.383', 3, '2008-10-17 15:05:16.583' UNION ALL
SELECT	'2008-10-15 13:41:59.423', 3, '2008-10-15 14:25:24.687' UNION ALL
SELECT	'2008-10-08 09:20:52.827', 3, '2008-10-08 12:07:04.010' UNION ALL
SELECT	'2008-10-22 12:46:34.877', 3, '2008-10-22 13:01:00.730' UNION ALL
SELECT	'2008-10-07 09:05:00.367', 3, '2008-10-07 14:12:56.010' UNION ALL
SELECT	'2008-10-22 09:33:36.633', 3, '2008-10-22 17:30:39.107' UNION ALL
SELECT	'2008-10-17 09:50:49.710', 3, '2008-10-17 10:15:58.450' UNION ALL
SELECT	'2008-10-07 13:20:46.153', 3, '2008-10-07 13:40:46.803' UNION ALL
SELECT	'2008-10-02 11:15:50.327', 3, '2008-10-02 12:14:11.270' UNION ALL
SELECT	'2008-10-24 20:14:00.987', 3, '2008-10-24 21:14:33.800' UNION ALL
SELECT	'2008-10-09 19:09:56.853', 3, '2008-10-09 19:09:58.853' UNION ALL
SELECT	'2008-10-07 11:33:07.070', 3, '2008-10-07 12:43:06.537' UNION ALL
SELECT	'2008-10-10 17:22:10.650', 3, '2008-10-10 19:52:04.173' UNION ALL
SELECT	'2008-10-10 12:25:33.967', 3, '2008-10-10 17:12:17.937' UNION ALL
SELECT	'2008-10-21 14:22:28.553', 3, '2008-10-21 18:09:38.897' UNION ALL
SELECT	'2008-10-24 09:56:40.023', 3, '2008-10-24 11:34:18.690' UNION ALL
SELECT	'2008-10-10 08:45:33.907', 3, '2008-10-10 12:12:27.500' UNION ALL
SELECT	'2008-10-10 11:28:01.520', 3, '2008-10-10 11:28:10.317' UNION ALL
SELECT	'2008-10-28 11:13:20.817', 3, '2008-10-28 11:13:20.817'

SELECT		userID,
		DATEPART(MONTH, timeAllocated) AS [Month],
		DATEPART(YEAR, timeAllocated) AS [Year],
		SUM(DATEDIFF(SECOND, timeAllocated, timeLastAccessed) / 360) AS NumberOfHours,
		SUM(DATEDIFF(SECOND, timeAllocated, timeLastAccessed) / 3600.0) AS Peso
FROM		@Sample
WHERE		timeAllocated > '01 october 2008'
		AND userID = 3
GROUP BY	userID,
		DATEPART(MONTH, timeAllocated),
		DATEPART(YEAR, timeAllocated)
ORDER BY	3,
		2 DESC


userID	Month	Year	NumberOfHours	Peso
3	10	2008	811		82.703598

E 12°55'05.63"
N 56°04'39.26"

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-28 : 11:07:50

Excel is wrong as well... for example take a look at record #4. that's 5 hours along. It's somewhere around 82

SELECT USERID,DATEPART(month,TimeAllocated) as "Month",
DATEPART(year,TimeAllocated) as "Year",
SUM(datediff(ss,TimeAllocated,TimeLastAccessed))/3600 as "NumberOfHours"
FROM SESSIONS
where timeallocated > '01 october 2008' and userid = '3'
GROUP BY USERID,DATEPART(month,TimeAllocated),DATEPART(year,TimeAllocated)
order by 3,2 desc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-28 : 11:09:34

[code]timeAllocated userID timeLastAccessed Seconds Minutes Hours
2008-10-16 11:46:28.910 3 2008-10-16 16:29:30.937 16982 283.033333 4.717222
2008-10-07 13:40:57.803 3 2008-10-07 16:59:14.063 11897 198.283333 3.304722
2008-10-09 18:07:36.310 3 2008-10-09 19:10:53.450 3797 63.283333 1.054722
2008-10-09 10:25:42.460 3 2008-10-09 16:14:30.900 20928 348.800000 5.813333
2008-10-13 16:49:29.437 3 2008-10-13 17:43:10.063 3221 53.683333 0.894722
2008-10-28 10:28:26.630 3 2008-10-28 10:28:26.630 0 0.000000 0.000000
2008-10-14 13:10:06.690 3 2008-10-14 16:07:33.907 10647 177.450000 2.957500
2008-10-09 14:12:26.683 3 2008-10-09 14:14:43.623 137 2.283333 0.038055
2008-10-24 18:23:33.820 3 2008-10-24 19:57:55.247 5662 94.366666 1.572777
2008-10-08 12:27:20.000 3 2008-10-08 13:20:57.060 3217 53.616666 0.893611
2008-10-13 09:33:18.080 3 2008-10-13 11:02:34.523 5356 89.266666 1.487777
2008-10-27 15:18:18.567 3 2008-10-27 15:18:18.567 0 0.000000 0.000000
2008-10-21 19:52:35.743 3 2008-10-21 19:52:35.743 0 0.000000 0.000000
2008-10-06 13:39:38.773 3 2008-10-06 14:59:52.030 4814 80.233333 1.337222
2008-10-06 15:19:09.617 3 2008-10-06 17:32:04.577 7975 132.916666 2.215277
2008-10-27 12:31:38.223 3 2008-10-27 12:31:38.223 0 0.000000 0.000000
2008-10-08 10:07:25.020 3 2008-10-08 11:49:38.070 6133 102.216666 1.703611
2008-10-21 14:37:57.180 3 2008-10-21 14:38:01.103 4 0.066666 0.001111
2008-10-07 17:25:41.363 3 2008-10-07 17:59:59.590 2058 34.300000 0.571666
2008-10-14 10:41:49.557 3 2008-10-14 15:39:58.720 17889 298.150000 4.969166
2008-10-10 08:31:55.483 3 2008-10-10 08:32:04.970 9 0.150000 0.002500
2008-10-24 11:58:56.780 3 2008-10-24 18:03:11.233 21855 364.250000 6.070833
2008-10-15 10:03:00.143 3 2008-10-15 13:34:16.007 12676 211.266666 3.521111
2008-10-17 12:22:15.383 3 2008-10-17 15:05:16.583 9781 163.016666 2.716944
2008-10-15 13:41:59.423 3 2008-10-15 14:25:24.687 2605 43.416666 0.723611
2008-10-08 09:20:52.827 3 2008-10-08 12:07:04.010 9972 166.200000 2.770000
2008-10-22 12:46:34.877 3 2008-10-22 13:01:00.730 866 14.433333 0.240555
2008-10-07 09:05:00.367 3 2008-10-07 14:12:56.010 18476 307.933333 5.132222
2008-10-22 09:33:36.633 3 2008-10-22 17:30:39.107 28623 477.050000 7.950833
2008-10-17 09:50:49.710 3 2008-10-17 10:15:58.450 1509 25.150000 0.419166
2008-10-07 13:20:46.153 3 2008-10-07 13:40:46.803 1200 20.000000 0.333333
2008-10-02 11:15:50.327 3 2008-10-02 12:14:11.270 3501 58.350000 0.972500
2008-10-24 20:14:00.987 3 2008-10-24 21:14:33.800 3633 60.550000 1.009166
2008-10-09 19:09:56.853 3 2008-10-09 19:09:58.853 2 0.033333 0.000555
2008-10-07 11:33:07.070 3 2008-10-07 12:43:06.537 4199 69.983333 1.166388
2008-10-10 17:22:10.650 3 2008-10-10 19:52:04.173 8994 149.900000 2.498333
2008-10-10 12:25:33.967 3 2008-10-10 17:12:17.937 17204 286.733333 4.778888
2008-10-21 14:22:28.553 3 2008-10-21 18:09:38.897 13630 227.166666 3.786111
2008-10-24 09:56:40.023 3 2008-10-24 11:34:18.690 5858 97.633333 1.627222
2008-10-10 08:45:33.907 3 2008-10-10 12:12:27.500 12414 206.900000 3.448333
2008-10-10 11:28:01.520 3 2008-10-10 11:28:10.317 9 0.150000 0.002500
2008-10-28 11:13:20.817 3 2008-10-28 11:13:20.817 0 0.000000 0.000000[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources