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
 dates dependency
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

slimt_slimt
Aged Yak Warrior

Switzerland
746 Posts

Posted - 02/18/2013 :  06:39:15  Show Profile  Reply with Quote
hi,

i have the following DDL:
<code>
Drop table Sub_orders

create table Sub_orders
(customerID int
,SubscriptionID int
,SubscriptionLength int -- in years
,SubscriptionStart datetime
)


insert into Sub_Orders values (1001, 2001, 1, '2009/03/28')
insert into Sub_Orders values (1001, 2341, 1, '2010/02/13')
insert into Sub_Orders values (1001, 3103, 1, '2011/03/16')
insert into Sub_Orders values (1001, 3759, 1, '2012/04/02')
insert into Sub_Orders values (1056, 3401, 1, '2012/01/28')
insert into Sub_Orders values (1058, 2912, 1, '2010/10/17')
insert into Sub_Orders values (1058, 3731, 1, '2012/03/21')
</code>

with desired output:
customerID SubscriptionID SubscriptionStart NewSubscriptionStart NewSubscriptionEnd
1001 2001 2009-03-28 00:00:00.000 2009-03-28 00:00:00.000 2010-03-28 00:00:00.000
1001 2341 2010-02-13 00:00:00.000 2010-03-28 00:00:00.000 2011-03-28 00:00:00.000
1001 3103 2011-03-16 00:00:00.000 2011-03-28 00:00:00.000 2012-03-28 00:00:00.000
1001 3759 2012-04-02 00:00:00.000 2012-04-02 00:00:00.000 2013-04-02 00:00:00.000
1056 3401 2012-01-28 00:00:00.000 2012-01-28 00:00:00.000 2013-01-28 00:00:00.000
1058 2912 2010-10-17 00:00:00.000 2010-10-17 00:00:00.000 2011-10-17 00:00:00.000
1058 3731 2012-03-21 00:00:00.000 2012-03-21 00:00:00.000 2013-03-21 00:00:00.000

The following example shows that dates: newsubscriptionStart and newsubscriptionEnd must always be calculated based on the unbroken set of dates, starting with first date. So I purchase magazine subscription and prolong it correctly - without any dates broken, all my next subscriptions start dates will be bound on the first date, if not, on the last unbroken.

Desired output shows this example for customerID: 1001 where all first three subscription dates are continuous all bound to first start date of subscription.

If have this solution written with CTE and, but would need it for SQL 2000 version.

thanks for help.

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 02/18/2013 :  07:48:50  Show Profile  Reply with Quote
Good posting! Easy to copy your code and write a query against it!!

Now here is a solution - in spite of your clear posting, whether my solution will work or not.... that I leave it up to you to be the judge :)
;WITH cte AS
(
	SELECT *, DATEADD(yy,SubscriptionLength,SubscriptionStart) AS SubscriptionEndTmp,
	ROW_NUMBER() OVER (PARTITION BY customerId ORDER BY SubscriptionStart) AS RN
	FROM Sub_orders so
)
SELECT
	a.*,
	COALESCE(b.NewSubscriptionStart,a.SubscriptionStart) AS NewSubscriptionStart,
	DATEADD(yy,SubscriptionLength,COALESCE(b.NewSubscriptionStart,a.SubscriptionStart))
		AS NewSubscriptionEnd,NewSubscriptionStart
FROM
	cte a
	OUTER APPLY
	(
		SELECT
			CASE
				WHEN a.SubscriptionStart > b.SubscriptionEndTmp THEN a.SubscriptionStart
				ELSE b.SubscriptionEndTmp
			END  AS NewSubscriptionStart
		FROM
			cte b
		WHERE
			a.RN = b.RN+1 AND b.customerID = a.customerID
		
	)b;
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 02/18/2013 :  07:57:17  Show Profile  Reply with Quote
quote:
Originally posted by James K

Good posting! Easy to copy your code and write a query against it!!

>>I have this solution written with CTE and, but would need it for SQL 2000 version.

Hi James, OP wants solution for SQL 2000 Version


--
Chandu
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 02/18/2013 :  07:58:17  Show Profile  Reply with Quote
quote:
Hi James, OP wants solution for SQL 2000 Version
I should learn to read the posting more carefully :)
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 02/18/2013 :  08:06:40  Show Profile  Reply with Quote
quote:
Originally posted by James K

quote:
Hi James, OP wants solution for SQL 2000 Version
I should learn to read the posting more carefully :)


no problem James.... am curious to know solution in SQL 2K...
am also in trails only......

--
Chandu
Go to Top of Page

slimt_slimt
Aged Yak Warrior

Switzerland
746 Posts

Posted - 02/18/2013 :  08:10:53  Show Profile  Reply with Quote
James K,

thank you for Idea, but nevertheless using CTE and APPLY, there is still issue with calculations for example for first customer 1001:

your output:
customerID SubscriptionID SubscriptionLength SubscriptionStart SubscriptionEndTmp RN NewSubscriptionStart NewSubscriptionEnd
1001 2001 1 2009-03-28 00:00:00.000 2010-03-28 00:00:00.000 1 2009-03-28 00:00:00.000 2010-03-28 00:00:00.000
1001 2341 1 2010-02-13 00:00:00.000 2011-02-13 00:00:00.000 2 2010-03-28 00:00:00.000 2011-03-28 00:00:00.000
1001 3103 1 2011-03-16 00:00:00.000 2012-03-16 00:00:00.000 3 2011-03-16 00:00:00.000 2012-03-16 00:00:00.000
1001 3759 1 2012-04-02 00:00:00.000 2013-04-02 00:00:00.000 4 2012-04-02 00:00:00.000 2013-04-02 00:00:00.000

but it should be:
customerID SubscriptionID SubscriptionLength SubscriptionStart SubscriptionEndTmp RN NewSubscriptionStart NewSubscriptionEnd
1001 2001 1 2009-03-28 00:00:00.000 2010-03-28 00:00:00.000 1 2009-03-28 00:00:00.000 2010-03-28 00:00:00.000
1001 2341 1 2010-02-13 00:00:00.000 2011-02-13 00:00:00.000 2 2010-03-28 00:00:00.000 2011-03-28 00:00:00.000
1001 3103 1 2011-03-16 00:00:00.000 2012-03-16 00:00:00.000 3 2011-03-28 00:00:00.000 2012-03-28 00:00:00.000
1001 3759 1 2012-04-02 00:00:00.000 2013-04-02 00:00:00.000 4 2012-04-02 00:00:00.000 2013-04-02 00:00:00.000

for third subscription: 3103; newsubscriptionStart should be: 2011-03-28 (and not 2011-03-16), because this customer prolonged his subscription regulary without any breaks (between 15 and 30 days prior to expiry of current subscription).

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 02/18/2013 :  08:27:38  Show Profile  Reply with Quote
Ah - given that, all solutions that I can think of end up requiring a recursive CTE or a while loop. Since you are on SQL 2000, CTE's, let alone those of the recursive kind is out of the question. That leaves while loops. Before I post anything silly using while loops, let us see if someone else can post a query using MAXs and MINs and subqueries that will work for SQL 2000. I feel like that should be possible, but I have been so corrupted by cte's and row_numbers that it is hard for me to think in those terms.
Go to Top of Page

slimt_slimt
Aged Yak Warrior

Switzerland
746 Posts

Posted - 02/18/2013 :  08:42:54  Show Profile  Reply with Quote
you can use MAX and/or MIN and Loops or Cursors.
thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  08:59:22  Show Profile  Reply with Quote
this is sql 2005 based CTE solution




create table Sub_orders
(customerID int
,SubscriptionID int
,SubscriptionLength int -- in years
,SubscriptionStart datetime
)


insert into Sub_Orders values (1001, 2001, 1, '2009/03/28')
insert into Sub_Orders values (1001, 2341, 1, '2010/02/13')
insert into Sub_Orders values (1001, 3103, 1, '2011/03/16')
insert into Sub_Orders values (1001, 3759, 1, '2012/04/02')
insert into Sub_Orders values (1056, 3401, 1, '2012/01/28')
insert into Sub_Orders values (1058, 2912, 1, '2010/10/17')
insert into Sub_Orders values (1058, 3731, 1, '2012/03/21')




;WITH cte AS
(
	SELECT *,SubscriptionStart AS SubscriptionStartTmp, DATEADD(yy,SubscriptionLength,SubscriptionStart) AS SubscriptionEndTmp,
	ROW_NUMBER() OVER (PARTITION BY customerId ORDER BY SubscriptionStart) AS RN
	FROM Sub_orders so
) 
,CTE1
AS
(
SELECT *
FROM CTE 
WHERE RN=1
UNION ALL
SELECT c1.customerID,c1.SubscriptionID,c1.SubscriptionLength,c2.SubscriptionStart,
CASE WHEN c2.SubscriptionStart BETWEEN c1.SubscriptionStart AND c1.SubscriptionEndTmp THEN c1.SubscriptionEndTmp ELSE c2.SubscriptionStart END,
DATEADD(yy,c1.SubscriptionLength,CASE WHEN c2.SubscriptionStart BETWEEN c1.SubscriptionStart AND c1.SubscriptionEndTmp THEN c1.SubscriptionEndTmp ELSE c2.SubscriptionStart END),
c2.RN
FROM CTE1 c1
INNER JOIN CTE c2
ON c2.customerID = c1.CustomerID
AND c2.RN = c1.RN+1
)




SELECT * FROM CTE1 
ORDER BY customerID,RN

Drop table Sub_orders



