Loading ...
Sorry, an error occurred while loading the content.

Please Help - Need to count duplicate records BUT exclude some exceptions

Expand Messages
  • Leslie
    I have a table of transactions which will occasionally have duplicate entries. When these duplicate entries are found, the admin will reverse the
    Message 1 of 8 , Nov 18, 2008
    • 0 Attachment
      I have a table of transactions which will occasionally have
      duplicate entries. When these duplicate entries are found, the
      admin will reverse the transactions, therefore creating a negative
      value (but the original duplicate still remains). Here's an example
      of what I'd like to do:

      **Transaction Date ; Transaction Qty ; Transaction Value ; Reversal**

      1/1/08 ; 14 ; 70.00 ; N
      1/1/08 ; 14 ; 70.00 ; N
      1/1/08 ; -14 ; -70.00 ; Y
      2/1/08 ; 17 ; 89.00 ; N
      2/15/08 ; 18 ; 95.00 ; N
      2/15/08 ; 18 ; 95.00 ; N
      3/1/08 ; 11 ; 54.00 ; N
      3/1/08 ; -11 ; -54.00 ; Y
      3/1/08 ; 11 ; 54.00 ; N
      3/1/08 ; 11 ; 54.00 ; N
      3/1/08 ; 11 ; 54.00 ; N

      Ideally, if I ran my "desired" query on the table above, I would
      receive the following result:

      **Transaction Date ; Transaction Qty ; Transaction Value ; Count**

      2/15/08 ; 18 ; 89.00 ; 2
      3/1/08 ; 11 ; 54.00 ; 3

      Does that make sense? I've already figured out how to write the
      query to give me a count of duplicates, but I can't figure out how
      to exclude duplicate records which have been "backed out" already.
      Any help would be greatly appreciated!
    • dinesh.mani@wipro.com
      Hi, Am I correct in saying, all duplicate rows are reversed by admin and those records are invalid and should not be considered as a part of the system,
      Message 2 of 8 , Nov 18, 2008
      • 0 Attachment
        Hi,



        Am I correct in saying, all duplicate rows are reversed by admin and
        those records are invalid and should not be considered as a part of the
        system, although they would remain in the database, for whatever
        business reasons you have.



        If the above understanding is correct, then all valid records have to be
        unique and hence the count is always supposed to be 1. So, I'm a bit
        unsure as to why you want to have a count column in your query?



        If you are trying to validate that all duplicate records have been
        reversed, then this operation is going to be, most likely, a manual
        operation. In that case, the simplest way would be to find the count of
        reversed records and the count of normal records and find the diff of
        the 2 counts. If it is 1 then your system is in harmony, else you have
        some problem to solve!



        Regards,



        Dinesh

        ________________________________

        From: SQLQueriesNoCode@yahoogroups.com
        [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Leslie
        Sent: Wednesday, November 19, 2008 1:40 AM
        To: SQLQueriesNoCode@yahoogroups.com
        Subject: [SQLQueriesNoCode] Please Help - Need to count duplicate
        records BUT exclude some exceptions



        I have a table of transactions which will occasionally have
        duplicate entries. When these duplicate entries are found, the
        admin will reverse the transactions, therefore creating a negative
        value (but the original duplicate still remains). Here's an example
        of what I'd like to do:

        **Transaction Date ; Transaction Qty ; Transaction Value ; Reversal**

        1/1/08 ; 14 ; 70.00 ; N
        1/1/08 ; 14 ; 70.00 ; N
        1/1/08 ; -14 ; -70.00 ; Y
        2/1/08 ; 17 ; 89.00 ; N
        2/15/08 ; 18 ; 95.00 ; N
        2/15/08 ; 18 ; 95.00 ; N
        3/1/08 ; 11 ; 54.00 ; N
        3/1/08 ; -11 ; -54.00 ; Y
        3/1/08 ; 11 ; 54.00 ; N
        3/1/08 ; 11 ; 54.00 ; N
        3/1/08 ; 11 ; 54.00 ; N

        Ideally, if I ran my "desired" query on the table above, I would
        receive the following result:

        **Transaction Date ; Transaction Qty ; Transaction Value ; Count**

        2/15/08 ; 18 ; 89.00 ; 2
        3/1/08 ; 11 ; 54.00 ; 3

        Does that make sense? I've already figured out how to write the
        query to give me a count of duplicates, but I can't figure out how
        to exclude duplicate records which have been "backed out" already.
        Any help would be greatly appreciated!





        [Non-text portions of this message have been removed]
      • Leslie
        Hi Dinesh- Thank you for your response. When duplicate records are discovered, the admin will reverse the transaction (thus leaving the duplicate transaction
        Message 3 of 8 , Nov 19, 2008
        • 0 Attachment
          Hi Dinesh-

          Thank you for your response. When duplicate records are discovered,
          the admin will reverse the transaction (thus leaving the duplicate
          transaction and an additional negative transaction in the database,
          per our regulatory requirements). That being said, I am creating a
          report that the admins can use to easily identify duplicate
          transactions since there are thousands of records created in the
          database each week.

          When they run this report, I don't want them to get tripped up over
          transactions that they have already reversed, which is why I'd like
          my report to "ignore" duplicates which already have a reversal
          record in place.

          So, it seems like I will need the manual process that you described
          in your last paragraph. That being said, I'm not sure of how they
          should compare the two reports if they don't match up exactly (with
          as many records as we have). Perhaps I'm just not understanding it
          clearly. Can you explain how you might accomplish that task?

          Thank you so much for your assistance!

          Leslie

          --- In SQLQueriesNoCode@yahoogroups.com, <dinesh.mani@...> wrote:
          >
          > Hi,
          >
          >
          >
          > Am I correct in saying, all duplicate rows are reversed by admin
          and
          > those records are invalid and should not be considered as a part
          of the
          > system, although they would remain in the database, for whatever
          > business reasons you have.
          >
          >
          >
          > If the above understanding is correct, then all valid records have
          to be
          > unique and hence the count is always supposed to be 1. So, I'm a
          bit
          > unsure as to why you want to have a count column in your query?
          >
          >
          >
          > If you are trying to validate that all duplicate records have been
          > reversed, then this operation is going to be, most likely, a manual
          > operation. In that case, the simplest way would be to find the
          count of
          > reversed records and the count of normal records and find the diff
          of
          > the 2 counts. If it is 1 then your system is in harmony, else you
          have
          > some problem to solve!
          >
          >
          >
          > Regards,
          >
          >
          >
          > Dinesh
          >
          > ________________________________
          >
          > From: SQLQueriesNoCode@yahoogroups.com
          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Leslie
          > Sent: Wednesday, November 19, 2008 1:40 AM
          > To: SQLQueriesNoCode@yahoogroups.com
          > Subject: [SQLQueriesNoCode] Please Help - Need to count duplicate
          > records BUT exclude some exceptions
          >
          >
          >
          > I have a table of transactions which will occasionally have
          > duplicate entries. When these duplicate entries are found, the
          > admin will reverse the transactions, therefore creating a negative
          > value (but the original duplicate still remains). Here's an
          example
          > of what I'd like to do:
          >
          > **Transaction Date ; Transaction Qty ; Transaction Value ;
          Reversal**
          >
          > 1/1/08 ; 14 ; 70.00 ; N
          > 1/1/08 ; 14 ; 70.00 ; N
          > 1/1/08 ; -14 ; -70.00 ; Y
          > 2/1/08 ; 17 ; 89.00 ; N
          > 2/15/08 ; 18 ; 95.00 ; N
          > 2/15/08 ; 18 ; 95.00 ; N
          > 3/1/08 ; 11 ; 54.00 ; N
          > 3/1/08 ; -11 ; -54.00 ; Y
          > 3/1/08 ; 11 ; 54.00 ; N
          > 3/1/08 ; 11 ; 54.00 ; N
          > 3/1/08 ; 11 ; 54.00 ; N
          >
          > Ideally, if I ran my "desired" query on the table above, I would
          > receive the following result:
          >
          > **Transaction Date ; Transaction Qty ; Transaction Value ; Count**
          >
          > 2/15/08 ; 18 ; 89.00 ; 2
          > 3/1/08 ; 11 ; 54.00 ; 3
          >
          > Does that make sense? I've already figured out how to write the
          > query to give me a count of duplicates, but I can't figure out how
          > to exclude duplicate records which have been "backed out" already.
          > Any help would be greatly appreciated!
          >
          >
          >
          >
          >
          > [Non-text portions of this message have been removed]
          >
        • John Warner
          One improvement to your process if I may, when a reversal is made, mark the transaction that is being reversed as well. Your problem is you cannot tell a
          Message 4 of 8 , Nov 19, 2008
          • 0 Attachment
            One improvement to your process if I may, when a reversal is made, mark
            the transaction that is being reversed as well. Your problem is you cannot
            tell a reversed transaction from one that has not been. You have a flag
            but it is not being used properly. The reversal and the reversed should
            trigger this flag. A Tristate flag might be a better way to go then a
            Boolean but that is something you would want to consider.
            Reversal
            1 --any new transaction
            2 --reversal of duplicate
            3 --reversed duplicate transaction

            Or without altering existing type
            No --Any new transaction
            Yes --reversal
            Yes --reversed duplicate transaction

            Now you can see what you have in the data.

            As it is now how do the admins know they have reversed everything or
            reversed non dup transactions?

            John Warner


            > -----Original Message-----
            > From: SQLQueriesNoCode@yahoogroups.com
            > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Leslie
            > Sent: Wednesday, November 19, 2008 9:04 AM
            > To: SQLQueriesNoCode@yahoogroups.com
            > Subject: [SQLQueriesNoCode] Re: Please Help - Need to count duplicate
            > records BUT exclude some exceptions
            >
            > Hi Dinesh-
            >
            > Thank you for your response. When duplicate records are discovered,
            > the admin will reverse the transaction (thus leaving the duplicate
            > transaction and an additional negative transaction in the database,
            > per our regulatory requirements). That being said, I am creating a
            > report that the admins can use to easily identify duplicate
            > transactions since there are thousands of records created in the
            > database each week.
            >
            > When they run this report, I don't want them to get tripped up over
            > transactions that they have already reversed, which is why I'd like
            > my report to "ignore" duplicates which already have a reversal
            > record in place.
            >
            > So, it seems like I will need the manual process that you described
            > in your last paragraph. That being said, I'm not sure of how they
            > should compare the two reports if they don't match up exactly (with
            > as many records as we have). Perhaps I'm just not understanding it
            > clearly. Can you explain how you might accomplish that task?
            >
            > Thank you so much for your assistance!
            >
            > Leslie
            >
            > --- In SQLQueriesNoCode@yahoogroups.com, <dinesh.mani@...> wrote:
            > >
            > > Hi,
            > >
            > >
            > >
            > > Am I correct in saying, all duplicate rows are reversed by admin
            > and
            > > those records are invalid and should not be considered as a part
            > of the
            > > system, although they would remain in the database, for whatever
            > > business reasons you have.
            > >
            > >
            > >
            > > If the above understanding is correct, then all valid records have
            > to be
            > > unique and hence the count is always supposed to be 1. So, I'm a
            > bit
            > > unsure as to why you want to have a count column in your query?
            > >
            > >
            > >
            > > If you are trying to validate that all duplicate records have been
            > > reversed, then this operation is going to be, most likely, a manual
            > > operation. In that case, the simplest way would be to find the
            > count of
            > > reversed records and the count of normal records and find the diff
            > of
            > > the 2 counts. If it is 1 then your system is in harmony, else you
            > have
            > > some problem to solve!
            > >
            > >
            > >
            > > Regards,
            > >
            > >
            > >
            > > Dinesh
            > >
            > > ________________________________
            > >
            > > From: SQLQueriesNoCode@yahoogroups.com
            > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Leslie
            > > Sent: Wednesday, November 19, 2008 1:40 AM
            > > To: SQLQueriesNoCode@yahoogroups.com
            > > Subject: [SQLQueriesNoCode] Please Help - Need to count duplicate
            > > records BUT exclude some exceptions
            > >
            > >
            > >
            > > I have a table of transactions which will occasionally have
            > > duplicate entries. When these duplicate entries are found, the
            > > admin will reverse the transactions, therefore creating a negative
            > > value (but the original duplicate still remains). Here's an
            > example
            > > of what I'd like to do:
            > >
            > > **Transaction Date ; Transaction Qty ; Transaction Value ;
            > Reversal**
            > >
            > > 1/1/08 ; 14 ; 70.00 ; N
            > > 1/1/08 ; 14 ; 70.00 ; N
            > > 1/1/08 ; -14 ; -70.00 ; Y
            > > 2/1/08 ; 17 ; 89.00 ; N
            > > 2/15/08 ; 18 ; 95.00 ; N
            > > 2/15/08 ; 18 ; 95.00 ; N
            > > 3/1/08 ; 11 ; 54.00 ; N
            > > 3/1/08 ; -11 ; -54.00 ; Y
            > > 3/1/08 ; 11 ; 54.00 ; N
            > > 3/1/08 ; 11 ; 54.00 ; N
            > > 3/1/08 ; 11 ; 54.00 ; N
            > >
            > > Ideally, if I ran my "desired" query on the table above, I would
            > > receive the following result:
            > >
            > > **Transaction Date ; Transaction Qty ; Transaction Value ; Count**
            > >
            > > 2/15/08 ; 18 ; 89.00 ; 2
            > > 3/1/08 ; 11 ; 54.00 ; 3
            > >
            > > Does that make sense? I've already figured out how to write the
            > > query to give me a count of duplicates, but I can't figure out how
            > > to exclude duplicate records which have been "backed out" already.
            > > Any help would be greatly appreciated!
            > >
            > >
            > >
            > >
            > >
            > > [Non-text portions of this message have been removed]
            > >
            >
            >
            >
            > ------------------------------------
            >
            > Yahoo! Groups Links
            >
            >
            >
          • Leslie
            Hi John- Thank you for your assistance. I definitely agree that your process improvement is a great idea. That being said, this data is being populated using
            Message 5 of 8 , Nov 19, 2008
            • 0 Attachment
              Hi John-

              Thank you for your assistance. I definitely agree that your process
              improvement is a great idea. That being said, this data is being
              populated using a vendor application and we cannot alter the code
              (from what I understand). In addition, I'm not actually in IST
              (although I was at one time) and I don't have the authority to make
              those changes... I'm simply writing a report for my admins. This is
              unfortunate because I really like the way you described the process
              and it would make much more sense!

              The admins don't really have a good clear-cut way of seeing which
              transactions have been reversed... But I'm hoping that this report
              will help.

              Since I am not able to alter the code or the actual data in the
              database, do you think it will be possible to do what I'm trying to
              do?

              --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@...>
              wrote:
              >
              > One improvement to your process if I may, when a reversal is made,
              mark
              > the transaction that is being reversed as well. Your problem is
              you cannot
              > tell a reversed transaction from one that has not been. You have a
              flag
              > but it is not being used properly. The reversal and the reversed
              should
              > trigger this flag. A Tristate flag might be a better way to go
              then a
              > Boolean but that is something you would want to consider.
              > Reversal
              > 1 --any new transaction
              > 2 --reversal of duplicate
              > 3 --reversed duplicate transaction
              >
              > Or without altering existing type
              > No --Any new transaction
              > Yes --reversal
              > Yes --reversed duplicate transaction
              >
              > Now you can see what you have in the data.
              >
              > As it is now how do the admins know they have reversed everything
              or
              > reversed non dup transactions?
              >
              > John Warner
              >
              >
              > > -----Original Message-----
              > > From: SQLQueriesNoCode@yahoogroups.com
              > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Leslie
              > > Sent: Wednesday, November 19, 2008 9:04 AM
              > > To: SQLQueriesNoCode@yahoogroups.com
              > > Subject: [SQLQueriesNoCode] Re: Please Help - Need to count
              duplicate
              > > records BUT exclude some exceptions
              > >
              > > Hi Dinesh-
              > >
              > > Thank you for your response. When duplicate records are
              discovered,
              > > the admin will reverse the transaction (thus leaving the
              duplicate
              > > transaction and an additional negative transaction in the
              database,
              > > per our regulatory requirements). That being said, I am
              creating a
              > > report that the admins can use to easily identify duplicate
              > > transactions since there are thousands of records created in the
              > > database each week.
              > >
              > > When they run this report, I don't want them to get tripped up
              over
              > > transactions that they have already reversed, which is why I'd
              like
              > > my report to "ignore" duplicates which already have a reversal
              > > record in place.
              > >
              > > So, it seems like I will need the manual process that you
              described
              > > in your last paragraph. That being said, I'm not sure of how
              they
              > > should compare the two reports if they don't match up exactly
              (with
              > > as many records as we have). Perhaps I'm just not understanding
              it
              > > clearly. Can you explain how you might accomplish that task?
              > >
              > > Thank you so much for your assistance!
              > >
              > > Leslie
              > >
              > > --- In SQLQueriesNoCode@yahoogroups.com, <dinesh.mani@> wrote:
              > > >
              > > > Hi,
              > > >
              > > >
              > > >
              > > > Am I correct in saying, all duplicate rows are reversed by
              admin
              > > and
              > > > those records are invalid and should not be considered as a
              part
              > > of the
              > > > system, although they would remain in the database, for
              whatever
              > > > business reasons you have.
              > > >
              > > >
              > > >
              > > > If the above understanding is correct, then all valid records
              have
              > > to be
              > > > unique and hence the count is always supposed to be 1. So, I'm
              a
              > > bit
              > > > unsure as to why you want to have a count column in your query?
              > > >
              > > >
              > > >
              > > > If you are trying to validate that all duplicate records have
              been
              > > > reversed, then this operation is going to be, most likely, a
              manual
              > > > operation. In that case, the simplest way would be to find the
              > > count of
              > > > reversed records and the count of normal records and find the
              diff
              > > of
              > > > the 2 counts. If it is 1 then your system is in harmony, else
              you
              > > have
              > > > some problem to solve!
              > > >
              > > >
              > > >
              > > > Regards,
              > > >
              > > >
              > > >
              > > > Dinesh
              > > >
              > > > ________________________________
              > > >
              > > > From: SQLQueriesNoCode@yahoogroups.com
              > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Leslie
              > > > Sent: Wednesday, November 19, 2008 1:40 AM
              > > > To: SQLQueriesNoCode@yahoogroups.com
              > > > Subject: [SQLQueriesNoCode] Please Help - Need to count
              duplicate
              > > > records BUT exclude some exceptions
              > > >
              > > >
              > > >
              > > > I have a table of transactions which will occasionally have
              > > > duplicate entries. When these duplicate entries are found, the
              > > > admin will reverse the transactions, therefore creating a
              negative
              > > > value (but the original duplicate still remains). Here's an
              > > example
              > > > of what I'd like to do:
              > > >
              > > > **Transaction Date ; Transaction Qty ; Transaction Value ;
              > > Reversal**
              > > >
              > > > 1/1/08 ; 14 ; 70.00 ; N
              > > > 1/1/08 ; 14 ; 70.00 ; N
              > > > 1/1/08 ; -14 ; -70.00 ; Y
              > > > 2/1/08 ; 17 ; 89.00 ; N
              > > > 2/15/08 ; 18 ; 95.00 ; N
              > > > 2/15/08 ; 18 ; 95.00 ; N
              > > > 3/1/08 ; 11 ; 54.00 ; N
              > > > 3/1/08 ; -11 ; -54.00 ; Y
              > > > 3/1/08 ; 11 ; 54.00 ; N
              > > > 3/1/08 ; 11 ; 54.00 ; N
              > > > 3/1/08 ; 11 ; 54.00 ; N
              > > >
              > > > Ideally, if I ran my "desired" query on the table above, I
              would
              > > > receive the following result:
              > > >
              > > > **Transaction Date ; Transaction Qty ; Transaction Value ;
              Count**
              > > >
              > > > 2/15/08 ; 18 ; 89.00 ; 2
              > > > 3/1/08 ; 11 ; 54.00 ; 3
              > > >
              > > > Does that make sense? I've already figured out how to write the
              > > > query to give me a count of duplicates, but I can't figure out
              how
              > > > to exclude duplicate records which have been "backed out"
              already.
              > > > Any help would be greatly appreciated!
              > > >
              > > >
              > > >
              > > >
              > > >
              > > > [Non-text portions of this message have been removed]
              > > >
              > >
              > >
              > >
              > > ------------------------------------
              > >
              > > Yahoo! Groups Links
              > >
              > >
              > >
              >
            • Leslie
              I have the solution! For the sake of others who may refer to this post in the future, here is the query: select dt, abs(qty), abs(val), sum(case when
              Message 6 of 8 , Nov 19, 2008
              • 0 Attachment
                I have the solution! For the sake of others who may refer to this
                post in the future, here is the query:


                select dt, abs(qty), abs(val),
                sum(case when reversal='Y' then -1 else 1 end) as count
                from transactions
                group by dt, abs(qty), abs(val)
                having sum(case when reversal='Y' then -1 else 1 end) > 1


                Thanks to you all.


                --- In SQLQueriesNoCode@yahoogroups.com, "Leslie"
                <lesliemcfarland@...> wrote:
                >
                > Hi John-
                >
                > Thank you for your assistance. I definitely agree that your
                process
                > improvement is a great idea. That being said, this data is being
                > populated using a vendor application and we cannot alter the code
                > (from what I understand). In addition, I'm not actually in IST
                > (although I was at one time) and I don't have the authority to
                make
                > those changes... I'm simply writing a report for my admins. This
                is
                > unfortunate because I really like the way you described the
                process
                > and it would make much more sense!
                >
                > The admins don't really have a good clear-cut way of seeing which
                > transactions have been reversed... But I'm hoping that this
                report
                > will help.
                >
                > Since I am not able to alter the code or the actual data in the
                > database, do you think it will be possible to do what I'm trying
                to
                > do?
                >
                > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@>
                > wrote:
                > >
                > > One improvement to your process if I may, when a reversal is
                made,
                > mark
                > > the transaction that is being reversed as well. Your problem is
                > you cannot
                > > tell a reversed transaction from one that has not been. You have
                a
                > flag
                > > but it is not being used properly. The reversal and the reversed
                > should
                > > trigger this flag. A Tristate flag might be a better way to go
                > then a
                > > Boolean but that is something you would want to consider.
                > > Reversal
                > > 1 --any new transaction
                > > 2 --reversal of duplicate
                > > 3 --reversed duplicate transaction
                > >
                > > Or without altering existing type
                > > No --Any new transaction
                > > Yes --reversal
                > > Yes --reversed duplicate transaction
                > >
                > > Now you can see what you have in the data.
                > >
                > > As it is now how do the admins know they have reversed
                everything
                > or
                > > reversed non dup transactions?
                > >
                > > John Warner
                > >
                > >
                > > > -----Original Message-----
                > > > From: SQLQueriesNoCode@yahoogroups.com
                > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Leslie
                > > > Sent: Wednesday, November 19, 2008 9:04 AM
                > > > To: SQLQueriesNoCode@yahoogroups.com
                > > > Subject: [SQLQueriesNoCode] Re: Please Help - Need to count
                > duplicate
                > > > records BUT exclude some exceptions
                > > >
                > > > Hi Dinesh-
                > > >
                > > > Thank you for your response. When duplicate records are
                > discovered,
                > > > the admin will reverse the transaction (thus leaving the
                > duplicate
                > > > transaction and an additional negative transaction in the
                > database,
                > > > per our regulatory requirements). That being said, I am
                > creating a
                > > > report that the admins can use to easily identify duplicate
                > > > transactions since there are thousands of records created in
                the
                > > > database each week.
                > > >
                > > > When they run this report, I don't want them to get tripped up
                > over
                > > > transactions that they have already reversed, which is why I'd
                > like
                > > > my report to "ignore" duplicates which already have a reversal
                > > > record in place.
                > > >
                > > > So, it seems like I will need the manual process that you
                > described
                > > > in your last paragraph. That being said, I'm not sure of how
                > they
                > > > should compare the two reports if they don't match up exactly
                > (with
                > > > as many records as we have). Perhaps I'm just not
                understanding
                > it
                > > > clearly. Can you explain how you might accomplish that task?
                > > >
                > > > Thank you so much for your assistance!
                > > >
                > > > Leslie
                > > >
                > > > --- In SQLQueriesNoCode@yahoogroups.com, <dinesh.mani@> wrote:
                > > > >
                > > > > Hi,
                > > > >
                > > > >
                > > > >
                > > > > Am I correct in saying, all duplicate rows are reversed by
                > admin
                > > > and
                > > > > those records are invalid and should not be considered as a
                > part
                > > > of the
                > > > > system, although they would remain in the database, for
                > whatever
                > > > > business reasons you have.
                > > > >
                > > > >
                > > > >
                > > > > If the above understanding is correct, then all valid
                records
                > have
                > > > to be
                > > > > unique and hence the count is always supposed to be 1. So,
                I'm
                > a
                > > > bit
                > > > > unsure as to why you want to have a count column in your
                query?
                > > > >
                > > > >
                > > > >
                > > > > If you are trying to validate that all duplicate records
                have
                > been
                > > > > reversed, then this operation is going to be, most likely, a
                > manual
                > > > > operation. In that case, the simplest way would be to find
                the
                > > > count of
                > > > > reversed records and the count of normal records and find
                the
                > diff
                > > > of
                > > > > the 2 counts. If it is 1 then your system is in harmony,
                else
                > you
                > > > have
                > > > > some problem to solve!
                > > > >
                > > > >
                > > > >
                > > > > Regards,
                > > > >
                > > > >
                > > > >
                > > > > Dinesh
                > > > >
                > > > > ________________________________
                > > > >
                > > > > From: SQLQueriesNoCode@yahoogroups.com
                > > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Leslie
                > > > > Sent: Wednesday, November 19, 2008 1:40 AM
                > > > > To: SQLQueriesNoCode@yahoogroups.com
                > > > > Subject: [SQLQueriesNoCode] Please Help - Need to count
                > duplicate
                > > > > records BUT exclude some exceptions
                > > > >
                > > > >
                > > > >
                > > > > I have a table of transactions which will occasionally have
                > > > > duplicate entries. When these duplicate entries are found,
                the
                > > > > admin will reverse the transactions, therefore creating a
                > negative
                > > > > value (but the original duplicate still remains). Here's an
                > > > example
                > > > > of what I'd like to do:
                > > > >
                > > > > **Transaction Date ; Transaction Qty ; Transaction Value ;
                > > > Reversal**
                > > > >
                > > > > 1/1/08 ; 14 ; 70.00 ; N
                > > > > 1/1/08 ; 14 ; 70.00 ; N
                > > > > 1/1/08 ; -14 ; -70.00 ; Y
                > > > > 2/1/08 ; 17 ; 89.00 ; N
                > > > > 2/15/08 ; 18 ; 95.00 ; N
                > > > > 2/15/08 ; 18 ; 95.00 ; N
                > > > > 3/1/08 ; 11 ; 54.00 ; N
                > > > > 3/1/08 ; -11 ; -54.00 ; Y
                > > > > 3/1/08 ; 11 ; 54.00 ; N
                > > > > 3/1/08 ; 11 ; 54.00 ; N
                > > > > 3/1/08 ; 11 ; 54.00 ; N
                > > > >
                > > > > Ideally, if I ran my "desired" query on the table above, I
                > would
                > > > > receive the following result:
                > > > >
                > > > > **Transaction Date ; Transaction Qty ; Transaction Value ;
                > Count**
                > > > >
                > > > > 2/15/08 ; 18 ; 89.00 ; 2
                > > > > 3/1/08 ; 11 ; 54.00 ; 3
                > > > >
                > > > > Does that make sense? I've already figured out how to write
                the
                > > > > query to give me a count of duplicates, but I can't figure
                out
                > how
                > > > > to exclude duplicate records which have been "backed out"
                > already.
                > > > > Any help would be greatly appreciated!
                > > > >
                > > > >
                > > > >
                > > > >
                > > > >
                > > > > [Non-text portions of this message have been removed]
                > > > >
                > > >
                > > >
                > > >
                > > > ------------------------------------
                > > >
                > > > Yahoo! Groups Links
                > > >
                > > >
                > > >
                > >
                >
              • John Warner
                I m not sure as I have no idea how to tell a reversed transaction from one that has not. Will the new transaction, the duplicate, and the reversal always have
                Message 7 of 8 , Nov 19, 2008
                • 0 Attachment
                  I'm not sure as I have no idea how to tell a reversed transaction from one
                  that has not. Will the new transaction, the duplicate, and the reversal
                  always have the same date? Approximately (your best guess) how many
                  transactions a day are there?

                  What idiot designed this program such that duplicates of the same
                  transaction are allowed in the first place?

                  John Warner


                  > -----Original Message-----
                  > From: SQLQueriesNoCode@yahoogroups.com
                  > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Leslie
                  > Sent: Wednesday, November 19, 2008 9:34 AM
                  > To: SQLQueriesNoCode@yahoogroups.com
                  > Subject: [SQLQueriesNoCode] Re: Please Help - Need to count duplicate
                  > records BUT exclude some exceptions
                  >
                  > Hi John-
                  >
                  > Thank you for your assistance. I definitely agree that your process
                  > improvement is a great idea. That being said, this data is being
                  > populated using a vendor application and we cannot alter the code
                  > (from what I understand). In addition, I'm not actually in IST
                  > (although I was at one time) and I don't have the authority to make
                  > those changes... I'm simply writing a report for my admins. This is
                  > unfortunate because I really like the way you described the process
                  > and it would make much more sense!
                  >
                  > The admins don't really have a good clear-cut way of seeing which
                  > transactions have been reversed... But I'm hoping that this report
                  > will help.
                  >
                  > Since I am not able to alter the code or the actual data in the
                  > database, do you think it will be possible to do what I'm trying to
                  > do?
                  >
                  > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@...>
                  > wrote:
                  > >
                  > > One improvement to your process if I may, when a reversal is made,
                  > mark
                  > > the transaction that is being reversed as well. Your problem is
                  > you cannot
                  > > tell a reversed transaction from one that has not been. You have a
                  > flag
                  > > but it is not being used properly. The reversal and the reversed
                  > should
                  > > trigger this flag. A Tristate flag might be a better way to go
                  > then a
                  > > Boolean but that is something you would want to consider.
                  > > Reversal
                  > > 1 --any new transaction
                  > > 2 --reversal of duplicate
                  > > 3 --reversed duplicate transaction
                  > >
                  > > Or without altering existing type
                  > > No --Any new transaction
                  > > Yes --reversal
                  > > Yes --reversed duplicate transaction
                  > >
                  > > Now you can see what you have in the data.
                  > >
                  > > As it is now how do the admins know they have reversed everything
                  > or
                  > > reversed non dup transactions?
                  > >
                  > > John Warner
                  > >
                  > >
                  > > > -----Original Message-----
                  > > > From: SQLQueriesNoCode@yahoogroups.com
                  > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Leslie
                  > > > Sent: Wednesday, November 19, 2008 9:04 AM
                  > > > To: SQLQueriesNoCode@yahoogroups.com
                  > > > Subject: [SQLQueriesNoCode] Re: Please Help - Need to count
                  > duplicate
                  > > > records BUT exclude some exceptions
                  > > >
                  > > > Hi Dinesh-
                  > > >
                  > > > Thank you for your response. When duplicate records are
                  > discovered,
                  > > > the admin will reverse the transaction (thus leaving the
                  > duplicate
                  > > > transaction and an additional negative transaction in the
                  > database,
                  > > > per our regulatory requirements). That being said, I am
                  > creating a
                  > > > report that the admins can use to easily identify duplicate
                  > > > transactions since there are thousands of records created in the
                  > > > database each week.
                  > > >
                  > > > When they run this report, I don't want them to get tripped up
                  > over
                  > > > transactions that they have already reversed, which is why I'd
                  > like
                  > > > my report to "ignore" duplicates which already have a reversal
                  > > > record in place.
                  > > >
                  > > > So, it seems like I will need the manual process that you
                  > described
                  > > > in your last paragraph. That being said, I'm not sure of how
                  > they
                  > > > should compare the two reports if they don't match up exactly
                  > (with
                  > > > as many records as we have). Perhaps I'm just not understanding
                  > it
                  > > > clearly. Can you explain how you might accomplish that task?
                  > > >
                  > > > Thank you so much for your assistance!
                  > > >
                  > > > Leslie
                  > > >
                  > > > --- In SQLQueriesNoCode@yahoogroups.com, <dinesh.mani@> wrote:
                  > > > >
                  > > > > Hi,
                  > > > >
                  > > > >
                  > > > >
                  > > > > Am I correct in saying, all duplicate rows are reversed by
                  > admin
                  > > > and
                  > > > > those records are invalid and should not be considered as a
                  > part
                  > > > of the
                  > > > > system, although they would remain in the database, for
                  > whatever
                  > > > > business reasons you have.
                  > > > >
                  > > > >
                  > > > >
                  > > > > If the above understanding is correct, then all valid records
                  > have
                  > > > to be
                  > > > > unique and hence the count is always supposed to be 1. So, I'm
                  > a
                  > > > bit
                  > > > > unsure as to why you want to have a count column in your query?
                  > > > >
                  > > > >
                  > > > >
                  > > > > If you are trying to validate that all duplicate records have
                  > been
                  > > > > reversed, then this operation is going to be, most likely, a
                  > manual
                  > > > > operation. In that case, the simplest way would be to find the
                  > > > count of
                  > > > > reversed records and the count of normal records and find the
                  > diff
                  > > > of
                  > > > > the 2 counts. If it is 1 then your system is in harmony, else
                  > you
                  > > > have
                  > > > > some problem to solve!
                  > > > >
                  > > > >
                  > > > >
                  > > > > Regards,
                  > > > >
                  > > > >
                  > > > >
                  > > > > Dinesh
                  > > > >
                  > > > > ________________________________
                  > > > >
                  > > > > From: SQLQueriesNoCode@yahoogroups.com
                  > > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Leslie
                  > > > > Sent: Wednesday, November 19, 2008 1:40 AM
                  > > > > To: SQLQueriesNoCode@yahoogroups.com
                  > > > > Subject: [SQLQueriesNoCode] Please Help - Need to count
                  > duplicate
                  > > > > records BUT exclude some exceptions
                  > > > >
                  > > > >
                  > > > >
                  > > > > I have a table of transactions which will occasionally have
                  > > > > duplicate entries. When these duplicate entries are found, the
                  > > > > admin will reverse the transactions, therefore creating a
                  > negative
                  > > > > value (but the original duplicate still remains). Here's an
                  > > > example
                  > > > > of what I'd like to do:
                  > > > >
                  > > > > **Transaction Date ; Transaction Qty ; Transaction Value ;
                  > > > Reversal**
                  > > > >
                  > > > > 1/1/08 ; 14 ; 70.00 ; N
                  > > > > 1/1/08 ; 14 ; 70.00 ; N
                  > > > > 1/1/08 ; -14 ; -70.00 ; Y
                  > > > > 2/1/08 ; 17 ; 89.00 ; N
                  > > > > 2/15/08 ; 18 ; 95.00 ; N
                  > > > > 2/15/08 ; 18 ; 95.00 ; N
                  > > > > 3/1/08 ; 11 ; 54.00 ; N
                  > > > > 3/1/08 ; -11 ; -54.00 ; Y
                  > > > > 3/1/08 ; 11 ; 54.00 ; N
                  > > > > 3/1/08 ; 11 ; 54.00 ; N
                  > > > > 3/1/08 ; 11 ; 54.00 ; N
                  > > > >
                  > > > > Ideally, if I ran my "desired" query on the table above, I
                  > would
                  > > > > receive the following result:
                  > > > >
                  > > > > **Transaction Date ; Transaction Qty ; Transaction Value ;
                  > Count**
                  > > > >
                  > > > > 2/15/08 ; 18 ; 89.00 ; 2
                  > > > > 3/1/08 ; 11 ; 54.00 ; 3
                  > > > >
                  > > > > Does that make sense? I've already figured out how to write the
                  > > > > query to give me a count of duplicates, but I can't figure out
                  > how
                  > > > > to exclude duplicate records which have been "backed out"
                  > already.
                  > > > > Any help would be greatly appreciated!
                  > > > >
                  > > > >
                  > > > >
                  > > > >
                  > > > >
                  > > > > [Non-text portions of this message have been removed]
                  > > > >
                  > > >
                  > > >
                  > > >
                  > > > ------------------------------------
                  > > >
                  > > > Yahoo! Groups Links
                  > > >
                  > > >
                  > > >
                  > >
                  >
                  >
                  >
                  > ------------------------------------
                  >
                  > Yahoo! Groups Links
                  >
                  >
                  >
                • dinesh.mani@wipro.com
                  Congrats on finding the solution, but I think you need to push the suggestion that John has made as a possible enhancement. That would make more sense and
                  Message 8 of 8 , Nov 19, 2008
                  • 0 Attachment
                    Congrats on finding the solution, but I think you need to push the
                    suggestion that John has made as a possible enhancement. That would make
                    more sense and would help I the long term.



                    Regards,



                    Dinesh

                    ________________________________

                    From: SQLQueriesNoCode@yahoogroups.com
                    [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Leslie
                    Sent: Wednesday, November 19, 2008 8:32 PM
                    To: SQLQueriesNoCode@yahoogroups.com
                    Subject: [SQLQueriesNoCode] Re: Please Help - Need to count duplicate
                    records BUT exclude some exceptions



                    I have the solution! For the sake of others who may refer to this
                    post in the future, here is the query:

                    select dt, abs(qty), abs(val),
                    sum(case when reversal='Y' then -1 else 1 end) as count
                    from transactions
                    group by dt, abs(qty), abs(val)
                    having sum(case when reversal='Y' then -1 else 1 end) > 1

                    Thanks to you all.

                    --- In SQLQueriesNoCode@yahoogroups.com
                    <mailto:SQLQueriesNoCode%40yahoogroups.com> , "Leslie"
                    <lesliemcfarland@...> wrote:
                    >
                    > Hi John-
                    >
                    > Thank you for your assistance. I definitely agree that your
                    process
                    > improvement is a great idea. That being said, this data is being
                    > populated using a vendor application and we cannot alter the code
                    > (from what I understand). In addition, I'm not actually in IST
                    > (although I was at one time) and I don't have the authority to
                    make
                    > those changes... I'm simply writing a report for my admins. This
                    is
                    > unfortunate because I really like the way you described the
                    process
                    > and it would make much more sense!
                    >
                    > The admins don't really have a good clear-cut way of seeing which
                    > transactions have been reversed... But I'm hoping that this
                    report
                    > will help.
                    >
                    > Since I am not able to alter the code or the actual data in the
                    > database, do you think it will be possible to do what I'm trying
                    to
                    > do?
                    >
                    > --- In SQLQueriesNoCode@yahoogroups.com
                    <mailto:SQLQueriesNoCode%40yahoogroups.com> , "John Warner" <john@>
                    > wrote:
                    > >
                    > > One improvement to your process if I may, when a reversal is
                    made,
                    > mark
                    > > the transaction that is being reversed as well. Your problem is
                    > you cannot
                    > > tell a reversed transaction from one that has not been. You have
                    a
                    > flag
                    > > but it is not being used properly. The reversal and the reversed
                    > should
                    > > trigger this flag. A Tristate flag might be a better way to go
                    > then a
                    > > Boolean but that is something you would want to consider.
                    > > Reversal
                    > > 1 --any new transaction
                    > > 2 --reversal of duplicate
                    > > 3 --reversed duplicate transaction
                    > >
                    > > Or without altering existing type
                    > > No --Any new transaction
                    > > Yes --reversal
                    > > Yes --reversed duplicate transaction
                    > >
                    > > Now you can see what you have in the data.
                    > >
                    > > As it is now how do the admins know they have reversed
                    everything
                    > or
                    > > reversed non dup transactions?
                    > >
                    > > John Warner
                    > >
                    > >
                    > > > -----Original Message-----
                    > > > From: SQLQueriesNoCode@yahoogroups.com
                    <mailto:SQLQueriesNoCode%40yahoogroups.com>
                    > > > [mailto:SQLQueriesNoCode@yahoogroups.com
                    <mailto:SQLQueriesNoCode%40yahoogroups.com> ] On Behalf Of Leslie
                    > > > Sent: Wednesday, November 19, 2008 9:04 AM
                    > > > To: SQLQueriesNoCode@yahoogroups.com
                    <mailto:SQLQueriesNoCode%40yahoogroups.com>
                    > > > Subject: [SQLQueriesNoCode] Re: Please Help - Need to count
                    > duplicate
                    > > > records BUT exclude some exceptions
                    > > >
                    > > > Hi Dinesh-
                    > > >
                    > > > Thank you for your response. When duplicate records are
                    > discovered,
                    > > > the admin will reverse the transaction (thus leaving the
                    > duplicate
                    > > > transaction and an additional negative transaction in the
                    > database,
                    > > > per our regulatory requirements). That being said, I am
                    > creating a
                    > > > report that the admins can use to easily identify duplicate
                    > > > transactions since there are thousands of records created in
                    the
                    > > > database each week.
                    > > >
                    > > > When they run this report, I don't want them to get tripped up
                    > over
                    > > > transactions that they have already reversed, which is why I'd
                    > like
                    > > > my report to "ignore" duplicates which already have a reversal
                    > > > record in place.
                    > > >
                    > > > So, it seems like I will need the manual process that you
                    > described
                    > > > in your last paragraph. That being said, I'm not sure of how
                    > they
                    > > > should compare the two reports if they don't match up exactly
                    > (with
                    > > > as many records as we have). Perhaps I'm just not
                    understanding
                    > it
                    > > > clearly. Can you explain how you might accomplish that task?
                    > > >
                    > > > Thank you so much for your assistance!
                    > > >
                    > > > Leslie
                    > > >
                    > > > --- In SQLQueriesNoCode@yahoogroups.com
                    <mailto:SQLQueriesNoCode%40yahoogroups.com> , <dinesh.mani@> wrote:
                    > > > >
                    > > > > Hi,
                    > > > >
                    > > > >
                    > > > >
                    > > > > Am I correct in saying, all duplicate rows are reversed by
                    > admin
                    > > > and
                    > > > > those records are invalid and should not be considered as a
                    > part
                    > > > of the
                    > > > > system, although they would remain in the database, for
                    > whatever
                    > > > > business reasons you have.
                    > > > >
                    > > > >
                    > > > >
                    > > > > If the above understanding is correct, then all valid
                    records
                    > have
                    > > > to be
                    > > > > unique and hence the count is always supposed to be 1. So,
                    I'm
                    > a
                    > > > bit
                    > > > > unsure as to why you want to have a count column in your
                    query?
                    > > > >
                    > > > >
                    > > > >
                    > > > > If you are trying to validate that all duplicate records
                    have
                    > been
                    > > > > reversed, then this operation is going to be, most likely, a
                    > manual
                    > > > > operation. In that case, the simplest way would be to find
                    the
                    > > > count of
                    > > > > reversed records and the count of normal records and find
                    the
                    > diff
                    > > > of
                    > > > > the 2 counts. If it is 1 then your system is in harmony,
                    else
                    > you
                    > > > have
                    > > > > some problem to solve!
                    > > > >
                    > > > >
                    > > > >
                    > > > > Regards,
                    > > > >
                    > > > >
                    > > > >
                    > > > > Dinesh
                    > > > >
                    > > > > ________________________________
                    > > > >
                    > > > > From: SQLQueriesNoCode@yahoogroups.com
                    <mailto:SQLQueriesNoCode%40yahoogroups.com>
                    > > > > [mailto:SQLQueriesNoCode@yahoogroups.com
                    <mailto:SQLQueriesNoCode%40yahoogroups.com> ] On Behalf Of Leslie
                    > > > > Sent: Wednesday, November 19, 2008 1:40 AM
                    > > > > To: SQLQueriesNoCode@yahoogroups.com
                    <mailto:SQLQueriesNoCode%40yahoogroups.com>
                    > > > > Subject: [SQLQueriesNoCode] Please Help - Need to count
                    > duplicate
                    > > > > records BUT exclude some exceptions
                    > > > >
                    > > > >
                    > > > >
                    > > > > I have a table of transactions which will occasionally have
                    > > > > duplicate entries. When these duplicate entries are found,
                    the
                    > > > > admin will reverse the transactions, therefore creating a
                    > negative
                    > > > > value (but the original duplicate still remains). Here's an
                    > > > example
                    > > > > of what I'd like to do:
                    > > > >
                    > > > > **Transaction Date ; Transaction Qty ; Transaction Value ;
                    > > > Reversal**
                    > > > >
                    > > > > 1/1/08 ; 14 ; 70.00 ; N
                    > > > > 1/1/08 ; 14 ; 70.00 ; N
                    > > > > 1/1/08 ; -14 ; -70.00 ; Y
                    > > > > 2/1/08 ; 17 ; 89.00 ; N
                    > > > > 2/15/08 ; 18 ; 95.00 ; N
                    > > > > 2/15/08 ; 18 ; 95.00 ; N
                    > > > > 3/1/08 ; 11 ; 54.00 ; N
                    > > > > 3/1/08 ; -11 ; -54.00 ; Y
                    > > > > 3/1/08 ; 11 ; 54.00 ; N
                    > > > > 3/1/08 ; 11 ; 54.00 ; N
                    > > > > 3/1/08 ; 11 ; 54.00 ; N
                    > > > >
                    > > > > Ideally, if I ran my "desired" query on the table above, I
                    > would
                    > > > > receive the following result:
                    > > > >
                    > > > > **Transaction Date ; Transaction Qty ; Transaction Value ;
                    > Count**
                    > > > >
                    > > > > 2/15/08 ; 18 ; 89.00 ; 2
                    > > > > 3/1/08 ; 11 ; 54.00 ; 3
                    > > > >
                    > > > > Does that make sense? I've already figured out how to write
                    the
                    > > > > query to give me a count of duplicates, but I can't figure
                    out
                    > how
                    > > > > to exclude duplicate records which have been "backed out"
                    > already.
                    > > > > Any help would be greatly appreciated!
                    > > > >
                    > > > >
                    > > > >
                    > > > >
                    > > > >
                    > > > > [Non-text portions of this message have been removed]
                    > > > >
                    > > >
                    > > >
                    > > >
                    > > > ------------------------------------
                    > > >
                    > > > Yahoo! Groups Links
                    > > >
                    > > >
                    > > >
                    > >
                    >





                    [Non-text portions of this message have been removed]
                  Your message has been successfully submitted and would be delivered to recipients shortly.