How to use sqlserver to count product sales at different times of the day

This article shows you how to use sqlserver statistics throughout the day at various times of product sales, concise and easy to understand, absolutely can make you shine, through the detailed introduction of this article I hope you can gain something.

There is a real-time table of product sales, and the table data is as follows:

The field name is the product name, the field type is the sales type, 1 means sold, 2 means returned, the field num is the quantity, and the field ctime is the operation time.

Requirements:

Count sales (sold, returned) of all goods in a 24-hour period in one row, taking into account dates.

Analysis:

This is actually an application of row to column conversion. Before row to column conversion, all data for 24 hours needs to be completed. Completing data can be done through the system's numerical auxiliary table

spt_values. When row conversion is performed, it can be grouped according to type and processed ctime.

1. Create tables and import data

CREATE TABLE snake (name VARCHAR (10), type INT, num INT, ctime DATETIME) INSERT INTO snake VALUES ('Instant Noodles', 1,10,'2015 - 08 - 10 1,6: 20: 05') INSERT INTO snake VALUES ('Cigarette 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 ('Cigarette C', 2,9,' 2015 - 08 - 10 20:21: 10')

2. Complete 24 hours of data

/* enumeration 0 - 23 natural series */WITH x 0 AS ( SELECT number AS h FROM master.. spt_values WHERE type = 'P' AND number >= 0 AND number