Link to home
Start Free TrialLog in
Avatar of Norm-al
Norm-alFlag for United States of America

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/TotalTime. 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!
Avatar of jimyX
jimyX

SQL has a way to handle that, you can check whether the division of FinalizedSalesAmount over TotalTime will result in error by checking the TotalTime if it's greater than zero:
select (case when TotalTime > 0 then TotalTime else 1 end) as Col1 from Table1
Avatar of Norm-al

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?
Avatar of Norm-al

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)/(TotalTimeOnJob) 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.
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 ';

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of jimyX
jimyX

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Norm-al

ASKER

The 1st query worked but records that did not have TotalTimeOnJob should show $0
Avatar of Norm-al

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?
Cheers :)
Avatar of Norm-al

ASKER

Thanks JimyX, you have helped me plenty of times before :)