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 |
PKS
Starting Member
1 Post |
Posted - 2013-01-28 : 20:26:58
|
Hi ExpertsHelp me with an MS SQL 2008 query for my monthly table Consolidated Monthly Table data----------------------------Date_Time Machine_Name UsageValue1/1/12 01:00:00 T21 101/1/12 02:00:00 T21 121/1/12 03:00:00 T21 141/1/12 04:00:00 T21 111/1/12 01:00:00 T25 101/1/12 01:00:00 T25 091/1/12 01:00:00 T23 101/1/12 01:00:00 H12 881/1/12 02:00:00 H12 221/2/12 01:00:00 T21 101/2/12 02:00:00 T21 131/2/12 03:00:00 T21 121/2/12 04:00:00 T21 111/2/12 01:00:00 T25 141/2/12 01:00:00 T25 091/2/12 01:00:00 T23 181/2/12 01:00:00 H12 381/2/12 02:00:00 H12 291/3/12 01:00:00 T21 101/3/12 02:00:00 T21 121/3/12 03:00:00 T21 141/3/12 04:00:00 T21 111/3/12 01:00:00 T25 331/3/12 01:00:00 T25 091/3/12 01:00:00 T23 101/3/12 01:00:00 H12 881/3/12 02:00:00 H12 221/3/12 02:00:00 H12 99Expected table Output below: For each machine name get the maximum UsageValue for that day and display with the respective time stamp.----------------Date_Time Machine_Name UsageValue1/1/12 03:00:00 T21 141/1/12 01:00:00 T25 101/1/12 01:00:00 T23 101/1/12 01:00:00 H12 881/2/12 02:00:00 T21 131/2/12 01:00:00 T25 141/2/12 01:00:00 T23 181/2/12 01:00:00 H12 381/3/12 02:00:00 T21 191/3/12 01:00:00 T25 331/3/12 01:00:00 T23 101/3/12 02:00:00 H12 99 |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-29 : 00:03:12
|
monthlyData is Table Name hereSELECT Date_Time, Machine_Name, UsageValue FROM (SELECT *, ROW_NUMBER() OVER(Partition by DAY(Date_Time), machine_name ORDER BY usageValue DESC) rn FROM monthlyData ) t WHERE rn=1ORDER BY Date_Time --Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-29 : 02:40:33
|
quote: Originally posted by bandi monthlyData is Table Name hereSELECT Date_Time, Machine_Name, UsageValue FROM (SELECT *, ROW_NUMBER() OVER(Partition by DAY(Date_Time), machine_name ORDER BY usageValue DESC) rn FROM monthlyData ) t WHERE rn=1ORDER BY Date_Time --Chandu
though that might work for sample data given, its not correct addthe below sample data and you'll see why2/1/12 01:00:00 T21 122/1/12 01:00:00 T21 30------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-29 : 02:41:25
|
it should beSELECT Date_Time, Machine_Name, UsageValue FROM (SELECT *, ROW_NUMBER() OVER(Partition by DATEDIFF(dd,0,Date_Time), machine_name ORDER BY usageValue DESC) rn FROM monthlyData ) t WHERE rn=1ORDER BY Date_Time ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|