Calculation of the difference between two Dates in SQLite based on multiple conditions

Question

please consider the following issue on the calculation of the difference between 2 dates in SQLite:

I have a table with the following information where I have 2 different cases. Each one of projects in this table represents 1 of 2 possible cases.

 Project name | date       | date1              | StageDuration* (header row) 

 Project1     | 2018-12-01 |-(no date or NULL)  |16

 Project2     | 2018-12-14 | 2018-12-06         |3

 Project2     | 2018-11-30 | 2018-12-06         |3

For project 2 (2nd case) 2 rows are possible because the given table is an outcome of left join. The project name and date column are from table1 and the project name, date1, StageDuration columns are from table 2. In case I have more than 1 row in table1 and 1 row in table 2 I get 2 rows in joined table (like in this example).

Currently I use the following SQLite code to get the Duration for case1/project1:

select *,
case when `date` IS NULL then `StageDuration` else '' end 
as "Duration"
from report

It works for project1. The problem I want to solve is to adapt the code and get the condition for 2nd case (project2) also included in it.

The condition I expect will be working is

if count(dates from report 1/date)>count(dates from report 2/date1) 

the difference should be (Duration I need) calculated as

today - max(JULIANDAY(`date`)) 

Will be very grateful for your help. This is my 2nd question here. Thank you for your time and understanding in advance.

Answer 1

Get the max date from a sub query and join against it on project name, then use that date to calculate duration

SELECT t1.*, proj_date1, stage_duration,
  CASE WHEN proj_date IS NULL then stage_duration 
  ELSE julianday(date('now')) - julianday(max_date)
  END as "Duration" 
FROM table1 t1
JOIN table2 t2 ON t1.proj_name = t2.proj_name
JOIN (SELECT proj_name, MAX(proj_date) as max_date 
      FROM table1 
      GROUP BY proj_name) AS s ON t1.proj_name = s.proj_name

I used my own table/column names but I hop the query makes sense anyway