Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gavakie
Posting Yak Master
221 Posts |
 Posted - 2009-11-05 : 10:36:18
|
| In my grouping im using this in a sum=sum(iif(Fields!Letter.Value="Service",0,Fields!Mailed.Value)) its working fine where my datatypes are INT but the others that I set as floats i get the #error. I cant set the other columns to ints because the numbers wouldnt be accurate. Any ideas? |
|
|
gavakie
Posting Yak Master
221 Posts |
Posted - 2009-11-05 : 10:50:03
|
| Edit on that its acutally doing it no matter the datatype. the proc im calling is error out on fields that are calculated int he query. the fields that arent erroring were just simple counts.Select intCampaignID, strCampaignName, Letter , cast(Mailed as int) as Mailed, cast(Emailed as int) as Emailed, cast(Sales as int) as Sales, Cast(Sales/[Distinct Customers Contacted] as float) as ResponsePer, Cast(isnull([Sales Revenue]/Sales,0) as float)as PER_Sales, cast(isnull([Sales Revenue],0) as float) as [Sales Revenue], cast((Mailed/[Distinct Customers Contacted]) * [Program Cost]as float) as Cost, cast(isnull([Sales Revenue]-((Mailed/[Distinct Customers Contacted]) * [Program Cost]),0) as float) as ROI, lngStoreId From (SELECT mjl.intCampaignId , mc.strCampaignName , ct.strDescription as 'Letter' , COUNT(DISTINCT(ccml_p.lngCustomerId)) AS 'Mailed' , COUNT(DISTINCT(ccml_e.lngCustomerId)) AS 'Emailed' , COUNT(v.lngVehicleId) AS 'Sales' , (SELECT CAST(COUNT(DISTINCT(distinctcount.lngCustomerId)) as DECIMAL) FROM tblCustomerContactLog distinctcount WHERE distinctcount.lngMailJobId = mjl.lngMailJobId AND distinctcount.intContactTypeId in (196,197,198)) as 'Distinct Customers Contacted' , SUM(v.curVehTotalGross) AS 'Sales Revenue' , ROUND(curBudget * (SELECT CAST(COUNT(DISTINCT(lngCustomerId)) as DECIMAL) FROM tblCustomerContactLog salescount WHERE salescount.lngMailJobId = mjl.lngMailJobId AND salescount.intContactTypeId in (196,197,198)) / (SELECT CAST(COUNT(DISTINCT(lngCustomerId)) AS DECIMAL) FROM tblCustomerContactLog salescount WHERE salescount.lngMailJobId = mjl.lngMailJobId AND salescount.intContactTypeId in (191,192,193,194,195,199,196,197,198)), 2) as 'Program Cost',mjl.lngStoreIdThis is the top part of the query showing the fields |
 |
|
|
|
|
|
|
|
error on conditional sum