[SOLVED] How to write a Stored Proc in SQL to fill Null values in certain timestamps

Issue

Let me describe this in a short and simple way. Here is the thing,
I have a table in MySql it has 8 columns. The 1st column is "TimeStamp" which has some time readings of interval 10mins(say 8.00pm,8.10pm, 8.20pm and so on) for a chosen date range. The rest of the columns are giving datas of that particular timestamp. All these timestamp and datas are generated through ASP.Net Microservices and it is hosted through IIS.

Ok, now if some time intervals are missed by the service then the entire row is not shown.
Example: After the reading of 8.00pm,directly there is the reading of 9.00pm, that means we dont have the middle datas of 8.10pm, 8.20pm, 8.30pm and so on.

Now the thing is, I need to write a stored procedure in SQL that can generate all the missing timestamps in the same table, as well as which can fill the other columns as Null.

Here is a table for better understanding:

  1. What I have:
Time Stamp Data1 Data 2 So on…….
8.00pm 1234 6758 ………….
8.10pm 5768 4658 ………….
9.00pm 6754 8674 ………….
  1. What I want to do:
Time Stamp Data1 Data 2
8.00pm 1234 6758
8.10pm 5768 4658
8.20pm NULL NULL
8.30pm NULL NULL
8.40pm NULL NULL
8.50pm NULL NULL
9.00pm 6754 8674

Can Any one please Help me out ? I know you guys can.

Solution

I suggest that you create a table with all the timeslots available each day.

create table apts (
tslot varchar(10),    
Data1 int,    Data2   int);
insert into apts values
('08:00', 1234,   6758),  
('08:10', 5768,   4658),  
('09:00', 6754,   8674);

3 rows affected

create table slots( tim varchar(10));
insert into slots values
('08:00'),('08:10'),('08:20'),
('08:30'),('08:40'),('08:50'),
('09:00');

7 rows affected

select tim slot,data1,data2
from apts 
right join slots on tslot = tim;
slot  | data1 | data2
:---- | ----: | ----:
08:00 |  1234 |  6758
08:10 |  5768 |  4658
08:20 |  null |  null
08:30 |  null |  null
08:40 |  null |  null
08:50 |  null |  null
09:00 |  6754 |  8674

db<>fiddle here

Answered By – Kendle

Answer Checked By – Mary Flores (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published. Required fields are marked *