question

lomas avatar image
lomas asked

How to Get below result in SQL

Note: T6 date always happens before T7.

sql-server-2008-r2joins
problem-1.jpg (41.6 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Oleg avatar image
Oleg answered

One way of doing it is via pivot. For this solution I will assume that there is a table named SomeTable with 3 columns named Barcode, Date (lovely named after reserved keyword), and TransID. The script below is based on the assumption that for each barcode there might be multiple records which may be paired based on the date because for each T6 record there has to be T7 record before the next T6 record may appear. If the total number of records per barcode is odd then the last record is the T6 record. To easily see how the records are paired before pivoting, please highlight and run just the script inside of the src sub-select (this will reveal how each T6/T7 records are "paired"). Here is the complete script:

select 
    Barcode, T7, T6
    from (
        select 
            *, (row_number() over (partition by Barcode order by [Date]) - 1) / 2 + 1 n 
            from SomeTable
    ) src
    pivot (max([Date]) for TransID in ([T6], [T7])) pvt
    order by Barcode, n;

Based on the sample data in question, the script produces the following results:

Barcode T7         T6
------- ---------- ----------
A       2018-01-05 2018-01-01
A       NULL       2018-01-10
B       2018-01-03 2018-01-01
C       NULL       2018-01-03

Hope this helps.

Oleg

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.