SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


splitting up a start time and end time into different dates


splitting up a start time and end time into different dates

Author
Message
meltingchain
meltingchain
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 75
what i need to do is find the hours both in the AM and PM for each day. so lets say i have the start date 6/20/2013 8:00:00 end date 6/22/2013 17:00:00 i need the out put for each day in AM and PM hours. so the out put would look like:
Date-AM-PM
6/20/13 - 4 - 5
6/21/13 - 4 - 5
6/22/13 - 4 - 5

the problem i'm having is that each date that's read in will not always be the same. some times it will only be one day between, others times it will be three days each with different starting time.
currently if it one day i have been using:
DateDiff(hh,cast(Convert(varchar(2),start,108)as int),cast(Convert(varchar(2),12,108)as int)) to find the AM hours.

Any suggestions would be helpful, thank you
Sean Lange
Sean Lange
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66016 Visits: 17980
Hi and welcome to the forums. In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
meltingchain
meltingchain
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 75
Sorry, here is the data.

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--------Create test table----------
CREATE TABLE #mytable
(
DateStart DATETIME,
DateEnd DATETIME
)
---------Sample Data-------
Insert into #mytable (DateStart,DateEnd)
Select '2013-06-03 08:00:00.000', '2013-06-05 17:00:00.000' UNION ALL
Select '2013-06-07 14:00:00.000', '2013-06-07 15:00:00.000' UNION ALL
Select '2013-06-08 08:00:00.000', '2013-06-08 09:00:00.000' Union ALL
Select '2013-06-27 08:00:00.000', '2013-06-28 17:00:00.000'

--------What i Tried-----
select * from #mytable

select
Convert(date,DateStart)as DateStart,
SUM(case
when Convert(time,DateEnd) < '12:00' then dateDiff(hh,Convert(time,DateStart), Convert(time,DateEnd))
when Convert(time,DateStart) < '12:00' then dateDiff(hh,Convert(time,DateStart), Convert(time,'12:00'))
else 0
end) as AM,
SUM(case
when Convert(time,DateStart) > '12:00' then dateDiff(hh,Convert(time,DateStart), Convert(time,DateEnd))
When Convert(time,DateEnd) > '12:00' then dateDiff(hh,Convert(time,'12:00'), Convert(time,DateEnd))
else 0
end) as PM

from #myTable group by DateStart

---Problem: Does not take into accout mutiple days--


Kingston Dhasian
Kingston Dhasian
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6225 Visits: 5291
meltingchain (6/20/2013)
Sorry, here is the data.

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--------Create test table----------
CREATE TABLE #mytable
(
DateStart DATETIME,
DateEnd DATETIME
)
---------Sample Data-------
Insert into #mytable (DateStart,DateEnd)
Select '2013-06-03 08:00:00.000', '2013-06-05 17:00:00.000' UNION ALL
Select '2013-06-07 14:00:00.000', '2013-06-07 15:00:00.000' UNION ALL
Select '2013-06-08 08:00:00.000', '2013-06-08 09:00:00.000' Union ALL
Select '2013-06-27 08:00:00.000', '2013-06-28 17:00:00.000'

--------What i Tried-----
select * from #mytable

select
Convert(date,DateStart)as DateStart,
SUM(case
when Convert(time,DateEnd) < '12:00' then dateDiff(hh,Convert(time,DateStart), Convert(time,DateEnd))
when Convert(time,DateStart) < '12:00' then dateDiff(hh,Convert(time,DateStart), Convert(time,'12:00'))
else 0
end) as AM,
SUM(case
when Convert(time,DateStart) > '12:00' then dateDiff(hh,Convert(time,DateStart), Convert(time,DateEnd))
When Convert(time,DateEnd) > '12:00' then dateDiff(hh,Convert(time,'12:00'), Convert(time,DateEnd))
else 0
end) as PM

from #myTable group by DateStart

---Problem: Does not take into accout mutiple days--



Great job posting the sample data and the structure of the table:-)
Can you also provide the expected results based on your sample data
That will help us to test the solution ourselves before we post


Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
meltingchain
meltingchain
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 75
Kingston Dhasian (6/20/2013)
Great job posting the sample data and the structure of the table:-)
Can you also provide the expected results based on your sample data
That will help us to test the solution ourselves before we post


