|
slimt_slimt
Aged Yak Warrior
Switzerland
746 Posts |
 Posted - 02/20/2013 : 09:21:38
|
hi,
i have a following DDL:
<code>
-- drop table crt
create 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 dateto
123 1 2011-02-12 2011-03-14
123 2 2011-03-14 2011-04-14
123 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 crt2
create table crt2
(
customerID int
,RN int
,dateFrom smalldatetime
,dateto smalldatetime
)
</code>
my expected results are:
CustomerID RN dateFrom dateto
123 1 2011-02-12 2011-03-14
123 2 2011-03-14 2011-04-14
123 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 int
declare @RN int
declare @orderDate smalldatetime
declare customer_cursor cursor for
select
customerID
from
crt
open customer_cursor
fetch next from customer_cursor
into @customerID
while @@fetch_Status = 0
begin
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 @customerID
end
close customer_cursor
deallocate customer_cursor
select * from crt2
group by customerID, rn, datefrom, dateto
truncate 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.
|
|
date dependency - cursor
