SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 date dependency - cursor
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

slimt_slimt
Aged Yak Warrior

Switzerland
746 Posts

Posted - 02/20/2013 :  09:21:38  Show Profile  Reply with Quote
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.


visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/20/2013 :  09:32:00  Show Profile  Reply with Quote
isnt this what i gave you solution for two days ago?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=183062

whats the purpose of using cursor for this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

slimt_slimt
Aged Yak Warrior

Switzerland
746 Posts

Posted - 02/20/2013 :  09:53:58  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/20/2013 :  10:04:46  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

slimt_slimt
Aged Yak Warrior

Switzerland
746 Posts

Posted - 02/20/2013 :  10:28:25  Show Profile  Reply with Quote
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!
Go to Top of Page

slimt_slimt
Aged Yak Warrior

Switzerland
746 Posts

Posted - 02/21/2013 :  01:32:22  Show Profile  Reply with Quote
If you find an error in code above, post it here.

thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000