完成按照日期排列

-- 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