February 27, 2017 at 3:31 am
hi
I have the following session columns
AM
PM
DAY
AM PM slots up as up to each 3:30
Day slot up 8:00
I wish to however if someone has booked a day slot, split this time between the AM and PM slots
please help
February 27, 2017 at 4:06 am
Can you please provide some sample data and your expected output? I don't understand your goals here. Please provide it in the format I gave you in your other post, so that it is consumable (see my the link in my signature).
Many thanks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 27, 2017 at 4:56 am
Hi
data like this
CustomerID Department Session Date Time Start End Time (used for time left session count)
1 softplay AM 01/01/2001 09:00 10:00 60
2 softplay PM 01/01/2001 13:00 15:00 120
3 softplay DAY 02/01/2001 09:00 17:00 480
BUT AS
CustomerID Department Session Date Time Start End Time
1 softplay AM 01/01/2001 09:00 10:00 60
2 softplay PM 01/01/2001 13:00 15:00 120
3 softplay AM 02/01/2001 09:00 13:00 240
3 softplay PM 02/01/2001 13:00 17:00 240
dividing customer 3, between the am and pm session
Please help
February 27, 2017 at 6:27 am
Hi
I don't understand sorry..this is an example of the data I am using
I am trying to split customer id 3, time between am and pm, and then get rid of the day column.
the time is there, as I use the minutes column to work out how much time of a session is left.
hope this helps please
February 27, 2017 at 6:51 am
joanna.seldon - Monday, February 27, 2017 6:27 AMHiI don't understand sorry..this is an example of the data I am using
I am trying to split customer id 3, time between am and pm, and then get rid of the day column.
the time is there, as I use the minutes column to work out how much time of a session is left.
hope this helps please
Have a look here at how I provided your sample data in your other question. Details on how to (similarly) achieve this are in the link my signature. it's important to provide data with DDL and consumable sample data, when asking questions, as we know exactly what to expect from your data. It also means you'll get an answer a lot quicker, as other users on the forum don't have to build your data themselves. Don't forget, we don't have access to your data, and a copy & paste from SSMS/Excel/etc doesn't help us use it in SQL, thus we need it in a consumable format.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 27, 2017 at 8:55 am
Without DDL, this should work:WITH DaySplit AS (
SELECT * FROM (
VALUES ('AM', '09:00', '13:00'),
('PM', '13:00', '17:00')
) AS a ([Session], StartTime, EndTime)
)
SELECT MT.CustomerID,
MT.Department,
ISNULL(DS.[Session], MT.[Session]) AS [Session],
MT.Date,
ISNULL(DS.StartTime, MT.StartTime) AS StartTime,
ISNULL(DS.EndTime, MT.EndTime) AS EndTime,
MT.YourOtherUnnamedColumn
FROM MyTable MT
LEFT JOIN DaySplit DS ON MT.[Session] = 'Day';
If it doesn't, please do give consumable data, thanks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply