May 7, 2008 at 1:35 pm
Hi All,
I have a query which has a sub query within it. The query itself works all but the last field required called DropRate, an expression calculated from a filed in the sub query divided by a field in the select query. All I get is 0 as an output. Ideally I need this field to product a percentage valuse to 2 decimal places i.e. Decimal (3, 2) - 100.00 or 0.20 etc.
The select statement is below, I need a result ASAP as I'm behind on this so a quick response is greatly apprecitecd. Thanks in advance...
SELECTCONVERT(Varchar, History.CallDateTime, 103) As CallDate, (Groups.GroupName) As CampaignName, Count(History.HistoryID) As Calls, ISNULL(DROPPEDCALLS.Dropped, 0) As Drops, (ISNULL(DROPPEDCALLS.Dropped, 0))/(Count(History.HistoryID)) 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()-60, 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()-60, 103)))
GROUP BY Groups.GroupName, CONVERT(Varchar, History.CallDateTime, 103), DROPPEDCALLS.Dropped
ORDER BY Groups.GroupName
May 7, 2008 at 1:51 pm
If you are always getting 0 it is because both fields are int and division between int's will always return and int. Convert one to decimal(3, 2) like this;
Select Convert(decimal(3,2), 1)/2
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 7, 2008 at 1:55 pm
Another way (less keystrokes) just multiply one side by 1.0
ISNULL(DROPPEDCALLS.Dropped, 0))/(Count(History.HistoryID) * 1.0)
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 7, 2008 at 2:05 pm
Thanks for the quick reply, I have tried the "CONVERT(DECIMAL (3,2)," given but had no luck... Have I misunderstood what you meant?
Example 1;
SELECTCONVERT(Varchar, History.CallDateTime, 103) As CallDate, (Groups.GroupName) As CampaignName, CONVERT(DECIMAL (3,2),Count(History.HistoryID)) As Calls, ISNULL(DROPPEDCALLS.Dropped, 0) As Drops, (ISNULL(DROPPEDCALLS.Dropped, 0))/(Count(History.HistoryID)) 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()-60, 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()-60, 103)))
GROUP BY Groups.GroupName, CONVERT(Varchar, History.CallDateTime, 103), DROPPEDCALLS.Dropped
ORDER BY Groups.GroupName
Example 1 Error:
Msg 8115, Level 16, State 8, Line 2
Arithmetic overflow error converting int to data type numeric.
Example 2:
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), Count(History.HistoryID)))/(CONVERT(DECIMAL (3,2), ISNULL(DROPPEDCALLS.Dropped, 0)))) 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()-60, 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()-60, 103)))
GROUP BY Groups.GroupName, CONVERT(Varchar, History.CallDateTime, 103), DROPPEDCALLS.Dropped
ORDER BY Groups.GroupName
Example 2 Error:
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting int to data type numeric.
May 7, 2008 at 2:10 pm
Ah the "((ISNULL(DROPPEDCALLS.Dropped, 0))/(Count(History.HistoryID) * 1.0)*100)" worked great! Thank you!!
SELECTCONVERT(Varchar, History.CallDateTime, 103) As CallDate, (Groups.GroupName) As CampaignName, Count(History.HistoryID) As Calls, ISNULL(DROPPEDCALLS.Dropped, 0) As Drops, ((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()-60, 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()-60, 103)))
GROUP BY Groups.GroupName, CONVERT(Varchar, History.CallDateTime, 103), DROPPEDCALLS.Dropped
ORDER BY Groups.GroupName
May 7, 2008 at 2:11 pm
Since you're using "convert decimal(3,2)", you're getting an overflow error if the number is higher than 9 in any case. In decimal, the numbers in the parentheses determine how many digits you can have, the first one is total, the second is how many after the decimal place. Either increase the number of digits, or use Float instead of decimal. (The trick mentioned of multiplying by 1.0 is an implicit conversion to Float, or you can use cast/convert.)
I recommend Float instead of decimal. Takes less tinkering to get it to work right.
- 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 7, 2008 at 2:14 pm
G's explanation is right on. I have used Float in the past, but it does not give precision and you had specifically mentioned Decimal(3,2) in your original post which is why I used it. I assumed you needed/wanted the 2 decimal precision which Float does not give you.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply