SQL Care_Days

-- QA Placement_mobility
select  fiscal_yr,sum(care_days),sum(placement_moves)
 from base.placement_care_days_mobility where age_yrs_exit=-99 and age_yrs_removal=-99 and cd_race=0 and county_cd=0 and exclude_7day=0 and exclude_trh=0 
group by fiscal_yr
 order by fiscal_yr


 	select fy_start_date
	 ,sum(DATEDIFF(d
         , iif(  begin_date < fy_start_date, fy_start_date , begin_date  )
--  if end_date > discharge_force_18 use discharge_force_18 else end_date
         , iif( isnull(IIF(end_date>( iif(dbo.lessorDate( [18bday],rp.discharge_dt) > cutoff_date
				, cutoff_date
				,dbo.lessorDate( [18bday],rp.discharge_dt))),( iif(dbo.lessorDate( [18bday],rp.discharge_dt) > cutoff_date
				, cutoff_date
				,dbo.lessorDate( [18bday],rp.discharge_dt))),end_date) , '9999-01-01')  > fy_stop_date , fy_stop_date 
		 , IIF(end_date>( iif(dbo.lessorDate( [18bday],rp.discharge_dt) > cutoff_date
				, cutoff_date
				,dbo.lessorDate( [18bday],rp.discharge_dt))),( iif(dbo.lessorDate( [18bday],rp.discharge_dt) > cutoff_date
				, cutoff_date
				,dbo.lessorDate( [18bday],rp.discharge_dt))),end_date))
           ) + 1 ) 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 ca_ods.base.rptPlacement_Events rp 
					on removal_dt <= cd.fy_stop_date
			and  iif(dbo.lessorDate( [18bday],rp.discharge_dt) > cutoff_date
				, cutoff_date
				,dbo.lessorDate( [18bday],rp.discharge_dt))  >=cd.fy_start_date
			and rp.begin_date<= cd.fy_stop_date
			and coalesce(rp.end_date,'12/31/3999') >=cd.fy_start_date
			and rp.begin_date< =( iif(dbo.lessorDate( [18bday],rp.discharge_dt) > cutoff_date
				, cutoff_date
				,dbo.lessorDate( [18bday],rp.discharge_dt)) )
			--where dbo.fnc_datediff_yrs(birthdate,removal_dt) = 0
		--	and rp.derived_county=17
group 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'

--  Method 1 Calculate the care days in your period by adjusting the removal_dt and the discharge_dt 
--  to fit  your time  frame and calculating date difference between the adjusted removal_dt 
--  and adjusted discharge_dt

  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