question

lomas avatar image
lomas asked

How would I get the last table as an out (see Image) from 3 Scenarios.

Need to display output table based on Transaction 1, Transaction 2 and Transaction 3 table.

joins
sql-problem.png (56.6 KiB)
2 comments
10 |1200

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

Moved to the general Q&A section of the site.

0 Likes 0 ·

"Barcodes are repeat more than once between date periods but Batch id is different for each transaction" Is this what you mean? The dates, dates barcodes and the transcodes are the same, however, the BatchID is changing?

Update Date TransCode Barcode BatchId

01/03/2018 T6 10003 B23235

01/03/2018 T2 10003 A55553

12/03/2018 T7 10003 A55553

13/03/2018 T3 10003 A55553

01/03/2018 T6 10003 B23236

01/03/2018 T2 10003 A55554

12/03/2018 T7 10003 A55554

13/03/2018 T3 10003 A55554

The final output can be achieved by using a PIVOT.

Looks like the comment is removing the table that I'm putting in through the text editor

0 Likes 0 ·

1 Answer

·
lomas avatar image
lomas answered

Yes, you are right, the dates of T3 and T6 are possible same but sometime 2-3 days different.

I will explain more,

The standard transactions are start from T2->T6->T7->T3 ( T's means scan locations). When T2 is scanned, the unique BATCHID will generate for other consecutive transactions (T6,T7 and T3). But sometimes we received T6 before T2 because the scan device at T6 location send data before T2 and T6 carries its old batch id. Once we received T2 after T6, it initializes new batch id for remaining transactions (T2,T7,T3) and T6 is separated from this transaction logically due to the different

BATCHID but in real its NOT.

The main purpose of this query is to identify missing T6 scan or T7 scan (which is quite normal) where there is T2 scan. We might capture T6 (received before T2) by checking the latest scan date of last T3 (by matching barcode).

Please help me out.

Thanks

1 comment
10 |1200

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

Sorry, the confusion for me is that you have a date in red for t6 scan date. However, I'm not sure how you would get a date in their (it would probably be NULL) because you can't join/lookup on just the date and barcode because you may multiple other repeat barcodes/scan dates, you also can't join on the batchid because it's different. You also couldn't guess which date it would be because what happens if their are multiple T6 entries that are entered early? So the only thing you could put in their would be a NULL.

if you're happy with a NULL going in rather than the red date. MSDN Pivot is the answer.

I would look at the process before the database and try to fix that i.e. stop the T6 being able to scan before T2. Or work out a way that T6 can create a new batchid that T2 can then match.

0 Likes 0 ·

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.