Finding Repeating Amount (more than 1 as group)

Question

Finding Repeating Amount (more than 1 as group)

karthik M

One Orange Chip

Points: 29139
More actions
November 10, 2010 at 10:20 am

#241074

Hi All,
I have table (Balance_due) in the following format:
---------------------------------------------------------
Namebalance_due_as_onAmount
--------------------------------------------------------
Ravi11/09/2010112.50
John11/09/2010100.25
Ravi11/08/2010112.50
John11/08/2010100.25
Ravi11/05/2010112.50
John11/05/2010200.75
Ravi11/04/2010111.00
Ravi11/03/2010105.25
John11/03/2010105.00
John11/02/2010100.25
John11/01/2010100.25
--------------------------------------------------------
I need reports like as follows:
Report 1: (Repeating Amount and how many days it is repeating) for the last 30 days from the current date.
-----------------------------------------------------------------------
NameAmountRepeating_daysFrom_dateTo_date
-----------------------------------------------------------------------
Ravi112.50511/05/201011/09/2010
John100.25211/08/201011/09/2010
Ravi 99.50210/19/201010/20/2010
John100.25211/01/201011/02/2010
-----------------------------------------------------------------------
SHOULD NOT DISPLAY LIKE BELOW:
John 100.25 4 11/01/2010 11/09/2010
Report 2: (Missing Name) for the last 30 days from the current date
-----------------------------------
NameMissing_Date
-----------------------------------
John11/04/2010
Ravi11/02/2010
John10/20/2010
-----------------------------------
Yours help is very much appreciated
karthik

Viewing 10 posts - 1 through 10 (of 10 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Karthik,

you've been around long enough to know how to ask questions. Table DDL and sample data in a ready to use format, expected output and what you've tried so far. Please.

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]

LutzM SSC Guru Points: 107049 More actions · Answer 2

CELKO (11/10/2010)
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

Other than your usual ISO references, what's the value added by your post??

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]

karthik M One Orange Chip Points: 29139 More actions · Answer 3

create table balance_due

(

name varchar(15),

balance_due_as_on datetime,

Amount float

)

go

insert into balance_due

select 'Ravi','11/09/2010',112.50

union all

select 'John','11/09/2010',100.25

union all

select 'Ravi','11/08/2010',112.50

union all

select 'John','11/08/2010',100.25

union all

select 'Ravi','11/05/2010',112.50

union all

select 'John','11/05/2010',200.75

union all

select 'Ravi','11/04/2010',111.00

union all

select 'Ravi','11/03/2010',105.25

union all

select 'John','11/03/2010',105.00

union all

select 'John','11/02/2010',100.25

union all

select 'John','11/01/2010',100.25

karthik

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Your sample data doesn't match your expected output, Karthik.

DROP table #balance_due

create table #balance_due

(

[name] varchar(15),

balance_due_as_on datetime,

Amount float

)

insert into #balance_due

select 'John','11/01/2010',100.25 union all --

select 'John','11/02/2010',100.25 union all --

select 'John','11/03/2010',105.00 union all

select 'John','11/05/2010',200.75 union all

select 'John','11/08/2010',100.25 union all --

select 'John','11/09/2010',100.25 union all --

select 'Ravi','11/03/2010',105.25 union all

select 'Ravi','11/04/2010',111.00 union all

select 'Ravi','11/05/2010',112.50 union all

select 'Ravi','11/08/2010',112.50 union all --

select 'Ravi','11/09/2010',112.50 --

SELECT

[name],

Amount,

Start= MIN(balance_due_as_on),

[End]= MAX(balance_due_as_on),

Days= COUNT(*)

FROM (

SELECT [name], balance_due_as_on, Amount,

rn1 = ROW_NUMBER() OVER (ORDER BY [name], balance_due_as_on),

rn2 = ROW_NUMBER() OVER (PARTITION BY [name], Amount ORDER BY [name], balance_due_as_on)

FROM #balance_due

) d

GROUP BY [name], rn1-rn2, Amount

HAVING COUNT(*) > 1

ORDER BY [name], MIN(balance_due_as_on)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 5

LutzM (11/10/2010)
Karthik,
you've been around long enough to know how to ask questions. Table DDL and sample data in a ready to use format, expected output and what you've tried so far. Please.

You said that[font="Arial Black"] so [/font]much nicer than I was going to. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 6

LutzM (11/10/2010)
CELKO (11/10/2010)
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html
Other than your usual ISO references, what's the value added by your post??

I agree... the link provided is a freakin' "book" on how to put up with the supposed "no BS" replies of people on supposed "hacker" sites. No where does it teach someone how to actually present DDL or sample data. The link has a lot of good tips and sound advice on how to ask questions but without teaching how to collect and post sample data, it's virtually useless as a guide on how to post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 7

Report 1: (Repeating Amount and how many days it is repeating) for the last 30 days from the current date.

Lutz and Chris,

I'm not sure what's going on but I've seen that very same request on 3 different posts in only as many days. I suspect someone is running a contest somewhere.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Jeff Moden (11/14/2010)
Report 1: (Repeating Amount and how many days it is repeating) for the last 30 days from the current date.
Lutz and Chris,
I'm not sure what's going on but I've seen that very same request on 3 different posts in only as many days. I suspect someone is running a contest somewhere.

Thanks Jeff, I appreciate the heads-up. I'm disappointed that Karthik hasn't bothered to respond, even if only to say "it doesn't work for me" or "how does it work?"

As a Junior Software Engineeer I find it quite rude.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

LutzM SSC Guru Points: 107049 More actions · Answer 9

Chris Morris-439714 (11/15/2010)
...
Thanks Jeff, I appreciate the heads-up. I'm disappointed that Karthik hasn't bothered to respond, even if only to say "it doesn't work for me" or "how does it work?"
As a Junior Software Engineeer I find it quite rude.

Sometimes I feel like I'm in a parallel universe where titles mean just the opposite of what they would in our original world... Chris as a Junior and "some others" as Seniors? WEIRD!!

Regarding the contest: I'm not aware of any contest covering such a subject. But neither I'm hanging around in too many forums... 😉

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]