-1

I have a table like this:

Date Category Item Value
01-01-23 Bike A 10
25-01-23 Bike A 20
01-01-23 Helmet B 100
01-03-23 Helmet B 200

I need to populate the date for the rows so the item A will have rows with 01-01-2023 until 24-01-2023 with 10 as value and for 25-01-2023 until TODAY will have value of 20. Same is applied with item B.

Thanks!

====

Edited

Thanks to @Ponder Stibbons for the answer, it's working. However when I applied it with some tweaks for my table, my Dedicated SQL Pool in Azure can't detect the Generate_series function. After some research, I assume that Dedicated SQL Pool doesn't support it yet. May someone helps with the same exact requirements but using SQL Server 2016 queries?

Thank you and please open the question.

Jkh30
  • 11
  • 1
  • 2
    [Please do not upload images of code/data/errors when asking a question.](//meta.stackoverflow.com/q/285551) – Thom A Jun 20 '23 at 08:52
  • Expected results would help immensely – Charlieface Jun 20 '23 at 09:40
  • Welcome to stackoverflow. Please read [ask], specifically the part that explains how your question should reflect the efforts you've made attempting to solve the problem you've encountered, and [edit] your question accordingly. – Zohar Peled Jun 20 '23 at 09:42

1 Answers1

1

For each row in the table, generate as many rows as the difference between the current Date and its next value (or today's date if it is the last row).

select dateadd(d, g.value - 1, Date) new_date, category, item, m.value 
from (
  select Date, category, item, value, 
         datediff(day, Date, 
                  lead(Date, 1, dateadd(d, 1, getdate())) 
                  over (partition by category, item order by Date)) diff
  from my_table ) m
cross apply generate_series(1, diff) g

dbfiddle demo

Ponder Stibbons
  • 14,723
  • 2
  • 21
  • 24
  • Hi Ponder, it works wonderfully! However when I applied into my table in my database it says that Generate_Series object not found. After few research, I assume that my Dedicated SQL Pool in Azure not supporting it yet. May you help me with the same requirement using SQL Server 2016? Much appreciated – Jkh30 Jun 22 '23 at 14:15
  • You need to generate rows somehow, for example like in this answer: [generate fixed number of rows in a table](https://stackoverflow.com/questions/21264498/generate-fixed-number-of-rows-in-a-table). I used this method and tested in [dbfiddle](https://dbfiddle.uk/8SzSXCb4) for SQL Server 2016. – Ponder Stibbons Jun 22 '23 at 14:31