Date alignment and pair matching extraction best done with TSQL or C#?

Conversation started Feb 6, 2011 at 23:52.

drachenstern

Feb 6, 2011 23:52

Q: Can this be done in SQL easily or does it need to go out to an application?

For starting with: my title sucks, so help me figure out a new one? I can't post all the SQL here (over 30k characters for the lot of it), so I stuck it on pastebin.com The problem: I get an XML file that I scrape some records from, and I need to extract some data from the records and build an...

new question posted that I could really use a hand on

also, needs a much better title

2 hours later…

drachenstern

Feb 7, 2011 01:25

in The Tavern (General) on Meta Stack Overflow Chat, 1 min ago, by mootinator

@drachenstern Hey, how would you resolve all the events at exactly the same time consistently?

in The Tavern (General) on Meta Stack Overflow Chat, 1 min ago, by drachenstern

sorted by discovered date

in The Tavern (General) on Meta Stack Overflow Chat, 1 min ago, by drachenstern

otherwise I'm led to believe it's in the order I presented in the spreadsheet

23 hours later…

Brian Ballsun-Stanton

Feb 7, 2011 23:56

huh, funky

exactly at the same time...

well, the question is: do they depend on each other?

(in the ACID sense)

3 hours later…

drachenstern

Feb 8, 2011 02:52

@BrianBallsunStanton do what depend on each other how?

Q: Schema to support dynamic object properties

Hi people. I'm working on an editor that enables its users to create "object" definitions in real-time. A definition can contain zero or more properties. A property has a name, a type and possibly a default value that corresponds to the type. Once a definition is created, a user can create an in...

schema

VtC: Off Topic Doesn't Belong

Brian Ballsun-Stanton

Feb 8, 2011 03:19

@drachenstern These

drachenstern

@BrianBallsunStanton ah, so I'm working with a technical team from the initial vendor to resolve that. It's reported from the hardware, so it's hard to know. I know I can sort on the DiscoveredAt to find most recent time, but otherwise I assume (and you know what they say about assume) that the time equivalent means they happened in the same minute (or thereabouts)

ever have the lights in your house go off and come right back on? Or dim really low but not quite go off?

that sort of event

Brian Ballsun-Stanton

Feb 8, 2011 03:40

wait, are you working at triggering them at the same time?

I don't understand the real-time requirements for resolve.

I'd say that you were working at the wrong granularity if this is a real problem.

drachenstern

It's a real problem, but not realtime

I can take as long as I need to process the items in the list

Brian Ballsun-Stanton

Hrmm

drachenstern

Which part of the question is becoming less clear, I tried to use the spreadsheet and the question to demonstrate the problem

I only added spacing to the spreadsheet to make it easier to follow

Brian Ballsun-Stanton

oooooh

ooooh

that's fun

no SQL is not the right way to do it, because order of rows matter

It's absolutely trivial to do in a procedural language

drachenstern

I know, that's what I'm thinking

Brian Ballsun-Stanton

Feb 8, 2011 03:44

I mean... roughly speaking in SQL, it would be to ETL the off events and on events to 2 separate tables, then start outer joining them

drachenstern

but I didn't know if there was a trick to get SQL to coerce the values quickly, cos they are sets

Brian Ballsun-Stanton

Becuase it's "really hard" to say "last 3 rows"

well, really hard or really easy

drachenstern

lol, yeah, I know

Brian Ballsun-Stanton

in oracle, I'd make a stored procedure.

drachenstern

at least with a procedural I can compare and backtrack

oh, it'll be a stored procedure no matter what, and all the TSQL goodies anyone wants to use

Brian Ballsun-Stanton

Feb 8, 2011 03:46

yeah, I don't know SQL-Server

But unless there's a compelling reason why it has to be on the DBMS, make it in the app

since you're doing stored procedures and that kind of nonsense anyways

iteration 1: clean up dupes to fit business rules.

Iteration 2: prune to table.

trivial to do in procedural

drachenstern

yeah, that's my thought process too

it's the way I started this morning, right up until my machine BSoD'd for the fourth time in 7 days

so I replaced it with another box and spent half a day or more getting it setup

Brian Ballsun-Stanton

eeew

drachenstern

yeah, tell me about it

So anyways, I know the logic, I know the access routines, I know the tables, I think I'll be ok

Brian Ballsun-Stanton

Feb 8, 2011 04:01

yep

Conversation ended Feb 8, 2011 at 4:01.

all rooms

Feb '116

Feb '118

General on- and off-site discussion for dba.stackexchange.com....

context of the conversation
join this room
about this room

Participants

drachenstern 50%
Brian Ballsun-Stanton 50%

all times are UTC

Date alignment and pair matching extraction best done with TSQL or C#?

The Heap™ – Consultancy ©®

Participants