sqlserver统计区间日月年

USE [transportplat] GO
/** Object: UserDefinedFunction [dbo].[fn_getCurrPeriod] Script Date: 04/25/2021 12:02:45 **/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/
qzf 2019-1-21 17:13:19
select from dbo.fn_getCurrPeriod(3,’2018-01-01’,’2019-02-11’)
select from dbo.fn_getCurrPeriod(3,’’,’’)
功能：本周，
本月，
本年(1-12),或者开始日期年份或者时间段之间的月份
历年
区间
/

ALTER FUNCTION [dbo].fn_getCurrPeriod,@enddate VARCHAR(10))
RETURNs @t TABLE(id int identity,begdate VARCHAR(10),enddate VARCHAR(10),enddate1 varchar(10),num NUMERIC(18,2) NOT NULL DEFAULT 0)
AS
BEGIN

DECLARE @j INT
DECLARE @count INT
DECLARE @sum NUMERIC(18,2)
DECLARE @tmpbegdate DATETIME

IF(@kind = 1)—按天(本周)
BEGIN
INSERT INTO @t(begdate,enddate)

SELECT convert(varchar(10),ww+id-1,120),convert(varchar(10),ww+id,120) FROM(
SELECT TOP 7 DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) AS ww,ROW_NUMBER() OVER(ORDER BY id) AS id FROM sysobjects AS s
) a
/ —累计开始日期之前的数量
SELECT @beforenum = COUNT(1)
FROM WxOnLineCustomer where SubscribeTime < @firstdate/

END ELSE IF(@kind = 2)—按天(本月/指定月)
BEGIN
set @tmpbegdate = CASE ISNULL(@begdate,’’) WHEN ‘’ THEN getdate() ELSE CONVERT(DATETIME,@begdate) end
DECLARE @i int,@daycount int
select @i=1,@daycount = 32-DAY( @tmpbegdate-DAY(@tmpbegdate)+32)
DECLARE @tday TABLE (id int )
WHILE (@i < = @daycount)
BEGIN
INSERT INTO @tday SELECT @i
SET @i = @i + 1
END
—SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)+ begdate - 1 FROM @tday;
INSERT INTO @t(begdate,enddate)

SELECT convert(varchar(10), DATEADD(mm, DATEDIFF(mm,0,@tmpbegdate), 0)+ id - 1,120),
convert(varchar(10), DATEADD(mm, DATEDIFF(mm,0,@tmpbegdate), 0)+ id ,120) FROM @tday

END ELSE IF(@kind = 3)—按月指定区间/本年月份
BEGIN
set @tmpbegdate = CASE ISNULL(@begdate,’’) WHEN ‘’ THEN getdate() ELSE CONVERT(DATETIME,@begdate) END
—如果开始日期 != 空，结束日期 != 空,返回从开始日期到结束日期的之间的月份
IF(ISNULL(@begdate,’’)!=’’ AND ISNULL(@enddate,’’)!=’’)
BEGIN
DECLARE @imonth int,@monthcount int
select @imonth=1,@monthcount = DATEDIFF(MONTH,@begdate,@enddate) + 1
DECLARE @tmonth1 TABLE (id int )
WHILE (@imonth <= @monthcount)
BEGIN
INSERT INTO @tmonth1 SELECT @imonth
SET @imonth = @imonth + 1
END
INSERT INTO @t(begdate,enddate,num)

SELECT convert(varchar(10), DATEADD(month,id-1, DATEADD(mm, DATEDIFF(mm,0,@tmpbegdate), 0) ) ,120),
convert(varchar(10), DATEADD(month,id, DATEADD(mm, DATEDIFF(mm,0,@tmpbegdate), 0) ) ,120),id FROM @tmonth1
END
—返回本年或者开始日期的年份对应的12个月
ELSE
BEGIN
DECLARE @tmonth TABLE (id int IDENTITY(1,1),a VARCHAR(1))
INSERT INTO @tmonth(a)
SELECT TOP 12 left(NAME,1) FROM sysobjects



INSERT INTO @t(begdate,enddate)

SELECT convert(varchar(10), DATEADD(month,id-1, DATEADD(yy, DATEDIFF(yy,0,@tmpbegdate), 0) ) ,120),
convert(varchar(10), DATEADD(month,id, DATEADD(yy, DATEDIFF(yy,0,@tmpbegdate), 0) ) ,120) FROM @tmonth
END
end ELSE IF(@kind = 4)—按年指定区间的年份/历史年
begin
if(isnull(@begdate,’’)=’’) set @begdate = getdate()
if(isnull(@enddate,’’)=’’) set @enddate = getdate()
DECLARE @tyear TABLE (id int IDENTITY(1,1),a VARCHAR(1))
INSERT INTO @tyear(a)
SELECT TOP(YEAR(@enddate)-YEAR(@begdate) + 1) left(NAME,1) FROM sysobjects


INSERT INTO @t(begdate,enddate)

SELECT convert(varchar(10), DATEADD(year,id-1, DATEADD(yy, DATEDIFF(yy,0,@begdate), 0) ) ,120),
convert(varchar(10), DATEADD(year,id, DATEADD(yy, DATEDIFF(yy,0,@begdate), 0) ) ,120) FROM @tyear
end

ELSE IF(@kind = 5)—按天指定区间
begin
if(isnull(@begdate,’’)=’’) set @begdate = getdate()
if(isnull(@enddate,’’)=’’) set @enddate = getdate()
select @i=1,@daycount = DATEDIFF(DAY,@begdate,@enddate) + 1
DECLARE @tperiod TABLE (id int )
WHILE (@i < = @daycount)
BEGIN
INSERT INTO @tperiod SELECT @i
SET @i = @i + 1
END

—SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)+ begdate - 1 FROM @tday;
INSERT INTO @t(begdate,enddate)

SELECT convert(varchar(10),DATEADD(day,id - 1, @begdate ),120),
convert(varchar(10),DATEADD(day,id, @begdate ) ,120) FROM @tperiod
end

ELSE IF(@kind = 6) —按周(指定区间/本年的周)
begin

IF(ISNULL(@begdate,’’) = ‘’ or ISNULL(@enddate,’’)!=’’)
begin
if(isnull(@begdate,’’)=’’) set @begdate = DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
if(isnull(@enddate,’’)=’’) set @enddate = dateadd(yy, 1, DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) - 1
END


insert into @t(begdate,enddate,num)
select convert(varchar(10), d2.FirstDay,120) , convert(varchar(10), d2.EndDay,120),d2.weekRange
from (
select datepart(WEEK, d.dates) weekRange,
dateadd( day, 1 -(datepart(Weekday, d.dates) + @@DATEFIRST -1)%7, d.dates ) FirstDay,
dateadd(wk, datediff(wk, 0, d.dates), 6) EndDay
from (
select dateadd(dd, number, @begdate) as dates
from master..spt_values
where type = ‘p’ and dateadd(dd, number, @begdate) <= @enddate
) d
) d2
group by d2.weekRange, d2.FirstDay, d2.EndDay


update @t set enddate = convert(varchar(10), dateadd(day,1, enddate),120)


end
update @t set enddate1 = enddate
update @t set enddate = convert(varchar(10), dateadd(dd,-1, enddate),120)
return
END