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.
| Author |
Topic |
|
mukhan85
Starting Member
46 Posts |
 Posted - 2009-06-30 : 11:51:53
|
| Hi folks, I have the following table:NAME AVERAGE MIN/MAXJane 3.4 4Mike 2.3 3..I need to generate above report for one Day, one Week, and one Month.I am using Java to get the results, but all computations are done with SQL query( average computation, min/max).The question is, is there a way to get dayly, weekly, and monthly reports by querying database once only, or do I need to query database each time for day, week, and month by changing the date range?Thanks a lot! |
|
|
rohitkumar
Constraint Violating Yak Guru
472 Posts |
Posted - 2009-06-30 : 11:58:50
|
| you'll be using case statements here |
 |
|
|
mukhan85
Starting Member
46 Posts |
Posted - 2009-06-30 : 12:04:24
|
how would I use them?Thanks.quote:
Originally posted by rohitkumar
you'll be using case statements here
|
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
mukhan85
Starting Member
46 Posts |
Posted - 2009-06-30 : 12:35:48
|
1.I need to generate above report for one Day, one Week, and one Month.I am using Java to get the results, but all computations are done with SQL query( average computation, min/max).The question is, is there a way to get dayly, weekly, and monthly reports by querying database once only, or do I need to query database each time for day, week, and month by changing the date range?2.create table mytable(name varchar(10), number int, date DATE)3.insert into mytable (name, number, date)select 'Jane', 4, 06/2009 union allselect 'Mike', 4, 06/2009 union allselect 'Jane', 4, 06/2009 union allselect 'Mike', 4, 06/2009 union all...select name, avg(number), min(number), max(number)from mytable where date = today group by name.I need the same report for one week and one month.so, is there a way to get all data in one database query, I am using java to get all processed data from Database.Thanks.quote:
Originally posted by X002548
Not with the data sample you listed hereRead the hint link below in my sig and reopostBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-30 : 12:44:08
|
| yup..you need to apply group by over year, month and day and apply AVG(),MIN(),MAX(),... functions. can you show your exact o/p format so that i can show you query |
|
|
|
mukhan85
Starting Member
46 Posts |
Posted - 2009-06-30 : 15:15:21
|
So, I need to get the statistics for one day, then for 7 days before today, and one monthI don't really care how the output will look like, it can either have 7 day , and one month results as a new two columns or as a new row which would repeat the "name" again with one column that would indicate the rage.Ex: NAME AVERAGE(1 day) MIN/MAX(1 day) AVERAGE(7 days) MIN/MAX(7 days) ----- -------- ------- ------ ----------------Jane 3.4 4 2.2 5 Mike 2.3 3 1.3 4or NAME AVERAGE MIN/MAX rangeJane 3.4 4 one_day Mike 2.3 3 one_dayJane 1.4 4 seven_day Mike 3.3 3 seven_day..quote:
Originally posted by visakh16
yup..you need to apply group by over year, month and day and apply AVG(),MIN(),MAX(),... functions. can you show your exact o/p format so that i can show you query
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-01 : 12:23:34
|
| [code]SELECT DISTINCT name,AVG(number*1.0) OVER (PARTITION BY DATEADD(dd,DATEDIFF(dd,0,date),0)) AS DayAvg,AVG(number*1.0) OVER (PARTITION BY DATEADD(wk,DATEDIFF(wk,0,date),0)) AS WeekAvg,AVG(number*1.0) OVER (PARTITION BY DATEADD(mm,DATEDIFF(mm,0,date),0)) AS MonthAvgFROM table[/code] |
|
|
|
|
|
|
|
|
Query Strategy