Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!
Solution provided by ralmada: create table payments ( pkey int identity(1,1), AgreeDate datetime, EndDate datetime, Frequency varchar(1), NPayments int ) insert payments values('2009-07-01', '2009-10-01', 'M', 4) insert payments values('2009-07-01', '2009-07-01', 'M', 1) insert payments values('2009-07-02','2009-08-02', 'M', 2) insert payments values('2009-07-02','2009-08-02', 'W', 6) select * from payments ;with CTE as ( select pkey, AgreeDate, AgreeDate as sDate, Frequency, NPayments, 1 as olevel from payments union all select a.pkey, case when a.Frequency = 'M' then dateadd(m, 1, d.AgreeDate) when a.Frequency = 'W' then dateadd(wk, 1, d.AgreeDate) else a.AgreeDate end as AgreeDate, a.AgreeDate as sDate, a.Frequency, a.NPayments, d.olevel + 1 from payments a inner join CTE d on a.pkey = d.pkey where olevel < a.NPayments ) select isnull(convert(varchar, sDate, 106), 'Total'), sum([January]) as January, sum([February]) as February, sum([March]) as March, sum([April]) as April, sum([May]) as May, sum([June]) as June, sum([July]) as July, sum([August]) as August, sum([September]) as September, sum([October]) as October, sum([November]) as November, sum([December]) as December from (select *, datename(m,AgreeDate) dmonth from CTE) o pivot (count(dmonth) for dmonth in ([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December]))p group by sDate with rollup
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.