CODEWITHSUNDEEP
sqlsql-servermaxmin
Vineeth V
Vineeth V

Reputation: 35

Get other columns that corresponds to MIN and MAX value of one column in SQL Server 2008

I've a situation, where I need to get both min() and max() of column and one more column that corresponds to min() and max() functions. I've tried the query using INNER JOIN, it was successful when using either min() or max(). When both are used nothing returned.

SELECT A.IslandCode, CONVERT(DATE, A.LogTime)LogTime
, MN.Demand MinDemand, MN.TIME MinDemandAt
FROM 
(
    SELECT IslandCode, CONVERT(DATE, LogTime) LogTime, SUM(KiloWatt) Demand
    FROM ph_MachineDailyReadings 
    WHERE IslandCode = 1 AND CONVERT(DATE, LogTime) = '2013-08-13'
    GROUP BY IslandCode, LogTime
) A
INNER JOIN 
(
    SELECT TOP 1 IslandCode, CONVERT(DATE, LogTime) LogTime, SUM(KiloWatt) Demand, 
    STR(DATEPART(HOUR, LogTime)) + ':' + LTRIM(STR(DATEPART(MINUTE, LogTime))) TIME
    FROM ph_MachineDailyReadings 
    WHERE IslandCode = 1 AND CONVERT(DATE, LogTime) = '2013-08-13'
    GROUP BY IslandCode, LogTime
    ORDER BY Demand
) MN
ON A.IslandCode = MN.IslandCode AND A.LogTime = MN.LogTime AND A.Demand = MN.Demand

the above query returns following result, that is correct

IslandCode  LogTime     MinDemand       MinDemandAt
1           2013-08-13  698         13:0

query for max()

SELECT A.IslandCode, CONVERT(DATE, A.LogTime)LogTime
, MX.Demand MaxDemand, MX.TIME MaxDemandAt
FROM 
(
    SELECT IslandCode, CONVERT(DATE, LogTime) LogTime, SUM(KiloWatt) Demand
    FROM ph_MachineDailyReadings 
    WHERE IslandCode = 1 AND CONVERT(DATE, LogTime) = '2013-08-13'
    GROUP BY IslandCode, LogTime
) A
INNER JOIN
(
    SELECT TOP 1 IslandCode, CONVERT(DATE, LogTime) LogTime, SUM(KiloWatt) Demand, 
    STR(DATEPART(HOUR, LogTime)) + ':' + LTRIM(STR(DATEPART(MINUTE, LogTime))) TIME
    FROM ph_MachineDailyReadings 
    WHERE IslandCode = 1 AND CONVERT(DATE, LogTime) = '2013-08-13'
    GROUP BY IslandCode, LogTime
    ORDER BY Demand DESC
) MX
ON A.IslandCode = MX.IslandCode AND A.LogTime = MX.LogTime AND A.Demand = MX.Demand

returns, this is also correct

IslandCode  LogTime       MaxDemand     MaxDemandAt
1           2013-08-13  1463            20:0

but when both the queries are used in same query, returns nothing

SELECT A.IslandCode, CONVERT(DATE, A.LogTime)LogTime
, MX.Demand MaxDemand, MX.TIME MaxDemandAt
, MN.Demand MinDemand, MN.TIME MinDemandAt
FROM 
(
    SELECT IslandCode, CONVERT(DATE, LogTime) LogTime, SUM(KiloWatt) Demand
    FROM ph_MachineDailyReadings 
    WHERE IslandCode = 1 AND CONVERT(DATE, LogTime) = '2013-08-13'
    GROUP BY IslandCode, LogTime
) A
INNER JOIN
(
    SELECT TOP 1 IslandCode, CONVERT(DATE, LogTime) LogTime, SUM(KiloWatt) Demand, 
    STR(DATEPART(HOUR, LogTime)) + ':' + LTRIM(STR(DATEPART(MINUTE, LogTime))) TIME
    FROM ph_MachineDailyReadings 
    WHERE IslandCode = 1 AND CONVERT(DATE, LogTime) = '2013-08-13'
    GROUP BY IslandCode, LogTime
    ORDER BY Demand DESC
) MX
ON A.IslandCode = MX.IslandCode AND A.LogTime = MX.LogTime AND A.Demand = MX.Demand
INNER JOIN 
(
    SELECT TOP 1 IslandCode, CONVERT(DATE, LogTime) LogTime, SUM(KiloWatt) Demand, 
    STR(DATEPART(HOUR, LogTime)) + ':' + LTRIM(STR(DATEPART(MINUTE, LogTime))) TIME
    FROM ph_MachineDailyReadings 
    WHERE IslandCode = 1 AND CONVERT(DATE, LogTime) = '2013-08-13'
    GROUP BY IslandCode, LogTime
    ORDER BY Demand
) MN
ON A.IslandCode = MN.IslandCode AND A.LogTime = MN.LogTime AND A.Demand = MN.Demand

expected result

IslandCode   LogTime     MinDemand  MinDemandAt   MaxDemand   MaxDemandAt
    1       2013-08-13  1463        20:0        698      13:0

can i get this result in single query or do i need to go for a stored procedure based solution?

thanks in advance

Upvotes: 2

Views: 4593

Answers (2)

GarethD
GarethD

Reputation: 69759

You can do this using Analytic functions:

WITH Data AS
(   SELECT  IslandCode, 
            LogDate = CAST(LogTime AS DATE),
            LogTime, 
            Demand = SUM(KiloWatt) ,
            RowNumAsc = ROW_NUMBER() OVER (PARTITION BY IslandCode, CAST(LogTime AS DATE) ORDER BY SUM(Kilowatt)),
            RowNumDesc = ROW_NUMBER() OVER (PARTITION BY IslandCode, CAST(LogTime AS DATE) ORDER BY SUM(Kilowatt) DESC) 
    FROM    ph_MachineDailyReadings 
    WHERE   IslandCode = 1 
    AND     CAST(LogTime AS DATE) = '20130813'
    GROUP BY IslandCode, LogTime
)
SELECT  ma.IslandCode,
        LogTime = ma.LogDate,
        MinDemand = mi.Demand,
        MinDemandAt = CAST(mi.LogTime AS TIME),
        MaxDemand = ma.Demand,
        MaxDemandAt = CAST(ma.LogTime AS TIME)
FROM    Data mi
        INNER JOIN Data ma
            ON ma.IslandCode = mi.IslandCode
            AND ma.LogDate = mi.LogDate
WHERE   mi.RowNumAsc = 1
AND     ma.RowNumDesc = 1;

Example on SQL Fiddle

EDIT

This does not require parameters, and if the WHERE clause is omitted as follows it would return the min and max for each island code on each date:

WITH Data AS
(   SELECT  IslandCode, 
            LogDate = CAST(LogTime AS DATE),
            LogTime, 
            Demand = SUM(KiloWatt) ,
            RowNumAsc = ROW_NUMBER() OVER (PARTITION BY IslandCode, CAST(LogTime AS DATE) ORDER BY SUM(Kilowatt)),
            RowNumDesc = ROW_NUMBER() OVER (PARTITION BY IslandCode, CAST(LogTime AS DATE) ORDER BY SUM(Kilowatt) DESC) 
    FROM    ph_MachineDailyReadings 
    GROUP BY IslandCode, LogTime
)
SELECT  ma.IslandCode,
        LogTime = ma.LogDate,
        MinDemand = mi.Demand,
        MinDemandAt = CAST(mi.LogTime AS TIME),
        MaxDemand = ma.Demand,
        MaxDemandAt = CAST(ma.LogTime AS TIME)
FROM    Data mi
        INNER JOIN Data ma
            ON ma.IslandCode = mi.IslandCode
            AND ma.LogDate = mi.LogDate
WHERE   mi.RowNumAsc = 1
AND     ma.RowNumDesc = 1;

Example on SQL Fiddle

One final note, I have put your logTime in the TIME format as this seems more usable than a VARCHAR Format, if you do need it in VARCHAR you can use:

CONVERT(VARCHAR(10), ma.LogTime, 8)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269643

I don't think you need the second join. You could do this with just a union all:

(SELECT TOP 1 IslandCode, CONVERT(DATE, LogTime) LogTime, SUM(KiloWatt) Demand, 
        STR(DATEPART(HOUR, LogTime)) + ':' + LTRIM(STR(DATEPART(MINUTE, LogTime))) TIME
 FROM ph_MachineDailyReadings 
 WHERE IslandCode = 1 AND CONVERT(DATE, LogTime) = '2013-08-13'
 GROUP BY IslandCode, LogTime
 ORDER BY Demand
) union all
(SELECT TOP 1 IslandCode, CONVERT(DATE, LogTime) LogTime, SUM(KiloWatt) Demand, 
 STR(DATEPART(HOUR, LogTime)) + ':' + LTRIM(STR(DATEPART(MINUTE, LogTime))) TIME
 FROM ph_MachineDailyReadings 
 WHERE IslandCode = 1 AND CONVERT(DATE, LogTime) = '2013-08-13'
 GROUP BY IslandCode, LogTime
 ORDER BY Demand desc
)

Upvotes: 0

Related Questions