BEGIN TRY
DECLARE @BeginTime DATETIME='2017-02-01'
DECLARE @EndTime DATETIME='2017-03-3 23:59:59'
DECLARE @ShowType INT=0 --展示的类型 0=日 1=十日 2=月
DECLARE @CusCount INT --成交客户数
DECLARE @TotlDate INT=10 --统计分组天数
SELECT @CusCount=COUNT(1) FROM
(
SELECT o.Memberid FROM dbo.[Order] o
WHERE o.PayedDateTime BETWEEN @BeginTime AND @EndTime
GROUP BY o.Memberid
) CusCount
IF(@ShowType=0)
BEGIN
----日
SELECT CONVERT(VARCHAR(10),A.PayedDateTime,23) AS ShowDay,COUNT(1) AS 成交笔数,SUM(A.Payed) AS 成交金额,@CusCount AS 成交客户数 FROM
(
SELECT o.Payed,o.PayedDateTime FROM dbo.[Order] o
WHERE o.PayedDateTime BETWEEN @BeginTime AND @EndTime
) AS A
GROUP BY CONVERT(VARCHAR(10),A.PayedDateTime,23) ;
END
ELSE IF(@ShowType=1)
BEGIN
----十日
SELECT A.FistDate AS ShowDay,COUNT(1) AS 成交笔数,SUM(A.Payed) AS 成交金额,@CusCount AS 成交客户数 FROM
(
SELECT o.Payed,t.evdate,CONVERT(int, evdate-@BeginTime)/@TotlDate Gid,@BeginTime+CONVERT(int, evdate-@BeginTime)/@TotlDate*@TotlDate FistDate from
(select @BeginTime+number evdate from master..spt_values where type='P' and @BeginTime+number<=@EndTime) t
LEFT JOIN dbo.[Order] o ON CONVERT(varchar(100), o.PayedDateTime, 112)=CONVERT(varchar(100), t.evdate, 112)
WHERE o.Tradestatus='TRADE_FINISHED' AND o.Paystatus=1 AND o.PayedDateTime IS NOT NULL AND o.IsTeamBuyRede !='rede' AND o.StoreId=0 AND o.PayedDateTime BETWEEN @BeginTime AND @EndTime
) AS A
GROUP BY A.Gid,A.FistDate;
END
ELSE IF(@ShowType=1)
BEGIN
----月
SELECT SUBSTRING(CONVERT(VARCHAR(10),A.PayedDateTime,23),1,7) AS ShowDay,COUNT(1) AS 成交笔数,SUM(A.Payed) AS 成交金额,@CusCount AS 成交客户数 FROM
(
SELECT o.Payed,o.PayedDateTime FROM dbo.[Order] o
WHERE o.PayedDateTime BETWEEN @BeginTime AND @EndTime
) AS A
GROUP BY SUBSTRING(CONVERT(VARCHAR(10),A.PayedDateTime,23),1,7)
END
ELSE
BEGIN
SELECT ''
END
END TRY
BEGIN CATCH
END CATCH
最主要是这里
(select @BeginTime+number evdate from master..spt_values where type='P' and @BeginTime+number<=@EndTime)