computing for the number of hours from 2 date attributes

Hi,

I want to build a calculated fields that compute for the number of hours between 2 date attributes.

let's say I have 2 dates:

1/1/2007 15:30

1/2/2007 9:10

It will give me 18 hours.

cherriesh

Try

VBA!DateDiff('h', DateValue1, DateValue2)

|||

Depend where you want ir and the importance for your project...

You can do that calculation inside a report, in a cube... or you can create it using SQL statment in a named calculation in teh datasourceview in Analysis Services...

helped?

Regards!

|||

Problem resolved?

regards!!

Computing Business Hours

I am attempting to compute Service Levels for an interaction based upon business hours. For example, an email arrives at 4pm and is handled the following day at 10am. Call Center Hours are 8-5.

Essentially I have a number of different alternatives, and have found some potential solutions, including:

www.dbforums.com/arch/7/2003/9/914261

However, my situation has a couple of additional twists to the standard 8hrs of business M-F. The call center is open different hours depending upon the day of the week. For example, 8-5 M, 10-7 T, 8-5 W Th F, 10-2 Sat, 10-12 Sun

Additionally, I would like to remove Holiday's from the calculation for service level as well.

I have explored a number of different table DTD's, but none seem to be a perfect fit for determining the number of "open" hours between when an interaction arrived, and when it was handled.

The DTD I have for the Holiday table is as follows:

CREATE Table Holidays (HolidayDate DateTime)
GO
Insert Into Holidays (HolidayDate) Values ('12-25-2004')

Please let me know what you feel would be the DTD for storing the business hours and also the query for extracting the number of Open hours between two dates

Thank you in advanceyour original question was

I am attempting to compute Service Levels for an interaction based upon business hours. For example, an email arrives at 4pm and is handled the following day at 10am.

Given your examples of business hours for Monday and Saturday what would your answer look like for the above question?

16 hours for Monday?
0 hours for Saturday?

What happens if one of these days is a holiday?|||Originally posted by Paul Young
your original question was

I am attempting to compute Service Levels for an interaction based upon business hours. For example, an email arrives at 4pm and is handled the following day at 10am.

Given your examples of business hours for Monday and Saturday what would your answer look like for the above question?

16 hours for Monday?
0 hours for Saturday?

What happens if one of these days is a holiday?

Based upon the previous example (8-5) the service level would be 3 hrs. Based upon the "flexible" (and actual scenario) it would have been 1 hour. Call center closed at 5pm and opened at 10am on Tuesday.

NET: I am trying to compute the number of hours the call center is open during a range of time.

Thanks|||okay, lets stick to Monday then...

if the call is received at 04:00 pm and the call center closes at 05:00pm then there was one hour on Monday that the call could have been worked. The call center opened on Tuesday at 10:00 am and the call was process during the first our hour so add an additional hour, that's two hours in total. I am missing something as I don't see three or one hours as the answer.

I am almost finished with an example but would like to present a close answer. could you help with the above question?|||Originally posted by Paul Young
okay, lets stick to Monday then...

if the call is received at 04:00 pm and the call center closes at 05:00pm then there was one hour on Monday that the call could have been worked. The call center opened on Tuesday at 10:00 am and the call was process during the first our hour so add an additional hour, that's two hours in total. I am missing something as I don't see three or one hours as the answer.

I am almost finished with an example but would like to present a close answer. could you help with the above question?

Sorry, the original question posed two scenarios:

Typical 8-5M-F
Reality (variable hours) I.E. Monday8a-5p , 10a-7p T, 8-5 W Th F, 10-2 Sat, 10-12 Sun.

Thus, the 3hrs versus 1hr.

Let's eliminate the "Typical" scenario, as I have examples of that. What I am struggling with is the reality (variable) open hours scenario, and how to best report SLA's when the center is open.

Sorry for the confuzzzzzion :)|||okay, still not sure I understand how you are calculating days but maybe you can use this:

declare @.HolidayCalendar table(Holiday datetime)
declare @.BusinessHours table(DOW tinyint, OpenAt smallint, CloseAt smallint)

declare @.dtFrom datetime
, @.dtTemp datetime
, @.dtTo datetime
, @.DOW tinyint
, @.HolidayCount tinyint
, @.Hours tinyint

insert into @.HolidayCalendar values('17-Mar-2004')
insert into @.BusinessHours values(1, 1000, 1200)
insert into @.BusinessHours values(2, 0800, 1700)
insert into @.BusinessHours values(3, 1000, 1900)
insert into @.BusinessHours values(4, 0800, 1700)
insert into @.BusinessHours values(5, 0800, 1700)
insert into @.BusinessHours values(6, 0800, 1700)
insert into @.BusinessHours values(7, 1000, 1400)

select @.dtFrom = '17-Mar-2004 12:00'
, @.dtTo = '18-Mar-2004 10:00'
, @.HolidayCount = 0
, @.Hours = 0

select ' Start', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'

--
-- If the problem was solved in one day then only need to calculate hours for a single day
--
if datediff(day,@.dtFrom,@.dtTo) = 0 begin
set @.Hours = datediff(hour,@.dtFrom,@.dtTo)
end else begin
--
-- Test for @.dtFrom being a holiday, whould need to move time to start of first business day after the holiday
--
if exists(select * from @.HolidayCalendar where convert(varchar(10),@.dtFrom,120) = Holiday) begin
while exists(select * from @.HolidayCalendar where convert(varchar(10),@.dtFrom,120) = Holiday) begin
--
-- Move @.dtFrom 1 day closer to a business day
--
set @.dtFrom = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
end
end

select ' @.dtFrom = Holiday', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Test for @.dtFrom being after business hours, would want to move to start of next day.
--
if exists(select * from @.BusinessHours where DOW = datepart(weekday,@.dtFrom) and datepart(hour,@.dtFrom) > (CloseAt/100)) begin
set @.dtFrom = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
end

