June 19, 2009 at 10:13 pm
Hi,
I am trying to find the efficient way of getting records from table which has around 5lakhs of record.
The DateTimeHours column of table tblAmount has date and hour part in it.
I need to show aggregate values for daily,weekly,monthly,yearly.
I will be using Avg() as the aggregate function.
My expected output should be like (For Yearly) the Year 2009, what was the Average Amount for every Set.
Please note the sorting ,its sorted by LocationFrom in ascending order but the JUN-A set should always be at bottom of the group.
tblLocation (2 columns)
SetId LocationFrom LocationTo
------------------------------------
1 A B
2 B C
3 C D
4 D E
5 E F
6 F G
7 G H
8 H I
9 I J
10 J K
11 K L
12 JUNC A
13 Z A
tblAmount (3 columns)
DateTimeHours SetId Amount
-------------------------------------------
20009-06-20 00:00:00:00 1 71
20009-06-20 01:00:00:00 1 72
20009-06-20 02:00:00:00 1 72
20009-06-20 03:00:00:00 1 77
20009-06-20 04:00:00:00 1 72
20009-06-20 05:00:00:00 1 28
20009-06-20 06:00:00:00 1 13
20009-06-20 07:00:00:00 1 58
20009-06-20 08:00:00:00 1 68
20009-06-20 09:00:00:00 1 18
20009-06-20 10:00:00:00 1 88
20009-06-20 11:00:00:00 1 58
20009-06-20 12:00:00:00 1 48
20009-06-20 13:00:00:00 1 28
20009-06-20 14:00:00:00 1 38
20009-06-20 15:00:00:00 1 48
20009-06-20 16:00:00:00 1 68
20009-06-20 17:00:00:00 1 18
20009-06-20 18:00:00:00 1 48
20009-06-20 19:00:00:00 1 28
20009-06-20 20:00:00:00 1 68
20009-06-20 21:00:00:00 1 91
20009-06-20 22:00:00:00 1 43
20009-06-20 23:00:00:00 1 54
20009-06-20 00:00:00:00 2 94
20009-06-20 01:00:00:00 2 78
20009-06-20 02:00:00:00 2 78
20009-06-20 03:00:00:00 2 78
20009-06-20 04:00:00:00 2 78
20009-06-20 05:00:00:00 2 78
20009-06-20 06:00:00:00 2 78
20009-06-20 07:00:00:00 2 78
20009-06-20 08:00:00:00 2 78
20009-06-20 09:00:00:00 2 78
20009-06-20 10:00:00:00 2 78
20009-06-20 11:00:00:00 2 78
20009-06-20 12:00:00:00 2 78
20009-06-20 13:00:00:00 2 78
20009-06-20 14:00:00:00 2 78
20009-06-20 15:00:00:00 2 78
20009-06-20 16:00:00:00 2 78
20009-06-20 17:00:00:00 2 78
20009-06-20 18:00:00:00 2 78
20009-06-20 19:00:00:00 2 78
20009-06-20 20:00:00:00 2 78
20009-06-20 21:00:00:00 2 78
20009-06-20 22:00:00:00 2 78
20009-06-20 23:00:00:00 2 78
Expected Output
Day Set AvgAmount
------------------------------------
20009-06-20 A-B 20.2
20009-06-20 B-C 52.3
20009-06-20 C-D 21.42
20009-06-20 D-E 8.56
20009-06-20 E-F 20.1
20009-06-20 F-G 23.25
20009-06-20 G-H 26.54
20009-06-20 H-I 335.1
20009-06-20 I-J 65.36
20009-06-20 J-K 89.25
20009-06-20 K-L 56.32
20009-06-20 Z-A 32.31
20009-06-20 JUNC-A 44.44
June 20, 2009 at 2:34 am
Your expected output doesn't match the sample data provided and the day column contains invalid dates.
So the following solution is untested:
SELECT MIN(CONVERT(CHAR(10), a.DateTimeHours,120)), l.LocationFromLocationTo , AVG(a.Amount) as AvgAmount
FROM tblAmount a
INNER JOIN tblLocation l ON a.SetId = l.SetId
GROUP BY(DATEADD(dd, DATEDIFF(dd, 0, a.DateTimeHours), 0)), l.LocationFromLocationTo
Please follow the link in my signature on how to post sample data if you'd like to get verified answers.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply