SQL Care_Days

-- QA Placement_mobility (source table rptPlacement_Events)
	select fy_start_date
	,sum(datediff(dd
			--begin_date
				,iif(  begin_date  < fy_start_date	, fy_start_date, begin_date )
			-- end_date
			 , iif( 
						IIF(end_date > -- lessor of cutoff_date,18bday,discharge_date  
													iif(dbo.lessorDate( [18bday],rp.discharge_dt) > cutoff_date
																	, cutoff_date
																	,dbo.lessorDate( [18bday],rp.discharge_dt))
				-- use lessor of cutoff_date,18bday,discharge_date 
						, iif(dbo.lessorDate( [18bday],rp.discharge_dt) > cutoff_date
						, cutoff_date
						,dbo.lessorDate( [18bday],rp.discharge_dt))
				--else luse end_date 
				,end_date)
						> fy_stop_date
						-- use stop_date
						, fy_stop_date 
						-- else use derived end_date
						, (IIF(end_date > -- lessor of cutoff_date,18bday,discharge_date  
													iif(dbo.lessorDate( [18bday],rp.discharge_dt) > cutoff_date
																	, cutoff_date
																	,dbo.lessorDate( [18bday],rp.discharge_dt))
				-- use lessor of cutoff_date,18bday,discharge_date 
						, iif(dbo.lessorDate( [18bday],rp.discharge_dt) > cutoff_date
						, cutoff_date
						,dbo.lessorDate( [18bday],rp.discharge_dt))
				--else luse end_date 
				,end_date))   )))
            n_care_days
		,sum(iif(plcmnt_seq > 1 and begin_date between fy_start_date and fy_stop_date,1,0)) placement_moves
	from (select  distinct state_fiscal_yyyy sfy
							,min(ID_CALENDAR_DIM)  over (partition by state_fiscal_yyyy order by ID_CALENDAR_DIM) fy_start_date_int
							,max(ID_CALENDAR_DIM)  over (partition by state_fiscal_yyyy order by ID_CALENDAR_DIM asc RANGE between current row and UNBOUNDED FOLLOWING) fy_stop_date_int
							,min(calendar_date)  over (partition by state_fiscal_yyyy order by calendar_date)  fy_start_date
							,max(calendar_date)  over (partition by state_fiscal_yyyy order by calendar_date asc RANGE between current row and UNBOUNDED FOLLOWING)   fy_stop_date 
						from ca_ods.dbo.calendar_dim 
						where state_fiscal_yyyy between 2008 and 2013
				) cd
join ref_last_dw_transfer dw on dw.cutoff_date=dw.cutoff_date
join base.rptPlacement_Events rp 
		on removal_dt <= cd.fy_stop_date
--		 discharge_dt >=fy_start_date
		and (iif( -- lessor date of 18th birthday is greater than cutoff_date use cutoff else use lessor date 18 bday or discharge date
			IIF([18bday]<rp.discharge_dt,[18bday],rp.discharge_dt) > cutoff_date
				, cutoff_date
				,IIF([18bday]<rp.discharge_dt,[18bday],rp.discharge_dt) )) >=fy_start_date	
			and rp.begin_date <= cd.fy_stop_date
			--  end_date >=cd.fy_start_date
			and (IIF(rp.end_date> -- discharg_frc_18
					iif(IIF([18bday]<rp.discharge_dt,[18bday],rp.discharge_dt)  > cutoff_date
						, cutoff_date
						,IIF([18bday]<rp.discharge_dt,[18bday],rp.discharge_dt) ) 
				--   choose discharg_frc_18
				,iif(IIF([18bday]<rp.discharge_dt,[18bday],rp.discharge_dt)  > cutoff_date
						, cutoff_date
						,IIF([18bday]<rp.discharge_dt,[18bday],rp.discharge_dt) ) 
				,rp.end_date) ) >= cd.fy_start_date
where rp.cd_epsd_type <> 5  
-- and placement begin date less than cutoff_date
and begin_date <= 
				iif( -- lessor of 18th birthday , discharge date,cutoff_date
						IIF([18bday]<rp.discharge_dt,[18bday],rp.discharge_dt) > cutoff_date
							, cutoff_date
							, IIF([18bday]<rp.discharge_dt,[18bday],rp.discharge_dt)) 
group by fy_start_date
order by fy_start_date

-- coded originally by Gregor & Erik
-- define your start & stop period
  declare @care_day_start  date = '2007-02-05'
  declare @care_day_end    date = '2007-03-06'

--FROM rptPlacement
  SELECT @care_day_start,@care_day_end,sum(n_care_days)
  FROM(SELECT iif( removal_dt < @care_day_start , @care_day_start , removal_dt )  care_start
  , iif(isnull(discharge_dt, '9999-01-01') > @care_day_end , @care_day_end , discharge_dt) care_end
  , DATEDIFF(d
         , iif( removal_dt < @care_day_start, @care_day_start , removal_dt )
         , iif( isnull(discharge_dt, '9999-01-01') > @care_day_end , @care_day_end , discharge_dt )
           ) + 1 n_care_days
  FROM [CA_ODS].[base].[rptPlacement]
  WHERE removal_dt <= @care_day_end and isnull(discharge_dt, '9999-01-01') >= @care_day_start) AS test