select ' @.dtFrom after hours', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Test for @.dtFrom being before business hours, would want to move to start of this business day.
--
if exists(select * from @.BusinessHours where DOW = datepart(weekday,@.dtFrom) and datepart(hour,@.dtFrom) < (OpenAt/100)) begin
select @.dtFrom = dateadd(hour,(OpenAt/100),convert(varchar(10),@.dtFrom,120))
from @.BusinessHours
where DOW = datepart(weekday,@.dtFrom)
end

select ' @.dtFrom before hours', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Calculate the number of hours for the day the ploblem was submmited
-- provided the adjusted dates, @.dtFrom & @.dtTo are not on the same day
--
if datediff(day,@.dtFrom,@.dtTo) > 0 begin
set @.dtTemp = convert(varchar(10),@.dtFrom,120)
select @.Hours = datediff(hour,@.dtFrom,dateadd(hour,(CloseAt/100),@.dtTemp))
from @.BusinessHours
where DOW = datepart(weekday,@.dtFrom)
end
select ' Hours for 1st day', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Step through each day, if it is a holiday skip else add the number of business hours for that day
--
set @.dtTemp = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
while datediff(day,@.dtTemp,@.dtTo) > 0 begin
if not exists(select * from @.HolidayCalendar where Holiday = @.dtTemp) begin
select @.Hours = @.Hours + (CloseAt - OpenAt) / 100
from @.BusinessHours
where DOW = datepart(weekday,@.dtTemp)
end
set @.dtTemp = dateadd(day,1,@.dtTemp)
end

select 'Hours for days between', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Calculate the number of hours for the day the problem was resolved
--
set @.dtTemp = convert(varchar(10),@.dtTo,120)
select @.Hours = @.Hours + datediff(hour,dateadd(hour,(OpenAt/100),@.dtTemp),@.dtTo)
from @.BusinessHours
where DOW = datepart(weekday,@.dtTo)
select ' Hours for Lst day', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours'

end

select ' End', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'

this is NOT the optimal code but any changes made would depend on your implamintation. I hope this get you going in the right direction.|||THANK YOU SO MUCH! I haven't fully digested this yet, but at first glance it appears to be EXACTLY what I needed.

Thank you again.

Mike

Originally posted by Paul Young
okay, still not sure I understand how you are calculating days but maybe you can use this:

declare @.HolidayCalendar table(Holiday datetime)
declare @.BusinessHours table(DOW tinyint, OpenAt smallint, CloseAt smallint)

declare @.dtFrom datetime
, @.dtTemp datetime
, @.dtTo datetime
, @.DOW tinyint
, @.HolidayCount tinyint
, @.Hours tinyint

insert into @.HolidayCalendar values('17-Mar-2004')
insert into @.BusinessHours values(1, 1000, 1200)
insert into @.BusinessHours values(2, 0800, 1700)
insert into @.BusinessHours values(3, 1000, 1900)
insert into @.BusinessHours values(4, 0800, 1700)
insert into @.BusinessHours values(5, 0800, 1700)
insert into @.BusinessHours values(6, 0800, 1700)
insert into @.BusinessHours values(7, 1000, 1400)

select @.dtFrom = '17-Mar-2004 12:00'
, @.dtTo = '18-Mar-2004 10:00'
, @.HolidayCount = 0
, @.Hours = 0

select ' Start', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'

--
-- If the problem was solved in one day then only need to calculate hours for a single day
--
if datediff(day,@.dtFrom,@.dtTo) = 0 begin
set @.Hours = datediff(hour,@.dtFrom,@.dtTo)
end else begin
--
-- Test for @.dtFrom being a holiday, whould need to move time to start of first business day after the holiday
--
if exists(select * from @.HolidayCalendar where convert(varchar(10),@.dtFrom,120) = Holiday) begin
while exists(select * from @.HolidayCalendar where convert(varchar(10),@.dtFrom,120) = Holiday) begin
--
-- Move @.dtFrom 1 day closer to a business day
--
set @.dtFrom = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
end
end

select ' @.dtFrom = Holiday', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Test for @.dtFrom being after business hours, would want to move to start of next day.
--
if exists(select * from @.BusinessHours where DOW = datepart(weekday,@.dtFrom) and datepart(hour,@.dtFrom) > (CloseAt/100)) begin
set @.dtFrom = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
end

select ' @.dtFrom after hours', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Test for @.dtFrom being before business hours, would want to move to start of this business day.
--
if exists(select * from @.BusinessHours where DOW = datepart(weekday,@.dtFrom) and datepart(hour,@.dtFrom) < (OpenAt/100)) begin
select @.dtFrom = dateadd(hour,(OpenAt/100),convert(varchar(10),@.dtFrom,120))
from @.BusinessHours
where DOW = datepart(weekday,@.dtFrom)
end

select ' @.dtFrom before hours', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Calculate the number of hours for the day the ploblem was submmited
-- provided the adjusted dates, @.dtFrom & @.dtTo are not on the same day
--
if datediff(day,@.dtFrom,@.dtTo) > 0 begin
set @.dtTemp = convert(varchar(10),@.dtFrom,120)
select @.Hours = datediff(hour,@.dtFrom,dateadd(hour,(CloseAt/100),@.dtTemp))
from @.BusinessHours
where DOW = datepart(weekday,@.dtFrom)
end
select ' Hours for 1st day', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Step through each day, if it is a holiday skip else add the number of business hours for that day
--
set @.dtTemp = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
while datediff(day,@.dtTemp,@.dtTo) > 0 begin
if not exists(select * from @.HolidayCalendar where Holiday = @.dtTemp) begin
select @.Hours = @.Hours + (CloseAt - OpenAt) / 100
from @.BusinessHours
where DOW = datepart(weekday,@.dtTemp)
end
set @.dtTemp = dateadd(day,1,@.dtTemp)
end

select 'Hours for days between', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Calculate the number of hours for the day the problem was resolved
--
set @.dtTemp = convert(varchar(10),@.dtTo,120)
select @.Hours = @.Hours + datediff(hour,dateadd(hour,(OpenAt/100),@.dtTemp),@.dtTo)
from @.BusinessHours
where DOW = datepart(weekday,@.dtTo)
select ' Hours for Lst day', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours'

end

select ' End', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'

this is NOT the optimal code but any changes made would depend on your implamintation. I hope this get you going in the right direction.|||I tried to keep this overly simple and did not worry about efficiency. My goal was to get you thinking about the problem in a diffrent light. If you have a question about any section drop me a line or post it here.

Please post back with your final solution.|||Paul,

Thanks a million. I doubt I would have ever come up with the solution that you presented. I have tweeked it a little, and am posting the code change.

Biggest Delta, is I removed the same day logic. This was making the assumption that an interaction starting and ending the same day was the duration of the interaction. This would have "dinged" the call center in three instances. Interaction came in before start of day and handled during day. If for some reason the day was a Holiday. Or, if the interaction was handled after business hours. (That time after close, but before the workers go home).

Additionally, I converted the computations into Seconds.

Finally, I handled when the interaction was handled with NO open business hours. I.E. Came in after close and handled before open.

However, the logic you provided was invaluable.

Thanks agian

HERE'S THE CODE

--okay, still not sure I understand how you are calculating days but maybe you can use this:

--code:------------------------
declare @.HolidayCalendar table(Holiday datetime)
declare @.BusinessHours table(DOW tinyint, OpenAt smallint, CloseAt smallint)

declare @.dtFrom datetime
, @.dtTemp datetime
, @.dtTo datetime
, @.DOW tinyint
, @.HolidayCount tinyint
, @.Hours tinyint
, @.Seconds int

insert into @.HolidayCalendar values('17-Mar-2004')
insert into @.BusinessHours values(1, 1000, 1200)
insert into @.BusinessHours values(2, 0800, 1700)
insert into @.BusinessHours values(3, 1000, 1900)
insert into @.BusinessHours values(4, 0800, 1700)
insert into @.BusinessHours values(5, 0800, 1730)
insert into @.BusinessHours values(6, 0800, 1700)
insert into @.BusinessHours values(7, 1000, 1400)

select @.dtFrom = '17-Mar-2004 07:00:00' --3
, @.dtTo = '18-Mar-2004 07:00:00' --7
, @.HolidayCount = 0
, @.Hours = 0
, @.Seconds = 0

select ' Start', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours', @.Seconds As 'Seconds'

--
-- If the problem was solved in one day then only need to calculate hours for a single day
--

--
-- Test for @.dtFrom being a holiday, whould need to move time to start of first business day after the holiday
--
if exists(select * from @.HolidayCalendar where convert(varchar(10),@.dtFrom,120) = Holiday) begin
while exists(select * from @.HolidayCalendar where convert(varchar(10),@.dtFrom,120) = Holiday) begin
--
-- Move @.dtFrom 1 day closer to a business day
--
set @.dtFrom = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
end
end

select ' @.dtFrom = Holiday', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours', @.Seconds as 'Seconds'
--
-- Test for @.dtFrom being after business hours, would want to move to start of next day.
--
if exists(select * from @.BusinessHours where DOW = datepart(weekday,@.dtFrom) and datepart(hour,@.dtFrom) > (CloseAt/100)) begin
set @.dtFrom = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
end

select ' @.dtFrom after hours', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours', @.Seconds as 'Seconds'
--
-- Test for @.dtFrom being before business hours, would want to move to start of this business day.
--
if exists(select * from @.BusinessHours where DOW = datepart(weekday,@.dtFrom) and datepart(hour,@.dtFrom) < (OpenAt/100)) begin
select @.dtFrom = dateadd(hour,(OpenAt/100),convert(varchar(10),@.dtFrom,120))
from @.BusinessHours
where DOW = datepart(weekday,@.dtFrom)
end

select ' @.dtFrom before hours', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours', @.Seconds as 'Seconds'
--
-- Calculate the number of hours for the day the ploblem was submmited
-- provided the adjusted dates, @.dtFrom & @.dtTo are not on the same day
--
if datediff(day,@.dtFrom,@.dtTo) > 0 begin
set @.dtTemp = convert(varchar(10),@.dtFrom,120)
select @.Hours = CASE WHEN (datediff(hour,@.dtFrom,dateadd(hour,(CloseAt/100),@.dtTemp)) < 0) Then 0
ELSE datediff(hour,@.dtFrom,dateadd(hour,(CloseAt/100),@.dtTemp))
END,
@.Seconds = CASE WHEN (datediff(ss,@.dtFrom,dateadd(minute, Floor(CloseAt/100 * 60) + Right(CloseAt, 2),@.dtTemp)) < 0) THEN 0
ELSE datediff(ss,@.dtFrom,dateadd(minute, Floor(CloseAt/100 * 60) + Right(CloseAt, 2),@.dtTemp))
END
from @.BusinessHours
where DOW = datepart(weekday,@.dtFrom)
end
select ' Hours for 1st day', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours', @.Seconds As 'Seconds'
--
-- Step through each day, if it is a holiday skip else add the number of business hours for that day
--
set @.dtTemp = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
while datediff(day,@.dtTemp,@.dtTo) > 0 begin
if not exists(select * from @.HolidayCalendar where Holiday = @.dtTemp) begin
select @.Hours = @.Hours + (CloseAt - OpenAt) / 100,
@.Seconds = @.Seconds + Floor((CloseAt - OpenAt) / 100 * 60 * 60) + (Right((CloseAt - OpenAt), 2) * 60)
from @.BusinessHours
where DOW = datepart(weekday,@.dtTemp)
end
set @.dtTemp = dateadd(day,1,@.dtTemp)
end

