| Author |
 Topic  |
|
|
Eugeneb
Starting Member
4 Posts |
 Posted - 02/17/2013 : 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
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 02/17/2013 : 23:10:49
|
Can you provide some sample data as well as expected output?
--
Chandu |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47152 Posts |
Posted - 02/17/2013 : 23:27:12
|
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/
|
|
|
|
Eugeneb
Starting Member
4 Posts |
Posted - 02/18/2013 : 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 - 02/18/2013 : 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
Flowing Fount of Yak Knowledge
1511 Posts |
Posted - 02/18/2013 : 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 - 02/18/2013 : 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
India
47152 Posts |
Posted - 02/18/2013 : 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/
|
|
|
| |
Topic |
|
|
|
finding max values per day /month
