kazzi kazzi - 1 month ago 10
SQL Question

DATEDIFF() Excluding Marked Days

I'd like to see how many days there are between two dates excluding certain dates that depend on a value stated in another table.

table1

ID in_date out_date
001 01/01/2017 01/05/2017
002 01/03/2017 01/05/2017


For example:

SELECT
id
,datediff(dd, t1.in_date, t1.out_date) as diff
FROM table1 t1


Would bring

ID diff
001 4
002 2


But let's say I have another table:

table2

date use
01/01/2017 Y
01/02/2017 N
01/03/2017 N
01/04/2017 Y
01/05/2017 Y


And I want to see the datediff between dates that have Y under column
use
.

So the result when joining table 1 and 2 should pull:

ID diff
001 3
002 2



  • Is that a case statement?

  • How would I use in the above example in table one? Would I join based on date?


Answer Source

I would think of something like this:

SELECT t1.id,
       t2.cnt as diff
FROM table1 t1 outer apply
     (select count(*) as cnt
      from table2 t2
      where t2.date >= t1.in_date and t2.date <= t1.out_date and t2.use = 'Y'
     ) t2;

That is count the matching days and dispense with the datediff().