求大神帮忙解读SQL语句
求告知计算可比水泥数据具体使用的数据或者全文解读
USE [SE_Dashboard]
GO
/****** Object: StoredProcedure [dbo].[GetDataForBarOrLineChart_ByMonth] Script Date: 05/12/2016 14:54:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Xiaolin-Merlin.Li>
-- Create date: <2014-08-26>
-- Description: <时间选择条件为某个月，查询该月下的KPI>
-- =============================================
ALTER PROCEDURE [dbo].[GetDataForBarOrLineChart_ByMonth]
-- Add the parameters for the stored procedure here
@plantName nvarchar(50),
@workcenterName nvarchar(4000),
@kpiName nvarchar(200),
@selectedMonth datetime,
@filters nvarchar(4000)
AS
BEGIN
insert into dbo.log(kpiname,message)values(@kpiName,CONVERT(nvarchar(120),@selectedMonth)+@filters)
declare @amplaFilters nvarchar(4000)
create Table #t2
(
first_starttime datetime,
first_endtime datetime,
first_amplaFilters nvarchar(20),
second_starttime datetime,
sencond_endtime datetime,
second_amplaFilters nvarchar(20)
)
insert into #t2 exec dbo.ConvertTime_Filter_ByMonth
@plantName,@selectedMonth,@filters
declare @first_starttime datetime
declare @first_endtime datetime
declare @second_starttime datetime
declare @second_endtime datetime
declare @first_amplaFilters nvarchar(20)
declare @second_amplaFilters nvarchar(20)
select
@first_starttime=first_starttime,
@first_endtime = first_endtime,
@first_amplaFilters=first_amplaFilters,
@second_starttime=second_starttime,
@second_endtime =sencond_endtime,
@second_amplaFilters =second_amplaFilters
from #t2
declare @username nvarchar(100)
declare @password nvarchar(100)
declare @servername nvarchar(100)
declare @dateOff datetime
select @username=UserName,@password=[Password],@servername=ServerName,@dateOff=WorkDayStartTime from T_AmplaServerInfo where PlantName=@plantName
declare @location nvarchar(4000)
declare @fields nvarchar(4000)
declare @datasrc nvarchar(200)
declare @nonemetricsfilters nvarchar(4000)
declare @spname nvarchar(2000)
declare @viewName nvarchar(200)
select @location=AmplaLocation,@fields=AmplaFields,@datasrc=DataSrc,@nonemetricsfilters=AmplaFilters,@spname=SPName
from T_KPIsLocation where PlantName=@plantName and WorkcenterName=@workcenterName and KPIName=@kpiName
set @viewName = ''
-- insert into dbo.log(kpiname,message)values(@kpiName,@datasrc)
create table #t1 (
日期 datetime,
值 float
)
if @plantName <> '默认'
begin
if @datasrc <> 'SP'
begin
if @datasrc = 'Metrics'
begin
if(@filters='月' or @first_starttime>@first_endtime)
begin
insert into #t1 exec GetDataByLocationV200806
'Metrics',
@location,
'',
@first_starttime,
@second_endtime,
@viewName,
@fields,
'',
@second_amplaFilters,
1,
'',
@username,
@password,
@servername,
1,
1
end
else
begin
insert into #t1 exec GetDataByLocationV200806
'Metrics',
@location,
'',
@first_starttime,
@first_endtime,
@viewName,
@fields,
'',
@first_amplaFilters,
1,
'',
@username,
@password,
@servername,
1,
1
insert into #t1 exec GetDataByLocationV200806
'Metrics',
@location,
'',
@second_starttime,
@second_endtime,
@viewName,
@fields,
'',
@second_amplaFilters,
1,
'',
@username,
@password,
@servername,
1,
1
end
end
else if @datasrc = '可比熟料综合能耗' or @datasrc = '可比熟料综合煤耗' or @datasrc = '可比熟料综合电耗'
or @datasrc = '可比水泥综合电耗' or @datasrc = '可比水泥综合能耗'
begin
create Table #t3
(
SampleDate datetime,
DH float,
MH float,
KBDH float,
KBMH float,
KBNH float
)
declare @DateValue datetime
set @DateValue=getdate()
IF EXISTS (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#Quality') and type='U')
Drop table #Quality
create Table #Quality (SampleDate Date, QualityValue float)
IF EXISTS (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#Production') and type='U')
Drop table #Production
create Table #Production (SampleDate Date, ProductionValue float)
IF EXISTS (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#KBKPI') and type='U')
Drop table #KBKPI
create Table #KBKPI (SampleDate Date, DH float,MH float)
declare @XZ float
declare @StartDate date
declare @EndDate date
declare @StartDateQly date
declare @EndDateQly date
declare @day int
set @day =DAY(@DateValue)
print (str(year(Dateadd(MONTH,-1,@DateValue))) + '-' + str(month(Dateadd(MONTH,-1,@DateValue))) + '-01')
set @StartDate=Convert(date,str(year(Dateadd(MONTH,0,@DateValue))) + '-' + str(month(Dateadd(MONTH,0,@DateValue))) + '-01')+@dateOff
set @EndDate=@DateValue--str(year(@DateValue)) + '-' +STR( month(@DateValue)) + '-01'
--if (@day>=28)
--begin
--set @StartDateQly=@StartDate --=Convert(date,str(year(Dateadd(MONTH,0,@DateValue))) + '-' + str(month(Dateadd(MONTH,0,@DateValue))) + '-01')+@dateOff
--set @EndDateQly=@EndDate --=str(year(@DateValue)) + '-' +STR( month(@DateValue)) + '-01'
--end
--else
begin
set @StartDateQly=Dateadd(MONTH,-1,@StartDate)--Convert(Date,str(year(Dateadd(MONTH,-1,@DateValue))) + '-' + str(month(Dateadd(MONTH,-1,@DateValue))) + '-01')+@dateOff
set @EndDateQly=@StartDate --str(year(Dateadd(MONTH,-1,@DateValue))) + '-' + str(month(Dateadd(MONTH,-2,@DateValue))) + '-01'+@dateOff
end
-- select @StartDate as a,@EndDate as b
set @location='奎山冀东.'+@plantName+'.熟料烧成.窑.质量信息'
insert into #Quality exec GetDataByLocationV200806
'Quality',
@location,--'奎山冀东.临城一厂.熟料烧成.窑.质量信息',
'',
@StartDateQly,
@EndDateQly,
'',
'Sample Period,检验结果',
'',
'检验项目={28天抗压强度}',
1,
'',
@username,
@password,
@servername,
1,
1
set @location='奎山冀东.'+@plantName+'.熟料烧成.窑.绩效信息'
insert into #Production exec GetDataByLocationV200806
'Metrics',
@location,--'奎山冀东.临城一厂.熟料烧成.窑.绩效信息',
'',
@StartDateQly,
@EndDateQly,
'',
'Start Time,产量',
'',
'Period={工作日.*}',
1,
'',
@username,
@password,
@servername,
1,
1
--采用当前月的值
select @XZ =POWER(( 52.5/(sum(QualityValue * ProductionValue )/SUM(ProductionValue))),0.25) from #Quality a inner join #Production b on a.SampleDate=b.SampleDate and a.QualityValue>0 and b.ProductionValue>0
set @location='奎山冀东.'+@plantName+'.厂级信息.绩效信息'
insert into #KBKPI exec GetDataByLocationV200806
'Metrics',
@location,--'奎山冀东.临城一厂.厂级信息.绩效信息',
'',
@selectedMonth,
@selectedMonth,
'',
'Start Time,熟料综合单位电耗,熟料综合单位标煤耗减余热',
'',
--'Period={工作月.*}',
'@GroupBy={工作月}',
1,
'',
@username,
@password,
@servername,
1,
1
--select * from #KBKPI
--Select SampleDate,DH,MH, DH * @XZ as KBDH,MH * @XZ as KBMH, 0.1229* DH * @XZ + MH *@XZ as KBNH from #KBKPI
declare @kbnh float
set @kbnh=0
declare @kbmh float
set @kbmh=0
Select @kbmh=MH * @XZ from #KBKPI
if @datasrc = '可比熟料综合煤耗'
begin
Select @kbnh=MH * @XZ from #KBKPI
insert into #t1 values(getdate(),@kbnh)
end
else if @datasrc = '可比熟料综合能耗'
begin
Select @kbnh=0.1229* DH *@XZ + MH *@XZ from #KBKPI
insert into #t1 values(getdate(),@kbnh)
end
else if @datasrc = '可比熟料综合电耗'
begin
Select @kbnh=DH*@XZ from #KBKPI
insert into #t1 values(getdate(),@kbnh)
end
if(@plantName='隆尧三厂' and (@datasrc = '可比水泥综合电耗' or @datasrc = '可比水泥综合能耗') )
begin
set @location='奎山冀东.'+@plantName+'.水泥粉磨.1号水泥磨.绩效信息'
delete from #Production
insert into #Production exec GetDataByLocationV200806
'Metrics',
@location,--'奎山冀东.临城一厂.熟料烧成.窑.绩效信息',
'',
@StartDateQly,
@EndDateQly,
'',
'Start Time,产量',
'',
'Period={工作日.*}',
1,
'',
@username,
@password,
@servername,
1,
1
set @location='奎山冀东.'+@plantName+'.水泥粉磨.1号水泥磨.质量信息'
delete from #Quality
insert into #Quality exec GetDataByLocationV200806
'Quality',
@location,--'奎山冀东.临城一厂.熟料烧成.窑.质量信息',
'',
@StartDateQly,
@EndDateQly,
'',
'Sample Period,检验结果',
'',
'检验项目={28天抗压强度}',
1,
'',
@username,
@password,
@servername,
1,
1
select @XZ =POWER(( 42.5/(sum(QualityValue * ProductionValue )/SUM(ProductionValue))),0.25)
from #Quality a inner join #Production b on a.SampleDate=b.SampleDate
and a.QualityValue>0 and b.ProductionValue>0
delete From #Production
set @location='奎山冀东.'+@plantName+'.厂级信息.绩效信息'
insert into #Production exec GetDataByLocationV200806
'Metrics',
@location,--'奎山冀东.临城一厂.厂级信息.绩效信息',
'',
@selectedMonth,
@selectedMonth,
'',
'Start Time,水泥综合单位电耗',
'',
--'Period={工作月.*}',
'@GroupBy={工作月}',
1,
'',
@username,
@password,
@servername,
1,
1
Select @kbnh=ProductionValue * @XZ from #Production
if @datasrc = '可比水泥综合电耗'
begin
insert into #t1 values(getdate(),@kbnh)
end
else if (@datasrc='可比水泥综合能耗')
begin
delete From #Production
set @location='奎山冀东.'+@plantName+'.水泥粉磨.绩效信息'
insert into #Production exec GetDataByLocationV200806
'Metrics',
@location,--'奎山冀东.临城一厂.厂级信息.绩效信息',
'',
@selectedMonth,
@selectedMonth,
'',
'Start Time,熟料水泥比',
'',
--'Period={工作月.*}',
'@GroupBy={工作月}',
1,
'',
@username,
@password,
@servername,
1,
1
declare @bi float
select @bi=ProductionValue from #Production
insert into #t1 values(getdate(),@kbmh*@bi+0.1229*@kbnh)
end
select * from #t1 order by 日期 desc
end
else
begin
select * from #t1 order by 日期 desc
end
--insert into dbo.log(kpiname,message)values(@datasrc,@kbnh)
--insert into dbo.log(kpiname,message)values(@datasrc,'ww')
end
--非绩效管理点
else
begin
insert into dbo.log_d(plantname,workcentername,kpiname,message)values(@plantname,@workcenterName,@kpiName,@second_amplaFilters)
set @second_amplaFilters=@nonemetricsfilters
insert into #t1 exec GetDataByLocationV200806
@datasrc,
@location,
'',
@first_starttime,
@second_endtime,
@viewName,
@fields,
'',
@second_amplaFilters,
1,
'',
@username,
@password,
@servername,
1,
1
end
--insert into t1 select * from #t1
select * from #t1 order by 日期 desc
end
else
begin
-- SP 处理逻辑
declare @strSQL nvarchar(4000)
--set @strSQL = 'insert into #t1 exec '+@spname+' '''+@amplaSamplePeriod+''','''+Convert(nvarchar(20),@startDateLocalTime,120)+''','''+Convert(nvarchar(20),@endDateLocalTime,120)+''''
set @strSQL = 'exec '+@spname+' '''+Convert(nvarchar(20),@first_starttime,120)+''','''+Convert(nvarchar(20),@second_endtime,120)+''''
--print @strSQL;
exec (@strSQL);
end
end
else
begin
select * from #t1 order by 日期 desc
end
END
