CODEWITHSUNDEEP
mysqlsqldatabaseexcelpivot-table
Shain Padmajan
Shain Padmajan

Reputation: 424

Represent Fare Chart in MySQL

I am working on a booking system for a bus transportation system. I am having difficulties representing the Fare table as a table in MySQL. An example of a fare table for one particular route that was given to me as a spreadsheet document has been attached below. Please let me know how this can be represented as a MySQL table.

Please note that this is only a portion of one of the specified routes. The actual route has over 40 locations and there are over 30 such routes. So, I am hoping to export this excel sheet as it is into SQL somehow.

location1   |           |           |           |
0.100       | location2 |           |           |    
0.200       | 0.200     |location3  |           |
0.300       | 0.300     |0.200      |location4  |           
0.500       | 0.500     |0.400      |0.300      |location5  |       
0.500       | 0.500     |0.400      |0.300      |0.200      |location6

Here the fare for traveling from location1 to location 3 would be 0.200 and from location3 to location6 would be 0.400.

Please let me know how I can query the rates given the source and destination form the MySQL table once implemented.

Upvotes: 0

Views: 422

Answers (2)

Shain Padmajan
Shain Padmajan

Reputation: 424

I managed to use the following VB Macro in Excel to convert it into a table which with some text processing converted into a MySQL query to insert it into a database

Sub ReversePivotTable()
'   Before running this, make sure you have a summary table with column headers.
'   The output table will have three columns.
    Dim SummaryTable As Range, OutputRange As Range
    Dim OutRow As Long
    Dim r As Long, c As Long

    On Error Resume Next
    Set SummaryTable = ActiveCell.CurrentRegion
    If SummaryTable.Count = 1 Or SummaryTable.Rows.Count < 3 Then
        MsgBox "Select a cell within the summary table.", vbCritical
        Exit Sub
    End If
    SummaryTable.Select
    Set OutputRange = Application.InputBox(prompt:="Select a cell for the 3-column output", Type:=8)
'   Convert the range
    OutRow = 2
    Application.ScreenUpdating = False
    OutputRange.Range("A1:C3") = Array("To", "From", "Fare")
    For r = 2 To SummaryTable.Rows.Count
        For c = 2 To SummaryTable.Columns.Count
            If SummaryTable.Cells(r, c) <> Empty And IsNumeric(SummaryTable.Cells(r, c)) Then
                OutputRange.Cells(OutRow, 1) = SummaryTable.Cells(r, r)
                OutputRange.Cells(OutRow, 2) = SummaryTable.Cells(c, c)
                OutputRange.Cells(OutRow, 3) = SummaryTable.Cells(r, c)
                OutputRange.Cells(OutRow, 3).NumberFormat = SummaryTable.Cells(r, c).NumberFormat
                OutRow = OutRow + 1
            End If
        Next c
    Next r
End Sub

This would give me 3 columns stating location_from, location_to and price as @luc-franken described. I saved this as a CSV file and with some regex substitutions managed to convert it into a MySQL query.

Upvotes: 0

Luc Franken
Luc Franken

Reputation: 3014

Just as:

RATES
location_from
location_to
price

And looking up:

SELECT price FROM rates WHERE location_from=3 AND location_to=4

Will return: 0.400

Always input location_form with the lowest one, so never add 4, 3. Then you get double records.

But depending on your needs you could also work with distances and calculating them for example. Totally depends on you business need.

Upvotes: 3

Related Questions