select case when dst.dstCompanyType = 'P' and dst.fiscalStartMonth != 1 then dst.dstId when dst.dstCompanyType = 'B' and pdst.dstCompanyType = 'P' and pdst.fiscalStartMonth != 1 then pdst.dstId when dst.fiscalStartMonth != 1 then dst.dstId else 0 end calendarDstId from tbl_CDR_Distributors cdr with (nolock) left join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId left outer join tbl_DST_Distributors pdst with (nolock) on pdst.dstId = dst.dstParentCo where cdr.ownerId = and cdr.fsl_tablecode = and cdr.cdr_recordId = declare @today as datetime; declare @currentMonth datetime; declare @ownerId int; declare @ownerType varchar(3); set @ownerId = ; set @ownerType = ; set @currentMonth = ( select top 1 fiscalStartMonth from tbl_CRM_FiscalPeriods with (nolock) where ownerId = @ownerId and ownerType = @ownerType and periodType = 'M' and percentComplete > 0 order by fiscalStartMonth desc ); set @today = getDate(); set @today = ( select top 1 dateadd( day, round( datediff( day, startDate, endDate ) * percentComplete, 0 ), startDate ) today from tbl_CRM_FiscalPeriods with (nolock) where ownerId = @ownerId and ownerType = @ownerType and percentComplete <= 1 and percentComplete > 0 order by endDate desc ); if @today is null begin set @today = getDate(); end declare @thisYear int = Year( @today ); declare @nextYear int = Year( @today ) + 1; declare @lastYear int = Year( @today ) - 1; with Fy as ( select periodType, startDate, endDate, fiscalYear, periodIndex, fiscalStartMonth, fiscalEndMonth from tbl_CRM_FiscalPeriods with (nolock) where ownerId = @ownerId and ownerType = @ownerType and periodType = 'Y' ), Fp as ( select periodType, startDate, endDate, fiscalYear, periodIndex, fiscalStartMonth, fiscalEndMonth, periodName from tbl_CRM_FiscalPeriods with (nolock) where ownerId = @ownerId and ownerType = @ownerType and periodType in ( ) ), NpRaw as ( select periodType,fiscalYear, periodIndex, dateFromParts( fiscalYear, 1, 1 ) firstOfYear from tbl_CRM_FiscalPeriods with (nolock) where ownerId = @ownerId and ownerType = @ownerType -- and fiscalYear in ( , , ) and fiscalYear in ( @thisYear, @lastYear, @nextYear ) and periodType in ( ) ), Np as ( select fiscalYear, periodType, periodIndex, case when periodType = 'H' then dateadd( month, 6 * ( periodIndex - 1), firstOfYear ) when periodType = 'M' then dateadd( month, 1 * ( periodIndex - 1), firstOfYear ) when periodType = 'W' then dateadd( day, 7 * ( periodIndex - 1), dateadd( day, -1 * ( datepart( weekday, firstOfYear ) -1), firstOfYear ) ) when periodType = 'Y' then dateadd( year, 1 * ( periodIndex - 1), firstOfYear ) when periodType = 'Q' then dateadd( month, 3 * ( periodIndex - 1), firstOfYear ) when periodType = 'T' then dateadd( month, 4 * ( periodIndex - 1), firstOfYear ) else null end startDate, case when periodType = 'H' then dateadd( day, -1, dateadd( month, 6 * periodIndex, firstOfYear )) when periodType = 'M' then dateadd( day, -1, dateadd( month, 1 * periodIndex, firstOfYear )) when periodType = 'W' then dateadd( day, -1, dateadd( day, 7 * periodIndex, dateadd( day, -1 * ( datepart( weekday, firstOfYear ) -1), firstOfYear ) )) when periodType = 'Y' then dateadd( day, -1, dateadd( year, 1 * periodIndex, firstOfYear )) when periodType = 'Q' then dateadd( day, -1, dateadd( month, 3 * periodIndex, firstOfYear )) when periodType = 'T' then dateadd( day, -1, dateadd( month, 4 * periodIndex, firstOfYear )) else null end endDate, cast( null as datetime ) fiscalStartMonth, cast( null as datetime ) fiscalEndMonth from NpRaw ) , blank as ( select '' calendarType, '' rangeCode, startDate, endDate, fiscalStartMonth, fiscalEndMonth, '' as rangeDisplay, fiscalYear, periodType, periodIndex, 0 displayOrder from Fp as p where 1 = 2 ) , #_calendarType##_rangeCode# as ( select '#_calendarType#' calendarType, '#_rangeCode#' rangeCode, startDate, endDate, fiscalStartMonth, fiscalEndMonth, 'This #_rangeTypeDisplay#' as rangeDisplay, fiscalYear, periodType, periodIndex, #_displayOrder# displayOrder from #_calendarType#p as p where periodType = '#_rangeType#' and @today between startDate and endDate ) ,#_calendarType##_rangeCode# as ( select top 1 '#_calendarType#' calendarType, '#_rangeCode#' rangeCode, p.startDate, p.endDate, p.fiscalStartMonth, p.fiscalEndMonth, 'Last #_rangeTypeDisplay#' as rangeDisplay, fiscalYear, periodType, periodIndex, #_displayOrder# displayOrder from #_calendarType#p as p where p.periodType = '#_rangeType#' and p.endDate < ( select top 1 startDate from #_calendarType#T#_rangeType# ) order by p.endDate desc ) ,#_calendarType##_rangeCode# as ( select top 1 '#_calendarType#' calendarType, '#_rangeCode#' rangeCode, p.startDate, p.endDate, p.fiscalStartMonth, p.fiscalEndMonth, 'Next #_rangeTypeDisplay#' as rangeDisplay, fiscalYear, periodType, periodIndex, #_displayOrder# displayOrder from #_calendarType#p as p where p.periodType = '#_rangeType#' and p.startDate > ( select top 1 endDate from #_calendarType#T#_rangeType# ) order by p.startDate ) , #_rangeCode# as ( select 'F' calendarType, '#_fiscalSelect##_periodType#' + cast( periodIndex as varchar) rangeCode, startDate, endDate, fiscalStartMonth, fiscalEndMonth, periodName as rangeDisplay, fiscalYear, periodType, periodIndex, #_displayOrder# + periodIndex as displayOrder from Fp as p where periodType = '#_periodType#' and fiscalYear = ( select fiscalYear from fy where @today between startDate and endDate ) ) , #_rangeCode# as ( select 'F' calendarType, '#_fiscalSelect##_periodType#' + cast( periodIndex as varchar) rangeCode, startDate, endDate, fiscalStartMonth, fiscalEndMonth, periodName as rangeDisplay, fiscalYear, periodType, periodIndex, #_displayOrder# + periodIndex as displayOrder from Fp as p where periodType = '#_periodType#' and fiscalYear = ( select fiscalYear - 1 from fy where @today between startDate and endDate ) ) , #_rangeCode# as ( select 'F' calendarType, '#_fiscalSelect##_periodType#' + cast( periodIndex as varchar) rangeCode, startDate, endDate, fiscalStartMonth, fiscalEndMonth, periodName as rangeDisplay, fiscalYear, periodType, periodIndex, #_displayOrder# + periodIndex as displayOrder from Fp as p where periodType = '#_periodType#' and fiscalYear = ( select fiscalYear + 1 from fy where @today between startDate and endDate ) ) -- including specialRanges #attributes.datastore.objectParams.includeSpecialRanges# -- rangeCode = '#_specialRangeCode#' , currentYear as ( select 'TY' rangeCode, 'This Year' rangeDisplay, startDate, endDate, fiscalStartMonth, fiscalEndMonth, fiscalYear from tbl_CRM_FiscalPeriods with (nolock) where periodType = 'Y' and ownerId = @ownerId and ownerType =@ownerType and @currentMonth between fiscalStartMonth and fiscalEndMonth ) , tyli as ( select 'F' calendarType, 'TYLI' rangeCode, 'Year to Last Invoice' rangeDisplay, ty.startDate, fp.endDate, ty.fiscalStartMonth, fp.fiscalEndMonth, fp.fiscalYear, null periodType, null as periodIndex, -302 displayOrder from tbl_CRM_fiscalPeriods fp with (nolock) inner join currentYear ty with (nolock) on ty.fiscalYear = fp.fiscalYear where fp.periodType = 'M' and fp.fiscalStartMonth = @currentMonth and fp.ownerId = @ownerId and fp.ownerType = @ownerType ) ,currentYear2 as ( select 'TY' rangeCode, 'This Year' rangeDisplay, startDate, endDate, fiscalStartMonth, fiscalEndMonth, fiscalYear from tbl_CRM_FiscalPeriods with (nolock) where periodType = 'Y' and ownerId = @ownerId and ownerType =@ownerType and dateadd( month, -1, @currentMonth ) between fiscalStartMonth and fiscalEndMonth ) ,tylm as ( select 'F' calendarType, 'TYLM' rangeCode, 'Year to Last Month' rangeDisplay, ty.startDate, fp.endDate, ty.fiscalStartMonth, fp.fiscalEndMonth, fp.fiscalYear, null periodType, null as periodIndex, -301 displayOrder from tbl_CRM_fiscalPeriods fp with (nolock) inner join currentYear2 as ty with (nolock) on ty.fiscalYear = fp.fiscalYear where fp.periodType = 'M' and fp.fiscalStartMonth = dateadd( month, -1, @currentMonth ) and fp.ownerId = @ownerId and fp.ownerType = @ownerType ) ,allPeriods as ( select 'F' calendarType, upper( format( fiscalStartMonth, 'MMMyyyy' )) rangeCode, fp.periodName rangeDisplay, fp.startDate, fp.endDate, fp.fiscalStartMonth, fp.fiscalEndMonth, fp.fiscalYear, fp.periodType, fp.periodIndex, ( row_number() over ( order by fp.fiscalStartMonth ) * -1 ) -200 displayOrder from tbl_CRM_fiscalPeriods fp with (nolock) where fp.ownerId = @ownerId and fp.ownerType = @ownerType and fp.periodType = 'M' and fp.startDate <= getDate() and fp.fiscalStartMonth >= ) , rollingMonths as ( select 'F' calendarType, 'R' + cast( datediff( month, sp.fiscalStartMonth, bp.fiscalStartMonth ) + 1 as varchar ) as rangeCode, 'Last ' + cast( datediff( month, sp.fiscalStartMonth, bp.fiscalStartMonth ) + 1 as varchar ) + ' Months' rangeDisplay, sp.startDate, bp.endDate, sp.fiscalStartMonth, bp.fiscalEndMonth, bp.fiscalYear, bp.periodType, null periodIndex, ( row_number() over( order by sp.fiscalStartMonth desc ) * -1 ) -100 displayOrder from tbl_CRM_FiscalPeriods bp with (nolock) inner join tbl_CRM_FiscalPeriods sp with (nolock) on sp.periodType= bp.periodType and sp.ownerId = bp.ownerId and sp.ownerType = bp.ownerType and datediff( month, sp.fiscalStartMonth, bp.fiscalStartMonth ) in ( 1, 2, 3, 5, 8, 11 ) where bp.ownerId = @ownerId and bp.ownerType = @ownerType and bp.periodType = 'M' and bp.fiscalStartMonth = dateadd( month, -1, @currentMonth ) ) , allRanges as ( union all select calendarType + ',' + rangeCode as rangeKey, case when left( rangeCode, 1 ) in ( 'T', 'L', 'N' ) then left( rangeCode, 1 ) else null end as rangeRelationship, case when fiscalStartMonth is not null then datediff( month, fiscalStartMonth, fiscalEndMonth ) + 1 else 0 end fiscalMonths, calendarType, rangeCode, startDate, endDate, fiscalStartMonth, fiscalEndMonth, rangeDisplay, fiscalYear, periodType, periodIndex, displayOrder from #_rangeCode# ) #forJSON( 'allRanges', attributes.dataStore, 'displayOrder' )#