• Replies 5 replies
  • Subscribers 415 subscribers
  • Views 170 views
  • Users 0 members are here
Related

Querying T_ORDER_SEAT_HIST

Michael Dorsey (Past Member)
$organization

Hello,

I'm trying to query T_ORDER_SEAT_HIST and use the query to make 2 lists:

  1. All the constituents who purchased a seat to a cancelled 2020 summer camp who is not currently a member (whether they ever have been or not)
  2. All the constituents who purchased a seat to a cancelled 2020 summer camp who is currently a member, regardless of whether or not they were at time of purchase

We do not print tickets for summer camps, so we could not "return" them, but had to delete SLI's to generate the refund, hence T_ORDER_SEAT_HIST.

I can get a straight list of constituent ID's, no problem. Adding the dimension of their membership current status seems to throw everything into disarray. Anyone have any ideas?

Here's where I start. I don't have any of my attempts at linking a membership table/view.

select distinct osh.order_no, osh.customer_no from T_ORDER_SEAT_HIST osh
join T_PERF p
on p.perf_no = osh.perf_no
join T_PROD_SEASON ps
on ps.prod_season_no = p.prod_season_no
join T_INVENTORY i
on osh.perf_no = i.inv_no


where ps.season in ('250','251') <The two season ID's for the seasons that our camp prod seasons live in; our FY ends in June so our offerings are split into two seasons.>
and p.perf_type = 1
and osh.event_code = 26

Any insight from the community is greatly appreciated!

Michael Dorsey

Parents
  • $organization

    Since getting current memberships and cancelled tickets are things you can use in other reporting you are better off creating two parameterized table functions holding the logic this way you don't have to keep pasting the same code over again if you need to reuse the logic.

    Below is an example of how we do it with ticket sales and returns. Because Sales and returns have different logic we put them into separate functions that we can call individually or as one via this function. We opted for the third function to be a table as it is still a work in progress and there will be some additional logic in place to handle some additional functionality.

    IF OBJECT_ID (N'dbo.LFT_MBA_Tkt_Data', N'TF') IS NULL
    begin
        Declare @SQL varchar(max) =
        'CREATE FUNCTION dbo.LFT_MBA_Tkt_Data()

        returns
         @tbl table (
        ID            int
        primary key (ID))
        as begin
            insert into @tbl
            Select 1
            return
        end'
        execute (@SQl)
    end
    GO  
    alter FUNCTION dbo.LFT_MBA_Tkt_Data(

    @StartDate        date  --= '03/12/2020'
    ,@EndDate        datetime
    ,@OriginIDs        varchar(Max) --= 1
    ,@PriceTypeIds    varchar(Max)  --= '140,104,61'
    ,@OrderIDs        varchar(Max) --= '2293267'
    ,@PerfIDs        varchar(Max) --= '6361,3937,6358'
    ,@OnlyZeroDollar    bit
    )
    returns
     @tbl table (
        [RecType] [varchar](10) NOT NULL,
        [Order_no] [int] NULL,
        [Sli_No] [int] NOT NULL,
        [TranDate] [date] NULL,
        [TranTime] [datetime] NULL,
        [Perf_no] [int] NULL,
        [PriceTypeID] [int] NULL,
        [price] [money] NULL,
        [Amount] [money] NULL,
        [qty] [int] NOT NULL,
        [ReturnQty] [int] NOT NULL,
        [SalesQty] [int] NOT NULL,
        [zone_no] [int] NULL,
        [SLIStatusID] [int] NULL,
        [CompCode] [int] NOT NULL,
        [batch_no] [int] NULL,
        [OriginID] [int] NOT NULL,
        [Origin] [varchar](30) NOT NULL,
        [machine_name] [varchar](30) NULL,
        [OriginalTicketNo] [int] NOT NULL,
        [CurrentTicketNo] [int] NULL,
        [seat_no] [int] NULL,
        [printed_by] [varchar](50) NULL,
        [RecipientNo] [int] NOT NULL,
        [ret_parent_sli_no] [int] NOT NULL
        ,ZeroDollar        bit
        primary Key (Sli_no))
    as begin


        insert into @tbl
        Select * from LFT_MBA_Tkt_Sales(@StartDate , @EndDate , @OriginIDs  , @PriceTypeIds , @OrderIDs , @PerfIDs,@OnlyZeroDollar  )
        union Select * from LFT_MBA_Tkt_Returns(@StartDate , @EndDate , @OriginIDs , @PriceTypeIds , @OrderIDs , @PerfIDs,@OnlyZeroDollar  )
        return
    end

Reply
  • $organization

    Since getting current memberships and cancelled tickets are things you can use in other reporting you are better off creating two parameterized table functions holding the logic this way you don't have to keep pasting the same code over again if you need to reuse the logic.

    Below is an example of how we do it with ticket sales and returns. Because Sales and returns have different logic we put them into separate functions that we can call individually or as one via this function. We opted for the third function to be a table as it is still a work in progress and there will be some additional logic in place to handle some additional functionality.

    IF OBJECT_ID (N'dbo.LFT_MBA_Tkt_Data', N'TF') IS NULL
    begin
        Declare @SQL varchar(max) =
        'CREATE FUNCTION dbo.LFT_MBA_Tkt_Data()

        returns
         @tbl table (
        ID            int
        primary key (ID))
        as begin
            insert into @tbl
            Select 1
            return
        end'
        execute (@SQl)
    end
    GO  
    alter FUNCTION dbo.LFT_MBA_Tkt_Data(

    @StartDate        date  --= '03/12/2020'
    ,@EndDate        datetime
    ,@OriginIDs        varchar(Max) --= 1
    ,@PriceTypeIds    varchar(Max)  --= '140,104,61'
    ,@OrderIDs        varchar(Max) --= '2293267'
    ,@PerfIDs        varchar(Max) --= '6361,3937,6358'
    ,@OnlyZeroDollar    bit
    )
    returns
     @tbl table (
        [RecType] [varchar](10) NOT NULL,
        [Order_no] [int] NULL,
        [Sli_No] [int] NOT NULL,
        [TranDate] [date] NULL,
        [TranTime] [datetime] NULL,
        [Perf_no] [int] NULL,
        [PriceTypeID] [int] NULL,
        [price] [money] NULL,
        [Amount] [money] NULL,
        [qty] [int] NOT NULL,
        [ReturnQty] [int] NOT NULL,
        [SalesQty] [int] NOT NULL,
        [zone_no] [int] NULL,
        [SLIStatusID] [int] NULL,
        [CompCode] [int] NOT NULL,
        [batch_no] [int] NULL,
        [OriginID] [int] NOT NULL,
        [Origin] [varchar](30) NOT NULL,
        [machine_name] [varchar](30) NULL,
        [OriginalTicketNo] [int] NOT NULL,
        [CurrentTicketNo] [int] NULL,
        [seat_no] [int] NULL,
        [printed_by] [varchar](50) NULL,
        [RecipientNo] [int] NOT NULL,
        [ret_parent_sli_no] [int] NOT NULL
        ,ZeroDollar        bit
        primary Key (Sli_no))
    as begin


        insert into @tbl
        Select * from LFT_MBA_Tkt_Sales(@StartDate , @EndDate , @OriginIDs  , @PriceTypeIds , @OrderIDs , @PerfIDs,@OnlyZeroDollar  )
        union Select * from LFT_MBA_Tkt_Returns(@StartDate , @EndDate , @OriginIDs , @PriceTypeIds , @OrderIDs , @PerfIDs,@OnlyZeroDollar  )
        return
    end

Children