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-18 : 06:39:15
|
hi,i have the following DDL: <code>Drop table Sub_orderscreate 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 NewSubscriptionEnd1001 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.0001058 2912 2010-10-17 00:00:00.000 2010-10-17 00:00:00.000 2011-10-17 00:00:00.0001058 3731 2012-03-21 00:00:00.000 2012-03-21 00:00:00.000 2013-03-21 00:00:00.000The 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,NewSubscriptionStartFROM 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 NewSubscriptionEnd1001 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.0001001 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.0001001 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.0001001 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.000but it should be:customerID SubscriptionID SubscriptionLength SubscriptionStart SubscriptionEndTmp RN NewSubscriptionStart NewSubscriptionEnd1001 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.0001001 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.0001001 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.0001001 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.000for 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 solutioncreate 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) ,CTE1AS(SELECT *FROM CTE WHERE RN=1UNION ALLSELECT 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.RNFROM CTE1 c1INNER JOIN CTE c2ON c2.customerID = c1.CustomerIDAND c2.RN = c1.RN+1)SELECT * FROM CTE1 ORDER BY customerID,RNDrop table Sub_ordersoutput-------------------------------------------------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 11001 2001 1 2010-02-13 00:00:00.000 2010-03-28 00:00:00.000 2011-03-28 00:00:00.000 21001 2001 1 2011-03-16 00:00:00.000 2011-03-28 00:00:00.000 2012-03-28 00:00:00.000 31001 2001 1 2012-04-02 00:00:00.000 2012-04-02 00:00:00.000 2013-04-02 00:00:00.000 41056 3401 1 2012-01-28 00:00:00.000 2012-01-28 00:00:00.000 2013-01-28 00:00:00.000 11058 2912 1 2010-10-17 00:00:00.000 2010-10-17 00:00:00.000 2011-10-17 00:00:00.000 11058 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 MVPhttp://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 solutionSELECT *,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 RunSubLenINTO #TmpFROM Sub_orders tSELECT 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 SubscriptionEndTmpFROm #Tmp t1LEFT JOIN #Tmp t2ON t2.customerID = t1.customerIDAND t2.RN = t1.RN -1outut--------------------------------------------------------------------------------------------------------------------------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.0001001 2341 1 2010-02-13 00:00:00.000 2010-03-28 00:00:00.000 2011-03-28 00:00:00.0001001 3103 1 2011-03-16 00:00:00.000 2011-03-28 00:00:00.000 2012-03-28 00:00:00.0001001 3759 1 2012-04-02 00:00:00.000 2012-04-02 00:00:00.000 2013-04-02 00:00:00.0001056 3401 1 2012-01-28 00:00:00.000 2012-01-28 00:00:00.000 2013-01-28 00:00:00.0001058 2912 1 2010-10-17 00:00:00.000 2010-10-17 00:00:00.000 2011-10-17 00:00:00.0001058 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 MVPhttp://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 MVPhttp://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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|