1

I have a table in MySQL like this:

Trans Time_In Placard Container Sztp Line Time_Out   
===== ======= ======= ========= ==== ==== ========
IN    10:15   254114  CLHU12345 40DH MAE  10:54   <In transaction
OUT   10:15   254114  MAEU45678 20DR SEA  10:54   <Out Transaction (same placard)
OUT   10:15   254114  TTNU98765 20DR CHI  10:54   <Out Transaction (same placard)

IN    11:23   664524  FSCU13479 40RH SEB  11:55   <In transaction
OUT   11:23   664524  PONU55588 40DR MAB  11:55   <Out Transaction (same placard)

IN    13:01   542234  TLHU77665 40RH MOL  13:23   <In transaction (no out)

OUT   13:36   232212  MLHU22341 20DR CMD  13:49   <Out Transaction (no in)

OUT   14:03   187852  AMFU56041 20DR CMD  14:48   <Out Transaction (no in)
OUT   14:03   187852  CCLU44112 20DR CHN  14:48   <Out Transaction, same placard (no in)

This is a table of trucks that enter our terminal to drop a container, and sometimes to pick one 40" or two 20" to gate out (3 transactions, thus 3 rows). Sometimes a trucker simply drops a container and goes away empty (1 transaction), so there is no OUT transaction. Or it may come empty to pick a full container (1 transaction), so there is no IN transaction, but just one or two OUT (2 transactions), if he picks one 40 or two 20s. There are even times when comes in with two 20 foot, and leaves with 2 20s as well, having 4 transactions. The time In and Time out is the same for every placard, so I can take it from any of the records, so no worries about that.

The key is Time_In + Placard, since the same placard can do multiple trips in and out on the same day. The timestamp is exactly the same for each trip.

In the end, it will be no more than 4 transactions for every trip, and we need a single row report per trip, displaying each transaction details (container, sztp, and line), and if it only have one or two, the other remaining transaction details will be null. For those unfamiliar with maritime terms, sztp means size/type, like 40DR means 40 foot dry, 20DR 20 foot dry, 40RH 40 foot reefer high cube, and so on and so forth.

I need to end up with something like this:

Time In Placard Cont1     Sztp1  Line1 Cont2     Sztp2 Line2 Cont3     Sztp3 Line3 Cont4     Sztp4 Line4 Time Out
======= ======= ========= =====  ===== ========= ===== ===== ========= ===== ===== ========= ===== ===== ========
10:15   254114  CLHU12345 40DH   MAE   MAEU45678 20DR  SEA   TTNU98765 20DR  CHI   null      null  null  10:54
11:23   664524  FSCU13479 40RH   SEB   PONU55588 40DR  MAB   null      null  null  null      null  null  11:55
13:01   542234  TLHU77665 40RH   MOL   null      null  null  null      null  null  null      null  null  13:23
13:36   232212  MLHU22341 20DR   CMD   null      null  null  null      null  null  null      null  null  13:49
14:03   187852  AMFU56041 20DR   CMD   CCLU44112 20DR  CHN   null      null  null  null      null  null  14:48

The intended use of this table will be for a BIRT report.

Thanks for your help.

BTW, I already asked a similar question, but It was not very clear from the beginning, so I reported it to moderation and posted this one. Apologies for that. Hope you can help me.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
Martin Ocando
  • 914
  • 2
  • 8
  • 18
  • BIRT calls pivot tables "crosstabs". fyi. – O. Jones Jun 10 '15 at 19:25
  • So you mean I can do with birt alone? Sounds like a deal. I'll research on that. Any point into the right direction is appreciated. – Martin Ocando Jun 10 '15 at 20:56
  • How many lines is the ultimate report going to have? if there is going to be in the thousands or more I would do the heavy lifting of the query in SQL with left joined sub queries. Dominique knows BIRT very well, so you can go with the answer they have. I learned how to do the SQL sub query solution on SE so you should be able to find it here if not, let me know and I will work to post something. – James Jenkins Jun 11 '15 at 11:26
  • The query returns no more than 200-300 records, since the user is only querying a specific shift, either day or night shift. Ideally, I would just update a table hourly with the data in columns, so I prefer to do the MySQL approach. If you can give me some guidance it'll be very helpful. – Martin Ocando Jun 11 '15 at 17:11

2 Answers2

0

A BIRT crosstab can handle this, for example this report is near from what you want to do: it is transposing 5 order lines to columns.enter image description here1

The advantage of this option is, this is completely dynamic: we don't have to hard-code anywhere the fields of each line number such Cont1, Cont2, Sztp1, Sztp2 etc.

To achieve the same in your context you need a line number for each transaction though. A such line number could be computed either in a MySQL stored procedure, either in a computed column of the BIRT dataset, by checking if "Time in" and "Placard" values of the current row are different from the previous row. Of course the query needs to order transactions by "Time-In" + "Placard" to do this.

Once a line number is available in the dataset we can design a datacube with 2 groups and 2 measures

  • First group: "Time-In"+"Placard". Add sublevels to display these fields in separate columns such as "Order" and "Date" in the screenshot.
  • Second group: Line number
  • Measure 1: field:"Container" type:STRING AggregateFunction: FIRST
  • Measure 2: field:"Sztp" type:STRING AggregateFunction: FIRST
Dominique
  • 4,272
  • 1
  • 16
  • 21
  • Awesome. I'll take a look at it. Although, I prefer to do a table with the columns, and save it to our Data Warehouse, so reports run faster. Any ideas on how I can compute a line number for each transaction? I have no lag or lead statements like in Oracle SQL, which I'm more familiar with. – Martin Ocando Jun 11 '15 at 17:13
  • @Martin there is an elegant method computing a row number in this topic: http://stackoverflow.com/questions/17939198/row-number-per-group-in-mysql – Dominique Jun 12 '15 at 19:45
0

You can create a single row of data with each unique data piece as column using sub queries. 

Select distinct yourdata.placard
, yourdata.Time_In
, yourdata.Time_Out
, Inbound.Insztp
, Inbound.InContainer

from dbo.yourdata

--Sub query gets inbound sztp and container info
left join (select placard
           , sztp as 'Insztp'
           , Container as 'InContainer'
           from dbo.yourdata
           where trans = 'IN') as Inbound 
               on yourdata.placard = Inbound.placard

You will need to join more sub queries to create the other columns, but this is the basic idea.

James Jenkins
  • 1,954
  • 1
  • 24
  • 43