Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.
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
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
17 Experts available now in Live!