同比：本期与去年同期比较
环比：本期与上月同期比较
旬比：每十天与上一个十天相比
如下例：
以下为sqlserver中实现数据的同比环比查询
表结构为：
create table( kmid uniqueidentifier not null primary key,--主键 QtyTD decimal(18,4),--数量 DateofArrival datetime--时间 )
为实现时间周期为旬的情况，建立两个标量值函数来进行datetime与带旬格式时间之间的换算，代码如下：
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: 从datetime类型转换为带旬的格式的字符串
-- =============================================
create FUNCTION [dbo].[dtGetXun]
(
-- Add the parameters for the function here
@dt datetime
)
RETURNS varchar(50)
AS
BEGIN
declare @dtWithXun varchar(50)
declare @xun varchar(50)
if(day(@dt) between 0 and 10)
begin
set @xun='上旬'
end
if(day(@dt) between 11 and 20)
begin
set @xun='中旬'
end
if(day(@dt) >20)
begin
set @xun='下旬'
end
select @dtWithXun=cast(year(@dt) as varchar(50))+'年'+cast(month(@dt) as varchar(50))+'月'+@xun
return @dtWithXun
END
-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: 实现带旬时间格式的计算，类似sqlserver中的dateadd
-- =============================================
create FUNCTION XunAdd
(
-- Add the parameters for the function here
@flag varchar(50), --计算标记:'year','month','xun'
@addnum int, --用于计算的值
@xundate varchar(50)--旬值，如：2011年3月上旬
)
RETURNS varchar(50)
AS
BEGIN
set @xundate=REPLACE(@xundate,'年','/')
set @xundate=REPLACE(@xundate,'月','')
set @xundate=REPLACE(@xundate,'上旬','/1')
set @xundate=REPLACE(@xundate,'中旬','/11')
set @xundate=REPLACE(@xundate,'下旬','/21')
declare @date datetime
set @date=convert(datetime,@xundate)
if(@flag='year')
BEGIN
set @date=dateadd(year,@addnum,@date)
return dbo.dtGetXun(@date)
END
if(@flag='month')
BEGIN
set @date=dateadd(month,@addnum,@date)
return dbo.dtGetXun(@date)
END
if(@flag='xun')
BEGIN
set @date=dateadd(day,@addnum*10,@date)
return dbo.dtGetXun(@date)
END
return ''
END
GO
两个标量值函数的运行效果如下图：
完成以上工作以后即可着手实现从数据中取出同比、环比数据了，如下是我写的存储过程，为满足业务需求，可能有部分内容你是不需要的：
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
create PROCEDURE [dbo].[DxoilData_GroupData_Get]
-- Add the parameters for the stored procedure here
@GroupType int--0:时间周期为年;1:时间周期为月;2:时间周期为旬;
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT off;
-- Insert statements for procedure here
create table #GroupData(
QtyTD decimal(18,4),
Counts int,
QtyTDTB decimal(18,4),
CountsTB int,
QtyTDHB decimal(18,4),
CountsHB int,
GroupFlag varchar(50))
if( @GroupType=0)--按年分组
begin
insert into #GroupData(QtyTD,Counts,QtyTDTB,CountsTB,QtyTDHB,CountsHB,GroupFlag)
SELECT sum(a.qtytd),count(a.pID),max(isnull(tb.QtyTD,0)),max(isnull(tb.Counts,0)),max(isnull(tb.QtyTD,0)),max(isnull(tb.Counts,0)),year(DateofArrival) from dxoildata as a
LEFT JOIN--同比
(SELECT sum(qtytd) as QtyTD,count(pID) as Counts,year(DateofArrival) as yearofDate
from dxoildata
group by year(DateofArrival)) as tb on datediff(year,dbo.getdatefromint(yearofDate,1,1),DateofArrival)=1
group by year(DateofArrival)
end
if( @GroupType=1)--按月分组
begin
insert into #GroupData(QtyTD,Counts,QtyTDTB,CountsTB,QtyTDHB,CountsHB,GroupFlag)
SELECT sum(a.qtytd),count(pID),max(isnull(tb.QtyTD,0)),max(isnull(tb.Counts,0)),max(isnull(hb.QtyTD,0)),max(isnull(hb.Counts,0)),cast(year(DateofArrival) as varchar(50))+'-'+cast(month(DateofArrival) as varchar(50)) from dxoildata as a
LEFT JOIN--同比
(SELECT sum(qtytd) as QtyTD,count(pID) as Counts,year(DateofArrival) as yearofDate,month(DateofArrival) as monthofDate
from dxoildata
group by year(DateofArrival),month(DateofArrival)) as tb on datediff(year,dbo.getdatefromint(tb.yearofDate,tb.monthofDate,1),DateofArrival)=1 and month(DateofArrival)=monthofDate
LEFT JOIN--环比
(SELECT sum(qtytd) as QtyTD,count(pID) as Counts,year(DateofArrival) as yearofDate ,month(DateofArrival) as monthofDate
from dxoildata
group by year(DateofArrival),month(DateofArrival)) as hb on datediff(month,dbo.getdatefromint(hb.yearofDate,hb.monthofDate,1),DateofArrival)=1
group by year(DateofArrival),month(DateofArrival)
order by year(DateofArrival),month(DateofArrival)
end
if(@GroupType=2)--按旬分组
BEGIN
insert into #GroupData(QtyTD,Counts,QtyTDTB,CountsTB,QtyTDHB,CountsHB,GroupFlag)
SELECT sum(a.qtytd),count(pID),max(isnull(tb.QtyTD,0)),max(isnull(tb.Counts,0)),max(isnull(hb.QtyTD,0)),max(isnull(hb.Counts,0)),dbo.dtGetXun(DateofArrival) from dxoildata as a
LEFT JOIN--同比
(SELECT sum(qtytd) as QtyTD,count(pID) as Counts,dbo.dtGetXun(DateofArrival) as xunofdate
from dxoildata
group by dbo.dtGetXun(DateofArrival)) as tb on dbo.xunAdd('year',1,tb.xunofdate)=dbo.dtGetXun(DateofArrival)
LEFT JOIN--环比
(SELECT sum(qtytd) as QtyTD,count(pID) as Counts,dbo.dtGetXun(DateofArrival) as xunofdate
from dxoildata
group by dbo.dtGetXun(DateofArrival)) as hb on dbo.xunAdd('month',1,hb.xunofdate)=dbo.dtGetXun(DateofArrival)
group by dbo.dtGetXun(DateofArrival)
order by dbo.dtGetXun(DateofArrival)
END
select * from #GroupData
drop table #GroupData
END
实际运行效果如下图：
