CODEWITHSUNDEEP
sql-serverdatetime
Jacek Jastrzębski
Jacek Jastrzębski

Reputation: 13

creating a report of how many times something occured

I need to build a query from dates like: 

2015-01-01 01:05:15  
2015-01-01 01:07:17  
2015-01-01 07:09:18  
2015-01-01 07:12:41  
2015-01-02 04:06:15  
2015-01-02 04:15:15

I need to have table in which there will be column for every hour and a row for every day of the month in which there will be the count of how many times did something occur during an hour.

For given example i`ll have data like this 

2015-01-01|0|2|0|0|0|0|0|2|0|0|0|0|....  
2015-01-02|0|0|0|2|0|0|0|0|0|0|0|0|....
....  
....

I appreciate every advice.

Upvotes: 1

Views: 43

Answers (2)

joordan831
joordan831

Reputation: 720

You need to create the table like this

Create Table EveryDay
(
  Day date,
  Hour0 int,
  Hour1 int,
  ...
  Hour23 int
)

Then create a query that will read from the dates list, parse, and convert them and insert or update if it's not a one time thing.

Parse SQL with delimiter - SQL parse delimited string

Upvotes: 0

B. Clay Shannon-B. Crow Raven
B. Clay Shannon-B. Crow Raven

Reputation: 10287

First, create the table with those 24 columns; something like:

CREATE TABLE ROMPECABEZA
DATECOL DATETIME,
HOUR1 INT,
. . .
HOUR24 INT

Then you will need to perform some operations and insert values such as:

INSERT INTO ROMPECABEZA (DATECOL, HOUR1, ... HOUR24) VALUES (@DATECOLVAL, @HOUR1VAL, ..., @HOUR24VAL);

Upvotes: 1

Related Questions