How to group consecutive months

Question

Post reply

How to group consecutive months

NewbieCoding

Say Hey Kid

Points: 689
More actions
June 2, 2010 at 3:17 pm

#222305

I have a table with the following data
account_numberpayment_amtDATE_PAYMENT_DUEnum
10043997379.282009-11-01 00:00:00.0001
10043997379.282009-12-01 00:00:00.0001
10043997379.282010-01-01 00:00:00.0001
10043997379.282010-06-01 00:00:00.0001
What I need to do is group the consecutive months and count how many payments that is
In this instance it should look like:
10043997 379.28 three payments from november 2009 through January2010
10043997 379.28 one pament for June 2010
This is killing me and I cant find anything online that can help me. HELP!!!!!:w00t:

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Would the following code snippet help you to get started?

DECLARE @tbl TABLE(account_number CHAR(8),payment_amt DECIMAL(6,2),DATE_PAYMENT_DUE DATETIME, num INT)

INSERT INTO @tbl

SELECT '10043997', 379.28 ,'2009-11-01 00:00:00.000', 1 UNION ALL

SELECT '10043997', 379.28 ,'2009-12-01 00:00:00.000', 1 UNION ALL

SELECT '10043997', 379.28 ,'2010-01-01 00:00:00.000', 1 UNION ALL

SELECT '10043997', 379.28 ,'2010-06-01 00:00:00.000', 1

;

WITH cte_numbered AS

(

SELECT account_number, payment_amt,DATE_PAYMENT_DUE,

ROW_NUMBER() OVER(PARTITION BY account_number ORDER BY DATE_PAYMENT_DUE) AS id

FROM @tbl

)

SELECT

account_number,

COUNT(payment_amt) AS number_of_payments,

MIN(DATE_PAYMENT_DUE),

MAX(DATE_PAYMENT_DUE)

FROM cte_numbered

GROUP BY account_number,DATEADD(mm,-id,DATE_PAYMENT_DUE)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

NewbieCoding Say Hey Kid Points: 689 More actions · Answer 2

How would I do the union all if I am selecting from the table

LutzM SSC Guru Points: 107049 More actions · Answer 3

I don't understand the question...

The UNION ALL is used to populate the sample table @tbl with the (unformatted) data you provided.

Basically, you could use the query after the semicolon. Just replace @tbl with your "real" table name.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

WayneS SSC Guru Points: 95460 More actions · Answer 4

Here's a method that utilizes an infrequently used form of the update statement, nicknamed the "Quirky Update". It's wicked fast, but has about a dozen rules that MUST be followed. See the referenced article within the following code to learn more about it and its rules.

-- See how this starts off with a table and data in it?

-- If you had provided us the data in this format,

-- it would have made things easier for all of the

-- volunteers on this site to help you out.

DECLARE @test-2 TABLE (account_number int, payment_amt numeric(8,2), DATE_PAYMENT_DUE datetime, num int, grp int, PRIMARY KEY CLUSTERED (account_number, date_payment_due))

INSERT INTO @test-2 (account_number, payment_amt, DATE_PAYMENT_DUE, num)

SELECT 10043997, 379.28, '2009-11-01 00:00:00.000', 1 UNION ALL

SELECT 10043997, 379.28, '2009-12-01 00:00:00.000', 1 UNION ALL

SELECT 10043997, 379.28, '2010-01-01 00:00:00.000', 1 UNION ALL

SELECT 10043997, 379.28, '2010-06-01 00:00:00.000', 1

-- declare some variables that are necessary for the following "Quirky Update".

declare @account_number int,

@last_date datetime,

@grp int

-- This form of the UPDATE statement has some particular rules.

-- See Jeff Moden's article at http://www.sqlservercentral.com/articles/T-SQL/68467/

-- for a complete discussion of how this works, and all of the rules for utilizing it.

UPDATE a

SET @account_number = account_number, -- anchor column

@grp = grp = CASE WHEN @last_date IS NOT NULL AND DATEADD(month, 1, @last_date) = DATE_PAYMENT_DUE THEN @grp

ELSE IsNull(@grp,0) + 1 END,

@last_date = DATE_PAYMENT_DUE

FROM @test-2 a -- if using a temp/perm table, need "WITH (TABLOCKX)" added here

OPTION (MAXDOP 1)

-- return the results

SELECT account_number, payment_amt, grp, Qty = COUNT(*)

FROM @test-2

GROUP BY account_number, payment_amt, grp

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

NewbieCoding Say Hey Kid Points: 689 More actions · Answer 5

Getting there thanks for the help

If I run the code below I get the correct output for one loan and one payment amount

DECLARE @test-2 TABLE (account_number int, payment_amt numeric(8,2), DATE_PAYMENT_DUE datetime, num int, grp int, PRIMARY KEY CLUSTERED (account_number, date_payment_due))

INSERT INTO @test-2 (account_number, payment_amt, DATE_PAYMENT_DUE, num)

--SELECT 10043997, 379.28, '2009-11-01 00:00:00.000', 1 UNION ALL

--SELECT 10043997, 379.28, '2009-12-01 00:00:00.000', 1 UNION ALL

--SELECT 10043997, 379.28, '2010-01-01 00:00:00.000', 1 UNION ALL

--SELECT 10043997, 379.28, '2010-06-01 00:00:00.000', 1

SELECT account_number,payment_amt,DATE_PAYMENT_DUE,COUNT(payment_amt) as num FROM T_LOAN_PAYMENT

where account_number = 10043997 and payment_amt = 379.28

group by account_number,payment_amt,DATE_PAYMENT_DUE

-- declare some variables that are necessary for the following "Quirky Update".

declare @account_number int,

@last_date datetime,

@grp int

-- This form of the UPDATE statement has some particular rules.

-- See Jeff Moden's article at http://www.sqlservercentral.com/articles/T-SQL/68467/

-- for a complete discussion of how this works, and all of the rules for utilizing it.

UPDATE a

SET @account_number = account_number, -- anchor column

@grp = grp = CASE WHEN @last_date IS NOT NULL AND DATEADD(month, 1, @last_date) = DATE_PAYMENT_DUE THEN @grp

ELSE IsNull(@grp,0) + 1 END,

@last_date = DATE_PAYMENT_DUE

FROM @test-2 a -- if using a temp/perm table, need "WITH (TABLOCKX)" added here

OPTION (MAXDOP 1)

-- return the results

SELECT account_number, payment_amt, grp, Qty = COUNT(*)

FROM @test-2

GROUP BY account_number, payment_amt, grp

*******************************************************************

However if I run the following code I dont get the desired output