Create a new row for each date in a date range

Other SQL Server Topics
1

Hi,

I've tried to find answers for this query but I can't find anything that matches. I'm building a skills matrix and I'm looking to replicate the single entry results with 90 to look at the next rolling 90 days. I've shortened it to 3 to make it easier for the example:

Current Table

Agent ID Sales Service Tech
Agent 1 Yes
Agent 2 Yes Yes
Agent 3 Yes Yes

After Change

Date Agent ID Sales Service Tech
25/05/2022 Agent 1 Yes
26/05/2022 Agent 1 Yes
27/05/2022 Agent 1 Yes
25/05/2022 Agent 2 Yes Yes
26/05/2022 Agent 2 Yes Yes
27/05/2022 Agent 2 Yes Yes
25/05/2022 Agent 3 Yes Yes
26/05/2022 Agent 3 Yes Yes
27/05/2022 Agent 3 Yes Yes

The code below is a simplified version of what I currently have to get the 1st table:

Select A.Agent_ID
,Case when
Count (Case when A.Skills = 'Sales' then 1 else 0 end) > 0 
then 'Yes' else ''
end as Sales

,Case when
Count (Case when A.Skills = 'Service' then 1 else 0 end) > 0 
then 'Yes' else ''
end as Service

,Case when
Count (Case when A.Skills = 'Tech' then 1 else 0 end) > 0 
then 'Yes' else ''
end as Tech   From AgentData as A

Do you have any ideas to add in the date range?

Thanks,
Gary

2

You can use the strategie of a tally table, look at below sample at the "Using a Tally Table to Expand a Row Set" section.

Tally Tables in T-SQL – SQLServerCentral