Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2013
    Posts
    20

    Question Unanswered: count the quantity of payments with 20000 step

    Hi all,



    my need is to count the quantity of payments with 20000 step.
    This is my Oracle code. I'm in process of moving from Oracle to SQL Server. Please, be so kind, help me to rewrite this code for SQL Server. Thanks ahead.

    WITH got_grps AS

    (

    SELECT TRUNC (sys_creation_date) AS date_creation

    , ( 1

    + FLOOR ( NVL (amount_original, 0)

    / 20000

    )

    ) * 20000 AS grp_20000

    FROM event_log

    WHERE sys_creation_date BETWEEN SYSDATE - 1000

    AND SYSDATE

    AND policy_action = 'REVIEW'

    AND NVL (amount_original, 0) >= 1

    )

    SELECT date_creation, grp_20000

    , COUNT (*) AS cnt

    FROM got_grps

    GROUP BY date_creation, grp_20000

    ORDER BY date_creation, grp_20000

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd try:
    Code:
    SELECT date_creation
    ,  20000 * Cast(amount_original / 20000 AS INT) AS grp_20000
    ,  Count(*) AS cnt
       FROM event_log
       WHERE  1000 < DateDiff(day, sys_creation_date, GetDate())
          AND 'REVIEW' = policy_action
          AND 1 < amount_original
       GROUP BY date_creation, 20000 * Cast(amount_original / 20000 AS INT)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •