May 9, 2008 at 4:09 am
Hi all,
This query does work, but then errors depending on the time of day which is really annoying the hell out of me.
SELECTCONVERT(Varchar, History.CallDateTime, 103) As CallDate, (Groups.GroupName) As CampaignName, Count(History.HistoryID) As Calls, ISNULL(DROPPEDCALLS.Dropped, 0) As Drops, Convert(DECIMAL(3,2),((ISNULL(DROPPEDCALLS.Dropped, 0))/(Count(History.HistoryID) * 1.0)*100)) As DropRate
FROM History INNER JOIN
GroupProject ON History.ProjectID = GroupProject.ProjectID INNER JOIN
Groups ON GroupProject.GroupID = Groups.GroupID
LEFT OUTER JOIN (
SELECTCONVERT(Varchar, History.CallDateTime, 103) As CallDate, (Groups.GroupName) As CampaignName, Count(History.HistoryID) As Dropped
FROM History INNER JOIN
GroupProject ON History.ProjectID = GroupProject.ProjectID INNER JOIN
Groups ON GroupProject.GroupID = Groups.GroupID
WHERE(CONVERT(Varchar, History.CallDateTime, 103) = (CONVERT(Varchar, GETDATE(), 103))) AND ((CRC = 'DROP') OR (CRC = 'OIVR3') OR (CRC = 'OIVR4'))
GROUP BY Groups.GroupName, CONVERT(Varchar, History.CallDateTime, 103)
)DROPPEDCALLS
ON CONVERT(Varchar, History.CallDateTime, 103) = DROPPEDCALLS.CallDate AND Groups.GroupName = DROPPEDCALLS.CampaignName
WHERE(CONVERT(Varchar, History.CallDateTime, 103) = (CONVERT(Varchar, GETDATE(), 103)))
GROUP BY Groups.GroupName, CONVERT(Varchar, History.CallDateTime, 103), DROPPEDCALLS.Dropped
ORDER BY Groups.GroupName
In a morning it works fine, but then in the afternoon we get the following error:
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
Ideally I want to use this as I will be running for historic info (the day after)
SELECTCONVERT(Varchar, History.CallDateTime, 103) As CallDate, (Groups.GroupName) As CampaignName, Count(History.HistoryID) As Calls, ISNULL(DROPPEDCALLS.Dropped, 0) As Drops, Convert(DECIMAL(3,2),((ISNULL(DROPPEDCALLS.Dropped, 0))/(Count(History.HistoryID) * 1.0)*100)) As DropRate
FROM History INNER JOIN
GroupProject ON History.ProjectID = GroupProject.ProjectID INNER JOIN
Groups ON GroupProject.GroupID = Groups.GroupID
LEFT OUTER JOIN (
SELECTCONVERT(Varchar, History.CallDateTime, 103) As CallDate, (Groups.GroupName) As CampaignName, Count(History.HistoryID) As Dropped
FROM History INNER JOIN
GroupProject ON History.ProjectID = GroupProject.ProjectID INNER JOIN
Groups ON GroupProject.GroupID = Groups.GroupID
WHERE(CONVERT(Varchar, History.CallDateTime, 103) = (CONVERT(Varchar, GETDATE()-1, 103))) AND ((CRC = 'DROP') OR (CRC = 'OIVR3') OR (CRC = 'OIVR4'))
GROUP BY Groups.GroupName, CONVERT(Varchar, History.CallDateTime, 103)
)DROPPEDCALLS
ON CONVERT(Varchar, History.CallDateTime, 103) = DROPPEDCALLS.CallDate AND Groups.GroupName = DROPPEDCALLS.CampaignName
WHERE(CONVERT(Varchar, History.CallDateTime, 103) = (CONVERT(Varchar, GETDATE()-1, 103)))
GROUP BY Groups.GroupName, CONVERT(Varchar, History.CallDateTime, 103), DROPPEDCALLS.Dropped
ORDER BY Groups.GroupName
And here is where I get really annoyed... This won't work in a morning, but late afternoon/evening it works...
Can anyone help?
May 9, 2008 at 6:14 am
[font="Verdana"]
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
From BOL
Severity Levels 11 through 16
These messages indicate errors that can be corrected by the user.
not sure but, it seems, User might be inputting some wrong value.
confirm on this.
Mahesh[/font]
MH-09-AM-8694
May 9, 2008 at 9:12 am
if it's an overflow my guess is that the input is too large for the output data type you are specifying. like taking a bigint and trying to put it into an int.
looking at your code i'd guess it's the decimal part and that in the afternoon you may get too many instances to fit into a decimal data type. can you try to make it an int?
May 9, 2008 at 9:44 am
[font="Comic Sans MS"]If there is exactly one call and exactly 1 dropped call, then the result of the caculation will be 100.00 % but drop rate is defined as numeric(3,2), hence the overflow error. Anything larger than 9.99% will cause the error.
Your SQL statement is also overly complex and due to the converts on dates will perform poorly. Here is a simplier solution.
First, create a view that will provide the starting and ending time range for today's date. This view should be used instead of date logic into each SQL Statement:
Create view TodaysDate
as
select cast(cast(dateadd(ms,-43200002,current_timestamp) as integer)as datetime) as TodayStartTs
,DATEADD(ms, -3, ( cast(cast(dateadd(ms,-43200002,current_timestamp) as integer) as datetime) + 1) )
as TodaysEndTs
Then simplier SQL:
SELECTCall.CallDate
, Call.CampaignName
, Call.Calls
, call.Drops
,( CAST( Call.Calls as numeric(38,2) ) / CAST( call.Drops as Numeric(38,2) ) ) * 100.0 as DropRate
FROM (
SELECT TodaysDate.TodayStartTs As CallDate
, Groups.GroupName As CampaignName
, Count(History.HistoryID) As Calls
,SUM ( CASE WHEN History.CRC IN 'DROP', 'OIVR3', 'OIVR4' then 1 else 0 end ) as Drops
fromTodaysDate
join History
on History.CallDateTime between TodaysDate.TodayStartTs and TodaysDate.TodayEndTs
JOINGroupProject
ON History.ProjectID = GroupProject.ProjectID
JOIN Groups
ON GroupProject.GroupID = Groups.GroupID
group by TodaysDate.TodayStartTs
, Groups.GroupName
) as Call
ORDER BY Call.CampaignName
[/font]
SQL = Scarcely Qualifies as a Language
May 9, 2008 at 1:40 pm
The overflow is caused by "Convert(DECIMAL(3,2)...". The numbers after "decimal" tell it how many digits to allow. The first one is total digits, the second one is digits after the decimal place.
So, with "(3,2)", any number over 9.99 will get an overflow error.
I use Float datatype for this kind of thing, instead of Decimal, unless I need a specific number of digits after the decimal place (and then Round function will handle that, too).
Since any precision (the first number) between 1 and 9, uses 5 bytes for storage, defining the number as (3,2), uses the same amount of disk space/RAM, as (9,2). (Books Online has a list of how many bytes each precision range uses.) So, that would also be a valid way to handle this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 11, 2008 at 2:28 pm
Of course! Guess I need to catch up on my data types! Thxs for your help with this everyone!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply