sql - how to find out days from another table -

declare   @daystaken table( application varchar(20), statusid varchar(2), flag int, createddate datetime)  insert @daystaken (createddate)values ('2015-03-06 17:59:59.410'), ('2015-03-02 17:59:59.410') select datediff(dd,min(createddate),getdate())d @daystaken  declare   @holiday table(holiday datetime)  insert @holiday (  holiday )values (   '2014-04-06 17:59:59.410'), (   '2014-06-06 17:59:59.410'), (   '2015-05-05 17:59:59.410'), (   '2015-05-01 17:59:59.410'), (   '2013-01-06 17:59:59.410'), (   '2015-02-03 17:59:59.410'), (   '2011-02-01 17:59:59.410') 

i have got days count daystaken table need substract days holidays table.for example daystaken table have 122 days in days 3 days holidays till present date came holidays table.so need minus result , show 119 days

will do:

declare @mindate date select @mindate = min(createddate) @daystaken;  select datediff(day, @mindate, getdate()) - count(*) @holiday     cast(holiday date) >= @mindate     , cast(holiday date) <= cast(getdate() date) 

---

here 1 not make use of variable , can turned itvf:

with e1(n) as(     select 1 union select 1 union select 1 union select 1 union select 1 union     select 1 union select 1 union select 1 union select 1 union select 1 ), e2(n) as(select 1 e1 cross join e1 b), e4(n) as(select 1 e2 cross join e2 b), tally(n) as(     select top (datediff(day, @mindate, getdate()))         row_number() over(order (select null))     e4 ), ctemindate(mindate) as(     select cast(min(createddate) date) @daystaken ) select     count(*) tally t cross join ctemindate md     dateadd(day, n - 1, md.mindate) <= cast(getdate() date)     , not exists(         select 1         @holiday          cast(holiday date) = dateadd(day, n - 1, md.mindate)     )