*更改为Statisticsdata_Dynamic_Consolidation脚本

Question

我应该在哪里对我的脚本进行以下更改：

1. Triggerdate应该是昨天的日期(如果脚本在6月6日午夜运行，触发日期应该是2017-06-05)
2. 数据应根据6月5日的最小分和6月5日的最大差来计算。

这是我的剧本：

BEGIN

    Declare MinID_ImeiNo int;
    Declare MaxID_ImeiNo int;
    Declare MinID_DynamicFields int;
    Declare MaxID_DynamicFields int;
    Declare Loop_ImeiNo int;
    Declare Loop_DynamicFields int;
    Declare Var_ImeiNo varchar(50);
    Declare Var_FieldName varchar(50);
    Declare DateVar_Previous    varchar(10);
    Declare MaxTriggerdate datetime;
    Declare CurrentValue  float;
    Declare ExistingValue float;
    Declare CurrentDay Datetime;
    Declare PreviousDay Datetime;
    Declare DateVar_Current varchar(10);
    Declare Table_String varchar(3000);
    Declare Value_String varchar(3000);
    Declare MaxTriggerdate_Previous datetime; 
    Declare CurrentValue_Copy       int;


    Create TEMPORARY TABLE DynamicFields 
    select FieldName from 
    dynamictabledetail where CreateConsolidationTable = 1 
    and ISOBSOLETE=0
    and TABLENAME = 'STATISTICAL';

    set CurrentDay = now();
    set PreviousDay = date_add(CurrentDay,INTERVAL -1 DAY);

    set Table_String = '';
    set Value_String ='';

    Create TEMPORARY TABLE ImeiNos 
    select distinct fabNo from statistical where 
    day(Triggerdate) = day(PreviousDay) 
    and month(Triggerdate) = month(PreviousDay) 
    and year(Triggerdate) = year(PreviousDay);

    alter TABLE DynamicFields  add IDVal int not null primary key auto_increment;

    alter TABLE ImeiNos  add IDVal int not null primary key auto_increment;


    set  MinID_ImeiNo = (select min(IDVal) from ImeiNos);
    set  MaxID_ImeiNo = (select max(IDVal) from ImeiNos);

    set MinID_DynamicFields =(select min(IDVal) from DynamicFields);
    set MaxID_DynamicFields =(select max(IDVal) from DynamicFields);

    set Loop_ImeiNo = MinID_ImeiNo;
    set Loop_DynamicFields = MinID_DynamicFields;
    set DateVar_Previous = (select substring(convert(PreviousDay,char),1,10));
    set DateVar_Current = (select substring(convert(CurrentDay,char),1,10));




    while (Loop_ImeiNo <= MaxID_ImeiNo) do

        set Var_ImeiNo = (select fabNo from  ImeiNos where IDVal = Loop_ImeiNo);
        set MaxTriggerdate  = (select max(Triggerdate) from statistical where fabNo = Var_ImeiNo and Day(Triggerdate) = Day(PreviousDay) and year(Triggerdate) = year(PreviousDay) and month(Triggerdate) = month(PreviousDay));
        set MaxTriggerdate_Previous = (select max(Triggerdate) from statistical where fabNo = Var_ImeiNo);
        set MaxTriggerdate = if(isnull(MaxTriggerdate) = 1,MaxTriggerdate_Previous,MaxTriggerdate);

        while (Loop_DynamicFields <= MaxID_DynamicFields) do
            set Var_FieldName = (select     FieldName from DynamicFields where IDVal = Loop_DynamicFields);
            set ExistingValue = 0;

            set @Query_Var = concat(' set @CurrentValue =(select ', Var_FieldName);
            set @Query_Var = concat(@Query_Var,' from statistical_consolidation where fabNo = ');
            set @Query_Var = concat(@Query_Var,'''');
            set @Query_Var = concat(@Query_Var, Var_ImeiNo);
            set @Query_Var = concat(@Query_Var,'''');
            set @Query_Var = concat(@Query_Var, ' and day(Triggerdate) = day('  );
            set @Query_Var = concat(@Query_Var,'''');
            set @Query_Var = concat(@Query_Var, DateVar_Previous);
            set @Query_Var = concat(@Query_Var,'''');
            set @Query_Var = concat(@Query_Var, ') and month(Triggerdate) = month(');
            set @Query_Var = concat(@Query_Var,'''');
            set @Query_Var = concat(@Query_Var, DateVar_Previous);
            set @Query_Var = concat(@Query_Var,'''');
            set @Query_Var = concat(@Query_Var, ') and year(Triggerdate) = year(');
            set @Query_Var = concat(@Query_Var,'''');

            set @Query_Var = concat(@Query_Var, DateVar_Previous);
            set @Query_Var = concat(@Query_Var,'''');
            set @Query_Var = concat(@Query_Var,'))');


            prepare Stmt1 from @Query_Var;
            execute Stmt1 ;


            set @Query_Var = concat(' set @CurrentValue_Existing =(select ', Var_FieldName);
            set @Query_Var = concat(@Query_Var,' from statistical where fabNo = ');
            set @Query_Var = concat(@Query_Var,'''');
            set @Query_Var = concat(@Query_Var, Var_ImeiNo);
            set @Query_Var = concat(@Query_Var,'''');
            set @Query_Var = concat(@Query_Var, '  and ');
            set @Query_Var = concat(@Query_Var, Var_FieldName);
            set @Query_Var = concat(@Query_Var, ' is not null ');
            set @Query_Var =concat(@Query_Var, ' and day(triggerdate) < ');
            set @Query_Var =concat(@Query_Var, day(MaxTriggerdate));
            set @Query_Var =concat(@Query_Var,' and month(triggerdate) <=');
            set @Query_Var =concat(@Query_Var, month(MaxTriggerdate));
            set @Query_Var =concat(@Query_Var,' and year(triggerdate) <=');
            set @Query_Var =concat(@Query_Var, year(MaxTriggerdate));
            set @Query_Var = concat(@Query_Var, ' order by Triggerdate desc  limit 1 );' );

            prepare Stmt1 from @Query_Var;
            execute Stmt1 ;

          set @CurrentValue_Existing =  if(isnull(@CurrentValue_Existing = 1),0,@CurrentValue_Existing); 

            set ExistingValue = if(isnull(@CurrentValue) = 1,@CurrentValue_Existing,@CurrentValue);






            set @Query_Var = concat('set @CurrentValue =(select ', Var_FieldName);
            set @Query_Var = concat(@Query_Var,' from statistical where fabNo = ');
            set @Query_Var = concat(@Query_Var,'''');
            set @Query_Var = concat(@Query_Var, Var_ImeiNo);
            set @Query_Var = concat(@Query_Var,'''');
            set @Query_Var = concat(@Query_Var, ' and Triggerdate = ' );
            set @Query_Var = concat(@Query_Var, '''');
            set @Query_Var = concat(@Query_Var, MaxTriggerdate);
            set @Query_Var = concat(@Query_Var, '''');
            set @Query_Var = concat(@Query_Var, ')');




            prepare Stmt1 from @Query_Var;
            execute Stmt1 ;

            set CurrentValue_Copy =  if(isnull(@CurrentValue = 1),0,@CurrentValue); 
            set CurrentValue_Copy =  abs(ExistingValue - CurrentValue_Copy);


            set Table_String =  if(isnull(@CurrentValue = 1),Table_String,concat(Table_String,Var_FieldName));
            set Table_String =  if(isnull(@CurrentValue = 1),Table_String,concat(Table_String,','));

            set Value_String = if(isnull(@CurrentValue = 1),Value_String,concat(Value_String,CurrentValue_Copy));
            set Value_String = if(isnull(@CurrentValue = 1),Value_String,concat(Value_String,','));




            set @Query_Var ='';
            set Loop_DynamicFields = Loop_DynamicFields + 1;  


        end while ;



            set Table_String = concat(Table_String,'fabNo');
            set Table_String = concat(Table_String,',');
            set Table_String = concat(Table_String,'TriggerDate');



           set Value_String  = concat(Value_String,'''');
            set Value_String  = concat(Value_String,Var_ImeiNo);
            set Value_String  = concat(Value_String,'''');
            set Value_String  = concat(Value_String,',');
            set Value_String  = concat(Value_String,'''');
            set Value_String = concat(Value_String,DateVar_Current );
            set Value_String = concat(Value_String,''''); 


            set @Query_Var  = 'Insert statistical_consolidation(';
            set @Query_Var  = concat(@Query_Var,Table_String);
            set @Query_Var   = concat( @Query_Var,') Values(');
            set @Query_Var   = concat( @Query_Var,Value_String);
            set @Query_Var   = concat( @Query_Var,')');


            set @Query_Var = if (Table_String = 'Imei,TriggerDate',' set @CurrentValue = (select 1 from statistical limit 1) ', @Query_Var);

            prepare Stmt1 from @Query_Var;
            execute Stmt1 ;

        set Loop_ImeiNo = Loop_ImeiNo + 1;

        set Loop_DynamicFields = MinID_DynamicFields; 

        set Table_String = '';
        set Value_String = '';
    end while ; 

    drop TEMPORARY TABLE DynamicFields; 
    drop TEMPORARY TABLE ImeiNos; 


END

谢谢

Answer 1

根据Server..。尝试：

关于第一个问题：

Select Convert(varchar(10), DATEADD(month, -1, GETDATE()), 120)

最后是按以下顺序排列的日期格式：yyyy-MM-dd

要只获取日期，可以用不同的格式更改转换函数中varchar的大小，例如：

Select Convert(varchar(2), DATEADD(month, -1, GETDATE()), 101)

以上转换将只返回一个月。关于其他格式，请访问日期时间格式。

关于第二个问题：

在您的示例中:选择DATEDIFF(hour，@startDateTime，@endDateTime)

hour表示要返回的度量期间。

有关DateDiff的详细信息，请参阅：DATEDIFF详细答案