0

Please help to fetch the monthnames between two date columns as a concatenated string in a new column.

enter image description here

the above image shows my table data..

if the end date is null, we need to take till end of the year

desired output is

enter image description here Below find the scripts needed for the table below

CREATE TABLE OfferTest(
    [ID] [int] NOT NULL,
    [StartDate] [datetime] NULL,
    [EndDate] [datetime] NULL
) 

insert into OfferTest values(1000,'01/01/2021','05/31/2021') 
insert into OfferTest values(2000,'01/01/2021','05/31/2021') 
insert into OfferTest values(3000,'07/01/2021','09/30/2021') 
insert into OfferTest values(4000,'11/01/2021',NULL)
PRI
  • 23
  • 5

1 Answers1

1

First create this number table if you don't have one already

SELECT TOP 10000 N=IDENTITY(INT, 0, 1)
INTO dbo.Numbers
FROM master.dbo.syscolumns a CROSS JOIN master.dbo.syscolumns  b;
ALTER TABLE dbo.Numbers ADD CONSTRAINT NBR_pk PRIMARY KEY(N);

;with cte(id, Months) as (
select id, STRING_AGG(format(dateadd(m,n.n,StartDate),'MMM'),',')
from offertest o cross join dbo.Numbers n
where dateadd(m,n.n,StartDate)<=isnull(EndDate, '20211231')
group by id)
select o.StartDate,o.EndDate,c.Months 
from cte c inner join offertest o on o.id=c.id

TEST

http://sqlfiddle.com/#!18/626e3/1

You can replace '20211231' with getdate() or other limit date

EDIT

You can create the numbers table as a tempt table just by adding #

SELECT TOP 10000 N=IDENTITY(INT, 0, 1)
INTO #Numbers
FROM master.dbo.syscolumns a CROSS JOIN master.dbo.syscolumns  b;
ALTER TABLE #Numbers ADD CONSTRAINT NBR_pk PRIMARY KEY(N);

Regarding using the query as subquery, there is no need, just use it as a CTE like this:

;with cte(id, Months) as (
select id, STRING_AGG(format(dateadd(m,n.n,StartDate),'MMM'),',')
from offertest o cross join #Numbers n
where dateadd(m,n.n,StartDate)<=isnull(EndDate, '20211231')
group by id)
,months(StartDate,EndDate,Months) as (
select o.StartDate,o.EndDate,c.Months 
from cte c inner join offertest o on o.id=c.id)

Select 'your query here joining Months'
Horaciux
  • 6,322
  • 2
  • 22
  • 41
  • is it possible to achieve this without Numbers table...i am not allowed to create tables in the server – PRI Dec 23 '22 at 14:13
  • also want to use this as a subquery in a join with another table – PRI Dec 23 '22 at 14:22
  • Syscolumns table is not supported....'Reference to database and/or server name in 'master.dbo.syscolumns' is not supported in this version of SQL Server.' – PRI Dec 25 '22 at 09:20
  • https://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table – Horaciux Dec 26 '22 at 12:22
  • @PRI did you make it work? if not, just insert numbers from 0 to 20 in the numbers table – Horaciux Dec 27 '22 at 22:10