>>Hi,
>>
>>I am working on airline reservation/ticketing data and have no control over the underlying database, I can only run queries on it.
>>
>>I have 3 Tables:
>>
>>
>>/****** Object: Table [dbo].[PNR] Script Date: 24/11/2016 09:09:28 AM ******/
>>
>>CREATE TABLE [dbo].[PNR](
>> [PnrId] [bigint] IDENTITY(1,1) NOT NULL,
>> [RecordLocator] [varchar](6) NULL,
>> [OfficeID] [varchar](50) NULL,
>> [OfficeIATA] [varchar](50) NULL,
>> [AgencyName] [varchar](50) NULL,
>> [Sign] [varchar](50) NULL,
>> [Observation] [varchar](255) NULL,
>> [DateCreated] [datetime] NULL,
>> [DateUpdated] [datetime] NULL,
>> [AppOrgId] [int] NOT NULL,
>> [PnrVersionId] [int] NOT NULL,
>> [LowestFare] [varchar](255) NULL,
>> [HighestFare] [varchar](255) NULL,
>> [TripNumber] [varchar](255) NULL,
>> [SAPNumber] [varchar](255) NULL,
>> [Policy] [varchar](255) NULL,
>> [ReasonCode] [varchar](255) NULL,
>> [AirJustificationCode] [varchar](255) NULL,
>> [AirJustificationText] [varchar](255) NULL,
>> [AppliedDiscountCode] [varchar](255) NULL,
>> [IsInPolicy] [varchar](255) NULL,
>> [FreeText1] [varchar](255) NULL,
>> [FreeText2] [varchar](255) NULL,
>> [FreeText3] [varchar](255) NULL,
>> [FreeText4] [varchar](255) NULL,
>> [FreeText5] [varchar](255) NULL,
>> [SiteCode] [varchar](10) NULL,
>> [WebFareTotal] [varchar](255) NULL,
>> CONSTRAINT [PK_Report] PRIMARY KEY CLUSTERED
>>(
>> [PnrId] ASC
>>)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
>>) ON [PRIMARY]
>>
>>GO
>>
>>SET ANSI_PADDING OFF
>>GO
>>
>>ALTER TABLE [dbo].[PNR] ADD CONSTRAINT [LM_REPORT] DEFAULT (getdate()) FOR [DateCreated]
>>GO
>>
>>ALTER TABLE [dbo].[PNR] ADD CONSTRAINT [DF_PNR_DateCreated1] DEFAULT (getdate()) FOR [DateUpdated]
>>GO
>>
>>ALTER TABLE [dbo].[PNR] WITH CHECK ADD CONSTRAINT [FK_PNR_PnrVersion] FOREIGN KEY([PnrVersionId])
>>REFERENCES [dbo].[PnrVersion] ([PnrVersionId])
>>GO
>>
>>ALTER TABLE [dbo].[PNR] CHECK CONSTRAINT [FK_PNR_PnrVersion]
>>GO
>>
>>/****** Object: Table [dbo].[TST] Script Date: 24/11/2016 09:11:25 AM ******/
>>
>>CREATE TABLE [dbo].[TST](
>> [Id] [bigint] IDENTITY(1,1) NOT NULL,
>> [BaseFareAmount] [float] NULL,
>> [TotalFareAmount] [float] NULL,
>> [TaxAmount] [float] NULL,
>> [CurrencyBaseFare] [varchar](3) NULL,
>> [PnrId] [bigint] NULL,
>> [FareBasis] [varchar](250) NULL,
>> [CurrencyTotalFare] [varchar](3) NULL,
>> [SegmentRPH] [varchar](100) NULL,
>> [EquivalentAmount] [float] NULL,
>> [EquivalentCurrency] [varchar](3) NULL,
>> [SEGMENTTATTOONUMBERS] [varchar](50) NULL,
>> [PAXTATTOONUMBERS] [varchar](50) NULL,
>> CONSTRAINT [PK_TST] PRIMARY KEY CLUSTERED
>>(
>> [Id] ASC
>>)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
>>) ON [PRIMARY]
>>
>>GO
>>
>>SET ANSI_PADDING OFF
>>GO
>>
>>ALTER TABLE [dbo].[TST] WITH CHECK ADD CONSTRAINT [FK_TST_PNR] FOREIGN KEY([PnrId])
>>REFERENCES [dbo].[PNR] ([PnrId])
>>ON UPDATE CASCADE
>>ON DELETE CASCADE
>>GO
>>
>>ALTER TABLE [dbo].[TST] CHECK CONSTRAINT [FK_TST_PNR]
>>GO
>>
>>
>>/****** Object: Table [dbo].[Ticket] Script Date: 24/11/2016 09:10:42 AM ******/
>>
>>CREATE TABLE [dbo].[Ticket](
>> [Id] [bigint] IDENTITY(1,1) NOT NULL,
>> [TicketNumber] [varchar](50) NULL,
>> [PnrId] [bigint] NULL,
>> [TicketDate] [date] NULL,
>> [AirlineTktCode] [varchar](50) NULL,
>> [TicketType] [varchar](3) NULL,
>> [IssuerCode] [varchar](15) NULL,
>> [FreeTex] [varchar](250) NULL,
>> [AssociatedSegmentTattooNumbers] [varchar](50) NULL,
>> [ASSOCIATEDPAXTATTOONUMBERS] [varchar](50) NULL,
>> CONSTRAINT [PK_Ticket] PRIMARY KEY CLUSTERED
>>(
>> [Id] ASC
>>)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
>>) ON [PRIMARY]
>>
>>GO
>>
>>SET ANSI_PADDING OFF
>>GO
>>
>>ALTER TABLE [dbo].[Ticket] WITH CHECK ADD CONSTRAINT [FK_Ticket_PNR] FOREIGN KEY([PnrId])
>>REFERENCES [dbo].[PNR] ([PnrId])
>>ON UPDATE CASCADE
>>ON DELETE CASCADE
>>GO
>>
>>ALTER TABLE [dbo].[Ticket] CHECK CONSTRAINT [FK_Ticket_PNR]
>>GO
>>
>>
>>The relationships between the tables are:
>>
>>A PNR is related to the TST and Ticket tables through:
>>
>>PNR.PNRId = TST.PNRId
>>
>>PNR.PNRId = Ticket.PNRId
>>
>>
>>The TST table is related to the Ticket table through:
>>
>>Ticket.pnrid = tst.pnrid
>>and Ticket.associatedsegmenttattoonumbers = tst.segmenttattoonumbers
>>and Ticket.associatedpaxtattoonumbers = tst.paxtattoonumbers
>>
>>The problem with the latter relationships is that the values in the TST.PaxTattooNumbers might be "1,2,3" while in the Ticket.AssociatedPaxTattooNumbers we might have 3 records , one with each of those values of 1, 2 and 3. Like this
>>
>>
>>TST Table:
>>
>>TSTId PNRId PaxTattooNumbers
>>1 1 1,2,3
>>
>>Ticket Table:
>>
>>TicketId PnrId AssociatedPaxTattooNumbers
>>1 1 1
>>2 1 2
>>3 1 3
>>
>>
>>The PaxtattooNumbers represent passengers and a passenger can be a child which will share a ticket with a regular passenger so it is also possible to have ticket records like this:
>>
>>
>>TicketId PnrId AssociatedPaxTattooNumbers
>>1 1 1,2
>>2 1 3
>>
>>
>>In this case 2 represents a child, so only 2 tickets were issued.
>>
>>My challenge is that I need to find all the PNR.PNRIds that have not been fully ticketed. That is each TST has a corresponding record in the ticket table for all passengers.
>>
>>This was my initial query when I didn't realise the PaxTattooNumbers got split up on the Ticket table:
>>
>>
select pnrid
>> ,[SEGMENTTATTOONUMBERS]
>> ,[PAXTATTOONUMBERS]
>>from tst
>>where not exists (select pnrid
>> ,associatedsegmenttattoonumbers
>> ,associatedpaxtattoonumbers
>> from ticket
>> where pnrid = tst.pnrid
>> and associatedsegmenttattoonumbers = segmenttattoonumbers
>> and associatedpaxtattoonumbers = paxtattoonumbers)
>> order by pnrid
>>
>>How would I adjust this to handle the splitting up of the PaxTattooNumbers?
>
>If you use SQL Server 2016 :-) there is biult-in function STRING_SPLIT.
>For smaller version google TSQL SPLIT :-)
I am on SQL 2012 so can't use the suggested built in function. From what I am seeing, I need to write (or copy one that was written already) a tsql function which will split it up.
I'm not seeing how I would then apply that to my problem, can you help fill in the blank for me?
Say I have a function:
CREATE FUNCTION [dbo].[fnSplitString]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
ENDHow would I adjust my query to get this to work?
