Here the main purpose is to get the maximum value from date1,date2,date3,date4 for each of the product, where ProdId is a unique column (Primary key column)
DECLARE @table TABLE
(
ProdId Int,
date1 datetime,
date2 datetime,
date3 datetime,
date4 datetime
)
INSERT INTO @table VALUES (1,'2014-09-20 08:14:57.647','2014-09-30 08:15:16.657','2014-09-28 08:15:28.067','2014-10-01 08:15:43.290')
INSERT INTO @table VALUES (2,'2014-09-21 08:14:57.647','2014-09-22 08:15:16.657','2014-09-23 08:15:28.067','2014-09-24 08:15:43.290')
SELECT * FROM @table
--- Method 1 ----
SELECT ProdId,Maxdate
FROM @table
CROSS APPLY (SELECT MAX(d) Maxdate FROM (VALUES (Date1), (Date2), (Date3), (Date4)) AS a(d)) md
--- Method 2
----
SELECT ProdId,MAX(Maxdate)Maxdate
FROM @table
UNPIVOT
(
Maxdate FOR E IN (Date1,Date2,Date3,Date4)
)AS Unpvt
GROUP BY ProdId
--- Method 3
----
SELECT ProdId,MAX(dt)Maxdate
FROM
(
SELECT ProdId,MAX(date1)dt FROM @table GROUP BY ProdId
UNION
SELECT ProdId,MAX(date2) FROM @table GROUP BY ProdId
UNION
SELECT ProdId,MAX(date3) FROM @table GROUP BY ProdId
UNION
SELECT ProdId,MAX(date4) FROM @table GROUP BY ProdId
)x
GROUP BY ProdId
No comments:
Post a Comment