Duplicate key issue

Question

Duplicate key issue

martinfalch

SSC Veteran

Points: 253
More actions
May 13, 2010 at 7:22 am

#235040

Hi there,
After some help from people in here, I got a specific table created yesterday that I needed. Now, however, I have a problem since I'm unable to index the date column of this new table due to some duplicate key error. I am unable to find any duplicate data, so I'm wondering what may be wrong.
The code for creating the table is the following:
---------------------------------------------
Select a.*, b.obstime as PrevObsTime, log(a.SPCLOSE)-log(b.SPCLOSE) as SPreturn
into ReturnSP
from SP a inner join SP b
on a.SPDATE = b.SPDATE
where b.obstime=(select max(obstime) from SP where obstime<a.obstime and SPDATE = a.SPDATE)
order by SPDATE asc
---------------------------------------------
The "SPCLOSE" is a price-observation and "SPDATE" is a date-observation, while "obstime" is the time of the day.
While the base-table, which I draw the above data from, has a PK on both the date and time-of-day columns, I'm unable to add this to the new table. I get the following error message when I try:
'ReturnSP' table
- Unable to create index 'PK_ReturnSP'.
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.ReturnSP' and the index name 'PK_ReturnSP'. The duplicate key value is (Jan 3 1983 12:00AM).
Could not create constraint. See previous errors.
The statement has been terminated.
I'm not sure if anyone knows what to do here, but if you have encountered this before and have a suggestion, I'd be grateful! 🙂
Thanks in advance!
Best regards,
Martin Falch

Viewing 5 posts - 1 through 4 (of 4 total)

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

Mike01 SSChampion Points: 11395 More actions · Answer 1

The reason for the duplicates has to do with the join. It is returning every date and then just finding the previous time. Try this

declare @sp-2 table (SPDATE smalldatetime, obstime varchar(8), SPCLOSE float)

insert into @sp-2

select '2010-05-13','01:00:00', 2 union all

select '2010-05-13','02:00:00', 2 union all

select '2010-05-13','03:00:00', 2 union all

select '2010-05-13','04:00:00', 2 union all

select '2010-05-13','05:00:00', 2 union all

select '2010-05-14','01:00:00', 3 union all

select '2010-05-14','02:00:00', 3

Select a.SPDate, b.obstime as PrevObsTime, log(a.SPClose) - log(b.SPClose) SPClose

from @sp-2 a inner join (select max(obstime) obstime, SPClose, SPDATE from @sp-2 group by SPClose, SPDate) b

on a.SPDATE = b.SPDATE

and a.obstime = b.obstime

order by a.SPDATE asc

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

martinfalch SSC Veteran Points: 253 More actions · Answer 2

Well the above seems to return two rows:

SPDATE PrevObsTime SPClose

2010-05-13 00:00:0005:00:00 0

2010-05-14 00:00:0002:00:00 0

I'm not sure if that's what you meant?

Thanks for wanting to help 🙂

Mike01 SSChampion Points: 11395 More actions · Answer 3

You said that you were trying to create a unique index on date. In order to do that, you can only have one date. What were you looking for for results?

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

martinfalch SSC Veteran Points: 253 More actions · Answer 4

Well the reason I need the key is because I think it is the key for solving the issue with the next piece of code I'm trying to run. Based on the SPReturn table, I need to do some computations as in the code below. However, when I try to run these, I just get the names of the columns, but no content. I'm guessing both the primary key and this latter problem is caused by the duplicate issue, so I'm trying to figure out how to solve that.

The code I intend to use when it works:

select

min(SPDate) as 'Week_Start',

max(SPDate) as 'Week_End',

datediff(week,'19830103',SPDate) as 'Week_no',

count( * ) as 'Obs pr week',

sum(SPReturn*SPReturn) as 'RVSP',

sum(SPReturn) as 'SPReturn'

from ReturnSP

where datediff(hour, PrevObsTime, ObsTime) < 8 and DatePart(hour,obstime) > 8 and datediff(minute, SPDate + '09am', ObsTime) >= 10

group by datediff(week,'19830103',SPDate)

order by Week_no asc

Again, thanks for the help!

- Martin