Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

splitting up a start time and end time into different dates Expand / Collapse
Author
Message
Posted Thursday, June 20, 2013 7:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 10, 2014 7:33 AM
Points: 14, 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
Post #1465684
Posted Thursday, June 20, 2013 7:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 13,296, Visits: 12,147
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 Moden's 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)
Post #1465699
Posted Thursday, June 20, 2013 8:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 10, 2014 7:33 AM
Points: 14, 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--

Post #1465730
Posted Thursday, June 20, 2013 8:27 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:13 AM
Points: 2,631, Visits: 4,723
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/
Post #1465732
Posted Thursday, June 20, 2013 10:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 10, 2014 7:33 AM
Points: 14, 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

Post #1465806
Posted Thursday, June 20, 2013 10:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 13,296, Visits: 12,147
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 Moden's 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)
Post #1465818
Posted Thursday, June 20, 2013 10:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 10, 2014 7:33 AM
Points: 14, 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.
Post #1465830
Posted Thursday, June 20, 2013 12:57 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 10, 2014 7:33 AM
Points: 14, 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
Post #1465903
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse