kazzi kazzi - 5 months ago 26
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.


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

For example:

,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:


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

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().

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download