sql 日期 时间段统计

---一天统计

WITH  t1 ( [hour], title )
          AS ( SELECT   0 ,
                        ' 0:00:00--- 1:00:00'
               UNION ALL
               SELECT   1 ,
                        ' 1:00:00--- 2:00:00'
               UNION ALL
               SELECT   2 ,
                        ' 2:00:00--- 3:00:00'
               UNION ALL
               SELECT   3 ,
                        ' 3:00:00--- 4:00:00'
               UNION ALL
               SELECT   4 ,
                        ' 4:00:00--- 5:00:00'
               UNION ALL
               SELECT   5 ,
                        ' 5:00:00--- 6:00:00'
               UNION ALL
               SELECT   6 ,
                        ' 6:00:00--- 7:00:00'
               UNION ALL
               SELECT   7 ,
                        ' 7:00:00--- 8:00:00'
               UNION ALL
               SELECT   8 ,
                        ' 8:00:00--- 9:00:00'
               UNION ALL
               SELECT   9 ,
                        ' 9:00:00--- 10:00:00'
               UNION ALL
               SELECT   10 ,
                        ' 10:00:00--- 11:00:00'
               UNION ALL
               SELECT   11 ,
                        ' 11:00:00--- 12:00:00'
               UNION ALL
               SELECT   12 ,
                        ' 12:00:00--- 13:00:00'
               UNION ALL
               SELECT   13 ,
                        ' 13:00:00--- 14:00:00'
               UNION ALL
               SELECT   14 ,
                        ' 14:00:00--- 15:00:00'
               UNION ALL
               SELECT   15 ,
                        ' 15:00:00--- 16:00:00'
               UNION ALL
               SELECT   16 ,
                        ' 16:00:00--- 17:00:00'
               UNION ALL
               SELECT   17 ,
                        ' 17:00:00--- 18:00:00'
               UNION ALL
               SELECT   18 ,
                        ' 18:00:00--- 19:00:00'
               UNION ALL
               SELECT   19 ,
                        ' 19:00:00--- 20:00:00'
               UNION ALL
               SELECT   20 ,
                        ' 20:00:00--- 21:00:00'
               UNION ALL
               SELECT   21 ,
                        ' 21:00:00--- 22:00:00'
               UNION ALL
               SELECT   22 ,
                        '22:00:00---23:00:00'
               UNION ALL
               SELECT   23 ,
                        '23:00:00---24:00:00'
             ),
        t2
          AS ( SELECT   DATEPART(HOUR, CreateDate) [hour] ,
                        COUNT(1) AS number -- 这些字段该怎么算自己调整
               FROM     [P2WMS_WH08].[dbo].[WMS_InboundPart]
               WHERE    CreateDate BETWEEN '2017-07-14 0:00:00'
                                    AND     '2017-07-14 23:59:59.998'
               GROUP BY DATEPART(HOUR, CreateDate)
             )
    SELECT  t1.title ,
            t2.number
    FROM    t1
            LEFT JOIN t2 ON t1.[hour] = t2.[hour]
            where number<>''
    ORDER BY t2.number

---月份统计

 
WITH  t1 ( CreateDate,[hour], title )
          AS ( 
               SELECT   0,0 ,
                        ' 0:00:00--- 1:00:00'
               UNION ALL
               SELECT   0,1 ,
                        ' 1:00:00--- 2:00:00'
               UNION ALL
               SELECT   0,2 ,
                        ' 2:00:00--- 3:00:00'
               UNION ALL
               SELECT   0,3 ,
                        ' 3:00:00--- 4:00:00'
               UNION ALL
               SELECT   0,4 ,
                        ' 4:00:00--- 5:00:00'
               UNION ALL
               SELECT   0,5 ,
                        ' 5:00:00--- 6:00:00'
               UNION ALL
               SELECT   0,6 ,
                        ' 6:00:00--- 7:00:00'
               UNION ALL
               SELECT   0,7 ,
                        ' 7:00:00--- 8:00:00'
               UNION ALL
               SELECT   0,8 ,
                        ' 8:00:00--- 9:00:00'
               UNION ALL
               SELECT   0,9 ,
                        ' 9:00:00--- 10:00:00'
               UNION ALL
               SELECT   0,10 ,
                        ' 10:00:00--- 11:00:00'
               UNION ALL
               SELECT   0,11 ,
                        ' 11:00:00--- 12:00:00'
               UNION ALL
               SELECT   0,12 ,
                        ' 12:00:00--- 13:00:00'
               UNION ALL
               SELECT   0,13 ,
                        ' 13:00:00--- 14:00:00'
               UNION ALL
               SELECT   0,14 ,
                        ' 14:00:00--- 15:00:00'
               UNION ALL
               SELECT   0,15 ,
                        ' 15:00:00--- 16:00:00'
               UNION ALL
               SELECT   0,16 ,
                        ' 16:00:00--- 17:00:00'
               UNION ALL
               SELECT   0,17 ,
                        ' 17:00:00--- 18:00:00'
               UNION ALL
               SELECT   0,18 ,
                        ' 18:00:00--- 19:00:00'
               UNION ALL
               SELECT   0,19 ,
                        ' 19:00:00--- 20:00:00'
               UNION ALL
               SELECT   0,20 ,
                        ' 20:00:00--- 21:00:00'
               UNION ALL
               SELECT   0,21 ,
                        ' 21:00:00--- 22:00:00'
               UNION ALL
               SELECT   0,22 ,
                        '22:00:00---23:00:00'
               UNION ALL
               SELECT   0,23 ,
                        '23:00:00---24:00:00'
             ),
        t2
          AS ( SELECT   DATEPART(HOUR, CreateDate) [hour] ,
                        COUNT(1) AS number -- 这些字段该怎么算自己调整
                        ,CONVERT(varchar(100), CreateDate, 23) as CreateDate
               FROM     [P2WMS_WH08].[dbo].[WMS_InboundPart]
               WHERE    CreateDate BETWEEN '2017-06-01 0:00:00'
                                    AND     '2017-06-30 23:59:59.998'
               GROUP BY CONVERT(varchar(100), CreateDate, 23),DATEPART(HOUR, CreateDate)
             ) 
    SELECT  t2.CreateDate as '日期',
            t1.title as '时间段',
            t2.number as '数量'
    FROM    t1
            LEFT JOIN t2 ON t1.[hour] = t2.[hour]
            where number<>''
    ORDER BY  t2.CreateDate,t1.title