How to count product sales for all day periods (SQL Server) _mssql

Database environment: SQL SERVER 2005

An existing product sales Real-time table, table data as follows:

The field name is the product name, the field type is the sales type, 1 is sold, 2 is the return, the field num is the quantity, and the field CTime is the operating time.

Requirements:

Count the sales (sale, return) of all goods within 24 hours on one line, taking into account the date.

Analysis:

This is actually an application of row-and-column, which requires 24 hours of all data to be filled before row-and-column transfers are made. The complement data can be passed through the system's digital auxiliary table

Spt_values to implement, in row-and-column, based on type and processed CTime group.

1. Build the table, import the data

CREATE TABLE Snake (name VARCHAR, type Int,num INT, CTime DATETIME)
INSERT into Snake VALUES (' instant noodles ', 1,10, ' 2015- 08-10 16:20:05 ')
insert into snake values (' Cigarettes a ', 2,2, ' 2015-08-10 18:21:10 ')
insert into snake values (' Cigarette a ', 1  , 5, ' 2015-08-10 20:21:10 ')
insert into snake values (' Cigarette b ', 1,6, ' 2015-08-10 20:21:10 ')
insert into snake values (' Cigarette B ', 2,9, ' 2015-08-10 20:21:10 ')
INSERT into snake VALUES (' Cigarettes C ', 2,9, ' 2015-08-10 20:21:10 ')

2. Complete 24 Hours of data

/* enum 0-23 Natural sequence  /with x0 as (SELECT number as
     h from   master. Spt_values
        WHERE  type = ' P '
            and number >= 0
            and number <=
       ),/* Find all the dates of the
    table
     /* x1 As (SELECT DISTINCT
            CONVERT (VARCHAR), CTime,)   as D
       from Snake),//24 hours for all dates
    */x2
     as (select  x1.d,
            x0.h
        from   x1
            CROSS JOIN x0
       ),
    X3 as
     (select  Name,
            type,
            num,
            DATEPART (Hour, CTime) as H
        from   snake
       ),/* The data to be used for row-and-column-forwarding is required * *
    x4 As
     (SELECT  x2.d,
            x2.h,
            x3.name,
            x3.type,
            x3.num from
        x2 left
            JOIN x3 on x3.h = X2.h
       )

3. Row-Turn column

SELECT ISNULL ([0], 0) as [], ISNULL ([1], 0) as [], ISNULL ([2], 0) as [[], ISNULL ([3], 0) as [03 ], ISNULL ([4], 0) as [[], ISNULL ([5], 0) as [], ISNULL ([6], 0) as [], ISNULL ([3], 7) as [ Modified], ISNULL ([8], 0) as [a], ISNULL ([9], 0) as [[], ISNULL ([ten], 0) as [ten], ISNULL ([3], 11) As [one], ISNULL ([a], 0) as [[], ISNULL ([], 0) as [], ISNULL ([a], 0) as [], ISNULL ([3] As [[ISNULL], 0) as [], ISNULL ([m], 0) as [], ISNULL ([0], as [[]], ISNU
      LL ([[ISNULL],) as [[], ([m], 0) as [], ISNULL ([m], 0) as [], ISNULL ([], 0) as [22],
            ISNULL, type, d as date from (SELECT D, h, type), num from x4) T PIVOT (SUM (num) for h in ([0], [1], [2], [3], [4], [5], [6], [ 7], [8], [9], [10], [11][[], [[], [], [[], [], [M], [m], [m], [M], [?] WHERE type is not NULL

Look at the final effect, only 1 days of data, may not seem very intuitive.

The technical points of this article are 2:

　1. Use of digital auxiliary form to complement all missing records

Use of the 2.pivot row-and-column function

The above content is how to count the whole day time period product sales (SQL Server) All content, hope everybody likes.