declare date_cursor cursor for select distinct fdt from cj_pnrp
open date_cursor -----打开游标
fetch next from date_cursor into @date ------从游标取出日期
while @@fetch_status=0 ------循环
begin
----------------以下根据航班号定义游标---------------------------
declare fln_cursor cursor for select distinct fln from cj_pnrp where fdt=@date
open fln_cursor --------打开游标
fetch next from fln_cursor into @fln --------从游标取出航班号
while @@fetch_status=0 --------循环
begin
declare od_cursor cursor for select distinct bpt,opt from cj_pnrp where fdt=@date and fln=@fln
open od_cursor
fetch next from od_cursor into @origin,@destination
while @@fetch_status=0
begin
select @flytime=left(StartTime,2)+':'+right(StartTime,2) from t_zhflight where flight=@fln and startcity=@origin and endcity=@destination
select @datetime=@date+' '+@flytime
select @datetime1=dateadd(mi,-90,@datetime) -----飞机离港前90分钟
select @datetime2=dateadd(mi,-30,@datetime) -----飞机离港前30分钟
declare class_cursor cursor for select distinct idp from cj_pnrp where fdt=@date and fln=@fln
open class_cursor
fetch next from class_cursor into @class
while @@fetch_status=0
begin
declare pnr_cursor cursor for select distinct ipn from cj_pnrp where fdt=@date and fln=@fln and bpt=@origin and opt=@destination and idp=@class
open pnr_cursor
fetch next from pnr_cursor into @pnr
while @@fetch_status=0
begin
if exists(select * from Tempdb..sysobjects where id=object_id( 'tempdb..#tmp') and type='U')
drop table #tmp
set nocount on
select top 1 * into #tmp from bidt where flydate=@date and flight=@fln and seatclass=@class and startcity=@origin and endcity=@destination and recordno=@pnr
--------统计定座数
select @BookedbyClass=count(*) from #tmp where flydate=@date and seatclass=@class and flight=@fln and issuedate<=@datetime1 ----各舱位定座数
select @booked=count(*) from #tmp where flydate=@date and flight=@fln and issuedate<=@datetime1 ----总定座数
-------统计GOSHOW2数据(其中@goshow21是航班结载前90~30的数据;@goshow22是航班结载前30内的数据,两部分的和就是GOSHOW2数据)
select @goshow21=count(*) from #tmp where flydate=@date and seatclass=@class and flight=@fln and issuedate>@datetime1 and issuedate<=@datetime2
if exists(select * from T_RMSysData where flydate=@date and class=@class and planeno=@fln)
select @goshow22=goshow2,@goshow1=goshow1,@upgrade=upgrading,@missconnection=missconnect,@denied=denied from T_RMSysData where flydate=@date and class=@class and planeno=@fln
else
select @goshow22=0,@goshow1=0,@upgrade=0,@missconnection=0, @denied=0
select @goshow2=@goshow21+@goshow22
---------统计实际承运人数
select @ActualbyClass=count(*) from cj_pnrp where fdt=@date and fln=@fln and idp=@class -----各舱位人数
select @actual=count(*) from cj_pnrp where fdt=@date and fln=@fln -----总人数