select 'Hours for days between', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours', @.Seconds as 'Seconds'
--
-- Calculate the number of hours for the day the problem was resolved
--
Declare @.SubResult int
Declare @.SubDate datetime
Declare @.SubREsult2 int

set @.dtTemp = convert(varchar(10),@.dtTo,120)
select @.Hours = @.Hours + CASE WHEN datediff(hour,dateadd(hour,(OpenAt/100),@.dtTemp),@.dtTo) < 0 THEN 0
ELSE datediff(hour,dateadd(hour,(OpenAt/100),@.dtTemp),@.dtTo)
END,
@.Seconds = @.Seconds + CASE WHEN datediff(ss,dateadd(ss, Floor((OpenAt/100 * 60 * 60)) + (Right(OpenAt, 2) * 60), @.dtTemp), @.dtTo) < 0 THEN 0
ELSE datediff(ss,dateadd(ss, Floor((OpenAt/100 * 60 * 60)) + (Right(OpenAt, 2) * 60), @.dtTemp), @.dtTo)
END
from @.BusinessHours
where DOW = datepart(weekday,@.dtTo)
select ' Hours for Lst day', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours', @.Seconds as 'Seconds'

--end

select ' End', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours', @.Seconds As 'Seconds'|||Originally posted by BMWM3LUV
Paul,

Thanks a million. I doubt I would have ever come up with the solution that you presented. I have tweeked it a little, and am posting the code change.

Biggest Delta, is I removed the same day logic. This was making the assumption that an interaction starting and ending the same day was the duration of the interaction. This would have "dinged" the call center in three instances. Interaction came in before start of day and handled during day. If for some reason the day was a Holiday. Or, if the interaction was handled after business hours. (That time after close, but before the workers go home).

Additionally, I converted the computations into Seconds.

Finally, I handled when the interaction was handled with NO open business hours. I.E. Came in after close and handled before open.

However, the logic you provided was invaluable.

Thanks agian

HERE'S THE CODE

Glad to help! I have been told I am not quite right, maybe that is why I enjoy working this type of problem.|||If it makes you sleep better, you are ALL RIGHT in my book. I searched/begged/and pleaded for this solution for months.

Finally, my work told me I didn't have any more time to "research" this. So, I ultimately had to tell the customer that this report was going to cost them 12k.

Now that I have the query, I can put together the front end and re-sell it! If I turn a profit, I will make sure to share the wealth.

Mike|||Originally posted by BMWM3LUV
If it makes you sleep better, you are ALL RIGHT in my book. I searched/begged/and pleaded for this solution for months.

Finally, my work told me I didn't have any more time to "research" this. So, I ultimately had to tell the customer that this report was going to cost them 12k.

Now that I have the query, I can put together the front end and re-sell it! If I turn a profit, I will make sure to share the wealth.

Mike

Cool, Do you own an M3 or lust after one? If you you do then you own me a ride! I am a Mercedes Benz guy but wouldn't pass up a ride in a bemer!|||Own 98 M3 4-door. Incredible car. Joys of being a computer geek.|||How about just as select statement? Also independent of datefirst setting.

CREATE TABLE WorkHours(
DayNum tinyint PRIMARY KEY CHECK(DayNum BETWEEN 1 AND 7),
StartTime smalldatetime CHECK(StartTime BETWEEN 0 AND 1),
EndTime smalldatetime CHECK(EndTime BETWEEN 0 AND 1),
DayName char(9))

insert WorkHours
select 1, '8:00 AM', '5:00 PM', 'Monday'
union all select 2, '10:00 AM', '7:00 PM', 'Tuesday'
union all select 3, '8:00 AM', '5:00 PM', 'Wednesday'
union all select 4, '8:00 AM', '5:00 PM', 'Thursday'
union all select 5, '8:00 AM', '5:00 PM', 'Friday'
union all select 6, '10:00 AM', '2:00 PM', 'Saturday'
union all select 7, '10:00 AM', '12:00 PM', 'Sunday'

CREATE TABLE Holidays(
Holiday datetime PRIMARY KEY CHECK(Holiday = CONVERT(char(8),Holiday,112)),
Descr char(12))

insert Holidays
select '20040101','New Years'
union all select '20040119', 'ML King Day'
union all select '20040216', 'President''s'
union all select '20040411', 'Easter'
union all select '20040531', 'Memorial'
union all select '20040704', 'Independence'
union all select '20040906', 'Labor'
union all select '20041011', 'Columbus'
union all select '20041111', 'Veteran''s'
union all select '20041125', 'Thanksgiving'
union all select '20041225', 'Christmas'

