Data Transform In SQL. Need Help

Transact-SQL
1

hi,
I have to do the following Data Transform in SQL . is it possible to do this in SQL ?
if it is then can someone please send a code for this ?

image

2

maybe this?

    create table #transform(groupno int, startdate date, enddate date, memberid int)

insert into #transform
select 1235, '2017-10-23', '2018-03-22', 456789

;with cte
as
(
SELECT
    DATEADD(d, D.number, BOT.StartDate) AS FullDate
FROM
    (
        SELECT
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS number
        FROM
            sys.all_columns AS AC
    ) D
    CROSS APPLY
    (
        -- Start date
        SELECT min(startdate) as startdate from #transform
    ) BOT
) 
select distinct groupno, DATENAME(month, d.FullDate), year(d.fulldate), memberid, month(fulldate)
  from cte d
  cross apply #transform t
  where FullDate between t.startdate and t.enddate
  order by year(fulldate), month(fulldate)

drop table #transform
1 Like
3

Alternative`

Query 
select top(1) with ties
       a.[group no]
      ,datename(month
               ,dateadd(month,row_number() over(partition by a.[group no]
                                                            ,a.member_id
                                                    order by (select null)
                                               )
               -1
               ,dateadd(month,datediff(month,0,a.[start date]),0))
               )
      +' '
      +datename(year
               ,dateadd(month,row_number() over(partition by a.[group no]
                                                            ,a.member_id
                                                    order by (select null)
                                               )
               -1
               ,dateadd(month,datediff(month,0,a.[start date]),0))
               )
       as [eligible month]
      ,a.member_id
  from yourtable as a
       cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as tally1(n)
       cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as tally2(n)
 order by sign(row_number() over(partition by a.[group no]
                                             ,a.member_id
                                     order by (select null)
                                )
              -datediff(month,a.[start date],a.[end date])
              -2
              )
;
1 Like