Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  how to do this?

Author  Topic  ajay Starting Member 34 Posts Posted - 2001-07-09 : 02:46:39 dear gurus, I have something like this. table billing user_id varchar(20) cat_id varchar(40) subcat_id varchar(40) channel_id char(3) product_id varchar(20) subscribed_date default getdate() table unsubscribe user_id cat_id subcat_id date_unsubscribed default getdate() channel_id product_id Now if anyone unsubscribe for any product the records are inserted in the unsubscribe table taking info from the billing table. The billing procedure runs on 25th of every month, taking records from billing table. like for one user select a.* from billing a,unsubscribe u where a.cat_id=u.cat_id and a.subcat_id=u.subcat_id and a.product not in (select product_id from unsubscribe where user_id='abc') Now for exampple if application runs on 07/25/2002 and if some user has unsubscribed for a particular product in that month only, then I am goin to charge for that product for that month. And next month it should not appear. something like create procedure sp_check_bill ( @userid varchar(30) ) as if exists(select * from unsubscribe where user_id=@userid) select month(date_unsubscribed) from unsubscribe where user_id=@userid) Now suppose here I get five records --three products unsubscribed for this month and two for previous month. What should be my query so that three products falling in this month should be taken into account leaving two previous months products. offcourse I have to match the ineuality of months here with the current months using loop but how? like product_id not in(those two previous month) . . . . etc end final sql? thanks ajay