June 20, 2013 at 7:22 am
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
June 20, 2013 at 7:39 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 20, 2013 at 8:22 am
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--
June 20, 2013 at 8:27 am
meltingchain (6/20/2013)
Sorry, here is the data.
--===== If the test table already exists, drop itIF 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
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 20, 2013 at 10:02 am
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
June 20, 2013 at 10:15 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 20, 2013 at 10:27 am
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.
June 20, 2013 at 12:57 pm
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply