Currently I have to make a report based on a query, the query that is made must return all the stoppages that have occurred in a certain date range every five minutes, if there is a record that meets this condition for that interval it should return 1, and if there is no record, it should return 0. To explain it a little easier I put an image of the example table: (*It does not allow uploading an image so I put a link below) image of the example table What I need is that after the query I return 0 for the interval 00:00 - 00:05, 00:05 - 00:10, .... , and for 06:05 - 06:10 it returns 1 and so on with this logic all the intervals between certain dates .
To achieve this I have found some help:
select variable,
convert(varchar(8), tiempo, 1)+' '
+convert(varchar(2), datepart(hh, tiempo))+':'
+convert(varchar(2), datepart(mi, tiempo)/5),
count(*)
from datos group by
variable,
convert(varchar(8), tiempo, 1)+' '
+convert(varchar(2), datepart(hh, tiempo))+':'
+convert(varchar(2), datepart(mi, tiempo)/5)
The problem is that this code does show me the intervals in which there are records but not in those that do not exist. I am working against a MSSQL Server 2012 Greetings.
This is a fairly recurring problem, that of generating records where there are none. In your case, you would need to set up the time periods in advance to determine whether or not there are readings in them. As you have mentioned that you work with a maximum of 24 hours, which would be 288 (26 * 60 / 5) periods, it is feasible to use the solution that I propose, and that is based on generating a dynamic table of time intervals from a date initial. With this table we can make a
LEFT JOIN
withdatos
and where there are no records we will count the quantity as 0.Let's see:
The first part does not have much mystery, we create a variable type table, (it could be a temporary one too) for an example of what your table would be
datos
and a new one that we will callIntervalos
. We then set the value of a variable@PrimerIntervalo
that will represent where we will query fromdatos
. As an exampleSELECT @PrimerIntervalo = '20170807 09:30:00 am'
our consultation will start on 7/8 at 9:30.Then we complete
Intervalos
with one of the possible methods to generate sequences or intervals that we can use in SQL Server, there are better ways in terms of performance but the one that I show you is quite simple to understand, they are aboutCROSS JOINS
three tables with 10 records each what It will allow us to generate a single table with 1000 records (10 x 10 x 10), numbered from 1 to 1000, of which we will only use 288 to generate the intervals. I suggest this excellent article on how to generate sequences in SQL server if you want to go deeper into the subject.To finish this stage, we simply generate the From / To fields by adding 5 minutes to the
@PrimerIntervalo
by dateNro
,Intervalos
which would leave us with something like this:This already gives us a guideline of what we are going to end up doing: "cross" these periods/intervals with
datos
if we do not have records in one we will count it as 0.And finally this query:
Which checks each distinct Variable you have in the requested 24 hours and for each of the 288 time intervals, the output is something like this:
If I have understood it correctly, you should paint all the ranges of 5 minutes of a day (according to what you have said, it would be 288 lines) and depending on whether there is a value in the data table, it will paint a 0 or a 1 right ?
If so, I think it can not be done by just a query. Since you cannot paint values that do not exist in the table, you should have one
for
or some control structure to iterate over those 288 ranges of 5 minutes that a day has and go querying in each round if there is a value in the table to know if paint a 1 or a 0To do that you would have to use procedures , the bad part that I see in this is that you would have to launch 288 queries (one for each range) and that could overload the DB a bit.