A table has the following columns (amongst others)

Transaction_ID

Type

Debit

Credit

Nominal_Code

Log_Time

All rows are posted in pairs (Debit = value and Credit = 0) and (Debit = 0 and Credit = value) so the log-time should be the same for both rows

I'm trying to return records where

Type = 'BR' or 'BP' and Nominal_Code is the same for both the first and second rows (and all subsequent pairs)...

I'm struggling to think of a way to returning those values, probably based on the log-time being the same for the two rows

Might have over complicated that description (it's Friday). Really returning any records where Nominal_Code is the same for any two rows that have the same log_time is the bit that's confusing me.

Any ideas?

Thanks

asked 09 Mar, 17:32

gchq's gravatar image

gchq
201121324
accept rate: 33%

edited 09 Mar, 17:40


This has NOT been tested...

SELECT "***** t1 columns: ',
       t1.*,
       "***** t2 columns: ',
       t2.* 
  FROM t AS t1
          INNER JOIN t AS t2
                  ON t1.Transaction_ID <> t2.Transaction_ID
                 AND t1.Log_Time       =  t2.Log_Time
                 AND t1.Nominal_Code   =  t2.Nominal_Code
 ORDER BY t1.Log_Time;
permanent link

answered 10 Mar, 12:05

Breck%20Carter's gravatar image

Breck Carter
27.6k473629907
accept rate: 21%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×98

question asked: 09 Mar, 17:32

question was seen: 105 times

last updated: 10 Mar, 12:05