SQLTeam.com | Weblogs | Forums

Maximum Value of multiple columns with date

sql2008r2

#1

Hi Experts!

I need write a select statement which retrieve the maximum value in the columns with date ( the date of maximum value of the column)

I tried google but failed appreciate your kind help please.


#2

what does your table look like?

create table dbo.Datemania(date1 datetime, date2 datetime, date3 datetime, date4 datetime, date5 datetime)

insert into dbo.Datemania
select getdate() + 1, getdate() + 2, getdate() + 3, getdate() + 4, getdate() + 5 union 
select getdate() + 6, getdate() + 7, getdate() + 8, getdate() + 9, getdate() + 10 union
select getdate() + 11, getdate() + 12, getdate() + 13, getdate() + 14, getdate() + 15 

select * from dbo.Datemania

SELECT   max(FieldValue) maxdate
    FROM 
    (
        SELECT [date1], [date2],[date3],[date4],[date5]
         FROM [dbo].[Datemania]
    ) MyTable
    UNPIVOT
    (FieldValue FOR FieldCode IN ([date1], [date2],[date3],[date4],[date5]))AS MyUnPivot
 

Drop table dbo.Datemania

#3

HI Yosiasz,

Thank you for your help...

The below I try the query the date from all columns retrieving ok , but the date having problem cannot get the date of the maximum value in the columns ?
please advise in below or another solution

SELECT TD.START_DATETIME,TD.ITEM_NAME, 
    (TD.PROD_GAS_VOL)'Max Gas Production', 
    (TD.PROD_OIL_VOL) 'Max Oil Production',
    (TD.PROD_WAT_VOL) 'Max Water Production',
    (TD.PROD_COND_VOL) 'Max Condensate Production'

    FROM 
       VT_TOTALS_DAY_en_US TD
     INNER JOIN
     (  SELECT MAX(START_DATETIME) as MAXDATE
    ,ITEM_NAME as ITEM_NAME
       FROM VT_TOTALS_DAY_en_US
       
    WHERE ITEM_NAME IN (
    'Gari',
  )     GROUP BY ITEM_NAME )TD1
    ON
    TD.ITEM_NAME=TD1.ITEM_NAME
    AND TD.START_DATETIME=TD1.MAXDATE
    AND  TD.START_DATETIME >= '1/1/2015'

#4

what is the result of this query? how many rows.
show us results of below query


#5

The query showing one row the date is showing the maximum , the data I find also not correct

START_DATETIME	             ITEM_NAME	Max Gas Production	Max Oil Production  	Max Water Production   
    **2016-02-29 00:00:00.000**	 Ghar	      0.067000	            900.00000	             0.10000

#6

now try this and show us results if any

select *
FROM 
       VT_TOTALS_DAY_en_US TD
where START_DATETIME='2016-02-29 00:00:00.000'

because that is what you are doing when joining subquery

ON
    TD.ITEM_NAME=TD1.ITEM_NAME
    AND TD.START_DATETIME=TD1.MAXDATE

#7

By querying the results are showing for maximum date in the database for ITEM_NAME ='Ghar' also the data of the columns belongs to same date maximum '2016-02-29 00:00:00.000'

Same results retrieved which are replied in previous post.

My requirement
How to get the Maximum value from the columns with exact date from which date the maximum value of the columns belongs too?


#8
  1. ok I see what you mean it is the same table/view --> VT_TOTALS_DAY_en_US
  2. ITEM_NAME ='Ghar' or ITEM_NAME ='Gari'?
  3. what do you mean by "How to get the Maximum value from the columns" which columns?

#9

ah ok now I understand now :frowning: sorry

try this

;with cteMaxDate(MAXDATE, ITEM_NAME)
as
(
SELECT MAX(START_DATETIME) as MAXDATE
               ,ITEM_NAME as ITEM_NAME
         FROM VT_TOTALS_DAY_en_US        
        WHERE ITEM_NAME IN ('Gari')     GROUP BY ITEM_NAME 
)
SELECT TD.START_DATETIME,
       TD.ITEM_NAME, 
       max(TD.PROD_GAS_VOL)'Max Gas Production', 
       max(TD.PROD_OIL_VOL) 'Max Oil Production',
       max(TD.PROD_WAT_VOL) 'Max Water Production',
       max(TD.PROD_COND_VOL) 'Max Condensate Production'
 FROM VT_TOTALS_DAY_en_US TD
     INNER JOIN cteMaxDate TD1
    ON TD.ITEM_NAME=TD1.ITEM_NAME
   AND TD.START_DATETIME=TD1.MAXDATE
   AND  TD.START_DATETIME >= '1/1/2015'
   group by TD.START_DATETIME, TD.ITEM_NAME

#10
Trying to run  having same results no change showing the Maximum  date  of  'Gari'  in the database and the data of the columns same belong to date `'2016-02-29 00:00:00.000'`

;with cteMaxDate(MAXDATE, ITEM_NAME)
    as
    (
    SELECT MAX(START_DATETIME) as MAXDATE
                   ,ITEM_NAME as ITEM_NAME
             FROM VT_TOTALS_DAY_en_US        
            WHERE ITEM_NAME IN ('Gari')     GROUP BY ITEM_NAME 
    )
    SELECT TD.START_DATETIME,
           TD.ITEM_NAME, 
           max(TD.PROD_GAS_VOL)'Max Gas Production', 
           max(TD.PROD_OIL_VOL) 'Max Oil Production',
           max(TD.PROD_WAT_VOL) 'Max Water Production',
           max(TD.PROD_COND_VOL) 'Max Condensate Production'
     FROM VT_TOTALS_DAY_en_US TD
         INNER JOIN cteMaxDate TD1
        ON TD.ITEM_NAME=TD1.ITEM_NAME
       AND TD.START_DATETIME=TD1.MAXDATE
       AND  TD.START_DATETIME >= '1/1/2015'
       group by TD.START_DATETIME, TD.ITEM_NAME


START_DATETIME	             ITEM_NAME	Max Gas Production	Max Oil Production  	Max Water Production   
    **2016-02-29 00:00:00.000**	 Ghari	      0.067000	            900.00000	    

If I try to use the date range in between what is the maximum value of the columns no results retrieved

AND  TD.START_DATETIME >= '1/1/2015'
AND  TD.START_DATETIME <= '12/31/2015'

#11

well since you are doing this

AND TD.START_DATETIME=TD1.MAXDATE

you will only get 2016-02-29 00:00:00.000 which is not

AND  TD.START_DATETIME >= '1/1/2015'
AND  TD.START_DATETIME <= '12/31/2015'