output
-------------------------------------------------
customerID	SubscriptionID	SubscriptionLength	SubscriptionStart	SubscriptionStartTmp	SubscriptionEndTmp	RN
--------------------------------------------------------------------------------------------------------------------------------------
1001	2001	1	2009-03-28 00:00:00.000	2009-03-28 00:00:00.000	2010-03-28 00:00:00.000	1
1001	2001	1	2010-02-13 00:00:00.000	2010-03-28 00:00:00.000	2011-03-28 00:00:00.000	2
1001	2001	1	2011-03-16 00:00:00.000	2011-03-28 00:00:00.000	2012-03-28 00:00:00.000	3
1001	2001	1	2012-04-02 00:00:00.000	2012-04-02 00:00:00.000	2013-04-02 00:00:00.000	4
1056	3401	1	2012-01-28 00:00:00.000	2012-01-28 00:00:00.000	2013-01-28 00:00:00.000	1
1058	2912	1	2010-10-17 00:00:00.000	2010-10-17 00:00:00.000	2011-10-17 00:00:00.000	1
1058	2912	1	2012-03-21 00:00:00.000	2012-03-21 00:00:00.000	2013-03-21 00:00:00.000	2



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

Go to Top of Page

slimt_slimt
Aged Yak Warrior

Switzerland
746 Posts

Posted - 02/18/2013 :  09:39:31  Show Profile  Reply with Quote
Visakh,

thank you.
do you have in 2000+ version? CTE is not supported in 2000.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  11:57:27  Show Profile  Reply with Quote
Here is 2000 based solution



SELECT *,
COALESCE((SELECT COUNT(*) FROM Sub_orders WHERE customerID = t.customerID AND SubscriptionStart < t.subscriptionStart),0) + 1 AS RN,
COALESCE((SELECT SUM(SubscriptionLength) FROM Sub_orders WHERE customerID = t.customerID AND SubscriptionStart <= t.subscriptionStart),0) AS RunSubLen
INTO #Tmp
FROM Sub_orders t

SELECT t1.customerID,
t1.subscriptionID,
t1.SubscriptionLength,
t1.SubscriptionStart,
CASE WHEN t1.SubscriptionStart  BETWEEN t2.SubscriptionStart AND DATEADD(yy,t1.RunSubLen-1,(SELECT SubscriptionStart FROM #Tmp WHERE RN=1 AND customerID = t1.customerid)) 
THEN  DATEADD(yy,t1.RunSubLen-1,(SELECT SubscriptionStart FROM #Tmp WHERE RN=1 AND customerID = t1.customerid)) 
ELSE t1.SubscriptionStart 
END AS SubscriptionStartTmp,
CASE WHEN t1.SubscriptionStart  BETWEEN t2.SubscriptionStart AND DATEADD(yy,t1.RunSubLen-1,(SELECT SubscriptionStart FROM #Tmp WHERE RN=1 AND customerID = t1.customerid)) 
THEN  DATEADD(yy,t1.RunSubLen,(SELECT SubscriptionStart FROM #Tmp WHERE RN=1 AND customerID = t1.customerid)) 
ELSE DATEADD(yy,t1.SubscriptionLength,t1.SubscriptionStart)
END AS SubscriptionEndTmp
FROm #Tmp t1
LEFT JOIN #Tmp t2
ON t2.customerID = t1.customerID
AND t2.RN = t1.RN -1




outut
--------------------------------------------------------------------------------------------------------------------------
customerID	subscriptionID	SubscriptionLength	SubscriptionStart	SubscriptionStartTmp	SubscriptionEndTmp
--------------------------------------------------------------------------------------------------------------------------
1001	2001	1	2009-03-28 00:00:00.000	2009-03-28 00:00:00.000	2010-03-28 00:00:00.000
1001	2341	1	2010-02-13 00:00:00.000	2010-03-28 00:00:00.000	2011-03-28 00:00:00.000
1001	3103	1	2011-03-16 00:00:00.000	2011-03-28 00:00:00.000	2012-03-28 00:00:00.000
1001	3759	1	2012-04-02 00:00:00.000	2012-04-02 00:00:00.000	2013-04-02 00:00:00.000
1056	3401	1	2012-01-28 00:00:00.000	2012-01-28 00:00:00.000	2013-01-28 00:00:00.000
1058	2912	1	2010-10-17 00:00:00.000	2010-10-17 00:00:00.000	2011-10-17 00:00:00.000
1058	3731	1	2012-03-21 00:00:00.000	2012-03-21 00:00:00.000	2013-03-21 00:00:00.000



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

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 02/18/2013 :  12:15:17  Show Profile  Reply with Quote
Very nice Visakh!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  12:20:50  Show Profile  Reply with Quote
Thanks James

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

Go to Top of Page

slimt_slimt
Aged Yak Warrior

Switzerland
746 Posts

Posted - 02/18/2013 :  14:31:15  Show Profile  Reply with Quote
Insane!
Very good Visakh :)

thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  23:41:31  Show Profile  Reply with Quote
welcome

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

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.15 seconds. Powered By: Snitz Forums 2000