Shanka Shanka - 7 months ago 6
SQL Question

Considering values from one table as column header in another

I have a base table where I need to calculate the difference between two dates based on the type of the entry.

tblA

+----------+------------+---------------+--------------+
| TypeCode | Log_Date | Complete_Date | Pending_Date |
+----------+------------+---------------+--------------+
| 1 | 18/04/2016 | 19/04/2016 | |
| 2 | 10/04/2016 | 18/04/2016 | 15/04/2016 |
| 3 | 12/04/2016 | 19/04/2016 | |
| 4 | 15/04/2016 | 17/04/2016 | 16/04/2016 |
| 5 | 16/04/2016 | 21/04/2016 | |
| 1 | 19/04/2016 | 20/04/2016 | |
| 2 | 20/03/2016 | 31/03/2015 | |
| 3 | 25/03/2016 | 28/03/2016 | |
| 4 | 26/03/2016 | 27/03/2016 | |
| 5 | 27/03/2016 | 30/03/2016 | |
+----------+------------+---------------+--------------+


I have another look up table which has the column names to be considered based on the TypeCode.

tblB

+----------+----------+---------------+
| TypeCode | DateCol1 | DateCol2 |
+----------+----------+---------------+
| 1 | Log_Date | Complete_Date |
| 2 | Log_Date | Pending_Date |
| 3 | Log_Date | Complete_Date |
| 4 | Log_Date | Pending_Date |
| 5 | Log_Date | Complete_Date |
+----------+----------+---------------+


I am doing a simple
DATEDIFF
between two dates for my calculation. However I want to lookup which columns to consider for this calculation from
tblB
and apply it on
tblA
based on the
TypeCode
.

Resulting table:

For example: When the
TypeCode
is
2
or
4
then the calculation should be
DATEDIFF(d, Log_Date, Pending_Date)
, otherwise
DATEDIFF(d, Log_Date, Complete_Date)


+----------+------------+---------------+--------------+----------+
| TypeCode | Log_Date | Complete_Date | Pending_Date | Cal_Days |
+----------+------------+---------------+--------------+----------+
| 1 | 18/04/2016 | 19/04/2016 | | 1 |
| 2 | 10/04/2016 | 18/04/2016 | 15/04/2016 | 5 |
| 3 | 12/04/2016 | 19/04/2016 | | 7 |
| 4 | 15/04/2016 | 17/04/2016 | 16/04/2016 | 1 |
| 5 | 16/04/2016 | 21/04/2016 | | 5 |
| 1 | 19/04/2016 | 20/04/2016 | | 1 |
| 2 | 20/03/2016 | 31/03/2015 | | |
| 3 | 25/03/2016 | 28/03/2016 | | 3 |
| 4 | 26/03/2016 | 27/03/2016 | | |
| 5 | 27/03/2016 | 30/03/2016 | | 3 |
+----------+------------+---------------+--------------+----------+


Any help would be appreciated. Thanks.

Answer

Use JOIN with CASE expression:

SELECT
    a.*,
    Cal_Days =  
        DATEDIFF(
            DAY,
            CASE 
                WHEN b.DateCol1 = 'Log_Date' THEN a.Log_Date
                WHEN b.DateCol1 = 'Complete_Date' THEN a.Complete_Date
                ELSE a.Pending_Date
            END,
            CASE 
                WHEN b.DateCol2 = 'Log_Date' THEN a.Log_Date
                WHEN b.DateCol2 = 'Complete_Date' THEN a.Complete_Date
                ELSE a.Pending_Date
            END
        )
FROM TblA a
INNER JOIN TblB b
    ON b.TypeCode = a.TypeCode