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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 finding max values per day /month
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Eugeneb
Starting Member

4 Posts

Posted - 02/17/2013 :  21:18:02  Show Profile  Reply with Quote
Hi

I don't have much experience of SQL queries and am trying to build a report. I have 2 tables with a common date field.

table 1 find 1 max row per day per month

Date | PeakKVA

table 2 find 1 max value per day per month

Date | Daily KWH Open | KWHClosedOccupied | KWHClosednotOccupied

The values in each row KWHClosedOccupied and KWHClosednotOccupied

need summing and then find the max per day ie KWHClosedOccupied + KWHClosednotOccupied = TotalKWHClosed


Final table

Date | Daily KWH Open | TotalKWHClosed | PeakKVA

Any help would be appreciated

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 02/17/2013 :  23:10:49  Show Profile  Reply with Quote
Can you provide some sample data as well as expected output?

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/17/2013 :  23:27:12  Show Profile  Reply with Quote

SELECT t1.[DateVal],PeakKVA,[Daily KWH Open],[TotalKWHClosed]
FROM
(
SELECT DATEADD(dd,DATEDIFF(dd,0,[Date]),0) AS DateVal, PeakKVA
FROM Table1
)t1
INNER JOIN 
(
SELECT DATEADD(dd,DATEDIFF(dd,0,[Date]),0) AS DateVal, [Daily KWH Open], [KWHClosedOccupied]  + [KWHClosednotOccupied] AS [TotalKWHClosed]
FROM Table1
)t2
ON t2.DateVal = t1.DateVal


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

Go to Top of Page

Eugeneb
Starting Member

4 Posts

Posted - 02/18/2013 :  14:09:06  Show Profile  Reply with Quote
Hi yeah this is what I'm looking for...


Date |Daily KWH Open | TotalKWHClosed | PeakKVA

1/1/2013 |1223 |500 | 150
2/1/2013 |1500 |250 | 157
3/1/2013 |900 |750 | 160

Basically 1 max value per day, the PeakKVA table has multiple rows throughout the day. The other table only has the 1 entry at midnight.
Go to Top of Page

Eugeneb
Starting Member

4 Posts

Posted - 02/18/2013 :  16:28:54  Show Profile  Reply with Quote
Hi

visakh16's solution nealry works, except I get multiple rows for each day, not just 1 with the max value in each column...

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3334 Posts

Posted - 02/18/2013 :  16:36:27  Show Profile  Reply with Quote
Can you try this - it's a slight tweak to the query Visakh posted. Usually it is more useful to have a set of sample input data and the CORRESPONDING sample output data. What is even better would be code that can generate that data in test tables. Take a look at this blog, that might help in posting: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

SELECT t1.[DateVal],
       PeakKVA,
       [Daily KWH Open],
       [TotalKWHClosed]
FROM   (
           SELECT DATEADD(dd, DATEDIFF(dd, 0, [Date]), 0) AS DateVal,
                  PeakKVA,
                  ROW_NUMBER() OVER (PARTITION BY DATEADD(dd, DATEDIFF(dd, 0, [Date]), 0)
			ORDER BY PeakKVA DESC ) AS RN1
           FROM   Table1
       )t1
       INNER JOIN (
                SELECT DATEADD(dd, DATEDIFF(dd, 0, [Date]), 0) AS DateVal,
                       [Daily KWH Open],
                       [KWHClosedOccupied] + [KWHClosednotOccupied] AS 
                       [TotalKWHClosed],
                       ROW_NUMBER() OVER (PARTITION BY DATEADD(dd, DATEDIFF(dd, 0, [Date]), 0)
				ORDER BY [KWHClosedOccupied] + [KWHClosednotOccupied] DESC) AS RN2
                FROM   Table2
            )t2
            ON  t2.DateVal = t1.DateVal AND t1.RN1 = 1 AND t2.RN2 = 1;
Go to Top of Page

Eugeneb
Starting Member

4 Posts

Posted - 02/18/2013 :  20:08:32  Show Profile  Reply with Quote
Hi

Thanks, works great, This is my first post on this forum so I'll try and remember to format my next post as suggested.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/18/2013 :  23:58:53  Show Profile  Reply with Quote
OP
question for you
In the output what value you want for [Daily KWH Open] field? is it value corresponding to day where you've maximum [TotalKWHClosed] value or do you just want to take max of [Daily KWH Open] field itself? if former, then earlier sugestion from James would work.

If latter use


SELECT t1.[DateVal],PeakKVA,[Daily KWH Open],[TotalKWHClosed]
FROM
(
SELECT DATEADD(dd,DATEDIFF(dd,0,[Date]),0) AS DateVal, MAX(PeakKVA) AS  PeakKVA
FROM Table1
GROUP BY DATEADD(dd,DATEDIFF(dd,0,[Date]),0)
)t1
INNER JOIN 
(
SELECT DATEADD(dd,DATEDIFF(dd,0,[Date]),0) AS DateVal, MAX([Daily KWH Open]) AS [Daily KWH Open], MAX([KWHClosedOccupied]  + [KWHClosednotOccupied]) AS [TotalKWHClosed]
FROM Table1
GROUP BY DATEADD(dd,DATEDIFF(dd,0,[Date]),0) 
)t2
ON t2.DateVal = t1.DateVal


------------------------------------------------------------------------------------------------------
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.08 seconds. Powered By: Snitz Forums 2000