Tricky question...maybe - 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)

Tricky question...maybe

Author

Topic

Crespo24
Village Idiot

144 Posts

Posted - 2003-01-09 : 11:23:58

This is a question that I've been wanting to post for quiet sometime. I must stress though that I have already solved the problem but I used a Cursor to do it.
Now I know that some of you think that only n00b SQL-ers use CURSORS, but I honestly don't think there is any other way to solve the problem. This goes to you
nr, since you are the CURSOR smasher around here, I dare you to find me a solution that does not use CURSORs AND takes less than 3 minutes to run on roughly
13000 records.

OK, this is the problem and I will try and explain it as best as I could.
Consider the following the scenario

KEY START_DATE END_DATE AMO1 AMO2 CODE1 CODE2
----- ------------- ------------- ------ ------ -------- --------
1416 11/08/2002 NULL NULL NULL BULLUK 1B
1416 09/11/1999 10/08/2002 80 3.75 BULLUK 2
1416 16/07/1999 08/11/1999 80 3.75 BULLUK 2
1416 01/09/1997 15/07/1999 80 3.75 BULLUK 2
1416 18/08/1997 31/08/1997 80 3.75 BULLUK 2
1416 17/08/1997 17/08/1997 NULL NULL BULLUK 1B
1416 12/04/1997 16/08/1997 80 3.75 BULLUK 2
1416 01/04/1997 11/04/1997 80 3.75 BULLUK 2
1416 01/07/1987 31/03/1997 80 3.75 BULLUK 2

I will now show you what the result should be and explain to you what needs to be done to the date.

KEY START_DATE END_DATE AMO1 AMO2 CODE1 CODE2
----- ------------- ------------- ------ ------ -------- --------
1416 11/08/2002 NULL NULL NULL BULLUK 1B
1416 18/08/1997 10/08/2002 80 3.75 BULLUK 2
1416 17/08/1997 17/08/1997 NULL NULL BULLUK 1B
1416 01/07/1987 16/08/1997 80 3.75 BULLUK 2

Can you see what I've done to the data?!
The data basically represents different dates and services that a particular employee has been involved in.
But as you can see.. there is no need to repeat the same type of code 2.. instead it is best to keep the earliest date for the service which 01/07/1987
and the last one before the new code2 which 16/08/1997
we then repeat the same process until we have a result set like the second one. Does that make sense?
Below you will see the cursor code I used to solve the problem... anyone who thinks they can do it without a cursor... well I will buy them a 100 beers :D
Good Luck.

DECLARE @MEMBER_KEY VARCHAR(10),
@DATE1 VARCHAR(30),
@DATE2 VARCHAR(30),
@AMOUNT1 VARCHAR(30),
@AMOUNT2 VARCHAR(30),
@AMOUNT3 VARCHAR(30),
@CODE1 VARCHAR(30),
@CODE2 VARCHAR(30)

DECLARE @PREV_MEMBER_KEY VARCHAR(10),
@PREV_DATE1 DATETIME,
@PREV_DATE2 DATETIME,
@PREV_AMOUNT1 VARCHAR(30),
@PREV_AMOUNT2 VARCHAR(30),
@PREV_AMOUNT3 VARCHAR(30),
@PREV_CODE1 VARCHAR(30),
@PREV_CODE2 VARCHAR(30),
@INDICATOR NUMERIC,
@THE_FIRST NUMERIC

DECLARE AHD_CUR CURSOR
READ_ONLY
FOR
SELECT *
FROM GNS_AHD_FINAL

OPEN AHD_CUR

FETCH NEXT FROM AHD_CUR
INTO @MEMBER_KEY,
@DATE1,
@DATE2,
@AMOUNT1,
@AMOUNT2,
@AMOUNT3,
@CODE1,
@CODE2
SET @THE_FIRST = 0

WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @THE_FIRST = 0
BEGIN
INSERT INTO GNS_AHD_HOLDER VALUES(@MEMBER_KEY, @DATE1, @DATE2, @AMOUNT1, @AMOUNT2, @AMOUNT3, @CODE1, @CODE2)

SET @PREV_MEMBER_KEY = @MEMBER_KEY
SET @PREV_DATE1 = @DATE1
SET @PREV_DATE2 = @DATE2
SET @PREV_AMOUNT1 = @AMOUNT1
SET @PREV_AMOUNT2 = @AMOUNT2
SET @PREV_AMOUNT3 = @AMOUNT3
SET @PREV_CODE1 = @CODE1
SET @PREV_CODE2 = @CODE2
SET @THE_FIRST = 1
END

ELSE
BEGIN
IF (@MEMBER_KEY = @PREV_MEMBER_KEY AND @CODE2 = @PREV_CODE2)
BEGIN
UPDATE GNS_AHD_FINAL
SET DATE2 = @DATE2
WHERE MEMBER_KEY = @MEMBER_KEY
AND DATE2 = @PREV_DATE2

UPDATE GNS_AHD_FINAL
SET MEMBER_KEY = 'XXXXX'
WHERE MEMBER_KEY = @MEMBER_KEY
AND DATE1 = @DATE1

SET @PREV_MEMBER_KEY = @MEMBER_KEY
SET @PREV_DATE1 = @DATE1
SET @PREV_DATE2 = @DATE2
SET @PREV_AMOUNT1 = @AMOUNT1
SET @PREV_AMOUNT2 = @AMOUNT2
SET @PREV_AMOUNT3 = @AMOUNT3
SET @PREV_CODE1 = @CODE1
SET @PREV_CODE2 = @CODE2

END
SET @PREV_MEMBER_KEY = @MEMBER_KEY
SET @PREV_DATE1 = @DATE1
SET @PREV_DATE2 = @DATE2
SET @PREV_AMOUNT1 = @AMOUNT1
SET @PREV_AMOUNT2 = @AMOUNT2
SET @PREV_AMOUNT3 = @AMOUNT3
SET @PREV_CODE1 = @CODE1
SET @PREV_CODE2 = @CODE2

END
FETCH NEXT FROM AHD_CUR
INTO @MEMBER_KEY,
@DATE1,
@DATE2,
@AMOUNT1,
@AMOUNT2,
@AMOUNT3,
@CODE1,
@CODE2
END

CLOSE AHD_CUR
DEALLOCATE AHD_CUR

DELETE FROM GNS_AHD_FINAL WHERE MEMBER_KEY = 'XXXXX'

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-09 : 11:32:17

you're processing the rows with a cursor, comparing one row to the next, but there is no ordering ????

is there an actual primary key on this table? how is it ordered?

A non-cursor solution, which I think would be pretty easy, would require field by which to order this table.

- Jeff

Edited by - jsmith8858 on 01/09/2003 11:43:31

Crespo24
Village Idiot

144 Posts

Posted - 2003-01-09 : 11:52:57

quote:

you're processing the rows with a cursor, comparing one row to the next, but there is no ordering ????

is there an actual primary key on this table? how is it ordered?

A non-cursor solution, which I think would be pretty easy, would require field by which to order this table.

- Jeff

Edited by - jsmith8858 on 01/09/2003 11:43:31

Sorry.. I forgot to add the extra bit of code,

Yes there is an order, firstly on the member_key then on DATE1.. the cursor would not work without the order on DATE1

INSERT INTO GNS_AHD_FINAL(MEMBER_KEY, DATE1, DATE2, AMOUNT1, AMOUNT2, AMOUNT3, CODE1, CODE2)
SELECT MEMBER_KEY,
CONVERT(DATETIME, DATE1),
CONVERT(DATETIME, DATE2),
AMOUNT1,
AMOUNT2,
AMOUNT3,
CODE1,
CODE2
FROM GNS_AHD_TMP
ORDER BY CAST(MEMBER_KEY AS NUMERIC), CONVERT(DATETIME, DATE1)
GO

Basically GNS_AHD_FINAL hold the data that is being manipulated in the cursos... GNS_AHD_TMP is the original data.. as you can see the data gets ordered when it is being in serted in to the table.

Thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-09 : 12:15:09

something like this - not saying it will be fast (or even compile or be correct)

find all entries where next entry is different service
select * from tbl t1 where CODE2 <>
(select t2.CODE2 from tbl t2 where t1.Key = t2.Key
and t2.START_DATE = (select min(START_DATE) from tbl t3 where t3.Key = t2.Key)
)

get last entry
select * from tbl where date =
(select max(t2.date) from tbl t2 where t1.Key = t2.Key)

put them together
select * from tbl t1 where CODE2 <>
(select t2.CODE2 from tbl t2 where t1.Key = t2.Key
and t2.START_DATE = (select min(START_DATE) from tbl t3 where t3.Key = t2.Key)
)
union
select * from tbl where date =
(select max(t2.date) from tbl t2 where t1.Key = t2.Key)

