Post reply

single code

  • October 9, 2017 at 7:57 pm

    #339810

    Hi,

    Application is using below logic .

    (sum(CASE WHEN a.RESERVEDOLLAR > 0 THEN a.RESERVEDOLLAR*a.STR_WEIGHT ELSE NULL END)) FINANCE1

    (sum(case when a.RESERVEDOLLAR > 0 then a.STR_WEIGHT else NULL end)) FINANCE2

    Need to implement above two Logics for single column like below code.
    Below logic is not giving correct results.

       ,REPLACE(FORMAT(FLOOR
        (CASE WHEN sum (CASE WHEN sum(CASE WHEN a.RESERVEDOLLAR > 0 THEN a.STR_WEIGHT    ELSE 0    END) = 0 THEN 0
                    ELSE sum(CASE WHEN a.RESERVEDOLLAR > 0 THEN a.RESERVEDOLLAR * a.STR_WEIGHT    ELSE 0    END) /
                      sum(CASE WHEN a.RESERVEDOLLAR > 0 THEN a.STR_WEIGHT    ELSE 0    END)
            END),'C'),'.00','') AS FINANCE

    Any help please.

  • October 10, 2017 at 1:44 am

    #1962952

    Hi,
    You can try doing something like the following.
    I have used a fictitious table variable, because the structure and data from your table weren't included in the information. (if you strip it out, and do the same in your query it will hopefully work for you)

    DECLARE @TBL TABLE (RESERVEDOLLAR DECIMAL(10,2) ,STR_WEIGHT INT)

    SELECT REPLACE(FORMAT(FLOOR(CASE WHEN FINANCE2 = 0 THEN 0 ELSE FINANCE1 / FINANCE2 END),'C'),'.00','')

    FROM (

    SELECT (SUM(CASE WHEN a.RESERVEDOLLAR > 0 THEN a.RESERVEDOLLAR * a.STR_WEIGHT ELSE NULL END)) AS FINANCE1

    , (SUM(CASE WHEN a.RESERVEDOLLAR > 0 THEN a.STR_WEIGHT ELSE NULL END)) AS FINANCE2

    FROM @TBL AS a

    ) E1

    Regards,
    Curtis


Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply