sql 报表服务器 销售kpi,[原]SQLSERVER 常用KPI的计算.解决思路

SQL code--基本上没有用SQL写过KPI,在QQ里有人问起,觉得用SQL试试,呵呵.

--本文只是抛砖引玉.欢迎参观.

create table Timer (

DTE_Date DateTime Not null,

Int_Year int not null,

Int_Q Int Not null,

Int_M Int Not null,

Int_D Int Not null

CONSTRAINT [PK_Timer] PRIMARY KEY

(

DTE_Date ASC

))

go

DECLARE @DTE_BEGDATE DATETIME

SET @DTE_BEGDATE='2000-1-1'

WHILE @DTE_BEGDATE<= '2099-12-31' BEGIN

INSERT INTO Timer VALUES(@DTE_BEGDATE,YEAR(@DTE_BEGDATE),

CASE WHEN MONTH (@DTE_BEGDATE)>=1 AND MONTH (@DTE_BEGDATE)<=3 THEN 1

WHEN MONTH (@DTE_BEGDATE)>=4 AND MONTH (@DTE_BEGDATE)<=6 THEN 2

WHEN MONTH (@DTE_BEGDATE)>=7 AND MONTH (@DTE_BEGDATE)<=9 THEN 3

WHEN MONTH (@DTE_BEGDATE)>=10 AND MONTH (@DTE_BEGDATE)<=12 THEN 4

END

,MONTH (@DTE_BEGDATE),DAY(@DTE_BEGDATE) )

SET @DTE_BEGDATE =DATEADD (DAY,1,@DTE_BEGDATE)

END

GO

-----------------------------------------

--建立测试业务表

-----------------------------------------

CREATE TABLE BILL_SELL(

DTE_DATETIME DATETIME,

VAR_BILLID VARCHAR(32),

VAR_PRODUCT VARCHAR(64),

INT_AMOUNT INT,

INT_MONEY INT

CONSTRAINT [PK_BILL_SELL] PRIMARY KEY

(

VAR_BILLID ASC

))

CREATE INDEX IX_SELL_DATETIME ON BILL_SELL (DTE_DATETIME)

GO

-----------------------------------------

--填充业务表.

-----------------------------------------

DECLARE @DTE_BEGDATE DATETIME

DECLARE @Int_BillID Int

SET @Int_BillID=1

SET @DTE_BEGDATE='2007-1-1'

WHILE @DTE_BEGDATE<= '2009-12-31' BEGIN

INSERT INTO BILL_SELL VALUES(@DTE_BEGDATE, @Int_BillID,LEFT(NEWID(),1),RAND() * 1000,RAND()*1000 )

SET @Int_BillID=@Int_BillID+1

SET @DTE_BEGDATE =DATEADD (DAY,1,@DTE_BEGDATE)

END

GO

/**************************************/

--1.建立中间表.

CREATE TABLE JX_TAB (

Int_Year int null,

Int_Q Int null,

Int_M Int null,

Int_D Int null,

INT_AMOUNT FLOAT NULL,

INT_MONEY FLOAT NULL,

)

CREATE INDEX IX_T_YEAR ON JX_TAB(INT_YEAR)

CREATE INDEX IX_T_Q ON JX_TAB(Int_Q)

CREATE INDEX IX_T_M ON JX_TAB(Int_M)

CREATE INDEX IX_T_D ON JX_TAB(Int_D)

GO

-------------------------------------

-- 计算同比(年,季,月).

-------------------------------------

--2.将数据 按统计时间 抽到中间表.

INSERT INTO JX_TAB

SELECT A.Int_Year ,A.Int_Q ,NULL,NULL,SUM(INT_AMOUNT),SUM(INT_MONEY) --因为是统计季度,所以 月 和 天 是 NULL

FROM

( SELECT YEAR(DTE_DATETIME) AS Int_Year

,CASE WHEN MONTH (DTE_DATETIME)>=1 AND MONTH (DTE_DATETIME)<=3 THEN 1

WHEN MONTH (DTE_DATETIME)>=4 AND MONTH (DTE_DATETIME)<=6 THEN 2

WHEN MONTH (DTE_DATETIME)>=7 AND MONTH (DTE_DATETIME)<=9 THEN 3

WHEN MONTH (DTE_DATETIME)>=10 AND MONTH (DTE_DATETIME)<=12 THEN 4

END AS Int_Q

,MONTH (DTE_DATETIME) AS Int_M

,DAY(DTE_DATETIME) AS Int_D

,INT_AMOUNT

,INT_MONEY

FROM BILL_SELL ) AS A

GROUP BY Int_Year,Int_Q

GO

--3.计算同比,计算 月,日,周 的,依次类推.

SELECT A.Int_Year ,A.Int_Q , (A.INT_MONEY -B.INT_MONEY )/B.INT_MONEY AS 季同比

FROM JX_TAB AS A INNER JOIN JX_TAB AS B ON A.Int_Year=B.Int_Year-1 AND A.Int_Q=B.Int_Q

-------------------------------------

-- 计算环比(年,季,月)

-------------------------------------

SELECT A.Int_Year ,A.Int_Q , (A.INT_MONEY -B.INT_MONEY )/B.INT_MONEY AS 季环比

FROM JX_TAB AS A INNER JOIN JX_TAB AS B ON A.Int_Year=B.Int_Year AND A.Int_Q=B.Int_Q-1

-------------------------------------

-- 计算YTD/MTD/QTD

-- YTD 是年的第一天开始到年的最后一天的累计值.

-- MTD 是某年的某月的第一天开始到某年该月的最后一天的累计值.

-- QTD 是某年的某季的第一天开始到某年该季的最后一天的累计值.

-- 这几个都是累计值 . 知道了概念后,在原表就可以求出.不举例子了.

-------------------------------------

--其它的,如 总体贡献度,父子贡献度,受欢迎程度 等等,可以用类拟的方法求出.