/
Stale Inventory

Stale Inventory

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

Requirements and Purpose

Report to search inventory that has low sales velocity

Report Definition

  1. Stakeholders

    1. @Mark Shaver

    2. @Kaitlyn Niemann (Unlicensed)

  2. Author

    1. @Kaitlyn Niemann (Unlicensed)

  3. Subject matter expert(s)

    1. Alex Goldberg

  4. Presentation Medium

    1. SSRS

  5. Fields (data)

    1. Pricer

    2. eEvent

    3. VenueName

    4. eDate

    5. aqt

    6. pred

    7. psold

Data source(s)

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

  1. eiboxoffice.dbo

    1. TicketGroup

    2. Event

    3. Venue

    4. BuyOrderTicketGroup

    5. BuyOrder

    6. SellOrderTicketGroup

    7. SellOrder

  2. STG.users

    1. PricerEvents

    2. Users

Calculations

Column Name

Calculation

Description

Column Name

Calculation

Description

CTE

Select CASE WHEN tg.SecondaryEventID = 1 THEN h.EventName ELSE CONCAT(h.EventName,'\',o.EventName) END eEvent, v.VenueName, tg.EventDate+ISNULL(tg.EventTime,0) edate, SUM(a.aqt) aqt, ROUND(SUM(a.aqt)*(SUM(s.sqt)/(SUM(s.sqt)+SUM(a.aqt))),0) pred, SUM(s.sqt)/(SUM(s.sqt)+SUM(a.aqt)) psold, DATEDIFF(dd,MAX(b.finalizeddatetime),GETDATE()) podate, DATEDIFF(dd,MAX(so.CreatedDateTime),GETDATE()) sodate, CONCAT(pr.Firstname,' ',pr.LastName) Pricer, CASE WHEN CHARINDEX(ISNULL(@event,h.eventname),h.eventname)+CHARINDEX(ISNULL(@event,o.eventname),o.eventname) > 0 THEN 1 ELSE 0 END sc FROM TicketGroup tg LEFT JOIN Event H ON tg.PrimaryEventID = h.EventID LEFT JOIN Event o ON tg.SecondaryEventID = o.EventID LEFT JOIN Venue V ON tg.VenueID = V.VenueID LEFT JOIN ( SELECT tg.TicketGroupID, CAST(ISNULL(tg.Quantity,0) as Decimal) aqt FROM TicketGroup tg WHERE tg.TicketStatusTypeID = 1 ) a ON tg.TicketGroupID = a.TicketGroupID LEFT JOIN ( SELECT tg.TicketGroupID, CAST(ISNULL(tg.Quantity,0) as Decimal) sqt FROM TicketGroup tg WHERE tg.TicketStatusTypeID = 3 ) s ON tg.TicketGroupID = s.TicketGroupID LEFT JOIN BuyOrderTicketGroup bt ON tg.TicketGroupGUID = bt.BuyOrderTicketGroupGUID LEFT JOIN BuyOrder b ON bt.BuyOrderID = b.BuyOrderID LEFT JOIN SellOrderTicketGroup st ON tg.TicketGroupID = ST.TicketGroupID LEFT JOIN SellOrder so ON st.SellOrderID = so.SellOrderID LEFT JOIN OPENQUERY( FUNNYTREE_TBDATA, 'SELECT DISTINCT stg.users.pricerevents.eventid, stg.users.users.FirstName, stg.users.users.LastName, stg.users.pricerevents.startdate, stg.users.pricerevents.enddate FROM stg.users.pricerevents LEFT JOIN stg.users.users ON stg.users.pricerevents.userid = stg.users.users.eiuserid WHERE stg.users.users.active = 1') pr ON tg.PrimaryEventID = pr.eventid AND tg.EventDate BETWEEN pr.StartDate AND ISNULL(pr.EndDate,DATEADD(YEAR,+10,GETDATE())) WHERE tg.EventDate >= DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),DAY(GETDATE())) GROUP BY H.EventName, o.EventName, tg.SecondaryEventID, v.VenueName, tg.EventDate, tg.EventTime, pr.FirstName, pr.LastName

Source Query

Parameters

  1. Filters

    1. @event

      1. text

      2. can be blank

    2. @pricer

      1. text

      2. multiple values

      3. gets values from a separate query

      4. defaults to all

    3. Only sales under a 25% margin

    4. Only sales where predicted sales are less than current inventory

    5. not purchased in the last week

    6. no sales in 2 weeks

  2. Grouping

    1. Event

  3. Intentions for printing vs. online viewing

    1. The report is delivered via SSRS

    2. This report can be accessed here: SSRS → Asset → Stale Inventory

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

    1. On demand

  5. Future maintenance tasks or manually maintained items

    1. None

Results

  1. Data Accuracy

    1. QA

      1. Invoice Report

        1. No sales in last 2 weeks for event

      2. PO Report

        1. Not purchased in last week

      3. Income by Event Report

        1. Sales for event under 25% margin

  2. Limitations

    1. Report shortfalls

Change Log

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

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

  3. https://ticketboat.atlassian.net/browse/SP-260

Images





Related content