SQL Care_Days

select fiscal_yr,sum(care_days),sum(placement_moves)
frombase.placement_care_days_mobilitywhere age_yrs_exit=-99and age_yrs_removal=-99and cd_race=0and county_cd=0and exclude_7day=0and exclude_trh=0group by fiscal_yr
order by fiscal_yr
-- 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 use 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 >1and begin_date between fy_start_date and fy_stop_date,1,0)) placement_moves
from (select distinct state_fiscal_yyyy sfy
,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 fromca_ods.dbo.calendar_dim where state_fiscal_yyyy between 2008and2013
) cd
, ref_last_dw_transfer dw
, base.rptPlacement_Events rp
where removal_dt <=fy_stop_date
and (selectmin(rtrim(ltrim(replace(arrValue,char(39),'') )))
fromdbo.fn_ReturnStrTableFromList(concat(char(39),
convert(varchar(10),dw.cutoff_date,121) , ',' ,
convert(varchar(10),rp.discharge_dt,121),',',
convert(varchar(10),[18bday],121),char(39)),1)) >=fy_start_date
and begin_date <= fy_stop_date
and (selectmin(rtrim(ltrim(replace(arrValue,char(39),''))) )
fromdbo.fn_ReturnStrTableFromList(concat(char(39),
convert(varchar(10),dw.cutoff_date,121) , ',' ,
convert(varchar(10),rp.discharge_dt,121),',',
convert(varchar(10),[18bday],121),',',
convert(varchar(10),end_date,121),char(39)),1)) >=fy_start_date
andrp.cd_epsd_type<>5-- and placement begin date less than cutoff_dateand begin_date <= (selectmin(rtrim(ltrim(replace(arrValue,char(39),'') )))
fromdbo.fn_ReturnStrTableFromList(concat(char(39),
convert(varchar(10),dw.cutoff_date,121) , ',' ,
convert(varchar(10),rp.discharge_dt,121),',',
convert(varchar(10),[18bday],121),char(39)),1))
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 rptPlacementSELECT @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