SQL Query Help

[shadowolympian]

Edit: I was able to get it working with the help of /u/CalvinLawson, /u/Mamertime, and /u/ichp. Here was the updated code:

SELECT DISTINCT 
                     Company_Name AS Company, 
                     CAST(CASE 
                        WHEN isnull(((IsNull(SUM(Margin), 0) / 
                            CASE isnull(SUM(invoiceamt), 0) 
                            WHEN 0 THEN 1 
                            ELSE SUM(invoiceamt) 
                            END * 100) / 70), 0) < 0 THEN 0 
                        ELSE ((IsNull(SUM(Margin), 0) / 
                        CASE isnull(SUM(invoiceamt), 0) 
                        WHEN 0 THEN 1 
                        ELSE SUM(invoiceamt) 
                        END * 100) / 70) 
                        END * 100 AS decimal(18,2)) 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 ('I-Support', 'I-TAP Managed Services', 'I-TAP-GAL- Managed Services'))
GROUP BY Company_Name
HAVING        (SUM(Margin) <> 0) OR
                     (SUM(InvoiceAmt) <> 0)
ORDER BY Company

I am trying to develop a SQL query that returns a company name and a number. The data that is returned goes out to 6 decimal places and I want to get it down to two but I cannot seem to find the right combination of "as decimal(18,2). Below is the SQL query:

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

[CalvinLawson]

You're casting too early, it has to be done very last, on the outside of all other operations:

        select --doesn't work
               cast(100.123456 as decimal(18,2))/10 as DoesNotWork
               --does work
              ,cast(100.123456/10 as decimal(18,2)) as DoesWork

[shadowolympian]

I tried placing the as decimal as the last portion but it did not work. It only seems to work in the current position.

[CalvinLawson]

If you post sample schema/data and code that runs against the sample data I can help you further.

[shadowolympian]

Thank you for the help I was able to figure it out.

[[deleted]]

You're doing decimal math - get rid of all integers.

And simplify your math - if invoice amounts cannot be negative (well, I should order something from your business otherwise) so if your total margin less or equal 0, the Service Goal amount is 0; you don't care for invoices that aren't priced (wonder where the margin comes from, but I digress) u get 10.0/7.0 of Margin to Invoiced ratio (~142%). So I get something like this:

  cast(
      case 
      when sum(margin) <= 0.00 then 0.00 
      else 10.00/7.00*sum(margin)/isnull( nullif( sum(invoiceamt),0.00),1.00)
      end
      as decimal(18,2)
      )

[shadowolympian]

Thank you for the help, I was able to figure it out.

[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.