CREATE PROC p_ProcessHours @.Opened datetime, @.Closed datetime, @.Hours int OUTPUT AS
IF @.Opened > @.Closed RETURN -1
SELECT @.Hours = --weeks between dates
(SELECT (DATEDIFF(d,@.Opened,@.Closed)/7)*SUM(DATEDIFF(hh,StartTime,EndTime))
FROM WorkHours)
- --holidays
(SELECT ISNULL(SUM(DATEDIFF(hh,StartTime,EndTime)),0)
FROM Holidays h JOIN WorkHours w ON DATEDIFF(d,0,h.Holiday)%7+1 = DATEDIFF(d,0,DayNum)
WHERE Holiday > @.Opened AND Holiday < @.Closed)
+ --hours of dates in same week
(SELECT SUM(DATEDIFF(hh,StartTime,EndTime))
FROM
(SELECT DayNum, StartTime, EndTime
FROM WorkHours
UNION ALL
SELECT DayNum+7,StartTime,EndTime
FROM WorkHours)x
WHERE DayNum BETWEEN DATEDIFF(d,0,@.Opened)%7+1 AND DATEDIFF(d,0,@.Closed)%7
+ CASE WHEN DATEDIFF(d,0,@.Opened)%7 > DATEDIFF(d,0,@.Closed)%7 THEN 8 ELSE 1 END)
- --hours not in opened date
ISNULL(
(SELECT DATEDIFF(hh,StartTime,CASE WHEN CONVERT(varchar,@.Opened,14) < EndTime THEN CONVERT(varchar,@.Opened,14) ELSE EndTime END)
FROM WorkHours
WHERE DayNum = DATEDIFF(d,0,@.Opened)%7+1 AND DATEDIFF(hh,StartTime,CONVERT(varchar,@.Opened,14)) > 0
AND NOT EXISTS
(SELECT *
FROM Holidays
WHERE Holiday = CONVERT(char(8),@.Opened,112))),0)
- --hours not in closed date
ISNULL(
(SELECT DATEDIFF(hh,CASE WHEN CONVERT(varchar,@.Closed,14) > StartTime THEN CONVERT(varchar,@.Closed,14) ELSE StartTime END,EndTime)
FROM WorkHours
WHERE DayNum = DATEDIFF(d,0,@.Closed)%7+1 AND DATEDIFF(hh,CONVERT(varchar,@.Closed,14),EndTime) > 0
AND NOT EXISTS
(SELECT *
FROM Holidays
WHERE Holiday = CONVERT(char(8),@.Closed,112))),0)

Compute sum of count(*) with group by

Hi,
Given the following table and test data:
CREATE TABLE test (
recordId numeric(18, 0) NOT NULL,
spId int NOT NULL,
startTime datetime NULL,
endTime datetime NULL )
INSERT INTO test VALUES (1,1,'2005-01-01 12:00','2005-01-01 14:33')
INSERT INTO test VALUES (2,2,'2005-01-01 12:26','2005-01-01 14:00')
INSERT INTO test VALUES (3,1,'2005-01-01 14:00','2005-01-01 14:33')
INSERT INTO test VALUES (4,2,'2005-01-01 14:00','2005-01-01 15:15')
INSERT INTO test VALUES (5,1,'2005-01-01 15:15','2005-01-01 15:20')
INSERT INTO test VALUES (6,2,'2005-01-01 15:15','2005-01-01 16:00')
INSERT INTO test VALUES (7,3,'2005-01-01 12:00','2005-01-01 14:30')
the following query lists only the spid's with non-unique spid's and
their respective counts:
SELECT spid, count(*) AS 'Count'
FROM test
GROUP BY spid
HAVING count(*) > 1 ORDER BY spid
I'm new to SQL and am having difficulty with a couple of things:
1. Modify the above query to compute the grand total for the Count, or
indeed a separate SQL statement to return just the grand total (= 6 in
this example).
2. This is the big challenge :). Taking the grouping returned by the
above query, write a query/stored procedure which looks for records
with identical spId's and the endtime of one spid equal to the
startTime of another. With the above test data, recordIds 3, 5, and 2,
4, 6 match this criteria.
Thanks very much for any help with this.1. Use a derived table construct:
SELECT SUM( total )
FROM ( SELECT spid, COUNT(*)
FROM tbl
GROUP BY spid
HAVING COUNT(*) > 1 ) D ( spid, total ) ;
2. Not sure if your requirements are clear since . Something like:
SELECT recordId, spId, ...
( SELECT MIN( t2.startTime )
FROM tbl t2 WHERE t2.spId = t1.spId
AND t2.startTime >= t1.endtime )
FROM tbl t1
ORDER BY t1.spid, startTime ;
If, not please post the sample resultset for the dataset you posted.
Anith|||Anith Sen wrote:
> 1. Use a derived table construct:
> SELECT SUM( total )
> FROM ( SELECT spid, COUNT(*)
> FROM tbl
> GROUP BY spid
> HAVING COUNT(*) > 1 ) D ( spid, total ) ;
>
Thanks. What does the 'D' mean above?

