Averages of Time by Month - 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

General SQL Server Forums

New to SQL Server Programming

Averages of Time by Month

Author

Topic

swl681
Starting Member

15 Posts

Posted - 2007-12-21 : 09:58:37

Hello all, I have the query below that is returning the difference in time between two scans, I now need to take that time and break it out by month and than break that out by shift and get an average for the 2 shifts for the month. Below is the query with some sample data. Your help is greatly appreciated. THANKS!!!

SELECT LoadID, DATEDIFF(MINUTE, mi, ma) AS DiffMinutes
FROM SELECT LoadID, MIN(ProcessDate) AS mi, MAX(ProcessDate) AS ma
FROM [Scan Detail]
WHERE (ScanSequence = 2 AND LoadSequence = 2) OR
(ScanSequence = 3 AND LoadSequence = 13)
GROUP BY LoadID) d

LoadID DiffMinutes
8048 5
30875 5
22681 6
16096 13
14779 10
31900 6
36435 5
39948 7
21802 0
11561 6
33947 9
28387 15
19609 13
39507 9
28828 9
8194 6
39653 10
8489 7
6001 7

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-21 : 10:21:27

This will give you by month.

SELECT 	LoadID, 
	ProcessMonth, 
	DiffMintues = DATEDIFF(minute, mi, ma)
FROM
(
	SELECT LoadID, 
	       DATEADD(MONTH, DATEDIFF(MONTH, 0, ProcessDate), 0) AS ProcessMonth, 
	       MIN(ProcessDate) AS mi, 
	       MAX(ProcessDate) AS ma
	FROM  [Scan Detail]
	WHERE (ScanSequence = 2 AND LoadSequence = 2) OR
	(ScanSequence = 3 AND LoadSequence = 13)
	GROUP BY LoadID, DATEADD(MONTH, DATEDIFF(MONTH, 0, ProcessDate), 0)
) a

[EDIT]
missing comma
[/EDIT]

KH
[spoiler]Time is always against us[/spoiler]

swl681
Starting Member

15 Posts

Posted - 2007-12-21 : 13:29:55

I changed the query around a bit. Below are the query and the results. What I would like to do now is take the DiffMinutes column and average it out by certain times for the month of November. I need to know the average time for first shift between 6:00am and 3:30pm and for second shift between 3:30pm and 12:00. As always your help is appreciated! THANKS AGAIN!!!

SELECT LoadID, MIN(ProcessDate) AS Minimum, MAX(ProcessDate) AS Maximum, DATEDIFF(MINUTE, MIN(ProcessDate), MAX(ProcessDate))
AS DiffMinutes
FROM [Scan Detail]
WHERE (ProcessDate >= '11/1/2007') AND (ProcessDate <= '11/30/2007')
GROUP BY LoadID

