Need to display output table based on Transaction 1, Transaction 2 and Transaction 3 table.
asked
How would I get the last table as an out (see Image) from 3 Scenarios.
"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
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
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.
25 People are following this question.
