/
Inventory Audit by Exchange

Inventory Audit by Exchange

Owned by Kaitlyn Niemann (Unlicensed), created
Last updated: Apr 09, 2024 by Albert Saludaga
2 min read

Requirements and Purpose

Compares ticket listings from exchanges with eibo inventory.

Report Definition

  1. Stakeholders

    1. Kaitlyn Niemann

    2. Mark Shaver

  2. Author

    1. Kaitlyn Niemann

  3. Subject matter expert(s)

    1. April Gahuman

  4. Presentation Medium

    1. SSRS

  5. Fields (data)

    1. Exchange Event

    2. Exchange Venue

    3. Exchange Event Date

    4. Exchange Section

    5. Exchange Row

    6. Exchange SeatFrom

    7. Exchange Seatto

    8. Exchange Quantity

    9. Exchange ExternalListingID

    10. Exchange TicketType

    11. Eibo TicketGroupID

    12. Eibo Host

    13. Eibo Opponent

    14. Eibo Venue

    15. Eibo EventDate

    16. Eibo Quantity

    17. EIBO SeatSection

    18. Eibo SeatRow

    19. Eibo Starting SEat

    20. Eibo Ending Seat

    21. Eibo Internal Note

    22. Eibo Description(External Note)

    23. Eibo ShareTypeName

    24. Status

    25. Exchange

    26. isMatched

Data source(s)

The data source(s) for this report are Eibo-215 Database and the STG Database. Using the stored procedure reporting.InventoryAuditByExchange.

Tables:

  1. eiboxoffice

    1. dbo.TicketGroup

    2. dbo.event(Host and Opponent)

    3. dbo.Venue

    4. dbo.Ticket

    5. dbo.BuyOrderTicketGroup

    6. dbo.BuyOrder

  2. STG

    1. api.TicketBoatListings

Calculations

Column Name

Data Type

Calculation

Description

Column Name

Data Type

Calculation

Description

Eibo EventDate

Date

=tg.eventdate + ISNULL(tg.eventtime, 0)

Combines Event Date and time

Status

VARCHAR

=CASE
WHEN el.TicketGroupID IS NULL THEN 'Missing Eibo'
WHEN tbl.xid IS NULL THEN 'Missing Exchange'
WHEN CHARINDEX(LEFT(el.Host,CHARINDEX(' ',el.VenueName)),tbl.xevent) > 0 AND
CHARINDEX(LEFT(el.VenueName,CHARINDEX(' ',el.VenueName)),tbl.xvenue) > 0 AND
DATEDIFF(day,el.eventdate,tbl.xdate) < 1 AND
el.SeatSection = tbl.xsection AND
el.SeatRow = tbl.xrow THEN 'Matched'
ELSE 'Exception'
END as Status

Defines Status

Exchange

VARCHAR

=CASE
WHEN tbl.tickettype = 0 THEN 'StubHub'
WHEN tbl.tickettype = 1 THEN 'TEVO'
WHEN tbl.tickettype = 3 THEN 'SeatGeek'
WHEN tbl.tickettype = 5 THEN 'VividSeats'
ELSE ''
END

Displays Exchange in view.

isMatched

BOOLEAN

CASE
WHEN
CHARINDEX(LEFT(el.Host,CHARINDEX(' ',el.VenueName)),tbl.xevent) > 0 AND
CHARINDEX(LEFT(el.VenueName,CHARINDEX(' ',el.VenueName)),tbl.xvenue) > 0 AND
DATEDIFF(day,el.eventdate,tbl.xdate) < 1 AND
el.SeatSection = tbl.xsection AND
el.SeatRow = tbl.xrow THEN 1
ELSE 0
END

Determines if the listings match through basic logic.

Parameters

  1. Filters

    1. @Exchange

      1. Vivid = 0

      2. TEVO = 1

      3. SeatGeek = 3

      4. VividSeats = 5

    2. @Venue

      1. If left blank returns everything

      2. Must match EIBO venue.

    3. @Event

      1. If left blank returns everything

      2. Must match EIBO event.

    4. @status

      1. All Records - Returns all records

      2. Exceptions - Returns all records that don't match eibo ↔ exchange

      3. Matched - Returns all records that match eibo ↔ exchange

      4. Missing Exchange - Returns all records from eibo that don't have an exchange listing.

      5. Missing Eibo - Returns all records from exchange that don't have an eibo listing

  2. Intentions for printing vs. online viewing

    1. The report is delivered via SSRS

    2. This report can be accessed here: SSRS → Asset → Inventory Audit By Exchange.

  3. Intended delivery mechanism (run on demand vs. delivered by subscription)

    1. On Demand

  4. Future maintenance tasks or manually maintained items

    1. None

Results

  1. Data Accuracy

    1. Report accuracy and reasons

  2. Limitations

    1. Report shortfalls

Change Log

  1. https://ticketboat.atlassian.net/browse/SP-218

  2. https://ticketboat.atlassian.net/browse/SP-223

  3. https://ticketboat.atlassian.net/browse/DW-235

  4. https://ticketboat.atlassian.net/browse/SP-236

Images





Related content