SELECT
    roster_instance.roster_id AS rosterId,
    cdt.cloud_date AS shiftDate,
    roster_instance.period_start AS periodStart,
    roster_instance.period_end AS periodEnd,
    roster_instance.period_index AS periodIndex,

    ri.roster_item_id AS rosterItemId,
    ri.slot_time AS slotTime,
    ri.slot_occupancy AS slotOccupancy,
    ri_cd.data_json AS rosterItemCustomData,

    pl.location_code AS locationCode,
    pl.name AS locationName,
    pl.description AS locationDescription,
    pl.latitude AS latitude,
    pl.longitude AS longitude,
    pl.timezone AS timezone,

    t.name AS shiftName,
    t.description AS shiftDescription,
    t.start_time_seconds AS startTimeSeconds,
    t.end_time_seconds AS endTimeSeconds,
    t.break_time_list AS breakTime

FROM
    (
        SELECT
            DISTINCT
            CASE
			WHEN(r.booking_period = 'MONTHLY' ) THEN DATEADD(MONTH,((DATEDIFF( MONTH, r.start_date, cd.cloud_date ) / (r.booking_period_value))* r.booking_period_value ) , r.start_date )
			WHEN (r.booking_period = 'WEEKLY') THEN DATEADD(WEEK, ((DATEDIFF( WEEK , r.start_date , cd.cloud_date) / (r.booking_period_value))*(r.booking_period_value)) , r.start_date )
			WHEN (r.booking_period = 'DAILY') THEN DATEADD(DAY, ((DATEDIFF( DAY, r.start_date , cd.cloud_date) / (r.booking_period_value))*(r.booking_period_value)), r.start_date )
                END AS period_start,

            CASE
			WHEN(r.booking_period = 'MONTHLY' ) THEN DATEADD(MONTH , ((DATEDIFF( MONTH, r.start_date, cd.cloud_date ) / (r.booking_period_value))* r.booking_period_value ) + booking_period_value , r.start_date)
			WHEN (r.booking_period = 'WEEKLY') THEN DATEADD(WEEK, ((DATEDIFF( WEEK , r.start_date , cd.cloud_date) / (r.booking_period_value))*(r.booking_period_value)) + booking_period_value , r.start_date)
			WHEN (r.booking_period = 'DAILY') THEN DATEADD(DAY, ((DATEDIFF( DAY, r.start_date , cd.cloud_date) / (r.booking_period_value))*(r.booking_period_value)) + booking_period_value , r.start_date)
                END AS period_end,

            CASE
			WHEN(r.booking_period = 'MONTHLY' ) THEN DATEDIFF( MONTH, r.start_date, cd.cloud_date ) / (r.booking_period_value)
			WHEN (r.booking_period = 'WEEKLY') THEN DATEDIFF( WEEK , r.start_date , cd.cloud_date) / (r.booking_period_value)
			WHEN (r.booking_period = 'DAILY') THEN DATEDIFF( DAY, r.start_date , cd.cloud_date) / (r.booking_period_value)
                END AS period_index,
            r.*
        FROM
            roster r
                CROSS JOIN cloud_date_table cd
                LEFT JOIN visit_scenario_config vsc ON
                    r.id = vsc.id
        WHERE
            cd.cloud_date BETWEEN :fromparam AND :toparam
          AND r.deleted_date IS NULL
          AND r.start_date <= cd.cloud_date
          AND (r.end_date IS NULL OR r.end_date >= cd.cloud_date)
          AND r.customer_id = :customerparam
          AND r.roster_creator = 'VISIT_JOB'

          :ROSTER_FILTER
          :KIOSK_FILTER
          :PUBLIC_FILTER

          AND r.start_date <= :toparam
          AND (r.end_date IS NULL
            OR r.end_date >= :fromparam)
    ) AS roster_instance
        INNER JOIN cloud_date_table cdt ON
                cdt.cloud_date >= roster_instance.period_start
            AND cdt.cloud_date < roster_instance.period_end
        INNER JOIN roster_item ri ON
                ri.roster_id = roster_instance.roster_id
            AND ri.deleted_date IS NULL
            AND
                ( CASE
		WHEN(roster_instance.roster_type = 'MONTHLY' ) THEN DATEDIFF(MONTH, cdt.cloud_date, DATEADD(MONTH, ((DATEDIFF( MONTH, roster_instance.start_date, cdt.cloud_date ) / (roster_instance.repetition))* roster_instance.repetition ) , roster_instance.start_date))
		WHEN (roster_instance.roster_type = 'WEEKLY') THEN DATEDIFF(WEEK, cdt.cloud_date, DATEADD(WEEK, ((DATEDIFF( WEEK , roster_instance.start_date , cdt.cloud_date) / (roster_instance.repetition))*(roster_instance.repetition)) , roster_instance.start_date))
		WHEN (roster_instance.roster_type = 'DAILY') THEN DATEDIFF(DAY, cdt.cloud_date, DATEADD(DAY , ((DATEDIFF( DAY, roster_instance.start_date , cdt.cloud_date) / (roster_instance.repetition))*(roster_instance.repetition)) , roster_instance.start_date))
                    END ) = ri.day_number
        INNER JOIN (
	SELECT
		ri.roster_item_id
	FROM
		roster_item ri
	LEFT JOIN rosteritem_person_group rpg ON
		ri.roster_item_id = rpg.roster_item_id
	LEFT JOIN user_group_membership ugm ON
		rpg.assigned_group_id = ugm.assigned_group_id
	WHERE
		ri.customer_id = :customerparam
        :EMPLOYEE_FILTER
		--	AND (ugm.[member] = :employee_id OR assigned_user_id = :employee_id)
	GROUP BY
		ri.roster_item_id

        ) AS employee_roster_item
        ON ri.roster_item_id = employee_roster_item.roster_item_id
        LEFT JOIN customdata ri_cd ON
            ri.custom_data_id = ri_cd.custom_data_id
        LEFT JOIN task t ON
            ri.task_id = t.task_id
        LEFT JOIN physical_locations pl ON
            ri.job_location_id = pl.id
        WHERE
        roster_instance.customer_id = :customerparam
        AND (roster_instance.end_date IS NULL OR roster_instance.end_date >= roster_instance.period_end)
        :BOOKING_FILTER
        :PERIOD_INDEX_FILTER
ORDER BY
	periodStart , shiftDate