> 2. Not sure if your requirements are clear since . Something like:
> SELECT recordId, spId, ...
> ( SELECT MIN( t2.startTime )
> FROM tbl t2 WHERE t2.spId = t1.spId
> AND t2.startTime >= t1.endtime )
> FROM tbl t1
> ORDER BY t1.spid, startTime ;
> If, not please post the sample resultset for the dataset you posted.
CREATE TABLE test (
recordId numeric(18, 0) NOT NULL,
spId int NOT NULL,
startTime datetime NULL,
endTime datetime NULL )
INSERT INTO test VALUES (1,1,'2005-01-01 12:00','2005-01-01 14:33')
INSERT INTO test VALUES (3,1,'2005-01-01 14:00','2005-01-01 14:33')
INSERT INTO test VALUES (5,1,'2005-01-01 14:33','2005-01-01 15:20')
INSERT INTO test VALUES (2,2,'2005-01-01 12:26','2005-01-01 14:00')
INSERT INTO test VALUES (4,2,'2005-01-01 14:00','2005-01-01 15:15')
INSERT INTO test VALUES (6,2,'2005-01-01 15:15','2005-01-01 16:00')
INSERT INTO test VALUES (7,3,'2005-01-01 12:00','2005-01-01 14:30')
(Sorry, no wonder it wasn't clear as there was mistake in my original
test data. I've corrected the data above and put records with the same
spId together to make the grouping more obvious.)
So, from the above test data the expected results contain 2 sets of
matching data:
1. recordIds 3 and 5 because they have the same spId (1) and the
endTime of recordId 3 is the same as the startTime of recordId 5.
2. recordIds 2, 4 and 6 because they have the same spId (2) and the
endTime of recordId 2 is the same as the startTime of recordId 4; the
endTime of 4 is the same as the startTime of 6.
I hope that makes sense now. cheers,|||On 11 Nov 2005 09:30:25 -0800, "J Williams"
<johnwilliams_esquire@.hotmail.com> wrote:
>SELECT spid, count(*) AS 'Count'
>FROM test
>GROUP BY spid
WITH ROLLUP
>HAVING count(*) > 1 ORDER BY spid
If that does the job, great, otherwise you can always store the
results of the first query in a table an do further summations against
it.
J.|||>SELECT spid, count(*) AS 'Count'
>FROM test
>GROUP BY spid
WITH ROLLUP
>HAVING count(*) > 1 ORDER BY spid
Thanks, but that doesn't give the expected result. The basic SELECT:
SELECT spid, count(*) AS 'Count'
FROM test
GROUP BY spid
HAVING count(*) > 1 ORDER BY spid
returns:
spid Count
1 3
2 3
The grand total of Count in the above resultset is 6 and the SQL posted
earlier by Anith Sen gives this result:
SELECT SUM( total )
FROM ( SELECT spid, COUNT(*)
FROM tbl
GROUP BY spid
HAVING COUNT(*) > 1 ) D ( spid, total )|||>> What does the 'D' mean above?
D in the query stands for an alias for the derived table ( some folks
explicitly use AS keyword before the alias as well. )
Can you post the sample resultset here ( as you'd want to see on the QA
results pane ).
Anith|||Anith Sen wrote:

> Can you post the sample resultset here ( as you'd want to see on the QA
> results pane ).
First recordId, Second recordId, spId, endTime, startTime
3 5 1 2005-01-01 14:33 2005-01-01 14:33
2 4 2 2005-01-01 14:00 2005-01-01 14:00
4 6 2 2005-01-01 15:15 2005-01-01 15:15
The resultset shows pairs of 'matching' records, which is slightly
different (and better) to how I first envisioned it.
Thanks.|||This is one way of getting it:
SELECT MAX( t1.recordid ),
t2.recordid, t1.spid, t1.endtime
FROM test t1
INNER JOIN test t2
ON t1.spId = t2.spId
AND t1.endTime = t2.starttime
GROUP BY t1.spid, t2.recordid, t1.endtime ;
Anith|||That's excellent, thanks.

Compute Sum (Again)

