Bogus data entry question

Question

Bogus data entry question

TSQLExplorer

Ten Centuries

Points: 1279
More actions
December 9, 2009 at 12:05 pm

#73411

I have a table created by my code below for your convenience (lame.. I know.. I didn't design it so don't blast me please) where problem report records were entered with a cuslink identifier, disposition code and report date. Users started updating these dispositions at some point in the distant past (years ago) by entering a whole new record with a new disposition code and date, but with the same cuslink number instead of modifying the initial record. Now I need a count of all of these transactions and need to count these cuslink related records with differend dispcodes and different dates as one based on the records having the same cuslink, specifid dispcodes, and the referdate being within X days of each other. I've tried varying types of joins, but can't seem to get what I need (all of the records meeting the criteria) to be counted / shown as one count record.
if object_id ('tempdb..#cuslink1','U')is not null
DROP TABLE #cuslink1
create table #cuslink1(
cuslink numeric(5,0),
DISCD varchar(4),
rfdate datetime)
INSERT INTO #cuslink1(cuslink,DISCD,rfdate)
select '2232','eex','10/21/2008 12:00am' union all
select '2232','co2','10/29/2008 12:00am' union all
select '2232','co1','11/21/2008 12:00am' union all
select '2241','eex','8/21/2008 12:00am' union all
select '2241','co2','9/21/2008 12:00am' union all
select '2241','co2','9/23/2008 12:00am' union all
select '232','eex','4/2/2008 12:00am' union all
select '232','eex','5/21/2008 12:00am' union all
select '232','eex','5/26/2008 12:00am' union all
select '92232','eex','10/21/2008 12:00am' union all
select '92232','co2','10/21/2009 12:00am' union all
select '92232','co3','11/21/2009 12:00am' union all
select '92232','eex','12/21/2009 12:00am' union all
select '92232','co2','12/23/2008 12:00am'
results desired (with parameters set to return data with
number of days between rfdates set to less than or equal to 10
and DISCCD in ('EEX','CO2')
would look like this
CUSLINK, TOTAL
2232, 1
92232, 2
Any suggestions would be appreciated.
Thanks..

Viewing 15 posts - 1 through 14 (of 14 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Hard to help with just the problem description provided. Please read the first article I reference below in my signature block regarding asking for assistance. If you follow the instructions in that article and include expected results based on the sample data, you will get several people jumping in to help, PLUS, you'll get tested code in return!

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

You may want to look at a solution posted by Lowell

http://www.sqlservercentral.com/Forums/Topic830694-8-2.aspx

Agreed the problem is NOT identical to yours but is similiar in nature and may give you an idea of how to create a solution.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

TSQLExplorer Ten Centuries Points: 1279 More actions · Answer 3

Thanks.. took your suggestion and edited the initial post to contain the recommended code.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

TSQLExplorer (12/9/2009)
Thanks.. took your suggestion and edited the initial post to contain the recommended code.

Okay, now how about the expected output based on the sample data provided?

TSQLExplorer Ten Centuries Points: 1279 More actions · Answer 5

I need a count of cuslinks with differend dispcodes and different dates as one record count based on the recordsets having the same cuslink, specified dispcodes, and the referdate being within X days of each other

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Please don't describe the results you want, show us using the sample data. This way we have something to test against.

TSQLExplorer Ten Centuries Points: 1279 More actions · Answer 7

Sorry .. guess I missed that in your suggested article..

I've edited the initial post to reflect this requirement.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

TSQLExplorer (12/9/2009)
Sorry .. guess I missed that in your suggested article..
I've edited the initial post to reflect this requirement.

Correct, it is missing from the article, but it wasn't missing from my post where I suggested reading the article.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

What should the result set for these be? They are a part of the sample data as well.

select '2241','eex','8/21/2008 12:00am' union all

select '2241','co2','9/21/2008 12:00am' union all

select '2241','co2','9/23/2008 12:00am' union all

select '232','eex','4/2/2008 12:00am' union all

select '232','eex','5/21/2008 12:00am' union all

select '232','eex','5/26/2008 12:00am' union all

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

Hmmm... are you saying that the only time you want to return a count is when an EEX is followed by a CO2 that's ten day or less appart?

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

TSQLExplorer Ten Centuries Points: 1279 More actions · Answer 11

TSQLExplorer

Ten Centuries

Points: 1279

December 10, 2009 at 8:41 am

#1090609

That is exactly what I need...

THANKS!!!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

TSQLExplorer (12/10/2009)
That is exactly what I need...
THANKS!!!

Okay, that helps but then how do you get a count of 1 from this, I'd assume it would be 2:

select '92232','eex','10/21/2008 12:00am' union all --

select '92232','co2','10/21/2009 12:00am' union all -- This is one

select '92232','co3','11/21/2009 12:00am' union all

select '92232','eex','12/21/2009 12:00am' union all --

select '92232','co2','12/23/2008 12:00am' -- This is the second

TSQLExplorer Ten Centuries Points: 1279 More actions · Answer 13

You are correct.. my bust.. missed the second one. I will edit the initial post to reflect. Thanks.

TSQLExplorer Ten Centuries Points: 1279 More actions · Answer 14

The result set with parameters set to return data with

number of days between rfdates set to less than or equal to 10

and DISCCD in ('EEX','CO2')

would look like this

CUSLINK, TOTAL

2232, 1

92232, 2

Thanks for all of your help with this!