dates dependency - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

dates dependency

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2013-02-18 : 06:39:15

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-18 : 07:48:50

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;

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-18 : 07:57:17

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-18 : 07:58:17

quote:
Hi James, OP wants solution for SQL 2000 Version

I should learn to read the posting more carefully :)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-18 : 08:06:40

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

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2013-02-18 : 08:10:53

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).

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-18 : 08:27:38

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.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2013-02-18 : 08:42:54

you can use MAX and/or MIN and Loops or Cursors.
thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 08:59:22

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/

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2013-02-18 : 09:39:31

Visakh,

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 11:57:27

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/

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-18 : 12:15:17

Very nice Visakh!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 12:20:50

Thanks James

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

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2013-02-18 : 14:31:15

Insane!
Very good Visakh :)

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 23:41:31

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources