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 |
Eugeneb
Starting Member
4 Posts |
Posted - 2013-02-17 : 21:18:02
|
HiI 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 monthDate | PeakKVAtable 2 find 1 max value per day per monthDate | Daily KWH Open | KWHClosedOccupied | KWHClosednotOccupiedThe values in each row KWHClosedOccupied and KWHClosednotOccupiedneed summing and then find the max per day ie KWHClosedOccupied + KWHClosednotOccupied = TotalKWHClosedFinal tableDate | Daily KWH Open | TotalKWHClosed | PeakKVAAny 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, PeakKVAFROM Table1)t1INNER JOIN (SELECT DATEADD(dd,DATEDIFF(dd,0,[Date]),0) AS DateVal, [Daily KWH Open], [KWHClosedOccupied] + [KWHClosednotOccupied] AS [TotalKWHClosed]FROM Table1)t2ON t2.DateVal = t1.DateVal[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 | PeakKVA1/1/2013 |1223 |500 | 1502/1/2013 |1500 |250 | 1573/1/2013 |900 |750 | 160Basically 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
|
Hivisakh16'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.aspxSELECT 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
|
HiThanks, 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
|
OPquestion for youIn 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 useSELECT t1.[DateVal],PeakKVA,[Daily KWH Open],[TotalKWHClosed]FROM(SELECT DATEADD(dd,DATEDIFF(dd,0,[Date]),0) AS DateVal, MAX(PeakKVA) AS PeakKVAFROM Table1GROUP BY DATEADD(dd,DATEDIFF(dd,0,[Date]),0))t1INNER 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 Table1GROUP BY DATEADD(dd,DATEDIFF(dd,0,[Date]),0) )t2ON t2.DateVal = t1.DateVal ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|