LoadID Minimum Maximum DiffMinutes
38129 11/1/07 5:52 AM 11/1/07 6:08 AM 16
38130 11/1/07 6:09 AM 11/1/07 6:17 AM 8
38131 11/1/07 6:19 AM 11/1/07 6:28 AM 9
38132 11/1/07 6:33 AM 11/1/07 6:42 AM 9
38133 11/1/07 6:45 AM 11/1/07 6:55 AM 10
38134 11/1/07 6:57 AM 11/1/07 7:10 AM 13
38135 11/1/07 7:41 AM 11/1/07 7:52 AM 11
38136 11/1/07 7:52 AM 11/1/07 8:02 AM 10
38137 11/1/07 8:03 AM 11/1/07 8:10 AM 7
38138 11/1/07 8:11 AM 11/1/07 8:19 AM 8
38139 11/1/07 8:21 AM 11/1/07 8:28 AM 7
38140 11/1/07 9:11 AM 11/1/07 9:18 AM 7
38141 11/1/07 9:19 AM 11/1/07 9:25 AM 6
38142 11/1/07 9:28 AM 11/1/07 9:34 AM 6
38143 11/1/07 9:35 AM 11/1/07 9:48 AM 13
38144 11/1/07 10:37 AM 11/1/07 10:46 AM 9
38145 11/1/07 10:47 AM 11/1/07 10:56 AM 9
38146 11/1/07 11:50 AM 11/1/07 11:59 AM 9
38147 11/1/07 11:59 AM 11/1/07 12:10 PM 11
38148 11/1/07 12:10 PM 11/1/07 12:19 PM 9
38149 11/1/07 1:43 PM 11/1/07 1:54 PM 11
38150 11/1/07 1:55 PM 11/1/07 2:03 PM 8
38151 11/1/07 2:03 PM 11/1/07 2:10 PM 7
38152 11/1/07 2:10 PM 11/1/07 2:18 PM 8
38153 11/1/07 2:18 PM 11/1/07 2:26 PM 8
38154 11/1/07 3:51 PM 11/1/07 3:57 PM 6
38155 11/1/07 3:58 PM 11/1/07 4:07 PM 9
38156 11/1/07 4:21 PM 11/1/07 4:30 PM 9
38157 11/1/07 4:30 PM 11/1/07 4:38 PM 8
38158 11/1/07 4:38 PM 11/1/07 4:44 PM 6
38159 11/1/07 4:45 PM 11/1/07 4:57 PM 12
38160 11/1/07 4:58 PM 11/1/07 5:04 PM 6
38161 11/1/07 5:06 PM 11/1/07 5:15 PM 9
38162 11/1/07 5:16 PM 11/1/07 5:25 PM 9
38163 11/1/07 6:05 PM 11/1/07 6:12 PM 7
38164 11/1/07 6:13 PM 11/1/07 6:23 PM 10
38165 11/1/07 6:23 PM 11/1/07 7:16 PM 53
38166 11/1/07 7:17 PM 11/1/07 7:23 PM 6
38167 11/1/07 7:24 PM 11/1/07 7:37 PM 13
38168 11/1/07 7:40 PM 11/1/07 7:46 PM 6
38169 11/1/07 7:47 PM 11/1/07 7:53 PM 6
38170 11/1/07 7:53 PM 11/1/07 8:00 PM 7
38171 11/1/07 8:00 PM 11/1/07 8:06 PM 6
38172 11/1/07 8:12 PM 11/1/07 8:19 PM 7
38173 11/1/07 8:20 PM 11/1/07 8:27 PM 7
38174 11/1/07 8:28 PM 11/1/07 8:36 PM 8
38175 11/1/07 8:37 PM 11/1/07 8:42 PM 5
38176 11/1/07 8:43 PM 11/1/07 8:49 PM 6
38177 11/1/07 8:50 PM 11/1/07 8:56 PM 6
38178 11/1/07 8:56 PM 11/1/07 9:03 PM 7
38179 11/1/07 9:04 PM 11/1/07 9:11 PM 7
38180 11/1/07 9:12 PM 11/1/07 9:21 PM 9
38181 11/1/07 9:21 PM 11/1/07 9:28 PM 7
38182 11/1/07 9:28 PM 11/1/07 9:34 PM 6
38183 11/1/07 10:38 PM 11/1/07 10:48 PM 10
38184 11/1/07 10:49 PM 11/1/07 10:57 PM 8
38185 11/1/07 11:23 PM 11/1/07 11:31 PM 8
38186 11/1/07 11:33 PM 11/1/07 11:46 PM 13

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-21 : 13:32:47

Now again!?

Already asked and answered here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93803

E 12°55'05.25"
N 56°04'39.16"

swl681
Starting Member

15 Posts

Posted - 2007-12-21 : 13:45:17

I appreciate your help but when I run that query I get an error that states Line1:Incorrect syntax near 'MIN'

swl681
Starting Member

15 Posts

Posted - 2007-12-21 : 13:46:13

Peso, I appreciate your attempted help but your query didn't work...thanks again for the attempt!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-21 : 13:49:33

For khtan's suggestion, put a simple comma sign "," after ProcessMonth in the derived table a.

E 12°55'05.25"
N 56°04'39.16"

swl681
Starting Member

15 Posts

Posted - 2007-12-21 : 13:56:08

I am getting some odd data back when I run khtan's query, below is an example of some of the data....I still need to take the DiffMinutes column and average it out by certain times for the month of November. I need to know the average time for first shift between 6:00am and 3:30pm and for second shift between 3:30pm and 12:00. As always your help is appreciated! THANKS AGAIN!!!

LoadID ProcessMonth DiffMintues
31519 5/1/05 12:00 AM 7
38492 5/1/05 12:00 AM 9
16551 5/1/05 12:00 AM 14
20909 5/1/05 12:00 AM 24
27603 5/1/05 12:00 AM 8
3513 5/1/05 12:00 AM 9
7871 5/1/05 12:00 AM 12
12635 5/1/05 12:00 AM 6
28700 5/1/05 12:00 AM 10
30047 5/1/05 12:00 AM 8
13498 5/1/05 12:00 AM 6
15011 5/1/05 12:00 AM 8
17482 5/1/05 12:00 AM 10
5468 5/1/05 12:00 AM 8
6940 5/1/05 12:00 AM 10
10680 5/1/05 12:00 AM 7
30978 5/1/05 12:00 AM 5
32475 5/1/05 12:00 AM 7
35791 5/1/05 12:00 AM 10
19437 5/1/05 12:00 AM 12
23629 5/1/05 12:00 AM 6
25326 5/1/05 12:00 AM 24
14470 5/1/05 12:00 AM 9
16010 5/1/05 12:00 AM 14
18023 5/1/05 12:00 AM 12
6399 5/1/05 12:00 AM 6
8412 5/1/05 12:00 AM 6
11679 5/1/05 12:00 AM 8
13025 5/1/05 12:00 AM 6
39033 5/1/05 12:00 AM 6
40072 5/1/05 12:00 AM 17
33016 5/1/05 12:00 AM 7
36249 5/1/05 12:00 AM 12
37263 5/1/05 12:00 AM 9
22406 5/1/05 12:00 AM 10

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources