SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL Table Min/Max DateTime for same date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wldodds
Starting Member

19 Posts

Posted - 10/14/2013 :  14:54:56  Show Profile  Reply with Quote
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?




James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 10/14/2013 :  16:41:53  Show Profile  Reply with Quote
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?






I must admit that I am a bit confused by the data - each row seems to have a start and an end time; are they different from shift start and shift stop end times? Or is it that the start and end are ranges rather than precise times?

In any case, give this a try - I am making a few assumptions here, which if not true can cause the code to break:
;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;
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4354 Posts

Posted - 10/14/2013 :  17:37:28  Show Profile  Reply with Quote
Given your sample data, what do want/expect for output?
Go to Top of Page

wldodds
Starting Member

19 Posts

Posted - 10/14/2013 :  17:43:03  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/15/2013 :  01:13:09  Show Profile  Reply with Quote
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


Sorry not quite clear how you ended up with above values for start and stop dates.
Why 11:30 as end value where you've record with end time as 12:00?
Also why Afternoon shift starts from 14:00? Why not 13:30 which is start date value in sample data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000