How to calculate remaining days using DATEDIFF

Question

I wrote a basic query to display all projects, due date and completion status but I want to extend my query to display days to completion which should show remaining days until due for each incomplete project. How can I use the DATEDIFF function to achieve desired result?

Can I use

=IIF(DATEDIFF(duedate.Day, Fields!Created.Value, Today) < 30, 1, 0)

Output:

id  name    duedate     today   
---------------------------------------------------------------
1   Alpha   2040-01-01  2018-07-01T01:49:43.943Z    Incomplete
2   Bravo   2030-03-01  2018-07-01T01:49:43.943Z    Incomplete
3   Charlie 2017-02-01  2018-07-01T01:49:43.943Z    Complete
4   Delta   2017-04-01  2018-07-01T01:49:43.943Z    Complete

My current query:

SELECT 
    id, name, duedate, GETDATE() AS today,
    CASE 
       WHEN duedate <= CURRENT_TIMESTAMP THEN 'Complete'
       WHEN duedate >= CURRENT_TIMESTAMP THEN 'Incomplete'
    END
FROM 
    Project
ORDER BY 
    Project.name ASC;

Desired result:

id  name    duedate     today                       Status      daystocomp
--------------------------------------------------------------------------
1   Alpha   2040-01-01  2018-07-01T01:49:43.943Z    Incomplete  8030 or 22y1m1d
2   Bravo   2030-03-01  2018-07-01T01:49:43.943Z    Incomplete  4380 or 12y1m1d
3   Charlie 2017-02-01  2018-07-01T01:49:43.943Z    Complete    0
4   Delta   2017-04-01  2018-07-01T01:49:43.943Z    Complete    0

Answer 1

Yes, you can use datediff():

SELECT id, name, duedate, GETDATE() AS today,
       (CASE WHEN duedate <= CURRENT_TIMESTAMP THEN 'Complete'
             WHEN duedate > CURRENT_TIMESTAMP THEN 'Incomplete'
        END),
       (CASE WHEN duedate > CURRENT_TIMESTAMP
             THEN DATEDIFF(day, CURRENT_TIMESTAMP, duedate)
        END) as daystocomp
FROM Project
ORDER BY Project.name asc;