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         --活动ID
as
begin
    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 日期    
            end    
end