August 10, 2017 at 6:31 pm
hi
i have table like this
id a_date b_date c_date d_date e_date
1 01/02/2013 01/02/2014 01/02/2012 01/02/2011
2 01/02/2011 01/05/2011
3 01/02 2015 01/02/2013
out of this i need to create 3 columns
id date number
here date should be max(date) among all columns, number should be (a_date = 1, b_date = 2,c_date= 3,d_date = 4,e_date = 5)
example,
id date number
1 01/02/2014 5(number will be calculated when the next is null, here its e_date so 5)
2 01/05/2011 3(as this will be next column which is null)
2 01/02/2015 4(it should be d_date column , as for ID 3 i dont need to consider b_date even if its empty, so next is d_date = 4)
please help?
August 10, 2017 at 7:22 pm
coool_sweet - Thursday, August 10, 2017 6:31 PMhi
i have table like thisid a_date b_date c_date d_date e_date
1 01/02/2013 01/02/2014 01/02/2012 01/02/2011
2 01/02/2011 01/05/2011
3 01/02 2015 01/02/2013out of this i need to create 3 columns
id date number
here date should be max(date) among all columns, number should be (a_date = 1, b_date = 2,c_date= 3,d_date = 4,e_date = 5)
example,
id date number
1 01/02/2014 5(number will be calculated when the next is null, here its e_date so 5)
2 01/05/2011 3(as this will be next column which is null)
2 01/02/2015 4(it should be d_date column , as for ID 3 i dont need to consider b_date even if its empty, so next is d_date = 4)please help?
I'm sure you know that you need some DDL and data to give people something to work with, so here it is.
IF OBJECT_ID('tempdb.dbo.#t', 'u') IS NOT NULL DROP TABLE #t;
CREATE TABLE #t (
id Integer,
a_date Date,
b_date Date,
c_date Date,
d_date Date,
e_date Date);
INSERT INTO #t(id, a_date, b_date, c_date, d_date, e_date)
VALUES(1, '01/02/2013', '01/02/2014', '01/02/2012', '01/02/2011', NULL),
(2, '01/02/2011', '01/05/2011', NULL, NULL, NULL),
(3, '01/02/2015', '01/02/2013', NULL, NULL, NULL);
This should get you the max of the 5 date columns per row, but I don't understand your logic for how to calculate your 3rd column. I hope this helps get you part-way there.
WITH cteRows AS (
SELECT id, d, RN = ROW_NUMBER() OVER(PARTITION BY id ORDER BY d DESC)
FROM (SELECT id, a_date, b_date, c_date, d_date, e_date
FROM #t) x
UNPIVOT (d FOR dates IN (a_date, b_date, c_date, d_date, e_date)) u
)
SELECT id, max_date = d
FROM cteRows
WHERE RN = 1
ORDER BY id;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply