Comments by satheeshkumar chinnadurai (Top 73 by date)

ALTER PROCEDURE [dbo].[p]
@Country INT,
@no_of_pass INT
AS
BEGIN

CREATE TABLE #temp1(id INT IDENTITY(1,1),SeatColumn INT,Seatrow INT,StartingPlace varchar(100),DestinationPlace varchar(100),Seatarr_Id int )

CREATE TABLE #outputdata(id INT IDENTITY(1,1),SeatColumn INT,Seatrow INT,StartingPlace varchar(100),DestinationPlace varchar(100),Seatarr_Id int )
insert into #temp1
SELECT

fsh.SeatColumn,Seatrow,sp.Placename as StartingPlace,st.Placename as DestinationPlace,fsh.Seatarr_Id
FROM FlightAvaliablity fa
Inner JOIN SourcePlace sp on fa.DestinationPlaceID =sp.PlaceID
LEFT JOIN SourcePlace st on fa.StartingPlaceID =st.PlaceID
Inner JOIN FlightSeatSize fsh on fsh.FlightAva_ID =fa.FlightAva_ID
WHERE sp.Countryid=@Country and fsh.BookedStatus=0




Declare @initcount int,@lastcount int

Declare @RowInit INt,@c INT, @RowCount INT
declare @t varchar(max),@SeatColumn int ,@Seatrow int,@SeatColumnprev int ,@Seatrowprev int,@match int
set @RowInit=1

select @c = count(1)
from #temp1

while(@RowInit<=@c)
begin



select @Seatrow = Seatrow
from #temp1
where id = @RowInit
if(@RowInit = 1)
begin
select @SeatColumn = SeatColumn
from #temp1
where id = @RowInit
set @SeatColumnprev = @SeatColumn
SET @Seatrowprev = @Seatrow
end
else
begin
-- print @SeatColumn
if(@Seatrow = @Seatrowprev)
begin
select @SeatColumn = SeatColumn
from #temp1
where id = @RowInit


if(@SeatColumn = @SeatColumnprev)
begin
print 'd '
end
else
begin

Declare @count int
set @count =1
select @SeatColumn = SeatColumn
from #temp1
where id = @RowInit AND Seatrow =@Seatrow


WHILE(@no_of_pass > @count )
BEGIN

IF (@count =1)
BEGIN
select @initcount=id from #temp1 where id = @RowInit AND Seatrow =@Seatrow
--select @SeatColumn = SeatColumn

END

IF (@count =@no_of_pass)
BEGIN
select @lastcount=id from #temp1 where id = @RowInit AND Seatrow =@Seatrow
--select @SeatColumn = SeatColumn

END
IF(@SeatColumn = @SeatColumnprev+1)
BEGIN
Insert into #outputdata
select SeatColumn,Seatrow,StartingPlace,DestinationPlace,Seatarr_Id from #temp1 where id = @RowInit AND Seatrow =@Seatrow
select @SeatColumn = SeatColumn
from #temp1
where id = @RowInit AND Seatrow =@Seatrow
END
ELSE
BEGIN
select @SeatColumnprev = SeatColumn
from #temp1
where id = @RowInit AND Seatrow =@Seatrow
END
SET @count =@count+1
END
END

--
END
ELSE
BEGIN
SET @Seatrowprev =@Seatrowprev +1
END
END
SET @RowInit =@RowInit +1
END
print @initcount
select * from #temp1
select * from #outputdata
drop table #outputdata
drop table #temp1
END

USE [Satrugan]
GO
/****** Object: StoredProcedure [dbo].[sp_print_flight_report] Script Date: 02/01/2016 18:45:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_print_flight_report]
@flight_no int
AS
BEGIN
select Firstname
from
UserDetails u inner join Ticket t on t.CreatedBy= u.userID
inner join FlightAvaliablity fa on t.TravelFlightAva_ID=fa.FlightAva_ID

Where fa.flightID=@flight_no

Select f.Fightno,PersonName,sp.Placename as startingplace
,sp1.Placename As deptureplace ,ts.Fare
from TicketSheet ts
inner join Ticket t on ts.TicketID=t.TicketID
inner join FlightAvaliablity fa on t.TravelFlightAva_ID=fa.FlightAva_ID
inner join SourcePlace sp on sp.PlaceID = ts.StartingPlaceID
inner join SourcePlace sp1 on sp1.PlaceID = ts.DepaturePlaceID
inner join Flights f on fa.flightID=f.flightID
--inner join FilghtSheets fcs on fcs.FlightAva_ID=fa.FlightAva_ID
Where f.flightID=@flight_no

END
GO
/****** Object: StoredProcedure [dbo].[search_flight_availability_report] Script Date: 02/01/2016 18:45:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[search_flight_availability_report]

@from_date DATETIMe

AS
BEGIN

select f.Fightno, fa.FlightID,t.TotalSheetSize,fa.TotalSeatCapacity,fa.TotalSeatCapacity-t.TotalSheetSize AS Remainingseat ,StartingTime
from Flights f
inner join
FlightAvaliablity fa
on f.FlightID = fa.FlightID
inner join Ticket t on t.TravelFlightAva_ID= fa.FlightAva_ID
Where CONVERT(VARCHAR(10),StartingDate,110) =CONVERT(VARCHAR(10),@from_date,110)
order by fa.FlightID
END
GO
/****** Object: StoredProcedure [dbo].[search_flight_availability] Script Date: 02/01/2016 18:45:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[search_flight_availability]
@from_destination INT,
@to_destination INT,
@from_date DATETIME,
@to_date DATETIME,
@no_of_pass INT
AS
BEGIN

CREATE TABLE #temp(id INT IDENTITY(1,1),FlightID INT,StartingPlaceID INT ,TotalSeatCapacity INT ,FlightAva_ID INT,DestinationPlaceID INT,StartingDate DATETIME, DestinationDate DATETIME)

insert into #temp
select FlightID ,TotalSeatCapacity ,FlightAva_ID,StartingPlaceID ,DestinationPlaceID ,StartingDate , DestinationDate
from FlightAvaliablity
where StartingPlaceID = @from_destination
AND DestinationPlaceID = @to_destination
AND CONVERT(VARCHAR(10),StartingDate,110) = CONVERT(VARCHAR(10),@from_date,110)
AND CONVERT(VARCHAR(10),DestinationDate,110) = CONVERT(VARCHAR(10),@to_date,110)
ORDER BY FlightID

-- select *
--from #temp

DECLARE @s INT
DECLARE @c INT

SET @s = 1

SELECT @c = COUNT(1)
FROM #temp

WHILE(@s <= @c)
BEGIN

declare @FlightID int ,@Seatrow int
--declare @TotalSeatCapacity int
declare @FlightAva_ID int

SELECT @FlightID = FlightID
--,
--@TotalSeatCapacity = TotalSeatCapacity ,
,@FlightAva_ID = FlightAva_ID
from #temp
where id = @s

--select @FlightID --,@TotalSeatCapacity ,@FlightAva_ID

CREATE TABLE #temp1(id INT IDENTITY(1,1),FlightID INT, AvailabityRow int)
select top 1 @Seatrow = Seatrow
from FlightSeatSize
where FlightID = @FlightID
order by Seatrow desc

declare @start int
set @start = 1
while(@start < = @Seatrow)
begin

--,@count@ int
--set @
-- if()
declare @s1 int, @c1 int
set @s1 = 1
select @c1 = count (1)
from FlightSeatSize
where FlightID = @FlightID and Seatrow = @Seatrow

declare @isAv int
set @isAv = 0

while(@s1 < = @c1)
begin
if exists( select 1 from TicketSheet where flightId = @FlightID and SeatRow = @start and SeatColumn = @s1 and FlightTravAreaID = @FlightAva_ID )
begin
set @isAv = @isAv+ 1
end
else
begin
if (@isAv > 0)
begin
set @isAv = @isAv - 1
end
end


SET @s1 = @s1 + 1
end
if (@isAv > 1)
begin
insert into #temp1
select @FlightID,@start
end
set @start =

can give me example please

ok thanks