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.
Author |
Topic |
slimt_slimt
Aged Yak Warrior
746 Posts |
Posted - 2013-02-20 : 09:21:38
|
hi,i have a following DDL:<code>-- drop table crtcreate table crt(customerID int,RN int,daydiff int,orderDate smalldatetime)insert into crt (customerID, RN, daydiff, orderDate) values (123,1,30,'2011/02/12')insert into crt (customerID, RN, daydiff, orderDate) values (123,2,30,'2011/03/02')insert into crt (customerID, RN, daydiff, orderDate) values (123,3,30,'2011/06/10')insert into crt (customerID, RN, daydiff, orderDate) values (123,4,30,'2011/06/15')insert into crt (customerID, RN, daydiff, orderDate) values (234,1,30,'2012/10/27')insert into crt (customerID, RN, daydiff, orderDate) values (345,1,14,'2012/11/13')insert into crt (customerID, RN, daydiff, orderDate) values (345,2,14,'2012/11/25')insert into crt (customerID, RN, daydiff, orderDate) values (555,1,10,'2010/10/17')insert into crt (customerID, RN, daydiff, orderDate) values (555,2,10,'2011/11/18')insert into crt (customerID, RN, daydiff, orderDate) values (555,3,10,'2012/12/19')expected results:partnerID RN dateFrom dateto123 1 2011-02-12 2011-03-14 123 2 2011-03-14 2011-04-14123 3 2011-06-10 2011-07-10 123 4 2011-07-10 2011-08-10 234 1 2012-10-27 2012-11-26 345 1 2012-11-13 2012-11-27 345 2 2012-11-25 2012-12-09 555 1 2010-10-17 2010-10-27 555 2 2011-11-18 2011-11-28 555 3 2012-12-19 2012-12-29 -- drop table crt2create table crt2( customerID int,RN int,dateFrom smalldatetime,dateto smalldatetime)</code>my expected results are:CustomerID RN dateFrom dateto123 1 2011-02-12 2011-03-14 123 2 2011-03-14 2011-04-14123 3 2011-06-10 2011-07-10 123 4 2011-07-10 2011-08-10 234 1 2012-10-27 2012-11-26 345 1 2012-11-13 2012-11-27 345 2 2012-11-25 2012-12-09 555 1 2010-10-17 2010-10-27 555 2 2011-11-18 2011-11-28 555 3 2012-12-19 2012-12-29 tricky part is customerID: 123 when altering from 2nd to 3rd subscription.my code is: <code>declare @customerID intdeclare @RN intdeclare @orderDate smalldatetimedeclare customer_cursor cursor forselect customerIDfrom crtopen customer_cursorfetch next from customer_cursorinto @customerIDwhile @@fetch_Status = 0begin print @customerID declare order_cursor cursor for select RN ,orderDate from crt where customerID = @customerID open order_cursor fetch next from order_cursor into @RN, @orderDate if @@fetch_status <> 0 print '------ cursor empty -------' while @@fetch_status = 0 begin insert into crt2 ( customerID,RN,dateFrom,dateTo) select @customerID ,@rn -- this part must be altered /start/ ,case when c1.orderDate between c2.datefrom and c2.dateto then c2.dateto else c1.orderDate+c1.daydiff end as dateFrom ,case when c1.orderDate between c2.datefrom and c2.dateto then c2.datefrom+c1.daydiff else c1.orderDate+c1.daydiff end as dateTo -- this part must be altered /end/ from crt as c1 left join crt2 as c2 on c1.customerID = c2.customerID and c1.rn+1 = isnull(c2.rn+1,1) where @customerID = c1.customerID and @rn = c1.rn print @rn print @orderDate fetch next from order_cursor into @RN, @orderDate end close order_cursor deallocate order_cursor fetch next from customer_cursor into @customerIDendclose customer_cursordeallocate customer_cursorselect * from crt2group by customerID, rn, datefrom, datetotruncate table crt2</code>the second inner cursor is not working properly when handling dates. Idea is (as in my previous post "Date dependency") to have continous prolonged date subscriptions dateFrom and dateTo. now each subscription can be 14 days or 30 days. If purchase of new subscription is within the dates of already existing subscription, user is automatically prolonged without any dates broken. if there is at least one day in between, user gets new startdate date. Code must be in SQL 2000+.thanks for code revision and help. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
slimt_slimt
Aged Yak Warrior
746 Posts |
Posted - 2013-02-20 : 09:53:58
|
Yes, this is it.Problem is that in sample above customerID: 123 should not get first date but the last first date of unbroken prolonged dates. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-20 : 10:04:46
|
quote: Originally posted by slimt_slimt Yes, this is it.Problem is that in sample above customerID: 123 should not get first date but the last first date of unbroken prolonged dates.
what do you mean by "last first date of unbroken prolonged dates"?illustrate with sample data------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
slimt_slimt
Aged Yak Warrior
746 Posts |
Posted - 2013-02-20 : 10:28:25
|
Yes.if you look at the sample data in this post, you will see that customer 123 has following dates:123 1 30 2011-02-12 123 2 30 2011-03-02 123 3 30 2011-06-10 123 4 30 2011-06-15 starting with first one is 2011-02-13. this subscription is valid for 30 day. that is until 2011-03-14. because the second one was purchased on 2011-03-02, so within the timeframe of first subscription, he is automatically prolonged. so subscription for the second one is from 2011-03-14 until 2011-04-14. the third subscription was not prolonged continuously - there were days of break in between. so his third subscription is valid from 2011-06-10 (and not from the first date 2011-02-13) for 30 days. until 2011-07-10. and fourth subscription is again prolonged continuously - without any breaks.solution from previous post can also be altered to be suited for this case.thanks! |
|
|
slimt_slimt
Aged Yak Warrior
746 Posts |
Posted - 2013-02-21 : 01:32:22
|
If you find an error in code above, post it here.thanks |
|
|
|
|
|
|
|