now need the start dates for all these
select
KEY,
START_DATE = coalesce((select min(START_DATE) from tbl t4 where a.KEY = t4.Key and a.CODE2 = t4.CODE2
and exists (select * from tbl t5 where t5.Key = t4.Key and t4.CODE2 <> t5.CODE2 and T4.START_DATE > t5.START_DATE))
, (select min(START_DATE) from tbl t6 where a.KEY = t6.Key))
END_DATE ,
AMO1,
AMO2,
CODE1,
CODE2
from
(select * from tbl t1 where CODE2 <>
(select t2.CODE2 from tbl t2 where t1.Key = t2.Key
and t2.START_DATE = (select min(START_DATE) from tbl t3 where t3.Key = t2.Key)
)
union
select * from tbl where date =
(select max(t2.date) from tbl t2 where t1.Key = t2.Key)
) as a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-09 : 12:20:59

OK, here you go:

declare @t table([key] int, start_date datetime, end_date datetime, AMO1 money, AMO2 money, CODE1 varchar(10), CODE2 varchar(10))

insert into @t
select 1416,'8/11/2002',null,null,null,'BULLUK','1B' union
select 1416,'11/9/1999','10/8/2002',80,3.75,'BULLUK','2' union
select 1416,'7/16/1999','11/8/1999',80,3.75,'BULLUK','2' union
select 1416,'9/1/1997','7/15/1999',80,3.75,'BULLUK','2' union
select 1416,'8/18/1997','8/31/1997',80,3.75,'BULLUK','2' union
select 1416,'8/17/1997','8/17/1997',null,null,'BULLUK','1B' union
select 1416,'4/12/1997','8/16/1997',80,3.75,'BULLUK','2' union
select 1416,'4/1/1997','4/11/1997',80,3.75,'BULLUK','2' union
select 1416,'7/1/1987','3/31/1997',80,3.75,'BULLUK','2'

SELECT [Key], Min(Start_date), Max(End_Date), AMO1, AMO2, CODE1, CODE2
FROM
(
select t1.*, (SELECT COUNT(*) FROM @t t2 where t1.[key] = t2.[key] and t1.start_Date > t2.start_date and t1.code2 <> t2.code2) as Grp
FROM @t t1
) A
GROUP BY [Key], AMO1, AMO2, CODE1, CODE2, Grp
ORDER BY Min(Start_Date) DESC

-----
same principles as the "longest number of wins in a row" question, to which I posted a long answer in another thread. I'll add the link later.

The idea is to group streaks .... kind of hard to do, until you realize how to group streaks together. The answer is that each streak of a given field has the same number of records before it (an ORDER BY is important in these, of course) in which that field has a different value.

See my long post in the other thread for more details.

here's the link:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=22583

- Jeff

Edited by - jsmith8858 on 01/09/2003 12:23:41

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-09 : 12:32:38

>> The answer is that each streak of a given field has the same number of records before it

Wish I'd thought of that.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-09 : 12:40:22

MaybeI have topic finally to write my first article??

I actually have never seen anyone use that technique before, I think it's pretty cool.

- Jeff

mr_mist
Grunnio

1870 Posts

Posted - 2003-01-10 : 03:11:24

quote:

MaybeI have topic finally to write my first article??

Before you do that, don't forget to collect on

quote:
anyone who thinks they can do it without a cursor... well I will buy them a 100 beers :D

Mmm, 100 beers.

-------
Moo.

Crespo24
Village Idiot

144 Posts

Posted - 2003-01-10 : 09:48:38

quote:

OK, here you go:

declare @t table([key] int, start_date datetime, end_date datetime, AMO1 money, AMO2 money, CODE1 varchar(10), CODE2 varchar(10))

insert into @t
select 1416,'8/11/2002',null,null,null,'BULLUK','1B' union
select 1416,'11/9/1999','10/8/2002',80,3.75,'BULLUK','2' union
select 1416,'7/16/1999','11/8/1999',80,3.75,'BULLUK','2' union
select 1416,'9/1/1997','7/15/1999',80,3.75,'BULLUK','2' union
select 1416,'8/18/1997','8/31/1997',80,3.75,'BULLUK','2' union
select 1416,'8/17/1997','8/17/1997',null,null,'BULLUK','1B' union
select 1416,'4/12/1997','8/16/1997',80,3.75,'BULLUK','2' union
select 1416,'4/1/1997','4/11/1997',80,3.75,'BULLUK','2' union
select 1416,'7/1/1987','3/31/1997',80,3.75,'BULLUK','2'

SELECT [Key], Min(Start_date), Max(End_Date), AMO1, AMO2, CODE1, CODE2
FROM
(
select t1.*, (SELECT COUNT(*) FROM @t t2 where t1.[key] = t2.[key] and t1.start_Date > t2.start_date and t1.code2 <> t2.code2) as Grp
FROM @t t1
) A
GROUP BY [Key], AMO1, AMO2, CODE1, CODE2, Grp
ORDER BY Min(Start_Date) DESC

-----
same principles as the "longest number of wins in a row" question, to which I posted a long answer in another thread. I'll add the link later.

The idea is to group streaks .... kind of hard to do, until you realize how to group streaks together. The answer is that each streak of a given field has the same number of records before it (an ORDER BY is important in these, of course) in which that field has a different value.

See my long post in the other thread for more details.

here's the link:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=22583

- Jeff

Edited by - jsmith8858 on 01/09/2003 12:23:41

Hmmmm... sorry Jeff but although your solution works and the speed is superb (a fraction of the time it takes to run my cursor).. I can not buy you the beer because it took you to olong to come up with the solution

Welldone though.. full marks for the query... I like it!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-10 : 09:54:50

1/2 hour was too long?

I'll try to do better next time!

- Jeff

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-01-10 : 09:56:11

quote:
I can not buy you the beer because it took you to olong to come up with the solution

That's not funny.

Jay White
{0}

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-01-10 : 09:56:38

quote:

I can not buy you the beer because it took you to olong to come up with the solution

Wow, writing queries never took me to olong. What is olong like this time of year ?

SQLTeam rules state that beer offers must be followed through with. You didn't specify a time limit.

Damian

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-01-10 : 09:58:25

Actually, considering your cursor probably took longer to run than Jeff's solution took to write......

Damian

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-10 : 09:58:36

I'd like to share my reward with Damian and Jay. And of course, Mr. Mist!

(Someone add the beers clinking together GIF for me here!)

(added by Merkin)

- Jeff

Edited by - jsmith8858 on 01/10/2003 09:59:00

Edited by - merkin on 01/10/2003 10:00:34

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-10 : 10:07:43

Willing donation of beers

.
Is that within the rules.
select 100/4
25

Only 25 each.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-10 : 10:10:31

Thanks!

Oh, I forgot, some for nigel too ... but it may be cold and/or fizzy.

- Jeff

Edited by - jsmith8858 on 01/10/2003 10:11:06

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-10 : 10:19:50

CHeers mate.

As long as Rob isn't included I think they should be allocated in proportion to total forum post count.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Crespo24
Village Idiot

144 Posts

Posted - 2003-01-10 : 10:33:03

quote:

I'd like to share my reward with Damian and Jay. And of course, Mr. Mist!

(Someone add the beers clinking together GIF for me here!)

(added by Merkin)

- Jeff

Edited by - jsmith8858 on 01/10/2003 09:59:00

Edited by - merkin on 01/10/2003 10:00:34

Well.... 225 quid to get my code rewritten!!!!! WTF!!

Mind you if I was to take you to my old Uni bar where a pint of beer use to cost 90p (in 1996 ) then it would still coost me, ummmm.. 99 quid...

Besides my cursor looks prettier than Jeff's code and I never asked for such a short solution

.. now that makes me a n00b SQL-er..

For the record... it took me about 20 minutes to do the cursor.. and 4 minutes to run...

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-01-10 : 10:37:28

well, everyone is a newbie, there will always be someone better than you.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-01-10 : 11:27:09

quote:
Well.... 225 quid to get my code rewritten!!!!! WTF!!

You made the rules, kid. Did you forget? Or did you you just wrongly think no one could out-code you?

If you don't want to be a stand up guy about it, I guess you don't have too. Just be lucky Jeff doesn't take you seriously ... or that someone with a night-elf-louisville-slugger didn't decide to answer.

Jay White
{0}

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-10 : 11:55:52

quote:

well, everyone is a newbie, there will always be someone better than you.

Nope.
There will always be someone who knows things you don't.
In fact everyone knows something you don't
The idea is to find out what that is and learn from it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources