Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>In answer to Paul's question: </p> <blockquote> <p>What happens when you have three entries, a, b, c. a = 00 secs b = 19 secs c = 39 secs >Are these all considered to be the same time? ( a is within 20 secs of b, b is within 20 >secs of c ) </p> </blockquote> <p>If the other comparisons are equal (AssociatedEntityid and AssociatedEntityType) then yes, they are considered the same thing, otherwise no.</p> <hr> <p>I would add to the original question, except that I used a different account to post the question and now can't remember my password. It was a very old account and didn't realize that I had connected to the site with it. </p> <p>I have been working with some the answers you guys have given me and there is one problem, you're using only one key column (AssociatedEntityid) when there are two (AssociatedEntityID and AssociatedEntityType). Your suggestions would work great for a single key column.</p> <p>What I have done so far is:</p> <p>Step 1: Determine which AssociatedEntityID and AssociatedEntityType pairs have duplicates and insert them into a temp table:</p> <pre><code>create table ##stage1 (ID int, AssociatedEntityID int, AssociatedEntityType int, [Timestamp] datetime) insert into ##stage1 (AssociatedEntityID, AssociatedEntityType) (select AssociatedEntityID, AssociatedEntityType from tblHBMLog group by AssociatedEntityID, AssociatedEntityType having COUNT(*) &gt; 1) </code></pre> <p>Step 2: Retrieve the ID of the earliest occurring row with a given AssociatedEntityID and AssociatedEntityType pair:</p> <pre><code>declare curStage1 cursor for select AssociatedEntityID, AssociatedEntityType from ##stage1 open curStage1 fetch next from curStage1 into @AssocEntity, @AssocType while @@FETCH_STATUS = 0 begin select top 1 @ID = ID, @Timestamp = [Timestamp] from tblHBMLog where AssociatedEntityID = @AssocEntity and AssociatedEntityType = @AssocType order by [Timestamp] asc update ##stage1 set ID = @ID, [Timestamp] = @Timestamp where AssociatedEntityID = @AssocEntity and AssociatedEntityType = @AssocType end </code></pre> <p>And this is where things slow down again. Now, granted, the result set has been pared down from ~17 million to just under 400,000, but it is still taking quite a long time to run through. </p> <p>I guess another question that I should ask is this; If I continue to write this in SQL will it just have to take quite a long time? Should I write this in C# instead? Or am I just stupid and not seeing the forest for the trees of this solution?</p> <hr> <p>Well, after much stomping of feet and gnashing of teeth, I have come up with a solution. It's just a simple, quick and dirty C# command line app, but it's faster than the sql script and it does the job.</p> <p>I thank you all for your help, in the end the sql script was just taking too much time to execute and C# is much better suited for looping.</p>
    singulars
    1. This table or related slice is empty.
    1. POde-duplicating rows in a sql server 2005 table
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USB0fh
    1. USB0fh
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COyou should **update** your original question - not answer it yourself with some additional info...
      singulars
      1. PO
      1. USmarc_s
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload