Tell me, please, how to add missing data by the hour to the current table (DataTable) by a query. Example: Current table:

Region User date hour pcs 2 131 01.01.2018 1 9 2 131 01.01.2018 2 9 2 131 01.01.2018 4 9 2 131 01.01.2018 6 9 2 262 01.01.2018 1 9 2 262 01.01.2018 4 9 2 262 01.01.2018 1 9 2 131 02.01.2018 2 7 2 131 02.01.2018 4 7 2 131 02.01.2018 6 7

Need to:

 Region User date hour pcs 2 131 01.01.2018 1 9 2 131 01.01.2018 2 9 2 131 01.01.2018 3 0 2 131 01.01.2018 4 9 2 131 01.01.2018 5 0 2 131 01.01.2018 6 9 2 131 01.01.2018 7 0

etc.

Note - Hours (from 1 to 23), months (from 01 to 12)

  • 3
    Specify the used DBMS and its version. In general, use a synthetic reference table of numbers. And, probably, do not forget to foresee the case when for a certain intermediate date there is not a single record (or at least to indicate exactly what to do in this case). - Akina
  • Microsoft SQL Server 2012 (SP1) - VetalDem
  • Well, then in CTE, build a table with numbers from 0 to 23, combine it with DISTINCT date and with the DATEADD function generate all possible date-hour pairs. To the resulting set prileftjoynite data table. - Akina

0