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:
- What I have:
|Time Stamp||Data1||Data 2||So on…….|
- What I want to do:
|Time Stamp||Data1||Data 2|
Can Any one please Help me out ? I know you guys can.
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
Answered By – Kendle
Answer Checked By – Mary Flores (BugsFixing Volunteer)