January 24, 2014 at 11:08 am
Hi Friends , I would like to thank you all for whom they tried to help in that query it was really challange. but for sure each problem has solution. I got the solution through one of my master brains friend and i am posting it here for sharing knowledge :
So here is the table and the sample data along with the solution needed:
<code>
CREATE TABLE [dbo].[table1](
[Pax ID] [int] NULL,
[Reservation] [int] NULL,
[Surname] [varchar](40) NULL,
[First Name] [varchar](40) NULL,
[Pax Type] [char](1) NULL,
[Flight Date] [smalldatetime] NULL,
[Flight Number] [varchar](10) NULL,
[Board] [varchar](3) NULL,
[Off] [varchar](3) NULL,
[Original Booking Date] [smalldatetime] NULL,
[Last Mod Date] [smalldatetime] NULL,
[lng_Res_Segments_Id_Nmbr] [varchar](15) NULL
) ON [PRIMARY]
</code>
<COde>
INSERT INTO table1([Pax ID],[Reservation],[Surname],[First Name],[Flight Date],[Flight Number],[Board],[Off], [Original Booking Date],[Last Mod Date],[lng_Res_Segments_Id_Nmbr])
SELECT '1558611','899842','SULIMAN','ALI','2/1/2013','FO151','RIY','SAH','1/1/2013','1/1/2013','1860178C' UNION ALL
SELECT '1558612','899842','ALGANADI','HASAN','2/1/2013','FO151','RIY','SAH','1/1/2013','1/1/2013','1860179C' UNION ALL
SELECT '1558613','899844','ALYAFEE','MOHMMED','1/1/2013','FO160','SAH','TAI','1/1/2013','1/1/2013','1860180C' UNION ALL
SELECT '1558616','899847','ASSIRI','ahmed','2/1/2013','FO873','AHB','ADE','1/1/2013','1/1/2013','1860181C' UNION ALL
SELECT '1558616','899847','ASSIRI','ahmed','4/1/2013','FO874','ADE','JED','1/1/2013','1/1/2013','1860182C' UNION ALL
SELECT '1558628','899847','asiri','alin','2/1/2013','FO873','AHB','ADE','1/1/2013','1/1/2013','1860183C' UNION ALL
SELECT '1558628','899847','asiri','alin','4/1/2013','FO874','ADE','JED','1/1/2013','1/1/2013','1860184C' UNION ALL
SELECT '1558696','899847','ASSIRI','ahmed','2/10/2013','FO875','JED','ADE','1/1/2013','1/1/2013','1860185C' UNION ALL
SELECT '1558696','899847','ASSIRI','ahmed','2/10/2013','FO876','ADE','AHB','1/1/2013','1/1/2013','1860186C' UNION ALL
SELECT '1558698','899847','asiri','alin','2/10/2013','FO875','JED','ADE','1/1/2013','1/1/2013','1860187C' UNION ALL
SELECT '1558698','899847','asiri','alin','2/10/2013','FO876','ADE','AHB','1/1/2013','1/1/2013','1860188C' UNION ALL
SELECT '1558618','899848','ALAQWAA','EBRAHEEM','1/1/2013','FO173','HOD','SAH','1/1/2013','1/1/2013','1860189C' UNION ALL
SELECT '1558621','899850','ALGELHM','HAMID','3/1/2013','FO196','SAH','AAY','1/1/2013','1/1/2013','1860190C' UNION ALL
SELECT '1558622','899851','ASGHAR','AMER','1/1/2013','FO205','ADE','SAH','1/1/2013','1/1/2013','1860191C' UNION ALL
SELECT '1558623','899852','ALHALILI','HAMZAH','2/1/2013','FO174','SAH','HOD','1/1/2013','1/1/2013','1860192C' UNION ALL
SELECT '1558624','899852','ALJAHDARI','GHALIAH','2/1/2013','FO174','SAH','HOD','1/1/2013','1/1/2013','1860193C' UNION ALL
SELECT '1558625','899853','ABDULLAH','ADEL','1/1/2013','FO173','HOD','SAH','1/1/2013','1/1/2013','1860194C' UNION ALL
SELECT '1558626','899854','alasmari','mohammed','2/1/2013','FO873','AHB','ADE','1/1/2013','1/1/2013','1860195C' UNION ALL
SELECT '1558627','899854','alasmari','mohammed','4/1/2013','FO874','ADE','AHB','1/1/2013','1/1/2013','1860196C' UNION ALL
SELECT '1558631','899856','ALI','FAWAZ','3/1/2013','FO196','SAH','AAY','1/1/2013','1/1/2013','1860197C' UNION ALL
SELECT '1558631','899856','ALI','FAWAZ','3/1/2013','FO197','AAY','TAI','1/1/2013','1/1/2013','1860198C' UNION ALL
SELECT '1558631','899856','ALI','FAWAZ','4/1/2013','FO198','TAI','CAI','1/1/2013','1/1/2013','1860199C' UNION ALL
SELECT '1558624','899891','Alhakimi','Rashed','1/4/2013','FO120','TAI','ADE','1/3/2013','1/3/2013','1860200C' UNION ALL
SELECT '1558625','899891','Alhakimi','Rashed','4/5/2013','FO121','ADE','TAI','1/3/2013','1/3/2013','1860201C'
</code>
the solution:
<code>
CREATE Function [dbo].[fnRemoveNonNumericCharacters](@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%', @strText) > 0
BEGIN
SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
END
RETURN @strText
END
GO
</code>
<code>
CREATE FUNCTION [dbo].[getCountPAX]
(
-- Add the parameters for the function here
@rgId int
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @Result int
-- Add the T-SQL statements to compute the return value here
SELECT @Result = count(DISTINCT Surname + [First Name]) from viw_table1 where Reservation = @rgId;
-- Return the result of the function
RETURN @Result
END
GO
</code>
<code>
CREATE FUNCTION [dbo].[getDatedif]
(
-- Add the parameters for the function here
@rgId int
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @Result int, @fd Date , @Ld Date
select @fd =min(t.[Original Booking Date]), @Ld =min(t.[Flight Date]) from table1 t
where Reservation = @rgId;
-- Add the T-SQL statements to compute the return value here
set @Result = datediff(day,@fd,@ld)
-- Return the result of the function
RETURN @Result
END
GO
</code>
<code>
CREATE FUNCTION [dbo].[getEndCity]
(
-- Add the parameters for the function here
@rgId int, @pid int
)
RETURNS nvarchar(5)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result nvarchar(5)
select @Result = t.[Off] from table1 t
where CONVERT(int, dbo.fnRemoveNonNumericCharacters(t.lng_Res_Segments_Id_Nmbr)) = (select max(CONVERT(int, dbo.fnRemoveNonNumericCharacters(t2.lng_Res_Segments_Id_Nmbr))) from table1 t2 where t2.Reservation = @rgId and @pid=[Pax ID] );
-- Add the T-SQL statements to compute the return value here
-- Return the result of the function
RETURN @Result
END
GO
</code>
<Code>
create FUNCTION [dbo].[getEndCityRev]
(
-- Add the parameters for the function here
@rgId int, @pid int
)
RETURNS nvarchar(5)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result nvarchar(5)
select @Result =t.Board from table1 t
where CONVERT(int, dbo.fnRemoveNonNumericCharacters(t.lng_Res_Segments_Id_Nmbr)) = (select max(CONVERT(int, dbo.fnRemoveNonNumericCharacters(t2.lng_Res_Segments_Id_Nmbr))) from table1 t2 where t2.Reservation = @rgId and @pid=[Pax ID] );
-- Add the T-SQL statements to compute the return value here
-- Return the result of the function
RETURN @Result
END
GO
</code>
<Code>
CREATE FUNCTION [dbo].[getMidCity]
(
-- Add the parameters for the function here
@rgId int , @pid int
)
RETURNS nvarchar(50)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result nvarchar(200), @cont int;
declare @fr nvarchar(10) , @tr nvarchar(10);
select @cont = count(*) from table1 where Reservation= @rgId and @pid=[Pax ID];
if @cont < 2
return null;
DECLARE curName CURSOR FOR SELECT Board,[Off] from table1 where Reservation = @rgId and [Pax ID] = @pid
OPEN curName
FETCH NEXT FROM curName INTO @fr , @tr
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM curName INTO @fr , @tr --do not forget this line.. will cause an infinite loop
if @@FETCH_STATUS = 0
begin
if @Result is null
set @Result = @fr;
else
set @Result = @Result + ',' + @fr;
end
--insert code here
END
CLOSE curName
DEALLOCATE curName
return @Result;
END
GO
</code>
<code>
CREATE FUNCTION [dbo].[getSamePAX]
(
-- Add the parameters for the function here
@pid1 int,@rgId int
)
RETURNS nvarchar(200)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result nvarchar(200)
-- Add the T-SQL statements to compute the return value here
SELECT @Result =
STUFF(
(SELECT
', ' + t2.[First Name]+' '+t2.Surname
FROM viw_table1 t2
WHERE t1.Reservation=t2.Reservation
and t2.[Pax ID] <> @pid1
ORDER BY t2.[First Name]
FOR XML PATH(''), TYPE
).value('.','varchar(max)')
,1,2, ''
)
FROM viw_table1 t1
where t1.Reservation = @rgId
GROUP BY t1.Reservation
-- Return the result of the function
RETURN @Result
END
GO
</code>
<code>
CREATE FUNCTION [dbo].[getStratCity]
(
-- Add the parameters for the function here
@rgId int, @pid int
)
RETURNS nvarchar(5)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result nvarchar(5)
select @Result = t.Board from table1 t
where CONVERT(int, dbo.fnRemoveNonNumericCharacters(t.lng_Res_Segments_Id_Nmbr)) = (select min(CONVERT(int, dbo.fnRemoveNonNumericCharacters(t2.lng_Res_Segments_Id_Nmbr))) from table1 t2 where t2.Reservation = @rgId and @pid=[Pax ID] );
-- Add the T-SQL statements to compute the return value here
-- Return the result of the function
RETURN @Result
END
GO
</code>
<code>
create FUNCTION [dbo].[getStratCityRev]
(
-- Add the parameters for the function here
@rgId int, @pid int
)
RETURNS nvarchar(5)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result nvarchar(5)
select @Result = t.[Off] from table1 t
where CONVERT(int, dbo.fnRemoveNonNumericCharacters(t.lng_Res_Segments_Id_Nmbr)) = (select min(CONVERT(int, dbo.fnRemoveNonNumericCharacters(t2.lng_Res_Segments_Id_Nmbr))) from table1 t2 where t2.Reservation = @rgId and @pid=[Pax ID] );
-- Add the T-SQL statements to compute the return value here
-- Return the result of the function
RETURN @Result
END
GO
</code>
stored procedure
<code>
CREATE PROCEDURE [dbo].[getData]
-- Add the parameters for the Mmstored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select * from table1;
END
GO
</code>
<code>
CREATE VIEW [dbo].[mainview]
AS
SELECT DISTINCT
[Pax ID], Reservation, [First Name], Surname, dbo.getSamePAX([Pax ID], Reservation) AS paxInsame, dbo.getDatedif(Reservation) AS paxDifDate,
dbo.getCountPAX(Reservation) AS paxNo, dbo.getStratCity(Reservation, [Pax ID]) AS DepartCity, dbo.getEndCity(Reservation, [Pax ID]) AS ArrivalCity,
dbo.getMidCity(Reservation, [Pax ID]) AS midCity, [Flight Number]
FROM dbo.table1 AS t
GO
</code>
<code>
CREATE VIEW [dbo].[view_main2]
AS
SELECT [Pax ID], Reservation, [First Name], Surname, paxInsame, paxDifDate, paxNo, DepartCity, ArrivalCity, midCity,[Flight Number]
FROM dbo.mainview
WHERE DepartCity <> ArrivalCity
UNION
SELECT [Pax ID], Reservation, [First Name], Surname, paxInsame, paxDifDate, paxNo, dbo.getStratCityRev(Reservation, [Pax ID]), dbo.getEndCityRev(Reservation, [Pax ID]),
midCity,[Flight Number]
FROM dbo.mainview
WHERE DepartCity = ArrivalCity
GO
</code>
<code>
CREATE VIEW [dbo].[viw_table1]
AS
SELECT DISTINCT [Pax ID], Surname, [First Name], Reservation
FROM dbo.table1
GO
</code>
this queries helped me alot to achieve my target and remaining some other requirements to be added to the current output.
i might need some of your help on that .
😀
January 24, 2014 at 2:24 pm
I'm a little concerned for the mTVF that has a cursor in it. It'll be interesting to see how well that scales.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 2 posts - 31 through 31 (of 31 total)
You must be logged in to reply to this topic. Login to reply