CODEWITHSUNDEEP
sql-servert-sql
P. MAJ
P. MAJ

Reputation: 159

MSSQL generating a column with rows dynamically

Hope you all have a fantastic time!

I am trying to create a temporary column with date values from the past 14 days.

so in essence, it should for example the below list whenever i re-call this stored procedure:

enter image description here

Reason for this is that we might not have data for a specific date, and we need to show that the count returns zero.

So the idea is to create a column, and then join with another table i created based on the date to do the counts per day.

I tried below code, but it does not work:

declare @a int
set @a = 1

select

    [Date] = while (@a <= 14)
            begin
            cast(dateadd(day, -@a, getdate()) as date)
            set @a = @a + 1
            end

into #Temp1


select

*

from #Temp1

Any help will be greatly appreciated!

Upvotes: 0

Views: 51

Answers (2)

Charlieface
Charlieface

Reputation: 71144

Instead of querying sys.sysobjects unnecessarily, you can generate the table on the fly with no reads, by using a VALUES constructor.

with numbers as (
    select Row_Number() over (order by (select null)) as n
    from (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) v(x)
)
select cast(dateadd(day, -n, getdate()) as date)
from numbers

Upvotes: 0

Stu
Stu

Reputation: 32579

Using a simulated numbers table in a cte (you would ideally have a permanent table) you can simply do

with numbers as (
    select top (14) Row_Number() over (order by (select null)) as n from sys.sysobjects
)
select cast(dateadd(day, -n, getdate()) as date)
from numbers

Upvotes: 2

Related Questions