Sql 按照指定天数时间段查询

时间:2022-10-09 17:09:54
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)