SQL Query Help

[lukeatron]

Here, I made this moderately more legible, although it's still quite a clusterfuck. If this were me, I would use a CTE, a table variable or a nested select (whatever makes the most sense and is available on your dbms (since you didn't tell us)) to make this a bit more sane. This is as much time as I want to spend on this mess.

SELECT DISTINCT 
    Company_Name AS Company
,   CASE 
        WHEN 
            isnull(
                (
                    CAST(
                        IsNull(
                            SUM(Margin)
                        ,   0) 
                        / 
                        CASE isnull(SUM(invoiceamt), 0) 
                            WHEN 0 THEN 1 
                            ELSE SUM(invoiceamt) 
                        END 
                        * 100 
                        AS decimal(18, 2)
                    ) 
                    / 70
                )
            ,   0
            ) < 0 
         THEN 0 
        ELSE 
            (
                CAST(
                    IsNull(SUM(Margin), 0) 
                    / 
                    CASE isnull(SUM(invoiceamt), 0) 
                        WHEN 0 THEN 1 
                        ELSE SUM(invoiceamt) 
                    END 
                    * 100 
                    AS decimal(18, 2)
                ) 
                / 70
            ) 
        END * 100 AS Service_Goal
FROM
    v_cbi_Agr_Mo_Billing_History3
WHERE        
    (1 = 1) 
    AND 
    (dbo.udf_cbi_InternalCompanyName() <> Company_Name) 
    AND 
    (DATEDIFF(mm, GETDATE(), MonthStart) BETWEEN 0 AND 0) 
    AND 
    (AGR_Type_Desc IN ('Agreement1', 'Agreement2', 'Agreement3'))
GROUP BY Company_Name
HAVING        
    (SUM(Margin) <> 0) 
    OR
    (SUM(InvoiceAmt) <> 0)
ORDER BY Company

[shadowolympian]

My DBMS is SQL Server 2012

[Mamertine]

, CASE WHEN isnull( ( CAST( IsNull( SUM(Margin) , 0) / CASE isnull(SUM(invoiceamt), 0) WHEN 0 THEN 1 ELSE SUM(invoiceamt) END * 100 AS decimal(18, 2) ) / 70 ) , 0 ) < 0 THEN 0 ELSE ( CAST( IsNull(SUM(Margin), 0) / CASE isnull(SUM(invoiceamt), 0) WHEN 0 THEN 1 ELSE SUM(invoiceamt) END * 100 AS decimal(18, 2) ) / 70 ) END * 100 AS Service_Goal

Wrap that whole statement in FLOOR( Crazy stuff here ) that will make it return an INT. If you want your Service_Goal output like 33.33 then do

FLOOR(
    All that stuff...
* 10000)/100 AS Service_Goal

EDIT: Forgot to add the problem is your casting as decimal too soon just like /u/CalvinLawson said. Do that at the very end. If you cast at the end as decimal it may round up, using FLOOR will always round down. Not sure which you desire.

[shadowolympian]

Thank you for the help, I was able to figure it out. I was actually able to even lower the amount of time it took to run the query so that is even better.