mehdis2
چهارشنبه 21 مهر 1389, 12:33 عصر
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Rep_Day_Result]
@EXPDate_Start nvarchar(10),
@EXPDate_End nvarchar(10),
@PGMyKey INT,
@EXPGMyKey INT,
@InsMyKey INT ,
@Is_Avg BIT,
@Rows_Inst INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
declare @EXPTime_master nchar(10);
declare @EXPInsName_master nchar(10);
declare @EXPTime nchar(10);
declare @EXPValue nchar(10);
declare @EXPTName nchar(10);
declare @EXPInsName nchar(10);
DECLARE @F1 nchar(10);
DECLARE @F2 nchar(10);
DECLARE @F3 nchar(10);
DECLARE @F4 nchar(10);
DECLARE @F5 nchar(10);
DECLARE @F6 nchar(10);
DECLARE @F7 nchar(10);
DECLARE @F8 nchar(10);
DECLARE @F9 nchar(10);
DECLARE @F10 nchar(10);
DECLARE @F11 nchar(10);
DECLARE @F12 nchar(10);
DECLARE @F13 nchar(10);
DECLARE @F14 nchar(10);
DECLARE @F15 nchar(10);
DECLARE @test INT;
DECLARE @P nchar(10);
DECLARE @Row int;
DECLARE @Row_Number int;
DECLARE @EXPGName1 nchar(20);
DECLARE @EXPGName2 nchar(20);
DECLARE @EXPGName3 nchar(20);
DECLARE @EXPGName4 nchar(20);
DECLARE @EXPGName5 nchar(20);
DECLARE @EXPGName6 nchar(20);
DECLARE @EXPGName7 nchar(20);
DECLARE @EXPGName8 nchar(20);
DECLARE @EXPGName9 nchar(20);
DECLARE @EXPGName10 nchar(20);
DECLARE @Name nchar(20);
DECLARE @EXPGName11 nchar(20);
DECLARE @EXPGName12 nchar(20);
DECLARE @EXPGName13 nchar(20);
DECLARE @EXPGName14 nchar(20);
DECLARE @EXPGName15 nchar(20);
DECLARE @EXPTMyKey1 int;
DECLARE @EXPTMyKey2 int;
DECLARE @EXPTMyKey3 int;
DECLARE @EXPTMyKey4 int;
DECLARE @EXPTMyKey5 int;
DECLARE @EXPTMyKey6 int;
DECLARE @EXPTMyKey7 int;
DECLARE @EXPTMyKey8 int;
DECLARE @EXPTMyKey9 int;
DECLARE @EXPTMyKey10 int;
DECLARE @EXPTMyKey11 int;
DECLARE @EXPTMyKey12 int;
DECLARE @EXPTMyKey13 int;
DECLARE @EXPTMyKey14 int;
DECLARE @EXPTMyKey15 int;
DELETE FROM ExpRep_Day_Result_Tittle_Tmp
IF EXISTS (SELECT * FROM tem_rep )
BEGIN
DELETE FROM ExpRep_Day_Result_Tittle_Tmp WHERE 1=1
DELETE FROM tem_rep
END
SET @test=0;
Create table #All_EXPTName
([InsMyKey] INT PRIMARY KEY ,
[F] [nchar](5) ,
)
DECLARE @EXPTMyKey_cr nchar(5);
DECLARE @EXPTName_cr nchar(20);
DECLARE @Number_Filed INT;
SET @Number_Filed=1
--DEALLOCATE cr_All_EXPTName
IF (@InsMyKey IS NOT NULL )
BEGIN
DECLARE cr_All_EXPTName CURSOR FOR SELECT DISTINCT EXPTMyKey, EXPTName FROM TempRep1 WHERE EXPDate>=@EXPDate_Start AND
EXPDate<=@EXPDate_end and
pgmykey=@PGMyKey AND EXPGMyKey=@EXPGMyKey AND EXPInsMyKey=@InsMyKey
--AND EXPValue IS NOT NULL
ORDER BY EXPTName
OPEN cr_All_EXPTName
END
ELSE IF 1=1
BEGIN
DECLARE cr_All_EXPTName CURSOR FOR SELECT DISTINCT EXPTMyKey, EXPTName FROM TempRep1 WHERE EXPDate>=@EXPDate_Start AND
EXPDate<=@EXPDate_end and
pgmykey=@PGMyKey AND EXPGMyKey=@EXPGMyKey AND EXPInsMyKey IS NULL
--ND EXPValue IS NOT NULL
ORDER BY EXPTName
OPEN cr_All_EXPTName
END
FETCH NEXT FROM cr_All_EXPTName INTO @EXPTMyKey_cr,@EXPTName_cr
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTo #All_EXPTName
(InsMyKey, F) VALUES ( @EXPTMyKey_cr,@Number_Filed)
IF (@Number_Filed=1)
BEGIN
set @EXPGName1=@EXPTName_cr
SET @EXPTMyKey1=@EXPTMyKey_cr
END
ELSE IF (@Number_Filed=2)
BEGIN
set @EXPGName2=@EXPTName_cr
set @EXPTMyKey2=@EXPTMyKey_cr
END
ELSE IF (@Number_Filed=3)
BEGIN
set @EXPGName3=@EXPTName_cr
SET @EXPTMyKey3=@EXPTMyKey_cr
END
ELSE IF (@Number_Filed=4)
BEGIN
set @EXPGName4=@EXPTName_cr
set @EXPTMyKey4=@EXPTMyKey_cr
END
ELSE IF (@Number_Filed=5)
BEGIN
set @EXPGName5=@EXPTName_cr
set @EXPTMyKey5=@EXPTMyKey_cr
END
IF (@Number_Filed=6)
BEGIN
set @EXPGName6=@EXPTName_cr
set @EXPTMyKey6=@EXPTMyKey_cr
END
ELSE IF (@Number_Filed=7)
BEGIN
set @EXPGName7=@EXPTName_cr
set @EXPTMyKey7=@EXPTMyKey_cr
END
ELSE IF (@Number_Filed=8)
BEGIN
set @EXPGName8=@EXPTName_cr
set @EXPTMyKey8=@EXPTMyKey_cr
END
ELSE IF (@Number_Filed=9)
BEGIN
set @EXPGName9=@EXPTName_cr
set @EXPTMyKey9=@EXPTMyKey_cr
END
ELSE IF (@Number_Filed=10)
BEGIN
set @EXPGName10=@EXPTName_cr
set @EXPTMyKey10=@EXPTMyKey_cr
END
ELSE IF (@Number_Filed=11)
BEGIN
set @EXPGName11=@EXPTName_cr
SET @EXPTMyKey11=@EXPTMyKey_cr
END
ELSE IF (@Number_Filed=12)
BEGIN
set @EXPGName12=@EXPTName_cr
SET @EXPTMyKey12=@EXPTMyKey_cr
END
ELSE IF (@Number_Filed=13)
BEGIN
set @EXPGName13=@EXPTName_cr
SET @EXPTMyKey13=@EXPTMyKey_cr
END
ELSE IF (@Number_Filed=14)
BEGIN
set @EXPGName14=@EXPTName_cr
SET @EXPTMyKey14=@EXPTMyKey_cr
END
ELSE IF (@Number_Filed=15)
BEGIN
set @EXPGName15=@EXPTName_cr
SET @EXPTMyKey15=@EXPTMyKey_cr
END
FETCH NEXT FROM cr_All_EXPTName INTO @EXPTMyKey_cr,@EXPTName_cr
SET @Number_Filed=@Number_Filed+1
END
CLOSE cr_All_EXPTName
DEALLOCATE cr_All_EXPTName
---قرار دادن كد هاي آزمايشات در رديف اول جدول
INSERT INTO tem_rep
(
ROW_NUMBER,F1,F2,F3,F4,F5,F6,F7,F8,F9, F10, F11, F12, F13, F14, F15
)
VALUES
(
0,null,NULL,@EXPTMyKey1,@EXPTMyKey2,@EXPTMyKey3,@E XPTMyKey4,@EXPTMyKey5,@EXPTMyKey6,@EXPTMyKey7,@EXP TMyKey8,@EXPTMyKey9, @EXPTMyKey10, @EXPTMyKey11, @EXPTMyKey12, @EXPTMyKey13
)
DECLARE @EXPDate nchar(10);
--- get unit
DECLARE @UnitFkey int;
IF @EXPTMyKey1 IS NOT NULL
BEGIN
SET @UnitFkey=-1;
SELECT @UnitFkey=eiu.UnitFkey FROM ExpInsUnits eiu WHERE eiu.PGFkey=@PGMyKey AND eiu.EGETFkey=(SELECT ee.MyKey FROM EXPGroupsEXPTypes ee WHERE ee.EXPGroupFkey=@EXPGMyKey AND ee.EXPTypeFkey=@EXPTMyKey1)
IF @UnitFkey =-1
SELECT @UnitFkey=pge.UnitTypeFkey FROM ProductGroupsEXPTypes pge WHERE pge.EXPTypesFkey=@EXPTMyKey1 AND pge.ProductGroupsFkey=@PGMyKey
IF (@UnitFkey <>-1)
BEGIN
select @EXPGName1=rtrim(ltrim(@EXPGName1))+'('+rtrim(ltri m(ut.Name))+')' FROM UnitTypes ut WHERE ut.MyKey= @UnitFkey
END;
END
--2
IF @EXPTMyKey1 IS NOT NULL
BEGIN
SELECT @UnitFkey=eiu.UnitFkey FROM ExpInsUnits eiu WHERE eiu.PGFkey=@PGMyKey AND eiu.EGETFkey=(SELECT ee.MyKey FROM EXPGroupsEXPTypes ee WHERE ee.EXPGroupFkey=@EXPGMyKey AND ee.EXPTypeFkey=@EXPTMyKey2)
IF @UnitFkey =-1
SELECT @UnitFkey=pge.UnitTypeFkey FROM ProductGroupsEXPTypes pge WHERE pge.EXPTypesFkey=@EXPTMyKey2 AND pge.ProductGroupsFkey=@PGMyKey
IF (@UnitFkey <>-1)
BEGIN
select @EXPGName2=rtrim(ltrim(@EXPGName2))+'('+rtrim(ltri m(ut.Name))+')' FROM UnitTypes ut WHERE ut.MyKey= @UnitFkey
END;
END;
--3
IF @EXPTMyKey3 IS NOT NULL
BEGIN
SELECT @UnitFkey=eiu.UnitFkey FROM ExpInsUnits eiu WHERE eiu.PGFkey=@PGMyKey AND eiu.EGETFkey=(SELECT ee.MyKey FROM EXPGroupsEXPTypes ee WHERE ee.EXPGroupFkey=@EXPGMyKey AND ee.EXPTypeFkey=@EXPTMyKey3)
IF @UnitFkey =-1
SELECT @UnitFkey=pge.UnitTypeFkey FROM ProductGroupsEXPTypes pge WHERE pge.EXPTypesFkey=@EXPTMyKey3 AND pge.ProductGroupsFkey=@PGMyKey
IF (@UnitFkey <>-1)
BEGIN
select @EXPGName3=rtrim(ltrim(@EXPGName3))+'('+rtrim(ltri m(ut.Name))+')' FROM UnitTypes ut WHERE ut.MyKey= @UnitFkey
END;
END;
IF @EXPTMyKey4 IS NOT NULL
BEGIN
SET @UnitFkey=-1;
SELECT @UnitFkey=eiu.UnitFkey FROM ExpInsUnits eiu WHERE eiu.PGFkey=@PGMyKey AND eiu.EGETFkey=(SELECT ee.MyKey FROM EXPGroupsEXPTypes ee WHERE ee.EXPGroupFkey=@EXPGMyKey AND ee.EXPTypeFkey=@EXPTMyKey1)
IF @UnitFkey =-1
SELECT @UnitFkey=pge.UnitTypeFkey FROM ProductGroupsEXPTypes pge WHERE pge.EXPTypesFkey=@EXPTMyKey1 AND pge.ProductGroupsFkey=@PGMyKey
IF (@UnitFkey <>-1)
BEGIN
select @EXPGName4=rtrim(ltrim(@EXPGName4))+'('+rtrim(ltri m(ut.Name))+')' FROM UnitTypes ut WHERE ut.MyKey= @UnitFkey
END;
END
--ستون 5
IF @EXPTMyKey5 IS NOT NULL
BEGIN
SET @UnitFkey=-1;
SELECT @UnitFkey=eiu.UnitFkey FROM ExpInsUnits eiu WHERE eiu.PGFkey=@PGMyKey AND eiu.EGETFkey=(SELECT ee.MyKey FROM EXPGroupsEXPTypes ee WHERE ee.EXPGroupFkey=@EXPGMyKey AND ee.EXPTypeFkey=@EXPTMyKey5)
IF @UnitFkey =-1
SELECT @UnitFkey=pge.UnitTypeFkey FROM ProductGroupsEXPTypes pge WHERE pge.EXPTypesFkey=@EXPTMyKey5 AND pge.ProductGroupsFkey=@PGMyKey
IF (@UnitFkey <>-1)
BEGIN
select @EXPGName5=rtrim(ltrim(@EXPGName5))+'('+rtrim(ltri m(ut.Name))+')' FROM UnitTypes ut WHERE ut.MyKey= @UnitFkey
END;
END
--ستون 6
IF @EXPTMyKey6 IS NOT NULL
BEGIN
SET @UnitFkey=-1;
SELECT @UnitFkey=eiu.UnitFkey FROM ExpInsUnits eiu WHERE eiu.PGFkey=@PGMyKey AND eiu.EGETFkey=(SELECT ee.MyKey FROM EXPGroupsEXPTypes ee WHERE ee.EXPGroupFkey=@EXPGMyKey AND ee.EXPTypeFkey=@EXPTMyKey6)
IF @UnitFkey =-1
SELECT @UnitFkey=pge.UnitTypeFkey FROM ProductGroupsEXPTypes pge WHERE pge.EXPTypesFkey=@EXPTMyKey6 AND pge.ProductGroupsFkey=@PGMyKey
IF (@UnitFkey <>-1)
BEGIN
select @EXPGName6=rtrim(ltrim(@EXPGName6))+'('+rtrim(ltri m(ut.Name))+')' FROM UnitTypes ut WHERE ut.MyKey= @UnitFkey
END;
END
--ستون 7
IF @EXPTMyKey7 IS NOT NULL
BEGIN
SET @UnitFkey=-1;
SELECT @UnitFkey=eiu.UnitFkey FROM ExpInsUnits eiu WHERE eiu.PGFkey=@PGMyKey AND eiu.EGETFkey=(SELECT ee.MyKey FROM EXPGroupsEXPTypes ee WHERE ee.EXPGroupFkey=@EXPGMyKey AND ee.EXPTypeFkey=@EXPTMyKey7)
IF @UnitFkey =-1
SELECT @UnitFkey=pge.UnitTypeFkey FROM ProductGroupsEXPTypes pge WHERE pge.EXPTypesFkey=@EXPTMyKey7 AND pge.ProductGroupsFkey=@PGMyKey
IF (@UnitFkey <>-1)
BEGIN
select @EXPGName7=rtrim(ltrim(@EXPGName7))+'('+rtrim(ltri m(ut.Name))+')' FROM UnitTypes ut WHERE ut.MyKey= @UnitFkey
END;
END
--ستون 8
IF @EXPTMyKey8 IS NOT NULL
BEGIN
SET @UnitFkey=-1;
SELECT @UnitFkey=eiu.UnitFkey FROM ExpInsUnits eiu WHERE eiu.PGFkey=@PGMyKey AND eiu.EGETFkey=(SELECT ee.MyKey FROM EXPGroupsEXPTypes ee WHERE ee.EXPGroupFkey=@EXPGMyKey AND ee.EXPTypeFkey=@EXPTMyKey8)
IF @UnitFkey =-1
SELECT @UnitFkey=pge.UnitTypeFkey FROM ProductGroupsEXPTypes pge WHERE pge.EXPTypesFkey=@EXPTMyKey8 AND pge.ProductGroupsFkey=@PGMyKey
IF (@UnitFkey <>-1)
BEGIN
select @EXPGName8=rtrim(ltrim(@EXPGName8))+'('+rtrim(ltri m(ut.Name))+')' FROM UnitTypes ut WHERE ut.MyKey= @UnitFkey
END;
END
--ستون 9
IF @EXPTMyKey9 IS NOT NULL
BEGIN
SET @UnitFkey=-1;
SELECT @UnitFkey=eiu.UnitFkey FROM ExpInsUnits eiu WHERE eiu.PGFkey=@PGMyKey AND eiu.EGETFkey=(SELECT ee.MyKey FROM EXPGroupsEXPTypes ee WHERE ee.EXPGroupFkey=@EXPGMyKey AND ee.EXPTypeFkey=@EXPTMyKey9)
IF @UnitFkey =-1
SELECT @UnitFkey=pge.UnitTypeFkey FROM ProductGroupsEXPTypes pge WHERE pge.EXPTypesFkey=@EXPTMyKey9 AND pge.ProductGroupsFkey=@PGMyKey
IF (@UnitFkey <>-1)
BEGIN
select @EXPGName9=rtrim(ltrim(@EXPGName9))+'('+rtrim(ltri m(ut.Name))+')' FROM UnitTypes ut WHERE ut.MyKey= @UnitFkey
END;
END
--ستون 10
IF @EXPTMyKey10 IS NOT NULL
BEGIN
SET @UnitFkey=-1;
SELECT @UnitFkey=eiu.UnitFkey FROM ExpInsUnits eiu WHERE eiu.PGFkey=@PGMyKey AND eiu.EGETFkey=(SELECT ee.MyKey FROM EXPGroupsEXPTypes ee WHERE ee.EXPGroupFkey=@EXPGMyKey AND ee.EXPTypeFkey=@EXPTMyKey10)
IF @UnitFkey =-1
SELECT @UnitFkey=pge.UnitTypeFkey FROM ProductGroupsEXPTypes pge WHERE pge.EXPTypesFkey=@EXPTMyKey10 AND pge.ProductGroupsFkey=@PGMyKey
IF (@UnitFkey <>-1)
BEGIN
select @EXPGName10=rtrim(ltrim(@EXPGName10))+'('+rtrim(lt rim(ut.Name))+')' FROM UnitTypes ut WHERE ut.MyKey= @UnitFkey
END;
END
--ستون 11
IF @EXPTMyKey11 IS NOT NULL
BEGIN
SET @UnitFkey=-1;
SELECT @UnitFkey=eiu.UnitFkey FROM ExpInsUnits eiu WHERE eiu.PGFkey=@PGMyKey AND eiu.EGETFkey=(SELECT ee.MyKey FROM EXPGroupsEXPTypes ee WHERE ee.EXPGroupFkey=@EXPGMyKey AND ee.EXPTypeFkey=@EXPTMyKey11)
IF @UnitFkey =-1
SELECT @UnitFkey=pge.UnitTypeFkey FROM ProductGroupsEXPTypes pge WHERE pge.EXPTypesFkey=@EXPTMyKey11 AND pge.ProductGroupsFkey=@PGMyKey
IF (@UnitFkey <>-1)
BEGIN
select @EXPGName11=rtrim(ltrim(@EXPGName11))+'('+rtrim(lt rim(ut.Name))+')' FROM UnitTypes ut WHERE ut.MyKey= @UnitFkey
END;
END
--ستون 12
IF @EXPTMyKey12 IS NOT NULL
BEGIN
SET @UnitFkey=-1;
SELECT @UnitFkey=eiu.UnitFkey FROM ExpInsUnits eiu WHERE eiu.PGFkey=@PGMyKey AND eiu.EGETFkey=(SELECT ee.MyKey FROM EXPGroupsEXPTypes ee WHERE ee.EXPGroupFkey=@EXPGMyKey AND ee.EXPTypeFkey=@EXPTMyKey12)
IF @UnitFkey =-1
SELECT @UnitFkey=pge.UnitTypeFkey FROM ProductGroupsEXPTypes pge WHERE pge.EXPTypesFkey=@EXPTMyKey12 AND pge.ProductGroupsFkey=@PGMyKey
IF (@UnitFkey <>-1)
BEGIN
select @EXPGName12=rtrim(ltrim(@EXPGName12))+'('+rtrim(lt rim(ut.Name))+')' FROM UnitTypes ut WHERE ut.MyKey= @UnitFkey
END;
END
--ستون 13
IF @EXPTMyKey13 IS NOT NULL
BEGIN
SET @UnitFkey=-1;
SELECT @UnitFkey=eiu.UnitFkey FROM ExpInsUnits eiu WHERE eiu.PGFkey=@PGMyKey AND eiu.EGETFkey=(SELECT ee.MyKey FROM EXPGroupsEXPTypes ee WHERE ee.EXPGroupFkey=@EXPGMyKey AND ee.EXPTypeFkey=@EXPTMyKey13)
IF @UnitFkey =-1
SELECT @UnitFkey=pge.UnitTypeFkey FROM ProductGroupsEXPTypes pge WHERE pge.EXPTypesFkey=@EXPTMyKey13 AND pge.ProductGroupsFkey=@PGMyKey
IF (@UnitFkey <>-1)
BEGIN
select @EXPGName13=rtrim(ltrim(@EXPGName13))+'('+rtrim(lt rim(ut.Name))+')' FROM UnitTypes ut WHERE ut.MyKey= @UnitFkey
END;
END
SET @EXPDate='تاريخ'
INSERT INTO ExpRep_Day_Result_Tittle_Tmp
(
F1,F2,F3,F4,F5, F6,F7,F8,F9,F10, F11, F12, F13, F14, F15
)
VALUES
(
@EXPDate,'ساعت',@EXPGName1,@EXPGName2,@EXPGNam e3,@EXPGName4,@EXPGName5,@EXPGName6,@EXPGName7,@EX PGName8,@EXPGName9,@EXPGName10,@EXPGName11,@EXPGNa me12,@EXPGName13
)
DECLARE @EXPDate_old nchar(10);
DECLARE @EXPTime_old nchar(8);
DECLARE @EXPTMyKey_old INT;
SET @EXPTime_old='-aaaa';
SET @EXPDate_old='dfsa'
If Exists(Select * from TempDB.dbo.sysObjects where [NAME] LIKE '%#Result_EXp22%')
drop table #Result_EXp22
CREATE TABLE #Result_EXp22
([Row_Num] INT,
[EXPdate] [nchar](10) ,
[EXPTime] [nchar](8) ,
[EXPValue] [nchar](50) ,
[EXPTMyKey] [nchar](10) ,
)
---(Row_Num,EXPTime,EXPValue,EXPTMyKey) VALUES
create clustered index Tind on #Result_EXp22 (row_num)
DECLARE @EXPTMyKey_cr_detail int;
IF (@InsMyKey IS not NULL)
BEGIN
INSERT INTo #Result_EXp22
SELECT CAST (ROW_NUMBER() OVER (ORDER BY EXPDate,EXPTime) AS INT),[EXPdate] ,
[EXPTime] ,
[EXPValue] ,
[EXPTMyKey]
FROM TempRep1 WHERE EXPDate>=@EXPDate_Start AND
EXPDate<=@EXPDate_end and
pgmykey=@PGMyKey AND EXPGMyKey=@EXPGMyKey AND EXPInsMyKey=@InsMyKey AND EXPValue IS NOT NULL
ORDER BY EXPDate,EXPTName
END
ELSE IF (1=1)
BEGIN
INSERT INTo #Result_EXp22
SELECT CAST (ROW_NUMBER() OVER (ORDER BY EXPDate,EXPTime) AS INT),[EXPdate] ,
[EXPTime] ,
[EXPValue] ,
[EXPTMyKey]
FROM TempRep1 WHERE EXPDate>=@EXPDate_Start AND
EXPDate<=@EXPDate_end and
pgmykey=@PGMyKey AND EXPGMyKey=@EXPGMyKey AND EXPInsMyKey IS NULL AND EXPValue IS NOT NULL
ORDER BY EXPDate,EXPTime,EXPTName
END
SET @Row=1
SET SET @Row=1
-- detal
DECLARE @Col_First BIT;
SET @Col_First=1
Declare @i INT
SET @i=1
Declare @sum INT
select @sum=count(row_num) FROM #Result_EXp22
SET @F1='--' SET @F2='--' SET @F3='--' SET @F4='--' SET @F5='--' SET @F6='--' SET @F7='--' SET @F8='--' SET @F9='--' SET @F10='--' SET @F11='--' SET @F12='--' SET @F13='--' SET @F14='--' SET @F15='--'
WHILE @i <= (@sum)+1
BEGIN
--SELECT @i
SELECT @EXPDate=EXPDate,@EXPTime=EXPTime,@EXPValue=EXPVal ue,@EXPTMyKey_cr_detail =EXPTMyKey
FROM #Result_EXp22
WHERE row_num = @i
--SELECT @i AS i,@EXPDate_old,@EXPTime_old,@F1 AS f1,@F2 AS f2,@F3 AS f3,@F4 AS f4,@F5,@F6,@F7,@F8,@F9,@F10,@F11,@F12,@F13 ,LEN(@EXPTime_old) AS len
IF ((ISNULL(LTRIM(@EXPTime_old),0) <>ISNULL (LTRIM(@EXPTime),0) AND (@EXPdate_old =@EXPdate)) OR (@EXPdate_old <>@EXPdate) OR (@i=@sum+1 ))
BEGIN
IF (@Col_First=0)
BEGIN
INSERT INTO tem_rep
(
ROW_NUMBER,F1,F2,F3,F4,F5,F6,F7,F8,F9, F10, F11, F12, F13, F14, F15
)
VALUES
(
@i,@EXPDate_old,@EXPTime_old,@F1,@F2,@F3,@F4,@F5,@ F6,@F7,@F8,@F9,@F10,@F11,@F12,@F13
)
--SELECT @i,1,@EXPDate_old,@EXPTime_old,'old_inserted',@EXP Date,@EXPTime
SET @F1='--' SET @F2='--' SET @F3='--' SET @F4='--' SET @F5='--' SET @F6='--' SET @F7='--' SET @F8='--' SET @F9='--' SET @F10='--' SET @F11='--' SET @F12='--' SET @F13='--' SET @F14='--' SET @F15='--'
END
END
set @Row=null
SELECT @Row=ae.F FROM #All_EXPTName ae WHERE ae.InsMyKey=@EXPTMyKey_cr_detail
IF (@Row=1)
SET @F1=@EXPValue
ELSE IF (@Row=2)
SET @F2=@EXPValue
ELSE IF (@Row=3)
SET @F3=@EXPValue
ELSE IF (@Row=4)
SET @F4=@EXPValue
ELSE IF (@Row=5)
SET @F5=@EXPValue
ELSE IF (@Row=6)
SET @F6=@EXPValue
ELSE IF (@Row=7)
SET @F7=@EXPValue
ELSE IF (@Row=8)
SET @F8=@EXPValue
ELSE IF (@Row=9)
SET @F9=@EXPValue
ELSE IF (@Row=10)
SET @F10=@EXPValue
ELSE IF (@Row=11)
SET @F11=@EXPValue
ELSE IF (@Row=12)
SET @F12=@EXPValue
ELSE IF (@Row=13)
SET @F13=@EXPValue
ELSE IF (@Row=14)
SET @F14=@EXPValue
ELSE IF (@Row=15)
SET @F15=@EXPValue
IF (@Col_First=1)
SET @Col_First=0
SET @EXPTime_old =@EXPTime
SET @EXPdate_old =@EXPdate
SET @EXPTMyKey_old = @EXPTMyKey_cr_detail
SET @i=@i+1;
END
SELECT @Rows_Inst=COUNT(*) FROM #All_EXPTName
IF (@Rows_Inst=1)
SELECT F1,F2,F3 FROM tem_rep
ELSE IF (@Rows_Inst=2)
SELECT F1,F2,F3,F4 FROM tem_rep
IF (@Rows_Inst=3)
SELECT F1,F2,F3,F4,F5 FROM tem_rep
ELSE IF (@Rows_Inst=4)
SELECT F1,F2,F3,F4,F5,F6 FROM tem_rep
ELSE IF (@Rows_Inst=5)
SELECT F1,F2,F3,F4,F5,F6,F7 FROM tem_rep
ELSE IF (@Rows_Inst=6)
SELECT F1,F2,F3,F4,F5,F6,F7,F8 FROM tem_rep
ELSE IF (@Rows_Inst=7)
SELECT F1,F2,F3,F4,F5,F6,F7,F8,F9 FROM tem_rep
ELSE IF (@Rows_Inst=8)
SELECT F1,F2,F3,F4,F5,F6,F7,F8,F9,F10 FROM tem_rep
ELSE IF (@Rows_Inst=9)
SELECT F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11 FROM tem_rep
ELSE IF (@Rows_Inst=10)
SELECT F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11, F12 FROM tem_rep
ELSE IF (@Rows_Inst=11)
SELECT F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11, F12, F13 FROM tem_rep
ELSE IF (@Rows_Inst=12)
SELECT F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11, F12, F13,F14 FROM tem_rep
ELSE IF (@Rows_Inst=13)
SELECT F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11, F12, F13,F14, F15 FROM tem_rep
--
--
-- SELECT CAST (ROW_NUMBER() OVER (ORDER BY EXPDate,EXPTime) AS INT),[EXPdate] ,
--[EXPTime] ,
--[EXPValue] ,
--[EXPTMyKey]
--
--FROM TempRep1 WHERE EXPDate>=@EXPDate_Start AND
--EXPDate<=@EXPDate_end and
--pgmykey=@PGMyKey AND EXPGMyKey=@EXPGMyKey AND EXPInsMyKey=@InsMyKey
-- ORDER BY EXPDate,EXPTime,EXPTName
----
-- SELECT * FROM #Result_EXp22 re
-- SELECT * FROM #All_EXPTName
-- SELECT * FROM ExpRep_Day_Result_Tittle_Tmp erdrtt
-- SELECT DISTINCT EXPTMyKey, EXPTName FROM TempRep1 WHERE EXPDate>=@EXPDate_Start AND
--EXPDate<=@EXPDate_end and
--pgmykey=@PGMyKey AND EXPGMyKey=@EXPGMyKey AND EXPInsMyKey=@InsMyKey AND EXPValue IS NOT NULL
--ORDER BY EXPTName
DROP TABLE #All_EXPTName
END
vBulletin® v4.2.5, Copyright ©2000-1403, Jelsoft Enterprises Ltd.