ansaurus

Question

Answer 1

+1 A:

You can do it using cursor. Language like c#/java are better choice for this problem.

DECLARE @date DATETIME
DECLARE @nextDate DATETIME
DECLARE @rangeStart DATETIME
DECLARE @rangeEnd DATETIME

DECLARE @value decimal(18,2)
DECLARE @valueSum decimal(18,2)
DECLARE @count int

DECLARE @PaymentCursor CURSOR

SET @PaymentCursor = CURSOR FOR
    SELECT Date_payment, Payment_value FROM #data
    ORDER BY Date_payment

OPEN @PaymentCursor
    FETCH NEXT FROM @PaymentCursor INTO @nextDate, @value   
    SET @date = @nextDate
    SET @rangeStart = @nextDate
    SET @valueSum = 0
    SET @count = 0

    WHILE (@@FETCH_STATUS = 0)
    BEGIN       
        FETCH NEXT FROM @PaymentCursor INTO @nextDate, @value

        SET @count = @count + 1
        SET @valueSum = @valueSum + @value      

        IF (DATEDIFF(mm, @date, @nextDate) > 1)
        BEGIN
            SELECT @rangeStart AS RangeStart, @date AS RangeEnd, @count AS Coount, @valueSum AS VALUE, DATEDIFF(mm, @date, @nextDate) AS months_until_next_payment
            SET @valueSum = 0               
            SET @count = 0
            SET @rangeStart = @nextDate
        END

        SET @date = @nextDate           
    END

    SELECT @rangeStart AS RangeStart, @date AS RangeEnd, @count AS Coount, @valueSum AS VALUE, null AS months_until_next_payment

CLOSE @PaymentCursor
DEALLOCATE @PaymentCursor

Branimir 2010-09-28 12:34:44

If you have to use a Cursor in SQL, you probably should look at putting the logic into your app instead of in the database.

JNK 2010-09-28 12:39:29

@JNK Yes, I agree.

Branimir 2010-09-28 12:45:06

@Branimir - thanks for this, but it produces each row as a separate result table (and I want all of them together for easier reference) and it seems to be running very slow (took around 6 minutes to pull 10 records, and I have a good few hundred thousand).

Davin 2010-09-28 13:07:59

You can insert result rows in a table, create a table and use INSERT INTO instead of SELECT in WHILE. Performance depends on your data and hardware and using CURSOR couse performance penalty...

Branimir 2010-09-28 13:24:17

Answer 2

+3 A:

Edit: I've added in the months_until_next_payment column. This would be more efficiently done in the application rather than with a self join however as SQL Server does not have any particularly satisfactory way of referencing next and previous rows.

;WITH base AS ( 
SELECT    Contact_reference  ,
          Payment_value,
          DATEPART(YEAR, Date_payment)*12 + DATEPART(MONTH, Date_payment) - 
               DENSE_RANK() OVER 
                   (PARTITION BY Contact_reference 
                    ORDER BY DATEPART(YEAR, Date_payment)*12 + DATEPART(MONTH, Date_payment)) AS G,
          Date_payment
 FROM     #data
 ),
 cte AS
 (
 SELECT 
          Contact_reference, 
          ROW_NUMBER() over (partition by Contact_reference 
                                 order by MIN(Date_payment)) RN,
          MIN(Date_payment) Range_start,
          MAX(Date_payment) Range_end, 
          COUNT(Payment_value) Payments, 
          SUM(Payment_value) Value
 FROM base
 GROUP BY Contact_reference, G
 )
 SELECT 
       c1.Contact_reference, 
       c1.Payments, 
       c1.Range_end, 
       c1.Range_start, 
       c1.Value, 
       DATEDIFF(month, c1.Range_end,c2.Range_start) months_until_next_payment
 FROM cte c1
 LEFT join cte c2 ON c1.Contact_reference=c2.Contact_reference and c2.RN = c1.RN+1

Martin Smith 2010-09-28 12:43:31

Sorry for the sheer ignorance of this, but why do you do DATEPART(year) * 12? and also if you could possibly let me know how to begin doing the left join for the months_until_next payment that would be fantastic, as I am a proper n00b with SQL! :) Thanks!

Davin 2010-09-28 13:10:28

Instead of another join, you could just add a computed column on the final select statement, altering it to read: SELECT Contact_reference, MIN(Date_payment) Range_start,MAX(Date_payment) Range_end, COUNT(Payment_value) Payments, SUM(Payment_value) Value , (select MIN(G) - q.g + 1 from cte1 where g > q.g) as months_until_next_payment FROM cte1 q GROUP BY Contact_reference, G

Richard Fawcett 2010-09-28 13:18:30

DATEPART(year) * 12 + DATEPART(month) is used to get an integer which changes by one every time we advance a calendar month. It's because there are 12 years in a month, when we reset to January, we subtract 11 for the change in month, and add 1*12 for the change in year, meaning the net result is adding one.

Richard Fawcett 2010-09-28 13:21:04

Wow, this answer is amazing! Thanks so much for this :)

Davin 2010-09-28 14:45:54

ansaurus

tags:

views:

answers:

Consecutive streak of dates

related questions