Brilliant! Wow!! wowwww!   Jason, Steve, Drew, and Jeff - THANK YOU ALL........   I have learnt many things and will always  keep this 'Swiss knife ' in my 'SQL...

yes i am allowed to. But i am confused how i can help my case using IVTF here. Can you please help me. I am super stuck.  thank you

Also change in requirement - i want hourly increment and not daily increment. thus there is an hour part included.

Hello Drew
cant thank you enough,
I just met another issue. 

SELECT value, dataitemuniverseid, DATEADD(DAY, n, startdate) AS startdate, Pubsourceid, PubsourceName
FROM PubDateRanges
INNER JOIN @Tally
  ON missingdays >= n
ORDER BY Pubsourceid, startdate

I have a dataitemuniverseid...

Hello Drew, i used the below mentioned SQL to get pass through the above issue., does it make sense to you?
THANK YOU for helping me here. i was...

thanks a ton Drew, however i am still having trouble 

see there is a gap  between 2016-08-05 rand 2017-01-10. how can i fix this?  Can you please help?

0 32833 2017-08-07 11:00:00.000 147942 EL
0 32835 2017-08-08 11:00:00.000 147942 EL
230 32835 2017-08-11 14:00:00.000 147942 EL

it should be
0 32833 2017-08-07 11:00:00.000 147942 EL

that is just a typo. Please excuse me for that.

technically, if Aug 8 is missing, keep using the value of previous day i.e. Aug 7, if no value in 7, use value of Aug 6 and so on.

yes this is expected. I need data in that particular range of hours in order to keep the result set small.

Thank you so much. I will try to make the changes and will get back with more questions.

I really appreciate you all for taking out your time and helping...

query execution plan is attached.

thanks a ton. This is what i was looking for. 🙂 have an awesome day.

Thanks Phil

U r the best. I dont know what i was thinking when i wrote that query.over thinking i guess.

It worked.

Thank you all.

thank you!