Need Help in aggregate query

• descentflower

  Ten Centuries

  Points: 1227

  More actions

  June 19, 2009 at 10:13 pm

  #131443

  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)

  SetIdLocationFromLocationTo

  ------------------------------------

  1AB

  2BC

  3CD

  4DE

  5EF

  6FG

  7GH

  8HI

  9IJ

  10JK

  11KL

  12JUNCA

  13ZA

  tblAmount (3 columns)

  DateTimeHoursSetIdAmount

  -------------------------------------------

  20009-06-20 00:00:00:00171

  20009-06-20 01:00:00:00172

  20009-06-20 02:00:00:00172

  20009-06-20 03:00:00:00177

  20009-06-20 04:00:00:00172

  20009-06-20 05:00:00:00128

  20009-06-20 06:00:00:00113

  20009-06-20 07:00:00:00158

  20009-06-20 08:00:00:00168

  20009-06-20 09:00:00:00118

  20009-06-20 10:00:00:00188

  20009-06-20 11:00:00:00158

  20009-06-20 12:00:00:00148

  20009-06-20 13:00:00:00128

  20009-06-20 14:00:00:00138

  20009-06-20 15:00:00:00148

  20009-06-20 16:00:00:00168

  20009-06-20 17:00:00:00118

  20009-06-20 18:00:00:00148

  20009-06-20 19:00:00:00128

  20009-06-20 20:00:00:00168

  20009-06-20 21:00:00:00191

  20009-06-20 22:00:00:00143

  20009-06-20 23:00:00:00154

  20009-06-20 00:00:00:00294

  20009-06-20 01:00:00:00278

  20009-06-20 02:00:00:00278

  20009-06-20 03:00:00:00278

  20009-06-20 04:00:00:00278

  20009-06-20 05:00:00:00278

  20009-06-20 06:00:00:00278

  20009-06-20 07:00:00:00278

  20009-06-20 08:00:00:00278

  20009-06-20 09:00:00:00278

  20009-06-20 10:00:00:00278

  20009-06-20 11:00:00:00278

  20009-06-20 12:00:00:00278

  20009-06-20 13:00:00:00278

  20009-06-20 14:00:00:00278

  20009-06-20 15:00:00:00278

  20009-06-20 16:00:00:00278

  20009-06-20 17:00:00:00278

  20009-06-20 18:00:00:00278

  20009-06-20 19:00:00:00278

  20009-06-20 20:00:00:00278

  20009-06-20 21:00:00:00278

  20009-06-20 22:00:00:00278

  20009-06-20 23:00:00:00278

  Expected Output

  DaySetAvgAmount

  ------------------------------------

  20009-06-20A-B20.2

  20009-06-20B-C52.3

  20009-06-20C-D21.42

  20009-06-20D-E8.56

  20009-06-20E-F20.1

  20009-06-20F-G23.25

  20009-06-20G-H26.54

  20009-06-20H-I335.1

  20009-06-20I-J65.36

  20009-06-20J-K89.25

  20009-06-20K-L56.32

  20009-06-20Z-A32.31

  20009-06-20JUNC-A44.44

• LutzM

  SSC Guru

  Points: 107049

  More actions

  June 20, 2009 at 2:34 am

  #1012774

  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.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]