0

Is this possible? For example, I want to get the latest date for the value of PC2Date,Re2Date,Rp2Date then the resut will be the value of PC2 or Re2 or Rp2. I want to add another column for the Result of the sorting.

+------------------------------------------------------------------------------------------------------+
|id  |  Pc2  |         Pc2Date         | Re2   | Re2Date                |Rp2   | Rp2Date               |
--------------------------------------------------------------------------------------------------------
|aaa | PCRej | 2016-12-28 07:44:08.000 | LARej |2016-11-18 20:24:14.000 |RpRej |2016-11-19 19:14:06.000|  
|bbb | PCRej | 2016-11-09 20:57:15.000 | NULL  |NULL                    |RpGd  |2016-11-12 12:23:51.000|
|ccc | PCRej | 2016-10-06 20:07:04.000 | NULL  |NULL                    |NULL  |NULL                   |
|ddd | PCRej | 2016-10-27 14:15:32.000 | NULL  |NULL                    |RpRej |2016-11-02 14:59:30.000|
|eee | PCRej | 2016-10-24 19:30:22.000 | LARej |2016-11-03 09:27:12.000 |NULL  |NULL                   |
|fff | PCRej | 2016-10-24 19:30:22.000 | LAGd  |2017-01-03 08:27:16.000 |NULL  |NULL                   |

So for that sample Condition the result will be something like this: Output

+--------------------------------------------------------------------------------------------------------------+
|id  |  Pc2  |         Pc2Date         | Re2   | Re2Date                |Rp2   | Rp2Date               | Result|
----------------------------------------------------------------------------------------------------------------
|aaa | PCRej | 2016-12-28 07:44:08.000 | LARej |2016-11-18 20:24:14.000 |RpRej |2016-11-19 19:14:06.000| PCRej |
|bbb | PCRej | 2016-11-09 20:57:15.000 | NULL  |NULL                    |RpGd  |2016-11-12 12:23:51.000| RpRgd |
|ccc | PCRej | 2016-10-06 20:07:04.000 | NULL  |NULL                    |NULL  |NULL                   | PCRej |
|ddd | PCRej | 2016-10-27 14:15:32.000 | NULL  |NULL                    |RpRej |2016-11-02 14:59:30.000| RPRej |
|eee | PCRej | 2016-10-24 19:30:22.000 | LARej |2016-11-03 09:27:12.000 |NULL  |NULL                   | LARej |
|fff | PCRej | 2016-10-24 19:30:22.000 | LAGd  |2017-01-03 08:27:16.000 |NULL  |NULL                   | LAgd  |

TIA!

Improve this question
3
  • In your example, result is not a date. In the first row, how do you come up with PCRej by using the three date fields as an input? Commented Jan 18, 2017 at 7:07
  • 1
    What you tried so far ? Commented Jan 18, 2017 at 7:08
  • @Nick.McDermaid I edited my question. Commented Jan 18, 2017 at 7:13

3 Answers 3

Reset to default
4

Probably something like this?

SELECT t.*, ca.Val AS Result
FROM <<table>> t
   CROSS APPLY (
       SELECT TOP (1) Val
       FROM (
           SELECT CONVERT (NVARCHAR(MAX), t.Re2) AS Val
                  , CONVERT (DATETIME, t.Re2Date) AS ORD
           UNION ALL
           SELECT CONVERT (NVARCHAR(MAX), t.Rp2) AS Val
                  , CONVERT (DATETIME, t.Rp2Date) AS ORD
           UNION ALL
           SELECT CONVERT (NVARCHAR(MAX), t.PC2) AS Val
                  , CONVERT (DATETIME, t.PC2Date) AS ORD
       )
       ORDER BY ORD
   ) ca
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Ill try this! thanks ! keep you updated if this works!
Nice! That's a better answer then mine. +1/
Order by ORD DESC , as max date is requierd
Using VALUES instead of SELECT .. UNION ALL is an option to consider.
2

You could use a case expression to figure out the latest date:

UPDATE TableName
SET Result = CASE WHEN ISNULL(Pc2Date, '1900-01-01') > ISNULL(Re2Date, '1900-01-01') AND 
                       ISNULL(Pc2Date, '1900-01-01') > ISNULL(Rp2Date, '1900-01-01') THEN 
             Re2
                  WHEN ISNULL(Re2Date, '1900-01-01') > ISNULL(Pc2Date, '1900-01-01') AND 
                       ISNULL(Re2Date, '1900-01-01') > ISNULL(Rp2Date, '1900-01-01') THEN 
             Re2
                  WHEN ISNULL(Rp2Date, '1900-01-01') > ISNULL(Pc2Date, '1900-01-01') AND 
                       ISNULL(Rp2Date, '1900-01-01') > ISNULL(Re2Date, '1900-01-01') THEN 
             Rp2
             END
Improve this answer

1 Comment

Ill try this first sir. Thanks! Will keep you updated if this meets my desired output.
0

you also can refer the following script:

    CREATE TABLE #t(id VARCHAR(100),Pc2 VARCHAR(100),Pc2Date DATETIME,Re2 VARCHAR(100),Re2Date DATETIME,Rp2 VARCHAR(100),Rp2Date DATETIME,[Result] VARCHAR(100))
    INSERT INTO #t(id,Pc2,Pc2Date,Re2,Re2Date,Rp2,Rp2Date)
    SELECT 'aaa','PCRej','2016-12-28 07:44:08.000','LARej','2016-11-18 20:24:14.000','RpRej','2016-11-19 19:14:06.000' UNION
    SELECT 'bbb','PCRej','2016-11-09 20:57:15.000',NULL ,NULL,'RpGd ','2016-11-12 12:23:51.000' UNION
    SELECT 'ccc','PCRej','2016-10-06 20:07:04.000',NULL ,NULL,NULL,NULL UNION             
    SELECT 'ddd','PCRej','2016-10-27 14:15:32.000',NULL,NULL,'RpRej','2016-11-02 14:59:30.000' UNION
    SELECT 'eee','PCRej','2016-10-24 19:30:22.000','LARej','2016-11-03 09:27:12.000',NULL,NULL UNION
    SELECT 'fff','PCRej','2016-10-24 19:30:22.000','LAGd','2017-01-03 08:27:16.000',NULL,NULL

    UPDATE #t SET Result=(SELECT TOP 1 c.title FROM (VALUES(Pc2,Pc2Date),(Re2,Re2Date),(Rp2,Rp2Date)) c(title,[date]) ORDER BY c.date desc)

id  Pc2 Pc2Date Re2 Re2Date Rp2 Rp2Date Result
aaa PCRej   2016-12-28 07:44:08.000 LARej   2016-11-18 20:24:14.000 RpRej   2016-11-19 19:14:06.000 PCRej
bbb PCRej   2016-11-09 20:57:15.000 NULL    NULL    RpGd    2016-11-12 12:23:51.000 RpGd 
ccc PCRej   2016-10-06 20:07:04.000 NULL    NULL    NULL    NULL    PCRej
ddd PCRej   2016-10-27 14:15:32.000 NULL    NULL    RpRej   2016-11-02 14:59:30.000 RpRej
eee PCRej   2016-10-24 19:30:22.000 LARej   2016-11-03 09:27:12.000 NULL    NULL    LARej
fff PCRej   2016-10-24 19:30:22.000 LAGd    2017-01-03 08:27:16.000 NULL    NULL    LAGd
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.