I have table called "Data". This contains three column Time (datetime), Tag (int), Value (float) Sample data given below

From the above table you can make out that there is a timestamp that is missing for tags 1002 and 1003 because there are no values for those tags at 2022-01-24 01:00:00

However I want to create a query that can fill my missing timestamps for the tag that does not have value with null value

Out put table should be like this

Can anyone help me with this

Thanks in advance

CodePudding user response：

You could first generate the full expected data-set using a cross-join of known values, and then outer join this to the existing data. This is assuming your SQL Server tag was correct:

with alldates as (
  select [time], tag
  from (select distinct [time] from data)d
  cross join (select distinct tag from data)t
)
select ad.[time], ad.tag, d.[value]
from alldates ad
left join data d on d.[time] = ad.[time] and d.tag = ad.tag
order by ad.[time], ad.tag;