I have tried everything to get a total of one of the columns in this query
including Rollup/Cube and compute sum. The problem is that compute sum canno
t
be used with select into and one cannot do a sum(count(distinct...
Rollup and cube give hierarchies and combination totals which is not what I
need.
I need something that operates as this should sum(count(distinct
a11.Policy_id)) but cannot find any function to do it. Can anyone please hel
p.
If one looks @. my select list, the final item is...
count(distinct(a11.Policy_id)) WJXBFS1
select a16.W_id W_id,
max(a16.w_desc) w_desc,
a14.Po_tr_bus_cat_id Po_tr_bus_cat_id,
max(a14.Po_tr_bus_cat_desc) Po_tr_bus_cat_desc,
a13.Pr_Group_id Pr_Group_id,
max(a13.Pr_group_desc) Pr_group_desc,
a12.Po_corp_unit_id Po_corp_unit_id,
count(distinct(a11.Policy_id)) WJXBFS1
into #ZZT4Z010YTNMD00B
from fat_bse_po_risk_detail a11
join POt_lu_policy a12
on (a11.Policy_id = a12.Policy_id)
join prt_lu_product a13
on (a11.product_id = a13.product_id)
join POv_lu_tr_Business_Type a14
on (a11.Po_tr_bus_type_id = a14.Po_tr_bus_type_id)
join TRt_lu_Trans_Subtype a15
on (a11.Tr_sub_type_id = a15.Tr_sub_type_id)
join TIt_lu_day a16
on (a11.Cur_trn_dt = a16.Cur_trn_dt)
where (a15.Tr_type_id in ('HNB', 'HNC', 'HRN', 'INB', 'IRN', 'HPR')
and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a12.Po_status_id in ('A')
and a11.Inception_date_id between CONVERT(datetime, '2004-02-01 00:00:00',
120) and CONVERT(datetime, '2005-01-29 00:00:00', 120)
and a13.Pr_Group_id in (2, 3, 4, 5, 6, 7)
and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a16.W_id in (select fiftytwo_w_roll_id from
tit_ta_fiftytwo_w_roll where latest_w_ind = 'Y')
and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED'))
group by a16.W_id,
a14.Po_tr_bus_cat_id,
a13.Pr_Group_id,
a12.Po_corp_unit_idPlease explain what was wrong with the answer I gave yesterday.
The best way to get help with a problem like this is to post DDL (CREATE
TABLE statement(s)), sample data (INSERT statements) and show your required
end result. Probably no need to post all your tables, a simplified example
including keys and constraints should do it. If you do that you should find
you get a helpful answer much faster. The following article should help:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Please explain what was wrong with the answer I gave yesterday.
The best way to get help with a problem like this is to post DDL (CREATE
TABLE statement(s)), sample data (INSERT statements) and show your required
end result. Probably no need to post all your tables, a simplified example
including keys and constraints should do it. Please try to do that and you
should find you get a helpful answer much faster. The following article
should help:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||sum(count(distinct(a11.Policy_id))
this statement is odd and it is not allowed use a sub query in an agregade
function like sum
even if you could do so
if you have 1000 of count a11.Policy_id then you get the result as 1000.1000
(it will sum the count for every row of result set)
do you realy want that? if so you can create function then sum it
create function thefunction(@.w_id nvarchar(20))
returns int
as
begin
declare @.count_id as int
select @.count_id=count(Policy_id)
from fat_bse_po_risk_detail
Return IsNull(@.count,0)
end
then
select sum(thefunction(@.w_id))...
"marcmc" wrote:

> I have tried everything to get a total of one of the columns in this query
> including Rollup/Cube and compute sum. The problem is that compute sum can
not
> be used with select into and one cannot do a sum(count(distinct...
> Rollup and cube give hierarchies and combination totals which is not what
I
> need.
> I need something that operates as this should sum(count(distinct
> a11.Policy_id)) but cannot find any function to do it. Can anyone please h
elp.
> If one looks @. my select list, the final item is...
> count(distinct(a11.Policy_id)) WJXBFS1
> select a16.W_id W_id,
> max(a16.w_desc) w_desc,
> a14.Po_tr_bus_cat_id Po_tr_bus_cat_id,
> max(a14.Po_tr_bus_cat_desc) Po_tr_bus_cat_desc,
> a13.Pr_Group_id Pr_Group_id,
> max(a13.Pr_group_desc) Pr_group_desc,
> a12.Po_corp_unit_id Po_corp_unit_id,
> count(distinct(a11.Policy_id)) WJXBFS1
> into #ZZT4Z010YTNMD00B
> from fat_bse_po_risk_detail a11
> join POt_lu_policy a12
> on (a11.Policy_id = a12.Policy_id)
> join prt_lu_product a13
> on (a11.product_id = a13.product_id)
> join POv_lu_tr_Business_Type a14
> on (a11.Po_tr_bus_type_id = a14.Po_tr_bus_type_id)
> join TRt_lu_Trans_Subtype a15
> on (a11.Tr_sub_type_id = a15.Tr_sub_type_id)
> join TIt_lu_day a16
> on (a11.Cur_trn_dt = a16.Cur_trn_dt)
> where (a15.Tr_type_id in ('HNB', 'HNC', 'HRN', 'INB', 'IRN', 'HPR')
> and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a12.Po_status_id in ('A')
> and a11.Inception_date_id between CONVERT(datetime, '2004-02-01 00:00:00',
> 120) and CONVERT(datetime, '2005-01-29 00:00:00', 120)
> and a13.Pr_Group_id in (2, 3, 4, 5, 6, 7)
> and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a16.W_id in (select fiftytwo_w_roll_id from
> tit_ta_fiftytwo_w_roll where latest_w_ind = 'Y')
> and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED'))
> group by a16.W_id,
> a14.Po_tr_bus_cat_id,
> a13.Pr_Group_id,
> a12.Po_corp_unit_id

COMPUTE SUM

How Can I get a computed sum on the last column in the select list
distinct(a11.Policy_id).
We can't use sum(count(... or compute sum in a select into statement so I am
really stumped...
select a16.W_id W_id,
max(a16.w_desc) w_desc,
a14.Po_tr_bus_cat_id Po_tr_bus_cat_id,
max(a14.Po_tr_bus_cat_desc) Po_tr_bus_cat_desc,
a13.Pr_Group_id Pr_Group_id,
max(a13.Pr_group_desc) Pr_group_desc,
a12.Po_corp_unit_id Po_corp_unit_id,
count(distinct(a11.Policy_id)) WJXBFS1
into #ZZT4Z010YTNMD00B
from fat_bse_po_risk_detail a11
join POt_lu_policy a12
on (a11.Policy_id = a12.Policy_id)
join prt_lu_product a13
on (a11.product_id = a13.product_id)
join POv_lu_tr_Business_Type a14
on (a11.Po_tr_bus_type_id = a14.Po_tr_bus_type_id)
join TRt_lu_Trans_Subtype a15
on (a11.Tr_sub_type_id = a15.Tr_sub_type_id)
join TIt_lu_day a16
on (a11.Cur_trn_dt = a16.Cur_trn_dt)
where (a15.Tr_type_id in ('HNB', 'HNC', 'HRN', 'INB', 'IRN', 'HPR')
and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a12.Po_status_id in ('A')
and a11.Inception_date_id between CONVERT(datetime, '2004-02-01 00:00:00',
120) and CONVERT(datetime, '2005-01-29 00:00:00', 120)
and a13.Pr_Group_id in (2, 3, 4, 5, 6, 7)
and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a16.W_id in (select fiftytwo_w_roll_id from
tit_ta_fiftytwo_w_roll where latest_w_ind = 'Y')
and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED'))
group by a16.W_id,
a14.Po_tr_bus_cat_id,
a13.Pr_Group_id,
a12.Po_corp_unit_idUse CUBE or ROLLUP.
http://msdn.microsoft.com/library/e..._qd_08_6g9x.asp
COMPUTE / COMPUTE BY exists for backwards compatibility reasons so you
should avoid it in any new code.
David Portas
SQL Server MVP
--|||Hi
I have simplified the query but I think you've got an idea
use northwind
select sum(o) orderid into #test
from (select count(*) o from orders) as d
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:23A4514D-EFA0-4965-87AD-0D3A7121B935@.microsoft.com...
> How Can I get a computed sum on the last column in the select list
> distinct(a11.Policy_id).
> We can't use sum(count(... or compute sum in a select into statement so I
am
> really stumped...
> select a16.W_id W_id,
> max(a16.w_desc) w_desc,
> a14.Po_tr_bus_cat_id Po_tr_bus_cat_id,
> max(a14.Po_tr_bus_cat_desc) Po_tr_bus_cat_desc,
> a13.Pr_Group_id Pr_Group_id,
> max(a13.Pr_group_desc) Pr_group_desc,
> a12.Po_corp_unit_id Po_corp_unit_id,
> count(distinct(a11.Policy_id)) WJXBFS1
> into #ZZT4Z010YTNMD00B
> from fat_bse_po_risk_detail a11
> join POt_lu_policy a12
> on (a11.Policy_id = a12.Policy_id)
> join prt_lu_product a13
> on (a11.product_id = a13.product_id)
> join POv_lu_tr_Business_Type a14
> on (a11.Po_tr_bus_type_id = a14.Po_tr_bus_type_id)
> join TRt_lu_Trans_Subtype a15
> on (a11.Tr_sub_type_id = a15.Tr_sub_type_id)
> join TIt_lu_day a16
> on (a11.Cur_trn_dt = a16.Cur_trn_dt)
> where (a15.Tr_type_id in ('HNB', 'HNC', 'HRN', 'INB', 'IRN', 'HPR')
> and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a12.Po_status_id in ('A')
> and a11.Inception_date_id between CONVERT(datetime, '2004-02-01 00:00:00',
> 120) and CONVERT(datetime, '2005-01-29 00:00:00', 120)
> and a13.Pr_Group_id in (2, 3, 4, 5, 6, 7)
> and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a16.W_id in (select fiftytwo_w_roll_id from
> tit_ta_fiftytwo_w_roll where latest_w_ind = 'Y')
> and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED'))
> group by a16.W_id,
> a14.Po_tr_bus_cat_id,
> a13.Pr_Group_id,
> a12.Po_corp_unit_id|||David: Invalid Query: CUBE and ROLLUP cannot compute distinct aggregates.
Uri, I cannot see what you are trying
This really is a clincher. There must be some way of totalling a distinct|||You haven't specified exactly what you want to sum. Sub Totals or just a
Grand Total for the result?
Here are two examples taken from the NorthWind database. Notice that the
answer is different in each case, depending on how you want to interpret a
"DISTINCT" total (number of distinct values or total of the distinct counts)
.
SELECT shipcountry, shipcity, COUNT(DISTINCT productid)
FROM Invoices
GROUP BY shipcountry, shipcity
UNION ALL
SELECT NULL, NULL, COUNT(DISTINCT productid)
FROM Invoices
SELECT shipcountry, shipcity, COUNT(DISTINCT productid)
FROM Invoices
GROUP BY shipcountry, shipcity
UNION ALL
SELECT NULL, NULL, SUM(dist)
FROM
(SELECT COUNT(DISTINCT productid)
FROM Invoices
GROUP BY shipcountry, shipcity) AS T(dist)
If you want sub-totals as well then you just need to add a GROUP BY list of
the second UNIONed query.
David Portas
SQL Server MVP
--|||thx for replying David.
I want a total sum like the compute sum operates on my last item in the
select list which is
count(distinct a11.Policy_id) WJXBFS1|||As it's just a straight total you can use ROLLUP:
SELECT
w_id, w_desc, po_tr_bus_cat_id, po_tr_bus_cat_desc,
pr_group_id, pr_group_desc, po_corp_unit_id, SUM(wjxbfs1) AS wjxbfs1
INTO #ZZT4Z010YTNMD00B
FROM
(select a16.W_id W_id,
max(a16.w_desc) w_desc,
a14.Po_tr_bus_cat_id Po_tr_bus_cat_id,
max(a14.Po_tr_bus_cat_desc) Po_tr_bus_cat_desc,
a13.Pr_Group_id Pr_Group_id,
max(a13.Pr_group_desc) Pr_group_desc,
a12.Po_corp_unit_id Po_corp_unit_id,
count(distinct(a11.Policy_id)) WJXBFS1
from fat_bse_po_risk_detail a11
join POt_lu_policy a12
on (a11.Policy_id = a12.Policy_id)
join prt_lu_product a13
on (a11.product_id = a13.product_id)
join POv_lu_tr_Business_Type a14
on (a11.Po_tr_bus_type_id = a14.Po_tr_bus_type_id)
join TRt_lu_Trans_Subtype a15
on (a11.Tr_sub_type_id = a15.Tr_sub_type_id)
join TIt_lu_day a16
on (a11.Cur_trn_dt = a16.Cur_trn_dt)
where (a15.Tr_type_id in ('HNB', 'HNC', 'HRN', 'INB', 'IRN', 'HPR')
and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a12.Po_status_id in ('A')
and a11.Inception_date_id >= '20040201'
AND a11.Inception_date_id < '20050130'
and a13.Pr_Group_id in (2, 3, 4, 5, 6, 7)
and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a16.W_id in (select fiftytwo_w_roll_id from
tit_ta_fiftytwo_w_roll where latest_w_ind = 'Y')
and a12.Po_corp_unit_id in ('GEI', 'GNI', 'GED'))
group by a16.W_id,
a14.Po_tr_bus_cat_id,
a13.Pr_Group_id,
a12.Po_corp_unit_id) AS T
GROUP BY w_id, w_desc, po_tr_bus_cat_id, po_tr_bus_cat_desc,
pr_group_id, pr_group_desc, po_corp_unit_id
WITH ROLLUP
HAVING GROUPING(w_id)=1
OR GROUPING(po_corp_unit_id)=0
(this is untested)
P.S. You don't need to CONVERT date strings if you stick to the ISO date
format above.
David Portas
SQL Server MVP
--|||thanks again David, I am running it now... what does the...
HAVING GROUPING(w_id)=1
OR GROUPING(po_corp_unit_id)=0
do?|||If you run the query without the HAVING part you'll see that the result set
has subtotals for each column in the GROUP BY list. The GROUPING() criteria
ensures that we only include the total row you wanted (the grand total) plus
the original rows. See Books Online for details of the GROUPING syntax.
David Portas
SQL Server MVP
--sqlsql