SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GOALTER PROC SelectData
@STARTTIME DATETIME,
@ENDTIME DATETIME
AS
BEGIN
BEGIN TRAN
CREATE TABLE #TT_STABLE
(
AreaID INT,
Box VARCHAR(50),
PlanTieTime DATETIME,
RealTieTime DATETIME,
PlanEndTime DATETIME,
RealEndTime DATETIME
)
CREATE TABLE #TT_STABLE2
(
AreaID INT,
SumCount INT,
Box VARCHAR(50),
COUNT1 INT,
COUNT2 INT,
COUNT3 INT
)
DECLARE @KKQ VARCHAR(50)
DECLARE @BX VARCHAR(50)
DECLARE @ZJ INT
DECLARE @TQ INT
DECLARE @ZD INT
DECLARE @YC INT DECLARE @AreaID INT
DECLARE @AreaID1 VARCHAR(50)
DECLARE @AreaID2 VARCHAR(50)
DECLARE @AreaID3 VARCHAR(50)
DECLARE @AreaID4 VARCHAR(50)
DECLARE @AreaID5 VARCHAR(50)
DECLARE @AreaID6 VARCHAR(50)
DECLARE @Box VARCHAR(50)
DECLARE @PlanTieTime DATETIME
DECLARE @RealTieTime DATETIME
DECLARE @PlanEndTime DATETIME
DECLARE @RealEndTime DATETIME
DECLARE @COUNT INT
DECLARE @COUNT1 INT
DECLARE @COUNT2 INT
DECLARE @COUNT3 INT
DECLARE Row_CurSor CURSOR for
SELECT AreaID, LEFT(BoxCode, 2) AS BoxCode, PlanTieTime, RealTieTime, PlanEndTime,RealEndTime
FROM tblUTPDetail
WHERE RealTieTime IS NOT NULL AND PlanTieTime IS NOT NULL AND
PlanEndTime IS NOT NULL AND RealEndTime IS NOT NULL AND
CONVERT(varchar(10), RealTieTime, 126) >= CONVERT(varchar(10), @STARTTIME, 126)
AND CONVERT(varchar(10), PlanTieTime, 126) >= CONVERT(varchar(10),@STARTTIME, 126)
AND CONVERT(varchar(10), PlanEndTime, 126) <= CONVERT(varchar(10), @ENDTIME, 126)
AND CONVERT(varchar(10), RealEndTime, 126) <= CONVERT(varchar(10), @ENDTIME, 126)
OPEN Row_CurSor
FETCH NEXT FROM Row_CurSor INTO @AreaID,@Box,@PlanTieTime,@RealTieTime,@PlanEndTime,@RealEndTime
while @@Fetch_Status=0
BEGIN
INSERT INTO #TT_STABLE (AreaID,Box,PlanTieTime,RealTieTime,PlanEndTime,RealEndTime) VALUES (@AreaID,@Box,@PlanTieTime,@RealTieTime,@PlanEndTime,@RealEndTime)
IF @AreaID=1
BEGIN
SET @AreaID1='#1'
END
ELSE IF @AreaID=2
BEGIN
SET @AreaID2='#2'
END
ELSE IF @AreaID=3
BEGIN
SET @AreaID3='#3'
END
ELSE IF @AreaID=4
BEGIN
SET @AreaID4='#4'
END
ELSE IF @AreaID=5
BEGIN
SET @AreaID5='#5'
END
ELSE IF @AreaID=6
BEGIN
SET @AreaID6='#6'
END
IF @AreaID1='#1'
BEGIN
SELECT @COUNT1=COUNT(*) FROM #TT_STABLE WHERE AreaID =1 AND CONVERT(varchar(10), PlanTieTime, 126) <= CONVERT(varchar(10), @STARTTIME, 126) AND CONVERT(varchar(10), RealTieTime, 126) <= CONVERT(varchar(10), @STARTTIME, 126) AND CONVERT(varchar(10), RealEndTime, 126) >= CONVERT(varchar(10), @PlanEndTime, 126) AND CONVERT(varchar(10), RealEndTime, 126) >= CONVERT(varchar(10), @RealEndTime, 126) and LEFT(Box,2)=@Box AND RealEndTime IS NOT NULL AND PlanEndTime IS NOT NULL AND PlanTieTime IS NOT NULL AND RealTieTime IS NOT NULL
SELECT @COUNT2=COUNT(*) FROM #TT_STABLE WHERE AreaID =1 AND CONVERT(varchar(10), PlanTieTime, 126) <= CONVERT(varchar(10), @STARTTIME, 126) AND CONVERT(varchar(10), RealTieTime, 126) <= CONVERT(varchar(10), @STARTTIME, 126) AND CONVERT(varchar(10), RealEndTime, 126) >= CONVERT(varchar(10), @PlanEndTime, 126) AND CONVERT(varchar(10), RealEndTime, 126) >= CONVERT(varchar(10), @RealEndTime, 126) and LEFT(Box,2)=@Box AND RealEndTime IS NOT NULL AND PlanEndTime IS NOT NULL AND PlanTieTime IS NOT NULL AND RealTieTime IS NOT NULL
SELECT @COUNT3=COUNT(*) FROM #TT_STABLE WHERE AreaID =1 AND CONVERT(varchar(10), PlanTieTime, 126) <= CONVERT(varchar(10), @STARTTIME, 126) AND CONVERT(varchar(10), RealTieTime, 126) <= CONVERT(varchar(10), @STARTTIME, 126) AND CONVERT(varchar(10), RealEndTime, 126) >= CONVERT(varchar(10), @PlanEndTime, 126) AND CONVERT(varchar(10), RealEndTime, 126) >= CONVERT(varchar(10), @RealEndTime, 126) and LEFT(Box,2)=@Box AND RealEndTime IS NOT NULL AND PlanEndTime IS NOT NULL AND PlanTieTime IS NOT NULL AND RealTieTime IS NOT NULL
INSERT INTO #TT_STABLE2 (AreaID,Box,SumCount,COUNT1,COUNT2,COUNT3) VALUES (1,@Box,@COUNT1+@COUNT2+@COUNT3,@COUNT1,@COUNT2,@COUNT3)
END FETCH NEXT FROM Row_CurSor INTO @AreaID,@Box,@PlanTieTime,@RealTieTime,@PlanEndTime,@RealEndTime
END
CLOSE Row_CurSor
DEALLOCATE Row_CurSor
select * from #TT_STABLE DROP TABLE #TT_STABLE
ROLLBACK TRAN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
运行速度非常之慢,不知道怎么回事,1分钟都查不出来,还是我哪里写错了啊?CONVERT(varchar(10), RealTieTime, 126) >= CONVERT(varchar(10), @STARTTIME, 126)
AND CONVERT(varchar(10), PlanTieTime, 126) >= CONVERT(varchar(10),@STARTTIME, 126)
AND CONVERT(varchar(10), PlanEndTime, 126) <= CONVERT(varchar(10), @ENDTIME, 126)
AND CONVERT(varchar(10), RealEndTime, 126) <= CONVERT(varchar(10), @ENDTIME, 126)
这点写的对吗?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货