finding max values per day /month

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

finding max values per day /month

Author

Topic

Eugeneb
Starting Member

4 Posts

Posted - 2013-02-17 : 21:18:02

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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-17 : 23:10:49

Can you provide some sample data as well as expected output?

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-17 : 23:27:12

[code]
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
[/code]

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

Eugeneb
Starting Member

4 Posts

Posted - 2013-02-18 : 14:09:06

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.

Eugeneb
Starting Member

4 Posts

Posted - 2013-02-18 : 16:28:54

Hi

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-18 : 16:36:27

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;

Eugeneb
Starting Member

4 Posts

Posted - 2013-02-18 : 20:08:32

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 23:58:53

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources