标签:结束时间 star class 年月日 var art style select sql server 按年月日分组-------------------------------------------------author:yangjinwang--date:2017-03-30--do:根据活动查询 每个奖品类的发放数量-----------------------------------------------create proc GetWinningInfoTypeCountByTimeActivity@CreateTimeStart datetime =null, --开始时间@CreateTimeEnd datetime =null, --结束时间@TimeType varchar(20)=‘m‘, --查询维度,年月日@ActivityId int=null --活动IDasbegin if(@TimeType=‘y‘) begin select c.id as ‘活动ID‘, c.Name as ‘活动名称‘, cast(datepart(YEAR,a.CreateTime) as varchar(4)) as ‘日期‘, b.Title as ‘奖品类别‘ ,COUNT(a.Id) as ‘中奖数量‘ from WinningInfo a left join PrizesInfo b on a.PrizesId=b.Id left join ActivityInfo c on b.ActivityId=c.Id where a.ActivityId=@ActivityId and (a.CreateTime>=@CreateTimeStart or @CreateTimeStart is null) and (a.CreateTime<=@CreateTimeEnd or @CreateTimeEnd is null) group by c.id,c.Name , cast(datepart(YEAR,a.CreateTime) as varchar(4)) , b.Title order by 日期 end else if(@TimeType=‘m‘) begin select c.id as ‘活动ID‘, c.Name as ‘活动名称‘, cast(datepart(YEAR,a.CreateTime) as varchar(4))+‘-‘+RIGHT(‘00‘+CAST(MONTH(a.CreateTime) AS VARCHAR(2)),2) as ‘日期‘, b.Title as ‘奖品类别‘ ,COUNT(a.Id) as ‘中奖数量‘ from WinningInfo a left join PrizesInfo b on a.PrizesId=b.Id left join ActivityInfo c on b.ActivityId=c.Id where a.ActivityId=@ActivityId and (a.CreateTime>=@CreateTimeStart or @CreateTimeStart is null) and (a.CreateTime<=@CreateTimeEnd or @CreateTimeEnd is null) group by c.id,c.Name , cast(datepart(YEAR,a.CreateTime) as varchar(4))+‘-‘+RIGHT(‘00‘+CAST(MONTH(a.CreateTime) AS VARCHAR(2)),2) , b.Title order by 日期 end else begin select c.id as ‘活动ID‘, c.Name as ‘活动名称‘, cast(datepart(YEAR,a.CreateTime) as varchar(4))+‘-‘+RIGHT(‘00‘+CAST(MONTH(a.CreateTime) AS VARCHAR(2)),2)+‘-‘+RIGHT(‘00‘+CAST(day(a.CreateTime) AS VARCHAR(2)),2) as ‘日期‘, b.Title as ‘奖品类别‘ ,COUNT(a.Id) as ‘中奖数量‘ from WinningInfo a left join PrizesInfo b on a.PrizesId=b.Id left join ActivityInfo c on b.ActivityId=c.Id where a.ActivityId=@ActivityId and (a.CreateTime>=@CreateTimeStart or @CreateTimeStart is null) and (a.CreateTime<=@CreateTimeEnd or @CreateTimeEnd is null) group by c.id,c.Name , cast(datepart(YEAR,a.CreateTime) as varchar(4))+‘-‘+RIGHT(‘00‘+CAST(MONTH(a.CreateTime) AS VARCHAR(2)),2)+‘-‘+RIGHT(‘00‘+CAST(day(a.CreateTime) AS VARCHAR(2)),2) , b.Title order by 日期 endend sql server 按年月日分组标签:
来源: http://www.bubuko.com/infodetail-2002170.html