SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Finding Maximum Value for each machine day wise.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

PKS
Starting Member

1 Posts

Posted - 01/28/2013 :  20:26:58  Show Profile  Reply with Quote
Hi Experts

Help me with an MS SQL 2008 query for my monthly table

Consolidated Monthly Table data
----------------------------

Date_Time Machine_Name UsageValue

1/1/12 01:00:00 T21 10
1/1/12 02:00:00 T21 12
1/1/12 03:00:00 T21 14
1/1/12 04:00:00 T21 11
1/1/12 01:00:00 T25 10
1/1/12 01:00:00 T25 09
1/1/12 01:00:00 T23 10
1/1/12 01:00:00 H12 88
1/1/12 02:00:00 H12 22
1/2/12 01:00:00 T21 10
1/2/12 02:00:00 T21 13
1/2/12 03:00:00 T21 12
1/2/12 04:00:00 T21 11
1/2/12 01:00:00 T25 14
1/2/12 01:00:00 T25 09
1/2/12 01:00:00 T23 18
1/2/12 01:00:00 H12 38
1/2/12 02:00:00 H12 29
1/3/12 01:00:00 T21 10
1/3/12 02:00:00 T21 12
1/3/12 03:00:00 T21 14
1/3/12 04:00:00 T21 11
1/3/12 01:00:00 T25 33
1/3/12 01:00:00 T25 09
1/3/12 01:00:00 T23 10
1/3/12 01:00:00 H12 88
1/3/12 02:00:00 H12 22
1/3/12 02:00:00 H12 99

Expected 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 UsageValue


1/1/12 03:00:00 T21 14
1/1/12 01:00:00 T25 10
1/1/12 01:00:00 T23 10
1/1/12 01:00:00 H12 88
1/2/12 02:00:00 T21 13
1/2/12 01:00:00 T25 14
1/2/12 01:00:00 T23 18
1/2/12 01:00:00 H12 38
1/3/12 02:00:00 T21 19
1/3/12 01:00:00 T25 33
1/3/12 01:00:00 T23 10
1/3/12 02:00:00 H12 99

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 01/29/2013 :  00:03:12  Show Profile  Reply with Quote
monthlyData is Table Name here

SELECT 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=1
ORDER BY Date_Time


--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/29/2013 :  02:40:33  Show Profile  Reply with Quote
quote:
Originally posted by bandi

monthlyData is Table Name here

SELECT 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=1
ORDER BY Date_Time


--
Chandu


though that might work for sample data given, its not correct add
the below sample data and you'll see why

2/1/12 01:00:00 T21 12
2/1/12 01:00:00 T21 30

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/29/2013 :  02:41:25  Show Profile  Reply with Quote
it should be

SELECT 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=1
ORDER BY Date_Time


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.83 seconds. Powered By: Snitz Forums 2000