CREATE PROCEDURE prCountInsurer
@BeginDate VarChar(10), --开始日期
@EndDate VarChar(10), --终止日期
@Insurer1 VarChar(100),
@Insurer2 VarChar(100),
@Insurer3 VarChar(100)
AS
BEGIN
DECLARE @SumBus Int,
@Total Float,
@Gather Float,
@Unit_no VarChar(100)-- Delete From tblInsurerTotal DECLARE crTotal Cursor
For
select DISTINCT tblBus.Unit_No From tblBusInsurer JOIN tblBus ON tblBusInsurer.Bus_No = tblBus.Bus_No
Where tblBusInsurer.InsurerDate between @BeginDate and @EndDate
Open crTotal Fetch First From crTotal Into @Unit_No ////////////// While (@@Fetch_Status = 0) ///////////////
Begin
Insert InTo tblInsurerTotal(Unit_Name,SumBus1,Total1,Gather1,SumBus2,Total2,Gather2,
SumBus3,Total3,Gather3,SumBus4,Total4,Gather4)
Values(@Unit_No,0,0.00,0.00,0,0.00,0.00,0,0.00,0.00,0,0.00,0.00) update tblInsurerTotal
set SumBus1 =
(Select Count(Receipt_No) FROM tblBusInsurer
JOIN tblBus ON tblBusInsurer.Bus_No = tblBus.Bus_No where Insurer_No = @Insurer1
AND tblBus.Unit_No = @Unit_No ),
Total1 =
(Select SUM(Total) FROM tblBusInsurer
JOIN tblBus ON tblBusInsurer.Bus_No = tblBus.Bus_No where Insurer_No = @Insurer1
AND tblBus.Unit_No = @Unit_No),
Gather1 =
(Select SUM(Gather) FROM tblBusInsurer
JOIN tblBus ON tblBusInsurer.Bus_No = tblBus.Bus_No where Insurer_No = @Insurer1
AND tblBus.Unit_No = @Unit_No),
SumBus2 =
(Select Count(Receipt_No) FROM tblBusInsurer
JOIN tblBus ON tblBusInsurer.Bus_No = tblBus.Bus_No where Insurer_No = @Insurer2
AND tblBus.Unit_No = @Unit_No),
Total2 =
(Select SUM(Total) FROM tblBusInsurer
JOIN tblBus ON tblBusInsurer.Bus_No = tblBus.Bus_No where Insurer_No = @Insurer2
AND tblBus.Unit_No = @Unit_No),
Gather2 =
(Select SUM(Gather) FROM tblBusInsurer
JOIN tblBus ON tblBusInsurer.Bus_No = tblBus.Bus_No where Insurer_No = @Insurer2
AND tblBus.Unit_No = @Unit_No ),
SumBus3 =
(Select Count(Receipt_No) FROM tblBusInsurer
JOIN tblBus ON tblBusInsurer.Bus_No = tblBus.Bus_No where Insurer_No = @Insurer3
AND tblBus.Unit_No = @Unit_No),
Total3 =
(Select SUM(Total) FROM tblBusInsurer
JOIN tblBus ON tblBusInsurer.Bus_No = tblBus.Bus_No where Insurer_No = @Insurer3
AND tblBus.Unit_No = @Unit_No),
Gather3 =
(Select SUM(Gather) FROM tblBusInsurer
JOIN tblBus ON tblBusInsurer.Bus_No = tblBus.Bus_No where Insurer_No = @Insurer3
AND tblBus.Unit_No = @Unit_No),
SumBus4 =
(Select Count(Receipt_No) FROM tblBusInsurer
JOIN tblBus ON tblBusInsurer.Bus_No = tblBus.Bus_No
where Insurer_No <> @Insurer3 and Insurer_No <> @Insurer2 and Insurer_No <> @Insurer1
AND tblBus.Unit_No = @Unit_No),
Total4 =
(Select SUM(Total) FROM tblBusInsurer
JOIN tblBus ON tblBusInsurer.Bus_No = tblBus.Bus_No
where Insurer_No = @Insurer3 and Insurer_No <> @Insurer2 and Insurer_No <> @Insurer1
AND tblBus.Unit_No = @Unit_No),
Gather4 =
(Select SUM(Gather) FROM tblBusInsurer
JOIN tblBus ON tblBusInsurer.Bus_No = tblBus.Bus_No
where Insurer_No = @Insurer3 and Insurer_No <> @Insurer2 and Insurer_No <> @Insurer1
AND tblBus.Unit_No = @Unit_No) WHERE Unit_Name = @Unit_No Fetch Next From crTotal Into @Unit_No
End
Close crTotal
Deallocate crTotalEND
@BeginDate VarChar(10), --开始日期
@EndDate VarChar(10), --终止日期
@Insurer1 VarChar(100),
@Insurer2 VarChar(100),
@Insurer3 VarChar(100)
AS
BEGIN
DECLARE @SumBus Int,
@Total Float,
@Gather Float,
@Unit_no VarChar(100)-- Delete From tblInsurerTotal DECLARE crTotal Cursor
For
select DISTINCT tblBus.Unit_No From tblBusInsurer JOIN tblBus ON tblBusInsurer.Bus_No = tblBus.Bus_No
Where tblBusInsurer.InsurerDate between @BeginDate and @EndDate
Open crTotal Fetch First From crTotal Into @Unit_No ////////////// While (@@Fetch_Status = 0) ///////////////
Begin
Insert InTo tblInsurerTotal(Unit_Name,SumBus1,Total1,Gather1,SumBus2,Total2,Gather2,
SumBus3,Total3,Gather3,SumBus4,Total4,Gather4)
Values(@Unit_No,0,0.00,0.00,0,0.00,0.00,0,0.00,0.00,0,0.00,0.00) update tblInsurerTotal
set SumBus1 =
(Select Count(Receipt_No) FROM tblBusInsurer
JOIN tblBus ON tblBusInsurer.Bus_No = tblBus.Bus_No where Insurer_No = @Insurer1
AND tblBus.Unit_No = @Unit_No ),
Total1 =
(Select SUM(Total) FROM tblBusInsurer
JOIN tblBus ON tblBusInsurer.Bus_No = tblBus.Bus_No where Insurer_No = @Insurer1
AND tblBus.Unit_No = @Unit_No),
Gather1 =
(Select SUM(Gather) FROM tblBusInsurer
JOIN tblBus ON tblBusInsurer.Bus_No = tblBus.Bus_No where Insurer_No = @Insurer1
AND tblBus.Unit_No = @Unit_No),
SumBus2 =
(Select Count(Receipt_No) FROM tblBusInsurer
JOIN tblBus ON tblBusInsurer.Bus_No = tblBus.Bus_No where Insurer_No = @Insurer2
AND tblBus.Unit_No = @Unit_No),
Total2 =
(Select SUM(Total) FROM tblBusInsurer
JOIN tblBus ON tblBusInsurer.Bus_No = tblBus.Bus_No where Insurer_No = @Insurer2
AND tblBus.Unit_No = @Unit_No),
Gather2 =
(Select SUM(Gather) FROM tblBusInsurer
JOIN tblBus ON tblBusInsurer.Bus_No = tblBus.Bus_No where Insurer_No = @Insurer2
AND tblBus.Unit_No = @Unit_No ),
SumBus3 =
(Select Count(Receipt_No) FROM tblBusInsurer
JOIN tblBus ON tblBusInsurer.Bus_No = tblBus.Bus_No where Insurer_No = @Insurer3
AND tblBus.Unit_No = @Unit_No),
Total3 =
(Select SUM(Total) FROM tblBusInsurer
JOIN tblBus ON tblBusInsurer.Bus_No = tblBus.Bus_No where Insurer_No = @Insurer3
AND tblBus.Unit_No = @Unit_No),
Gather3 =
(Select SUM(Gather) FROM tblBusInsurer
JOIN tblBus ON tblBusInsurer.Bus_No = tblBus.Bus_No where Insurer_No = @Insurer3
AND tblBus.Unit_No = @Unit_No),
SumBus4 =
(Select Count(Receipt_No) FROM tblBusInsurer
JOIN tblBus ON tblBusInsurer.Bus_No = tblBus.Bus_No
where Insurer_No <> @Insurer3 and Insurer_No <> @Insurer2 and Insurer_No <> @Insurer1
AND tblBus.Unit_No = @Unit_No),
Total4 =
(Select SUM(Total) FROM tblBusInsurer
JOIN tblBus ON tblBusInsurer.Bus_No = tblBus.Bus_No
where Insurer_No = @Insurer3 and Insurer_No <> @Insurer2 and Insurer_No <> @Insurer1
AND tblBus.Unit_No = @Unit_No),
Gather4 =
(Select SUM(Gather) FROM tblBusInsurer
JOIN tblBus ON tblBusInsurer.Bus_No = tblBus.Bus_No
where Insurer_No = @Insurer3 and Insurer_No <> @Insurer2 and Insurer_No <> @Insurer1
AND tblBus.Unit_No = @Unit_No) WHERE Unit_Name = @Unit_No Fetch Next From crTotal Into @Unit_No
End
Close crTotal
Deallocate crTotalEND
Fetch:The fetch type first cannot be used with forward only cursors.