March 23, 2010 at 11:55 am
Hi,
I have four different date fields in table. I have to find out the max and min date in each row and find the datediff ....How can i find the max and min date in each row from 4 diff coloumns
Thanks
March 23, 2010 at 12:16 pm
try using MAX and MIN functions????
Maybe I am making this too simple, or do not understand your question.
SELECT MAX(<ColName>), MIN(<ColName>)
FROM <TableName>
WHERE ??????
Andrew SQLDBA
March 23, 2010 at 12:35 pm
Would something like this help?
DECLARE @tbl TABLE
(id INT,
c1 DATETIME,
c2 DATETIME,
c3 DATETIME,
c4 DATETIME
)
INSERT INTO @tbl
SELECT 1,GETDATE(), GETDATE()-1,GETDATE()-2,GETDATE()+3 UNION ALL
SELECT 2,GETDATE()-5, GETDATE()-6,GETDATE()-4,GETDATE()-3
;WITH cte AS
(
SELECT id,DateVal
FROM
(SELECT id, c1, c2, c3, c4
FROM @tbl) p
UNPIVOT
(DateVal FOR Col IN
(c1, c2, c3, c4)
)AS unpvt
)
SELECT
id,
MIN(DateVal) AS min_date,
MAX(DateVal) AS max_date,
DATEDIFF(dd,MIN(DateVal),MAX(DateVal)) AS diff_date
FROM cte
GROUP BY id
March 23, 2010 at 12:43 pm
You can write your own User Defined Functions for the same. Take four parameters and return the maximum and minimum among them.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 23, 2010 at 12:54 pm
Lutz was close and gave me what I needed to write the following, hope this helps:
CREATE TABLE #TestTable
( id INT,
c1 DATETIME,
c2 DATETIME,
c3 DATETIME,
c4 DATETIME
)
INSERT INTO #TestTable
SELECT 1,GETDATE(), GETDATE()-1,GETDATE()-2,GETDATE()+3 UNION ALL
SELECT 2,GETDATE()-5, GETDATE()-6,GETDATE()-4,GETDATE()-3
GO
create function dbo.MinDate4(
@date1 datetime,
@date2 datetime,
@date3 datetime,
@date4 datetime
)
returns table
as return
(WITH cte AS
(
SELECT DateVal
FROM
(SELECT @date1 as c1, @date2 as c2, @date3 as c3, @date4 as c4
) p
UNPIVOT
(DateVal FOR Col IN
(c1, c2, c3, c4)
)AS unpvt
)
SELECT
MIN(DateVal) AS min_date
FROM cte
);
go
create function dbo.MaxDate4(
@date1 datetime,
@date2 datetime,
@date3 datetime,
@date4 datetime
)
returns table
as return
(WITH cte AS
(
SELECT DateVal
FROM
(SELECT @date1 as c1, @date2 as c2, @date3 as c3, @date4 as c4
) p
UNPIVOT
(DateVal FOR Col IN
(c1, c2, c3, c4)
)AS unpvt
)
SELECT
MAX(DateVal) AS max_date
FROM cte
);
go
select
*,
datediff(dd,mind4.min_date,maxd4.max_date) as date_diff
from
#TestTable t1
cross apply dbo.MinDate4(c1,c2,c3,c4) mind4
cross apply dbo.MaxDate4(c1,c2,c3,c4) maxd4
go
March 23, 2010 at 12:59 pm
Thanks guys
March 23, 2010 at 1:01 pm
Why would you use two functions for this task rather than doing it all at once?
My wild guess would be that the pure UNPIVOT solution should be more efficient. What downside do I miss?
I think both solutions will return identical results (haven't tested it though...).
March 23, 2010 at 1:06 pm
lmu92 (3/23/2010)
@Lynn:Why would you use two functions for this task rather than doing it all at once?
My wild guess would be that the pure UNPIVOT solution should be more efficient. What downside do I miss?
I think both solutions will return identical results (haven't tested it though...).
You don't have to, it just was easier for me to do it in pieces. Plus, if they are separate, each function does only one thing.
March 23, 2010 at 1:12 pm
Lynn Pettis (3/23/2010)
lmu92 (3/23/2010)
@Lynn:Why would you use two functions for this task rather than doing it all at once?
My wild guess would be that the pure UNPIVOT solution should be more efficient. What downside do I miss?
I think both solutions will return identical results (haven't tested it though...).
You don't have to, it just was easier for me to do it in pieces. Plus, if they are separate, each function does only one thing.
Makes sense. Those functions are easier to reuse anyway. I was just afraid of beginning to move to the dark side... 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply