Number of days patient was on ventilator



declare @datetable table (dateval date);
declare @cur date = '20120101';
while @cur < '20140101'
begin
insert @datetable values (@cur);
set @cur = dateadd(day, 1, @cur);
end
declare @temp table (patent_id int, visit_id int, [name of PR] nvarchar(100),ventilator_StartDate datetime, ventilator_StopDate datetime)
insert into @temp values(1,100,'Adult Ventilator',getdate()-10,getdate()-8)
insert into @temp values(1,100,'Adult Ventilator',getdate()-8,getdate()-8)
insert into @temp values(3,100,'Adult Ventilator','02/05/2013','02/11/2013')
insert into @temp values(3,100,'Adult Ventilator','02/11/2013','02/11/2013')
insert into @temp values(4,100,'Adult Ventilator','01/18/2013','01/20/2013')
insert into @temp values(4,100,'Adult Ventilator','01/20/2013','01/23/2013')
------------------------
select patent_id, count( Distinct d.dateval) as numRows
from @datetable d
inner join 
@temp as t
on d.dateval between ventilator_StartDate and ventilator_StopDate
group by patent_id

This version deals with non-contiguous start and stop dates.

---

Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com