SELECT
    roster_instance.roster_id AS rosterId,
    cdt.cloud_date AS shiftDate,
    DATETIME(roster_instance.period_start) AS periodStart,
    DATETIME(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
                DATE(r.start_date, ((((CASE
            WHEN STRFTIME('%d', cd.cloud_date) < STRFTIME('%d', r.start_date)
            THEN CAST(STRFTIME('%Y', cd.cloud_date) AS INTEGER)* 12 + CAST(STRFTIME('%m', cd.cloud_date) AS INTEGER) -1 ELSE CAST(STRFTIME('%Y', cd.cloud_date) AS INTEGER)* 12 + CAST(STRFTIME('%m', cd.cloud_date) AS INTEGER)
            END) - (CAST(STRFTIME('%Y', r.start_date) AS INTEGER)* 12 + CAST(STRFTIME('%m', r.start_date) AS INTEGER))) / r.booking_period_value) * r.booking_period_value)|| ' MONTHS')
            WHEN(r.booking_period = 'WEEKLY') THEN
                   (DATE(r.start_date, (CAST((JULIANDAY(cd.cloud_date) - JULIANDAY(r.start_date)) / (7 * r.booking_period_value) AS INTEGER) * (7 * r.booking_period_value)) || ' DAYS'))
            WHEN(r.booking_period = 'DAILY') THEN
                   (DATE(r.start_date, (CAST((JULIANDAY(cd.cloud_date) - JULIANDAY(r.start_date)) / (r.booking_period_value) AS INTEGER) * (r.booking_period_value)) || ' DAYS'))
            END AS period_start,

            CASE
            WHEN(r.booking_period = 'MONTHLY') THEN
            DATE(r.start_date, ((((CASE
            WHEN STRFTIME('%d', cd.cloud_date) < STRFTIME('%d', r.start_date)
            THEN CAST(STRFTIME('%Y', cd.cloud_date) AS INTEGER)* 12 + CAST(STRFTIME('%m', cd.cloud_date) AS INTEGER) -1 ELSE CAST(STRFTIME('%Y', cd.cloud_date) AS INTEGER)* 12 + CAST(STRFTIME('%m', cd.cloud_date) AS INTEGER)
            END) - (CAST(STRFTIME('%Y', r.start_date) AS INTEGER)* 12 + CAST(STRFTIME('%m', r.start_date) AS INTEGER))) / r.booking_period_value) * r.booking_period_value + r.booking_period_value) || ' MONTHS')
            WHEN(r.booking_period = 'WEEKLY') THEN
                   (DATE(r.start_date, (CAST((JULIANDAY(cd.cloud_date) - JULIANDAY(r.start_date)) / (7 * r.booking_period_value) AS INTEGER) * (7 * r.booking_period_value) + (7 * r.booking_period_value)) || ' DAYS'))
            WHEN(r.booking_period = 'DAILY') THEN
                   (DATE(r.start_date, (CAST((JULIANDAY(cd.cloud_date) - JULIANDAY(r.start_date)) / (r.booking_period_value) AS INTEGER) * (r.booking_period_value) + (r.booking_period_value)) || ' DAYS'))
            END AS period_end,

            CASE
            WHEN(r.booking_period = 'MONTHLY' ) THEN
                   ((((CASE
            WHEN STRFTIME('%d', cd.cloud_date) < STRFTIME('%d', r.start_date)
            THEN CAST(STRFTIME('%Y', cd.cloud_date) AS INTEGER)* 12 + CAST(STRFTIME('%m', cd.cloud_date) AS INTEGER) -1
            ELSE CAST(STRFTIME('%Y', cd.cloud_date) AS INTEGER)* 12 + CAST(STRFTIME('%m', cd.cloud_date) AS INTEGER)
            END) - (CAST(STRFTIME('%Y', r.start_date) AS INTEGER)* 12 + CAST(STRFTIME('%m', r.start_date) AS INTEGER))) / r.booking_period_value) )
            WHEN(r.booking_period = 'WEEKLY') THEN
                   (CAST((JULIANDAY(cd.cloud_date) - JULIANDAY(r.start_date)) / (7 * r.booking_period_value) AS INTEGER))
            WHEN(r.booking_period = 'DAILY') THEN
                   (CAST((JULIANDAY(cd.cloud_date) - JULIANDAY(r.start_date)) / (r.booking_period_value) AS INTEGER))
            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
                          ROUND(JULIANDAY(cdt.cloud_date) - JULIANDAY( DATE(roster_instance.start_date, ((((CASE
	                		 			WHEN STRFTIME('%d', cdt.cloud_date) < STRFTIME('%d', roster_instance.start_date)
	                		 			THEN CAST(STRFTIME('%Y', cdt.cloud_date) AS INTEGER)* 12 + CAST(STRFTIME('%m', cdt.cloud_date) AS INTEGER) -1 ELSE CAST(STRFTIME('%Y', cdt.cloud_date) AS INTEGER)* 12 + CAST(STRFTIME('%m', cdt.cloud_date) AS INTEGER)
										 END) - (CAST(STRFTIME('%Y', roster_instance.start_date) AS INTEGER)* 12 + CAST(STRFTIME('%m', roster_instance.start_date) AS INTEGER))) / roster_instance.repetition) * roster_instance.repetition)|| ' MONTHS')))
                      WHEN(roster_instance.roster_type = 'WEEKLY' ) THEN
                          ROUND(JULIANDAY(cdt.cloud_date) - JULIANDAY(DATE(roster_instance.start_date, (CAST((JULIANDAY(cdt.cloud_date) - JULIANDAY(roster_instance.start_date)) / (7 * roster_instance.repetition) AS INTEGER) * (7 * roster_instance.repetition) ) || ' DAYS')))
                      WHEN(roster_instance.roster_type = 'DAILY' ) THEN
                          ROUND(JULIANDAY(cdt.cloud_date) - JULIANDAY(DATE(roster_instance.start_date, (CAST((JULIANDAY(cdt.cloud_date) - JULIANDAY(roster_instance.start_date)) / (roster_instance.repetition) AS INTEGER) * (roster_instance.repetition) ) || ' DAYS')))
                    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