2 Replies Latest reply: Jul 26, 2013 12:47 PM by Frank Kulash RSS

    Query to pull duplicates in last 15 days

    varun

      I have a table with Account and Trans_date columns. I am trying to write a query to pull the accounts that are present today and also present in the last 15 days.

       

      [SQL]

      select distinct  a.*

      from

          (

          select

                  SFD.ACCOUNT,

                  SFD.TRANS_DATE

               

          FROM CIRC_RPT.VW_SUBS_FINANCIAL_DRW SFD

               

          WHERE  SFD.trans_date between trunc(:Enter_date) -15 and trunc(:Enter_date)

             

          ) a

          join

          (

          select

                  SFD.ACCOUNT,

                  SFD.TRANS_DATE

                  FROM CIRC_RPT.VW_SUBS_FINANCIAL_DRW SFD

          WHERE SFD.trans_date = trunc(:Enter_date)

             

          )b on a.account =b.account

          order by a.account

      [\SQL]

       

      This query is pulling the duplicates but it is also pulling accounts that are only present on :Enter_date(not present in the past 15 days).

       

      Please advice on the logic. Thanks in advance for your help.

        • 1. Re: Query to pull duplicates in last 15 days
          Solomon Yakobson

          SELECT  ACCOUNT

            FROM  CIRC_RPT.VW_SUBS_FINANCIAL_DRW

            WHERE TRANS_DATE < TRUNC(:Enter_date) + 1

              AND TRANS_DATE >= TRUNC(:Enter_date) - 14

            GROUP BY ACCOUNT

            HAVING SUM(

                       DISTINCT CASE

                                  WHEN TRANS_DATE >= TRUNC(:Enter_date) THEN 1

                                  ELSE 2

                                END

                      ) = 3

          /

           

          SY.

          • 2. Re: Query to pull duplicates in last 15 days
            Frank Kulash

            Hi,

             

            I think you want something like this:

             

            SELECT  *

            FROM    circ_rpt.vw_subs_financial_drw p15

            WHERE   trans_date  >= TRUNC (SYSDATE) - 15

            AND     trans_date  <  TRUNC (SYSDATE)

            AND     EXISTS (

                               SELECT  1

                               FROM    circ_rpt.vw_subs_financial_drw

                               WHERE   account = p15.account

                               AND    trans_date = TRUNC (SYSDATE)

                           )

            ;

             

            You can use :Enter_date instead of SYSDATE, but make sure it really is a DATE.  If :enter_date is a string, then remember to use TO_DATE.

            If you do a join, you'll get duplicate rows if there happen to be 2 (or more) rows for the same account dated today.

             

            The main problem with what you posted was that the way you checked for the past 15 days:

             

            WHERE  SFD.trans_date between trunc(:Enter_date) -15 and trunc(:Enter_date)

             

            Any row that met the condition for occurring on :enter_date

             

            WHERE SFD.trans_date = trunc(:Enter_date)

             

            would also meet the other condition, since BETWEEN includes both end-points.