SQL help - why doesn't my code work! Waaaah!

(later . . . . . )

No, exactly the same. Besides, it would have trouble with the line AFTER if it was the SQL that was iffy, surely?

I dunno.

I have done some VB, but all I can say is to delete the 'command' part and see what the suggestion box gives you etc..

Uh, Sam . . . . and what does that involve? Sorry, you're gonna have to spell it out to me - I'm not as hot as I'd like on this kinda thing.

Yeah, mine too.

but if i change "select *" to "delete" SqlServer complains incorrect syntax near t1. can anyone help? i'm sure there's a better, more efficient way to eliminate dupe rows, i'm not a sql programmer and i'd appreciate any help.

Create a SELECT that gets all the data with the duplicates removed. Then create a new table with the same structure as this one and use INSERT INTO newtable SELECT ... to populate it. Then rename (and eventually delete) the original table and move the new table into its place.

(note I don't know anything about SQL Server specifically)

I'm a bit rusty, but if the tables aren't huge, (this may not be the most efficient way but) this should work:

delete from CorrelationsFinalTest
where CorrelationsFinalTest.(keyfield) in
select ( keyfield
FROM CorrelationsFinalTest t1
WHERE EXISTS
(SELECT SymbolA
FROM CorrelationsFinalTest t2
WHERE t2.SymbolA = t1.SymbolA AND t2.SymbolB = t1.SymbolB))

I would normally suggest creating a temporary table to hold the delete criteria (i.e. the nested select where exists stuff) and then delete where table 1 mmatches the temp table. Seems safer to me, but as I said, I'm rusty.

(xpost ..)

xpost the table has 3.5 million lines :\

SELECT t1.keyfield FROM CorrelationsFinalTest t1, CorrelationsFinalTest t2 WHERE t1.SymbolA = t2.SymbolA AND t1.SymbolB = t2.SymbolB AND t1.keyfield != t2.keyfield

dave225, your suggestion gives me "Error in list of values in IN clause." i wonder if this is some SqlServer bullshit. i found a page that gives three solutions for eliminating dupes using oracle's variety of sql but they generate syntax errors when i try 'em in SqlServer.

But, yeah, it might be better to just create a view containing the IDs of the rows you need to delete, and then reference that view in the DELETE.

Anyone know how to convert a binary value that's contained in a varchar variable to a binary variable? I've written a procedure that takes info from sysprocesses, makes it more user friendly and can be viewed from the intranet, and I want to convert the job_id to job_name (using msdb..sysjobs).

The string looks like this:

'SQLAgent - TSQL JobStep (Job 0x57926A9F88B2D7468BB07BDF51E51C63 : Step 18)'

So I used substring to get it down to '0x57926A9F88B2D7468BB07BDF51E51C63' but if I convert that to binary it comes out as 0x30783537393236413946383842324437! Any idea how to convert a string directly to binary as it is?

I have a work-around involving dynamic SQL and a cursor but it would be better if I could just use a CAST/CONVERT statement and do it in one go.

Okay, so I've got a list of site references and dates.

site_no date
123456 2000-01-04
123456 2000-01-08
123456 2000-01-09
123456 2000-01-10
456789 2001-05-18

I want to turn these into a series of start and end date ranges, like this.

Site_No startdate end date
123456 2000-01-04 2000-01-05
123456 2000-01-08 2000-01-11
456789 2001-05-18 2001-05-19

Anyone got any ideas how I could do this in SQL? I'd rather do it without looping if at all possible.

What do the dates mean/relate to?

They're just arbitrary dates, these don't actually relate to anything! In real life I want to use this principle to work out where there are gaps in billing - so, in this example, site 123456 needs to be billed from the 04/01 to the 05/01 and 08/01 to 11/01.

You have dates 04, 08, 09, 10th for 12345

How would that specific example relate to ranges?

For site 123456, I want two records, one saying 04/01 - 05/01, and one saying 08/01 - 11/01. Start and end dates, if you like.

Oh I see, one for Date 1 to the first day break and then one to the next.

Blimey, I'll have to think.

I don't think this would be very easy to do.

That is my useless answer so far.

Siteno Date

123456 2000-01-04
123456 2000-01-08
123456 2000-01-09
123456 2000-01-10
456789 2001-05-18

Select SiteNo, DateFrom, DateTo
from
(Select SiteNo, Date as DateFrom
from table
left outer join table as table2
on table.siteno = table2.siteno
and dateadd(1, table.date) = table2.date
where table2.date is null)

inner join

(Select SiteNo, Date as DateFrom from table left outer join table as table2 where table.siteno = table2.siteno and dateadd(1, tabale.date) = table2.date where table2.date is null)

etc, I dunno. Something like that.

The first record is fairly easy, I'm getting records that don't have the corresponding next day record.

The second one is harder, as you want 08 rather than 10.

I guess a pre-pass type view, then a "min" function from linking that view to the original table.

Hope that helps in your path.

I think I see what you're doing. I get the idea . . kinda. I'll have a play with that idea tho. Cheers mate!

(tho if you get any brainwaves, do tell!)

SELECT SireRef, DATEADD(dd, 1, DateFrom) AS DateTo
FROM (SELECT table1.SireRef, table1.Date AS DateFrom
FROM dbo.tmp_Date AS table1 LEFT OUTER JOIN
dbo.tmp_Date AS table2 ON table1.SireRef = table2.SireRef AND DATEADD(dd, 1, table1.Date) = table2.Date
WHERE (table2.Date IS NULL)) AS derivedtbl_1

That one gives you, one per break, the site code and the break date.

So, relink your orig table to that, select where origtabledate < breakdate, then :

Select SiteRef, min(origtable.date) as DateFrom, DateTo from
(blahdiblah)
Group by SiteRef, DateTo

Bob's yer.

(change that "select where orig...." to "inner join" etc, you know.

RIGHT! HERE IS THE ANSWER!!!

SELECT SireRef, MIN(DateFrom) AS DateFrom, DateTo
FROM (SELECT dbo.tmp_Date.SireRef, dbo.tmp_Date.Date AS DateFrom, MIN(eee.DateTo) AS DateTo
FROM dbo.tmp_Date INNER JOIN
(SELECT SireRef, MIN(DATEADD(dd, 1, DateFrom)) AS DateTo
FROM (SELECT table1.SireRef, table1.Date AS DateFrom
FROM dbo.tmp_Date AS table1 LEFT OUTER JOIN
dbo.tmp_Date AS table2 ON table1.SireRef = table2.SireRef AND DATEADD(dd, 1, table1.Date) = table2.Date
WHERE (table2.Date IS NULL)) AS derivedtbl_1
GROUP BY SireRef, DateFrom) AS eee ON eee.SireRef = dbo.tmp_Date.SireRef AND dbo.tmp_Date.Date < eee.DateTo
GROUP BY dbo.tmp_Date.Date, dbo.tmp_Date.SireRef) AS yeah
GROUP BY SireRef, DateTo

I thangyew!

Awesome! Cheers sir, much appreciated.

http://www.linksoflondon.com/dynamic/eshop/product_images/thumbnail_cache/360x360/607-thumbprint-star-gold-charm-image-1.jpg