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.
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.
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
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'
what is the result of this query? how many rows.
show us results of below query
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
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
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?
ah ok now I understand now 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
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'
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'