-----------------------------------------------

--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(''+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(''+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(''+CAST(MONTH(a.CreateTime) AS VARCHAR(2)),2)+'-'+RIGHT(''+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(''+CAST(MONTH(a.CreateTime) AS VARCHAR(2)),2)+'-'+RIGHT(''+CAST(day(a.CreateTime) AS VARCHAR(2)),2)   ,

                b.Title

                order by 日期

            end

end

--还可以这样

--年

select datepart(YEAR,getdate())

select datepart(yyyy,getdate())

select datepart(yy,getdate())

--月

select datepart(MONTH,getdate())

select datepart(mm,getdate())

select datepart(m,getdate())

--日

select datepart(dd,getdate())

--1年中的第多少天

select datepart(dy,getdate())

--季度

select datepart(qq,getdate())

select datepart(qq,'2017-07-01')

--1年中的第多少周

select datepart(wk,getdate())

--星期 --因为从周日算第一天的，星期4，计算结果是5

select datepart(dw,getdate())-1

SELECT CONVERT(VARCHAR(10),GETDATE(),120)  --2015-07-13

SELECT CONVERT(VARCHAR(10),GETDATE(),101)  --07/13/2015

--按日分组：

select convert(nvarchar(10),GETDATE(),120)

--按年月分组：

select  cast(datepart(YEAR,GETDATE()) as varchar(4))+'-'+RIGHT(''+CAST(MONTH(GETDATE()) AS VARCHAR(2)),2)

--按年分组

select DATEPART(year,GETDATE())

--按旬分组

 select case (datepart(day,GETDATE())-1)/10 when 0 then '上旬' when 1 then '中旬' else '下旬' end as 旬,

 sum(1) as 统计

 from 表A group by

 case (datepart(day,dt)-1)/10 when 0 then '上旬' when 1 then '中旬' else '下旬' end 

------------------------------------------------------------------------------------

--按日

select convert(nvarchar(10),GETDATE(),120)

--按月

select convert(nvarchar(7),GETDATE(),120)

--按年

select convert(nvarchar(4),GETDATE(),120)