CODEWITHSUNDEEP
sqldatetimehour
MachuPichu92
MachuPichu92

Reputation: 81

SQL - Combine custom date range with hourly data to hourly trading volumes

I am very new to SQL. I am facing an issue with my query in relation to hourly traded volumes.

I've already made a query to extract hourly volumes within custom date range, but database only consists of hours with actual traded volumes - it looks like this:

07/03/2020 09:00    2
07/03/2020 13:00    5
07/03/2020 14:00    10
07/03/2020 18:00    10
07/03/2020 22:00    12

What I want to do is to present this volumes for entire day (in this example for 7/3/2020), whether there were some traded volumes in specific hours or not. Hours with no traded volume should have zeros.

I've also succesfully made a query of hourly timeframe for 7/3/2020 :

 DECLARE @minDateTime AS DATETIME;
 DECLARE @maxDateTime AS DATETIME;

SET @minDateTime = '2020-03-07 01:00:00';
SET @maxDateTime = '2020-03-07 23:00:00';

;
WITH Dates_CTE
     AS (SELECT @minDateTime AS Dates
         UNION ALL
         SELECT Dateadd(hh, 1, Dates)
         FROM   Dates_CTE
         WHERE  Dates < @maxDateTime)

SELECT *
FROM   Dates_CTE
OPTION (MAXRECURSION 0)

Thank you for your help.

Upvotes: 1

Views: 123

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

Using the dates_cte, you want a left join:

SELECT d.datetimecol, COALESCE(t.val, 0)
FROM d LEFT JOIN
     trades t
     ON t.datetimecol = d.datetimecol;

dates doesn't make much sense to me as the name of a column with a single date. You could call it datetime, but that conflicts with the name of a type. I might just call it dt.

Upvotes: 1

Related Questions