SELECT
	sched.dt,
	sched.store,
	sched.empno,
	sched.labor_sched_num,
	wsc.schedule_code
FROM
	work_schedule_codes wsc,
	(
	SELECT
		lsd.start_shift::date AS dt,
		lsd.start_shift::time AS tm,
		ls.store,
		ls.empno,
		ls.labor_sched_num,
		emppos.regular_hours,
		emppos.regular_days
	FROM
		labor_schedule.store_info si,
		entities.employee_master emp,
		entities.employee_position_master emppos,
		labor_schedule ls,
		(
			SELECT lsd.labor_sched_num, min(lsd.start_shift) AS start_shift
			FROM labor_schedule_details lsd,
				(
				SELECT labor_sched_num, shift_type, max(modified_on) AS modified_on
				FROM labor_schedule_details
				GROUP BY shift_type, labor_sched_num
				ORDER BY labor_sched_num, shift_type
				) lsd1
			WHERE lsd.labor_sched_num=lsd1.labor_sched_num AND
				lsd.shift_type=lsd1.shift_type AND
				lsd.modified_on=lsd1.modified_on AND
				lsd.is_deleted=false
			GROUP BY lsd.labor_sched_num
			ORDER BY lsd.labor_sched_num
		) lsd
	WHERE
		ls.labor_sched_num=lsd.labor_sched_num AND
		ls.empno=emp.employee_number AND
		emp.employee_position_code=emppos.employee_position_code AND
		si.emailid=ls.store AND
		si.cost_center_code=emp.primary_store AND
		ls.dt <= '2007-07-13'
	ORDER BY ls.labor_sched_num
	) sched
WHERE
	wsc.regular_hours = sched.regular_hours AND
	wsc.regular_days = sched.regular_days AND
	wsc.start_shift = sched.tm