Need some help - 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)

Need some help

Author

Topic

Crespo

85 Posts

Posted - 2002-04-25 : 06:11:12

Hi there,

I have a very tight deadline and I need to get this migration done in the next 48 hours. This is just a small problem which I don't have to do at the moment as I am working on a more complicated script.

Here is the problem and what I need from one of you guys to help me with.

INSERT INTO AHD_CHAR (MEMBERKEY, EFFDATE, DATE1, AMOUNT1, AMOUNT2, AMOUNT3, DATE2)
SELECT
CONVERT(CHAR(100),MEMBER_KEY),
CONVERT(CHAR(100),DATE1,103),
CONVERT(CHAR(100),DATE1,103),
CONVERT(CHAR(100),CONVERT(DECIMAL(10,2),AMOUNT1)),
CONVERT(CHAR(100),CONVERT(DECIMAL(10,2),AMOUNT2)),
CONVERT(CHAR(100),CONVERT(DECIMAL(10,2),AMOUNT3)),
CONVERT(CHAR(100),DATE2,103)
FROM GNS_AHD AS A,
SCHEMEMEMBER_CHAR AS B
WHERE A.MEMBER_KEY = B.MEMBERKEY

Now if a member key has two entries with the same DATE1 then there is a problem.
What I need is some nice piece if code, some PL/SQL or cursor to find memberkeys with two entries that have the same DATE1. If that is the case then the first memberkey is inserted to the table, but the second one is incremented by 1 and inserted into the EFFDATE column.
If the memberkey has three DATE1 fields with the same data, then the third date is incremented by 2 etc.

Does it makee sense and if so, could somebody help me out on this as I really don't have time to work on this right now.

Good Luck!

Crespo.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-17 : 13:54:50


create table #blah (member_key int, date1 datetime)

insert #blah
select 1,'1/1/01' union all
select 1,'1/1/01' union all
select 1,'1/1/01' union all
select 2,'1/1/01'

declare @counter int
set @counter = -1

update
    #blah
set
    date1 = date1 + @counter,
    @counter = @counter + 1
where
    member_key in (
        select
            member_key
        from
            #blah
        group by 
            member_key
        having count(*) > 1 )

select * from #blah

drop table #blah

Ok, so its a couple days late . . .but cursor-smursor (and pl/sql-pl/smequel . . . )

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources