Hi,
I have table with four fields ID, Date1, Date2,Date3,Date4. Dates are in numeric farmat like 20070927. How can i get largest date (from date1 to date4) on each row. Please suggest query.
Thanks
One posible solution is to unpivot those columns and then use "group by".
Code Block
declare @.t table (
[id] int not null identity unique,
date1 int,
date2 int,
date3 int,
date4 int
)
insert into @.t(date1, date2, date3, date4) values(20070305, 20070401, 20070928, 20070704)
insert into @.t(date1, date2, date3, date4) values(20070315, 20070411, 20070228, 20070714)
select
[id],
max(dt) max_dt
from
@.t as t
unpivot
(
dt
for date_col in ([date1], [date2], [date3], [date4])
) as unpvt
group by
[id]
go
AMB|||
Bukhari wrote:
Hi,
I have table with four fields ID, Date1, Date2,Date3,Date4. Dates are in numeric farmat like 20070927. How can i get largest date (from date1 to date4) on each row. Please suggest query.
Thanks
A function like Oracle's GREATEST would come in handy here
What about
Code Block
CREATE TABLE tableA
( id INT IDENTITY PRIMARY KEY
, date1 INT
, date2 INT
, date3 INT
, date4 INT)
INSERT INTO tableA SELECT 20070927, 20070928, 20070926, 20080927
INSERT INTO tableA SELECT 20070927, 20071028, 20070926, 20060927
SELECT id, MAX(iDate) AS maxdate
FROM
(SELECT id,
CASE i
WHEN 1 THEN date1
WHEN 2 THEN date2
WHEN 3 THEN date3
WHEN 4 THEN date4
END AS iDate
FROM tableA
CROSS JOIN
(SELECT 1 AS i
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4) T) T
GROUP BY id
SELECT id,
CASE
WHEN date1 > date2 AND date1 > date3 AND date1 > date4 THEN date1
WHEN date2 > date3 AND date2 > date4 THEN date2
WHEN date3 > date4 THEN date3
ELSE date4 END AS maxdate
FROM tableA
DROP TABLE tableA
id maxdate
-- --
1 20080927
2 20071028
(2 row(s) affected)
id maxdate
-- --
1 20080927
2 20071028
(2 row(s) affected)
|||First off, I just have to note that this is not really a relational design. Each of these dates should be stored in it's own row, and this is an easy SQL problem.
Second, the best relational solution I have found uses a subquery with a UNION ALL operator to "normalize" the date values for an aggregate:
create table dates
(
dateKey int primary key ,
date1 int,
date2 int,
date3 int,
date4 int
)
insert into dates
select 1, 20070101,20070102,20070103,20070104
union
select 2, 20070201,20070202,20070203,20070104
go
select dateKey,
(select min(date)
from (select date1 as date union all select date2 union all
select date3 union all select date4) as dates) as minDate
from dates
It is really quite fast, but if you need more performance, consider using a CLR function to do this. I have found that this technique is about the same speed with you have a few dates, or less than millions of rows to deal with (and it is certainly easier to code and deploy...)
|||Excilent solution. Thanks very much hunchback and frank.
I will go with CASE--WHEN solution.
|||I was really struggling with it. Thanks a lot Louis.|||I just want to mention that what the solution using "case" is doing is unpivoting. Well, all posted solutions are unpivoting some how.
AMB
|||Can i specify criteria in Where clause? Where maxdate less than last 60 days.
Where maxdate < CONVERT(VARCHAR, DATEADD(day, - 60, GETDATE()), 112)
|||but maxdate is not column so it generate error, how can i use CASE WHEN statement in WHERE clause?
thanks
Yes, but you need to use a derived table:
select *
from ( --your original query goes here
select dateKey,
(select min(date)
from (select date1 as date union all select date2 union all
select date3 union all select date4) as dates) as minDate
from dates) as dateQuery
where minDate > 20070101
or CTE if you are using 2005:
with dateQuery as (
select dateKey,
(select min(date)
from (select date1 as date union all select date2 union all
select date3 union all select date4) as dates) as minDate
from dates )
select *
from dateQuery
where minDate > 20070101
Or you can just repeat the subquery:
select dateKey,
(select min(date)
from (select date1 as date union all select date2 union all
select date3 union all select date4) as dates) as minDate
from dates
where (select min(date)
from (select date1 as date union all select date2 union all
select date3 union all select date4) as dates) > 20070101
But that is the least astetically pleasing... 
Add a "having" clause to the solution I suggested.
Code Block
declare @.t table (
[id] int not null identity unique,
date1 int,
date2 int,
date3 int,
date4 int
)
insert into @.t(date1, date2, date3, date4) values(20070305, 20070401, 20070928, 20070704)
insert into @.t(date1, date2, date3, date4) values(20070315, 20070411, 20070228, 20070114)
select
[id],
max(dt) max_dt
from
@.t as t
unpivot
(
dt
for date_col in ([date1], [date2], [date3], [date4])
) as unpvt
group by
[id]
having
max(dt) < CONVERT(CHAR(8), DATEADD(day, - 60, GETDATE()), 112)
go
AMB|||Great Stuff, Thanks
|||Hunchback, nice stuff. I would say that both yours and mine have merits, mine is a bit more clean in one way (I am doing a rowwise operation, so I could include most any kind of operation in mine, whereas yours includes an aggregate that would be limiting, but definitely does what was called for, and if you were doing multiple operations only on the date data, might be a lot faster.
|||Hi Louis,
Well, I would prefer to follow what you mentioned (normalizing) in your first post to this thread.
Thanks,
Alejandro Mesa
P.S. I am the guy who asked you to sign my book (I was staying at the "Red Lion") during this year summit. I hope to see you again, to ask you to sign also the one about DMVs & DMFs.
|||>>Well, I would prefer to follow what you mentioned (normalizing) in your first post to this thread.<<
Amen to that, but I have had to do this sort of operation for other reasons. In our etl, we join a bunch of tables together and then get the earliest update date from all of the joined rows so we can include/exclude rows for ETL.
>>also the one about DMVs & DMFs.<<
I definitely remember. I just hope I ever finish that book
Just to throw another approach into the mix.
create function Util.fn_max( @.p1 int, @.p2 int)
returns int
as
begin
return (select case when @.p1>=@.p2 then @.p1 else @.p2 end as int)
end
Select Util.fn_max(Util.fn_max(Util.fn_max(date1, date2), date3), date4)
Change util to the schema that you want, aka dbo. I keep things like this in a Util schema.
