quote:

---

Originally posted by wldodds

I have a table called Appointments. This table has the following fields:
AppointmentID, AppointmentStart, AppointmentEnd, Type

The Appointment ID is a randomly generated unique number, out of my control, the start and end are simply date/time fields and the type is a text field which for this purpose is storing the text 'Shift Start' and 'Shift Stop'.

Data in the table looks like this:

AppointmentID, ProviderId, AppointmentStart, AppointmentEnd, Type
ZZZZ0023489, ZZZ0001, 2013-05-28 07:00:00.000, 2013-05-28 07:30:00.000, Shift Start
ZZZZ0013479, ZZZ0001, 2013-05-28 11:30:00.000, 2013-05-28 12:00:00.000, Shift Stop
ZZZZ00N3209, ZZZ0001, 2013-05-28 13:30:00.000, 2013-05-28 14:00:00.000, Shift Start
ZZZZ00B9023, ZZZ0001, 2013-05-28 16:00:00.000, 2013-05-28 16:30:00.000, Shift Stop

I'm trying to get the Start and Stop time onto a single row if only 1 start and stop exist for that day and provider but if they have a morning shift and an afternoon shift then I want 2 rows, 1 for each shift basically.

I can't assume morning and afternoon shifts as the shift start and stops could occur at anytime of day and the provider could have 1 or more per day.

Originally I was thinking just using the min(AppointmentStart) and Max(AppointmentEnd) but that won't work if the provider has more than 1 shift in the day.

Any ideas?

---

;WITH cte AS
(
SELECT
	*,
	ROW_NUMBER() OVER (PARTITION BY ProviderID, CAST(AppointmentSTart AS DATE) ORDER BY AppointmentStart) AS RN
FROM
	Appointments
)
SELECT
	a.*,
	b.AppointmentStart,
	b.AppointmentEnd
FROM
	cte a
	LEFT JOIN cte b ON a.RN+1 = b.RN
WHERE
	a.RN%2 = 0;

quote:

---

Originally posted by wldodds

James,
Yes both a start and end time exist unfortunately it is a poorly written scheduling method to attempt to determine a persons actual shift based on booking an 15 minute apt at the beginning of the shift and a 15 min apt at the end of the shift. In this case I want to grab the end time of the start shift and the start time of the stop shift.

Lamprey,

the result set would look something like this:

Provider_Id, Date, ShiftStart, ShiftStop, Duration
ZZZ0001, 5/28/2013, 7:30, 11:30, 240
ZZZ0001, 5/28/2013, 14:00, 16:00, 120

I can get the date and duration just can't figure out how to get the start and stop values on the same row to allow me to create the calculation.

I'm going to try working with James example

---