SELECT problem - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SELECT problem

Author

Topic

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-10-06 : 10:06:36

Hi guys,

1986-04-01 B
1988-04-01 B
1989-04-06 A
1991-04-01 LC
1995-04-01 A

I need to get

1984-09-10 B
1989-04-06 A
1991-04-01 LC
1995-04-01 A

__________________
Make love not war!

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-10-06 : 10:11:29

I can't seem to figure out the business logic here...how did you get the strange date in the first row of the resultset? Why didn't that happen to the A's?

Owais

Make it idiot proof and someone will make a better idiot

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-06 : 10:16:31

Looks like you have posted two result sets. It will be difficult to propose a select statement without the source table DDL and sample data.

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-10-06 : 10:41:29

Hala Owais lek weenak?, Hi Sam

CREATE TABLE #TEST (MEMBERKEY SMALLINT, EFFDATE DATETIME, CAT CHAR(2))

INSERT INTO #TEST (MEMBERKEY, EFFDATE, CAT)
SELECT 896, '01/04/1986', 'B' UNION ALL
SELECT 896, '01/04/1988', 'B' UNION ALL
SELECT 896, '01/04/1991', 'LC' UNION ALL
SELECT 896, '01/04/1995', 'A' UNION ALL
SELECT 896, '06/04/1989', 'A' UNION ALL
SELECT 896, '10/09/1984', 'B'

As you can see I have 3 entries for B but I only want to keep the entry with the minimum date. Like :

1984-09-10 B
1989-04-06 A
1991-04-01 LC
1995-04-01 A

__________________
Make love not war!

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-10-06 : 10:44:56

Something like :




SELECT * FROM #Test A
WHERE 
	EFFDATE = (SELECT Min(EFFDATE) FROM #Test WHERE CAT = A.CAT) OR
	A.CAT != 'B'

Damian

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-10-06 : 10:49:52

This is the solution that I've come up with :


SELECT	MIN(EFFDATE), 
	CAT
FROM	
(SELECT	EFFDATE,
	CAT,
	(SELECT COUNT(*)
	FROM #TEST AS T2
	WHERE T1.CAT <> T2.CAT
	AND T1.EFFDATE >= T2.EFFDATE) AS TT
FROM	#TEST AS T1) AS EE
GROUP BY CAT, EE.TT
ORDER BY 1

__________________
Make love not war!

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-10-06 : 10:51:05

quote:
Originally posted by Merkin

Something like :

SELECT * FROM #Test A
WHERE
EFFDATE = (SELECT Min(EFFDATE) FROM #Test WHERE CAT = A.CAT) OR
A.CAT != 'B'

Damian

This works too, but the category is not only B... I have many

__________________
Make love not war!

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-10-06 : 10:59:23

You are being a little light on with the info.....
In your example, you WANT a duplicate A, but not a duplicate B. What is the business logic there ?

Damian

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-06 : 11:00:16

SELECT CAT, MIN(EFFDATE)

FROM #Temp A

GROUP BY CAT

Nazim
A custom title

1408 Posts

Posted - 2003-10-06 : 11:11:12

this is a quick dirty reply.but it does what u r looking for

select cat,effdate
from #test
WHERE cat in (
select cat from #test
group by cat
having count(1)<=2)
union
select cat,MIN(effdate)
from #test
group by cat
having count(1)>2

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-10-06 : 11:13:34

Apologies again guys. I will explain what I was trying to achieve.

1984-04-01 B
1986-04-01 B
1988-04-01 B
1989-04-06 A
1991-04-01 LC
1995-04-01 A

Think of the letters as categories for pension benefits. This dude started off in benefit B way back in 1984. His next change of benefit (i.e. switch) occurred in 1989 when he opted for A. Two years later he switched from A to LC then decided to go back to A again. It is obvious now that whoever administered the data got this wrong because they have repeated the same category twice. There is no reason to record 2 further entries for B. All we need to know is when he joined a particular category/benefit and when he decided to switch to another. In other words we only need :

1984-04-01 B
1989-04-06 A
1991-04-01 LC
1995-04-01 A

Sam, your query will eliminate the other entry for A which is not what I want. It is vital that I keep both entries as the change occurred after he switched from LC.

Hope this makes it clearer.

__________________
Make love not war!

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-06 : 11:32:10

Taking a look at your first post, it's a candidate for "most misleading or inadequately stated post".

Sounds like you need a list of all records, eliminating the streaks of repeated sequences, leaving only the first in each streak of Column CAT.

Given the source data

B
B
A
C
B
B
A
A
A
C
C
A

Reduce it to (assume date order)

B
A
C
B
A
C
A

Is that right?

Sam

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-10-06 : 11:36:09

Spot on Sam.

__________________
Make love not war!

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-06 : 11:48:57

quote:
Originally posted by Amethystium

Spot on Sam.

__________________
Make love not war!

No....Where does

"1984-09-10 B"

Come from?

Damn I hate Mondays...

quote:

God knows I work, so many hours
I neeed a change of scene
I know some day I will fight the power
To be a man of means

Be a part of that game
Where your money's talking
Ride that train to where your fortune's smiling
Leave behind a poor man's life
His days are bitches
I have dreams of trading rags for riches

Monday morning blues
You know a prayer will see me through
Monday morning blues
You know I'll make my dreams come true.

It's so routine, this life I'm leading
I've got to break away
I must to be mad, it's soul destroying
No man should live this way.

Should I reach for the sky
Or just wait for mercy
Don't ask why then leave the answer to history
If a man should know his place
Some men live in sorrow
With or without grace I'll fight for tomorrow

Monday morning blues
You know a prayer will see me through
Monday morning blues
You know I'll make my dreams come true.

Hey at least in australia...it's almost Tuesday...

And...It must be 5:00pm somewhere...

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-06 : 12:06:33

http://www.sqlteam.com/item.asp?ItemID=12654

let me know if you need help, but it should be pretty easy using that technique.

EDIT: actually, it looks like you already applied this technique ... what's the problem exactly? how is the way you are currently doing it not working for you?

- Jeff

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-10-06 : 12:16:50

Jeff, apologies to you as well. Should have mentioned your excellent article. I actually used your technique.

This is my final solution :


SELECT	MEMBERKEY,
	MEMBERBENEFITKEY,
	CONVERT(CHAR(10), MIN(CONVERT(DATETIME,EFFDATE )), 103) AS EFFDATE, 
	CAT,
	DJS,
	DEPS,
	NRD INTO ##BENEFITSWITCHHISTORYDEFERREDS
FROM	(SELECT *,
		(SELECT COUNT(*)
		FROM #HOLDER AS T2
		WHERE T1.CAT <> T2.CAT
		AND CONVERT(DATETIME, T1.EFFDATE) >= CONVERT(DATETIME, T2.EFFDATE)
		AND T1.MEMBERKEY = T2.MEMBERKEY) AS TT
	FROM	#HOLDER AS T1) AS EE
GROUP BY  EE.MEMBERKEY, EE.TT, MEMBERBENEFITKEY, CAT, DJS, DEPS, NRD
ORDER BY 1,3
GO

I like this bit.... nifty to say the least!

oh and yes... this does work.

__________________
Make love not war!

AK
Starting Member

27 Posts

Posted - 2003-10-08 : 06:59:52

I think this gives the same result:

select c.memberkey, c.cat, effdate=min(c.effdate)
from
(select a.memberkey, a.cat, a.effdate, next_effdate=min(b.effdate)
from #test a
left join #test b
on a.memberkey=b.memberkey and
a.cat<>b.cat and
a.effdate < b.effdate
group by a.memberkey, a.cat, a.effdate
) as c
group by c.memberkey, c.cat, c.next_effdate
order by 1,3,2

Any idea which is more efficient?

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-10-08 : 09:42:08

Andrew,

Superb solution...

I have not checked the execution plan as I am very busy at the moment but will do when I get home tonight.

Nice one.

__________________
Make love not war!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources