Query Help

I have this scenario. What will be my query?

Table:

Account#, Name, RMR, Billing_Date, Invoice#

1000,Dave,50, 5/1/2006,10

1000,Dave,50, 6/1/2006,11

1000,Dave,50, 7/1/2006,12

1000,Dave,50, 8/1/2006,13

1000,Dave,50, 9/1/2006,14

2000,Al,50, 5/15/2006,15

2000,Al,50, 6/15/2006,16

2000,Al,50, 7/15/2006,17

2000,Al,50, 8/15/2006,18

3000,Jim,50, 8/10/2006,19

3000,Jim,50, 9/10/2006,20

3000,Jim,50, 10/10/2006,21

I use this query to calculate revenue sharing.

Account hit for revenue sharing after we bill the 4^th billing month. In this case for Account# 1000 qualified after 8/1/2006, 2000 – 8/15/2006 and 3000 will be qualified after 11/10/2006. Each month we will pay 4% of RMR for all qualified accounts.

I want a query result with return for each month and find out how many accounts are qualified for each month.

In this case:

Septmeber-2006 -1000

Septmeber-2006 -2000

Octomber-2006 -1000

Could you define your result again based on the sample data you posted, which will help to understand your question. Thanks.|||

Dhaval:

I was able to mock-up what you described below. Is this more-or-less what you are looking for?

Dave

declare @.rmrMockUp table
( Account# integer not null,
Name varchar (20) not null,
RMR numeric (9,2) not null,
Billing_Date datetime not null,
Invoice# integer not null
)

insert into @.rmrMockUp values ( 1000, 'Dave', 50, '5/1/2006', 10 )
insert into @.rmrMockUp values ( 1000, 'Dave', 50, '6/1/2006', 11 )
insert into @.rmrMockUp values ( 1000, 'Dave', 50, '7/1/2006', 12 )
insert into @.rmrMockUp values ( 1000, 'Dave', 50, '8/1/2006', 13 )
insert into @.rmrMockUp values ( 1000, 'Dave', 50, '9/1/2006', 14 )
insert into @.rmrMockUp values ( 2000, 'Al', 50, '5/15/2006', 15 )
insert into @.rmrMockUp values ( 2000, 'Al', 50, '6/15/2006', 16 )
insert into @.rmrMockUp values ( 2000, 'Al', 50, '7/15/2006', 17 )
insert into @.rmrMockUp values ( 2000, 'Al', 50, '8/15/2006', 18 )
insert into @.rmrMockUp values ( 3000, 'Jim', 50, '8/10/2006', 19 )
insert into @.rmrMockUp values ( 3000, 'Jim', 50, '9/10/2006', 20 )
insert into @.rmrMockUp values ( 3000, 'Jim', 50, '10/10/2006', 21 )

declare @.monthList varchar (250)
set @.monthList = 'January February March April May June July August September October November December '

select Account#,
rtrim (substring (@.monthList,
month (dateadd (month, 1, Billing_date))*10-9, 10)) + '-' +
convert (char (4), year (dateadd (month, 1, Billing_date)))
as RMR_Month
from
( select Account#,
rmr,
Rank() over (partition by Account# order by Billing_Date)
as activeMonths,
Billing_Date
from @.rmrMockUp
) x
where activeMonths >= 4
order by Billing_Date, Account#

-- -
-- Query Output:
-- -

-- Account# RMR_Month
-- --
-- 1000 September-2006
-- 2000 September-2006
-- 1000 October-2006