Norm-al
asked on
Show Calculated Result on FormShow of Delphi cxGrid
I have 2 fields in my grid, FinalizedSalesAmount (currency) and TotalTime (float). I want a new field called "Finalized/Time" which is a calculation of FinalizedSalesAmount/Total Time. I cannot run this calculation in SQL because not ALL records have a TotalTime thus giving me a 'cannot divide by zero error'. I tried using the columns 'OnDisplayText' event but can't seem to get that working... Any ideas on the best way to accomplish this?
Thanks!
Thanks!
ASKER
Is there a way for me to enter this formula into the actual field itself to ONLY run the calculation if the TotalTime field is not 0?
Can you show your original SQL statement please?
ASKER
SELECT JobNum, Contractor, Sales_Class, Job_Name, Buyer, Owner, Part, Approved_Sales, Sales_Amount, Sales_Initial, LF_SIZE, Job_Location, ' +
'Finalized_Sales, Finalized_Sales_Amount, LF_Size_Final, TotalTimeOnJob, TotalTimeOnJobEng, Department, Street_Name, Street_Num, LFbid, ' +
'bidsubmitted, bidamount, bidresult, Number_Of_Units, bidresultdate, awardedto, awardreason, Lost_Sales_Date, Lead_Category, Lead_Source, ' +
'Lead_Provider, Close_Detail_ID, team, bidtype, bidlogtype, ctjobid, nextfollowupdate, JobStage, loggedin, Site_Ready_Date, Lead_Type, Region, ' +
'County, City_Region, Sub_Region, JobMargin, Factor, StaticStatus, StaticStatusDetail, Deposit_Received, Deposit_Invoiced, Cut_Job, Shipped, ' +
'CabinetCost, ComputerCost, TotalCost, ' +
// '(Finalized_Sales_Amount)/ (TotalTime OnJob) FROM SalesReports_View ' +
// 'WHERE ((TotalTimeOnJob > 0) AND (Finalized_Sales_Amount > 0)) AS AmountPerTime ' +
'FROM SalesReports_View ';
I had to comment out the calculation because as soon as it hits a record that has TotalTimeOnJob = NULL then I get the divide by zero error.
'Finalized_Sales, Finalized_Sales_Amount, LF_Size_Final, TotalTimeOnJob, TotalTimeOnJobEng, Department, Street_Name, Street_Num, LFbid, ' +
'bidsubmitted, bidamount, bidresult, Number_Of_Units, bidresultdate, awardedto, awardreason, Lost_Sales_Date, Lead_Category, Lead_Source, ' +
'Lead_Provider, Close_Detail_ID, team, bidtype, bidlogtype, ctjobid, nextfollowupdate, JobStage, loggedin, Site_Ready_Date, Lead_Type, Region, ' +
'County, City_Region, Sub_Region, JobMargin, Factor, StaticStatus, StaticStatusDetail, Deposit_Received, Deposit_Invoiced, Cut_Job, Shipped, ' +
'CabinetCost, ComputerCost, TotalCost, ' +
// '(Finalized_Sales_Amount)/
// 'WHERE ((TotalTimeOnJob > 0) AND (Finalized_Sales_Amount > 0)) AS AmountPerTime ' +
'FROM SalesReports_View ';
I had to comment out the calculation because as soon as it hits a record that has TotalTimeOnJob = NULL then I get the divide by zero error.
make it:
SELECT JobNum, Contractor, Sales_Class, Job_Name, Buyer, Owner, Part, Approved_Sales, Sales_Amount, Sales_Initial, LF_SIZE, Job_Location, ' +
'Finalized_Sales, Finalized_Sales_Amount, LF_Size_Final, TotalTimeOnJob, TotalTimeOnJobEng, Department, Street_Name, Street_Num, LFbid, ' +
'bidsubmitted, bidamount, bidresult, Number_Of_Units, bidresultdate, awardedto, awardreason, Lost_Sales_Date, Lead_Category, Lead_Source, ' +
'Lead_Provider, Close_Detail_ID, team, bidtype, bidlogtype, ctjobid, nextfollowupdate, JobStage, loggedin, Site_Ready_Date, Lead_Type, Region, ' +
'County, City_Region, Sub_Region, JobMargin, Factor, StaticStatus, StaticStatusDetail, Deposit_Received, Deposit_Invoiced, Cut_Job, Shipped, ' +
'CabinetCost, ComputerCost, TotalCost, ' +
'(Finalized_Sales_Amount)/(case when TotalTimeOnJob > 0 then TotalTimeOnJob else 1 end) as Col1' +
// '(Finalized_Sales_Amount)/(TotalTimeOnJob) FROM SalesReports_View ' +
// 'WHERE ((TotalTimeOnJob > 0) AND (Finalized_Sales_Amount > 0)) AS AmountPerTime ' +
'FROM SalesReports_View ';
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The 1st query worked but records that did not have TotalTimeOnJob should show $0
ASKER
The 2nd query works beautifully, thanks!!!
Because we told it to divide Finalized_Sales_Amount by one if TotalTimeOnJob is zero:
(Finalized_Sales_Amount)/( case when TotalTimeOnJob > 0 then TotalTimeOnJob else 1 end) as Col1' +
What you want it to show if TotalTimeOnJob is zero?
(Finalized_Sales_Amount)/(
What you want it to show if TotalTimeOnJob is zero?
Cheers :)
ASKER
Thanks JimyX, you have helped me plenty of times before :)
select (case when TotalTime > 0 then TotalTime else 1 end) as Col1 from Table1