Anmelden
 
HomeLibraryDownloadsSupportWebcasts & EventsCommunityForenBlogs
Ressourcen für IT-Professionals >
> 
> 
How to tweak rounding a decimal Number and avoid unwanted row generating through a query ??

Beantwortet How to tweak rounding a decimal Number and avoid unwanted row generating through a query ??

  • Freitag, 8. März 2013 00:44
     
     
    Anmelden
    0

    Hi All,

    Appreciate if some one can enlighten me to avoid last row coming out of this query please ??

    Table: FactMaster  ( TotalRefund and Monthlyrefund are defined as - Decimal(15,2)

    OrderID     Policy_ID   TotalRefund                             MonthlyRefund                           Start_Date End_Date
    ----------- ----------- --------------------------------------- --------------------------------------- ---------- ----------
    259         15          -632.93                                 -52.74                                  2012-05-20 2013-05-20

    Query:

    with Tally as (
    select top (240) row_number() over(order by a.number)-1 as n
    from master.dbo.spt_values a
    cross join master.dbo.spt_values b
    where a.type = 'P'
      and b.type = 'P'
    )

     SELECT                   
                                M.OrderID
                                ,M.Policy_ID
                                ,case t.n
                               when floor(M.TotalRefund/M.MonthlyRefund) then M.TotalRefund - ((floor(M.TotalRefund/M.MonthlyRefund))*M.MonthlyRefund)
                               when datediff(m,M.start_date,M.end_date) then M.TotalRefund - ((datediff(m,M.start_date,M.End_Date))*M.MonthlyRefund)
                               else M.MonthlyRefund
                               end as MonthlyPayment
                               ,dateadd(m,-t.n,M.End_Date) as PaymentDate
                                FROM      dbo.factMaster as M
                      INNER JOIN  Tally t
                         ON  floor(M.TotalRefund/(M.MonthlyRefund)) >= t.n
                         AND datediff(m,M.Start_Date,M.End_Date) >= t.n

    Query Result:

    OrderID     Policy_ID   MonthlyPayment                          PaymentDate
    ----------- ----------- --------------------------------------- -----------
    259         15          -52.74                                  2013-05-20
    259         15          -52.74                                  2013-04-20
    259         15          -52.74                                  2013-03-20
    259         15          -52.74                                  2013-02-20
    259         15          -52.74                                  2013-01-20
    259         15          -52.74                                  2012-12-20
    259         15          -52.74                                  2012-11-20
    259         15          -52.74                                  2012-10-20
    259         15          -52.74                                  2012-09-20
    259         15          -52.74                                  2012-08-20
    259         15          -52.74                                  2012-07-20
    259         15          -52.74                                  2012-06-20
    259         15          -0.05                                   2012-05-20

    (13 row(s) affected)

    Look at the last row coming out of this query... where MonthlyPayment is -0.05

    I don't want to see this row in the query result,, can I format decimal value  in somewhere in the query , just to get rid of this small portion of 0.05??and not to have last row at all in the query result??


    • Bearbeitet Mira Pimbi Freitag, 8. März 2013 00:47 change Table Name
    •  
     

Alle Antworten

  • Freitag, 8. März 2013 09:00
     
     Beantwortet
    Anmelden
    0

    You'll need to define some hard rules on when to include a row and when not to.  Only then can you build in the logic to remove a row like this.

    Do you know when a value is too small to display?  Would you want the small amount added to the previous MonthlyPayment?

    • Als Antwort markiert Mira Pimbi Montag, 11. März 2013 22:37
    •  
     
  • Montag, 11. März 2013 22:37
     
     
    Anmelden
    0

    Thanks Mike, actually I cannot omit small amount( 0.05 in this case ), therefore I need last row, I need to prevent updating another column, after 12 rows, which I have handled through a CASE statement now, therefore issue has been resolved....

    ,CASE WHEN t.n >= 12 THEN  0

    ELSE  ELSE -1 * CAST(1.00 AS DECIMAL) / CAST(12.00 AS DECIMAL)

    END as EPCT