The What I Tried section comes close to how i want it to output. But notice the date 2013-06-03 is spanning 3 days. This isn't shown in that. Instead i need the output to look like the result from this table

create Table #tempOutput(datestart date, AM int, PM int)
insert Into #TempOutput (dateStart,AM,PM)

Select '2013-06-03',4,5 Union ALL --first day between 2013-06-03, 2013-06-05 hours split between AM and PM
Select '2013-06-04',4,5 Union ALL --Second day between 2013-06-03, 2013-06-05 hours split between AM and PM
Select '2013-06-05',4,5 Union ALL --Third day between 2013-06-03, 2013-06-05 hours split between AM and PM
Select '2013-06-07',0,1 Union ALL -- Time between 2013-06-07 14:00:00.000, 2013-06-07 15:00:00.000 hours split between AM and PM
Select '2013-06-08',1,0 Union ALL -- Time between 2013-06-08 08:00:00.000, 2013-06-08 09:00:00.000 hours split between AM and PM
Select '2013-06-27',4,5 Union ALL --first day between 2013-06-27, 2013-06-28 hours split between AM and PM
Select '2013-06-28',4,5 Union ALL --Second day between 2013-06-27, 2013-06-28 hours split between AM and PM


Sean Lange
Sean Lange
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66016 Visits: 17980
meltingchain (6/20/2013)
Kingston Dhasian (6/20/2013)
Great job posting the sample data and the structure of the table:-)
Can you also provide the expected results based on your sample data
That will help us to test the solution ourselves before we post


The What I Tried section comes close to how i want it to output. But notice the date 2013-06-03 is spanning 3 days. This isn't shown in that. Instead i need the output to look like the result from this table

create Table #tempOutput(datestart date, AM int, PM int)
insert Into #TempOutput (dateStart,AM,PM)

Select '2013-06-03',4,5 Union ALL --first day between 2013-06-03, 2013-06-05 hours split between AM and PM
Select '2013-06-04',4,5 Union ALL --Second day between 2013-06-03, 2013-06-05 hours split between AM and PM
Select '2013-06-05',4,5 Union ALL --Third day between 2013-06-03, 2013-06-05 hours split between AM and PM
Select '2013-06-07',0,1 Union ALL -- Time between 2013-06-07 14:00:00.000, 2013-06-07 15:00:00.000 hours split between AM and PM
Select '2013-06-08',1,0 Union ALL -- Time between 2013-06-08 08:00:00.000, 2013-06-08 09:00:00.000 hours split between AM and PM
Select '2013-06-27',4,5 Union ALL --first day between 2013-06-27, 2013-06-28 hours split between AM and PM
Select '2013-06-28',4,5 Union ALL --Second day between 2013-06-27, 2013-06-28 hours split between AM and PM



Are all days considered to only be between 8am and 5pm?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
meltingchain
meltingchain
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 75
Sean Lange (6/20/2013)
Are all days considered to only be between 8am and 5pm?


Not necessarily in the sample data i added times when it was from 8am to 9am or 2pm to 3pm.
But if the dates go past more than one day it is.
meltingchain
meltingchain
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 75
After a week of trying different things i finally got a idea that works.
creating a stored proc that would inserts new dates for each date that spans multiple days
declare @Maxdays int; --counter
select @Maxdays = Max(dateDiff(dd,dateStart,dateEnd)) from #mytable --find max of dates that span multiple days
while @Maxdays > 0 --while loop
begin
insert into #mytable(dateStart, dateEnd) -- inserts a new row that is plus one day of the start date if it spans multiple days
select Dateadd(day, 1,datestart), dateend
from #mytable
where Datediff (dd,Convert(date,DateStart), Convert(date,DateEnd)) =@Maxdays
update #mytable -- changes the date end so that its not found again in the insert part
set dateend=dateadd(d,-@Maxdays,dateend)
where Datediff (dd,Convert(date,DateStart), Convert(date,DateEnd)) = @Maxdays;
set @maxdays = @maxdays - 1 -- minus count
end



How ever i have never made a stored proc, if any one could help me create one that would be great.
Also if anyone has a different way, i would love to hear it. The less temp tables and stored procs the better
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search