完成按照日期排列

最新推荐文章于 2024-11-10 11:28:30 发布

fanfanxg

最新推荐文章于 2024-11-10 11:28:30 发布

阅读量353

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/wangqifanjob/article/details/42748025

本文介绍了一个SQL存储过程，用于按天或按月统计条形码打印数量。该过程使用了临时表和CTE（公用表表达式）来实现灵活的数据统计，并提供了两个示例参数设置，帮助用户理解如何调用此过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

-- exec sp_text 'dbo.proc_LabelChartByDate'
if exists (select * from sysobjects where id=object_id('dbo.proc_LabelChartByDate'))
drop procedure dbo.proc_LabelChartByDate
go
set ansi_nulls off
go
CREATE procedure [dbo].[proc_LabelChartByDate]
/***
Name : 根据天或者月统计条码数量
Param: @Type类型(1月,2天);@InsertSql(SQL语句);@start(开始统计时间);@end(结束统计时间)
Remark:
Example:注意1master..spt_values这个函数记录所有的次序数据；2@Type类型(1月,2天) 由于类型不同故number的计算方法不一样
exec proc_LabelChartByDate 2,'','2014-10-01','2014-12-31'
author : WQF 2014.4.18
***/
@Type int,
@InsertSql nvarchar(3000),
@start datetime,
@end datetime
as
set xact_abort on
begin
--构造临时表#LabelData
create table #LabelData
(
ID int identity(1,1),
Label_ID uniqueidentifier,
PrintDate datetime
)
--构造临时表#ResultData
create table #ResultData(
DateStr nvarchar(20),
LabelNum int
)

--exec(@InsertSql)
insert into #LabelData(Label_ID,PrintDate)
select Print_UID,Print_Date from Label_Print
where Print_Date between @start and @end

--Type=1表示按照月份进行统计
if(@Type=1)
begin
;WITH TT AS (SELECT number FROM master..spt_values WHERE type = 'P' AND number BETWEEN 0 AND (DATEPART(MONTH,@end)-DATEPART(MONTH,@start)))

insert into #ResultData(DateStr,LabelNum)
select DataStr=CONVERT(NVARCHAR(12), b.BMonth,23)+'月',LabelNum=isnull(a.AC,0)
from
(select year(Print_Date) as AY,month(Print_Date) AS AM,COUNT(*) AS AC from Label_Print
group by year(Print_Date),month(Print_Date)) AS a right join
(select BMonth=DATEADD(MONTH,TT.number,@start) from TT) AS b ON A.AM=MONTH(BMonth) AND A.AY=YEAR(BMonth)
--查看临时表#ResultData
select * from #ResultData
end

--Type=2表示按照天数进行统计
else if(@Type=2)
begin
DECLARE @sint int
DECLARE @eint int
set @sint=DATEPART(day,@start)
set @eint=DATEPART(day,@start)+DATEdiff(DAY,@start,@end)
;WITH TT AS (SELECT number FROM master..spt_values WHERE type = 'P' AND number BETWEEN @sint-1 AND @eint-1)

insert into #ResultData(DateStr,LabelNum)
select DataStr=CONVERT(NVARCHAR(12), b.BDate,23)+'日',LabelNum=isnull(a.AC,0)
from
(select year(Print_Date) as AY,month(Print_Date) AS AM,day(Print_Date) AS AD,COUNT(*) AS AC from Label_Print
group by year(Print_Date),month(Print_Date),day(Print_Date)) AS a
right join
(select BDate=DATEADD(DAY,TT.number,@start) from TT) AS b ON A.AD=DAY(BDate) AND A.AM=MONTH(BDate) AND A.AY=YEAR(BDate)
--查看临时表#ResultData
select * from #ResultData
end

--释放临时表
drop table #ResultData
drop table #LabelData
end

go
set ansi_nulls off
go