Filtering on multiple input pairs

Question

Filtering on multiple input pairs

Viewing 10 posts - 1 through 11 (of 11 total)

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

pietlinden SSC Guru Points: 63621 More actions · Answer 1

Eirkur would no doubt be able to do this in a nanosecond, but in a nutshell, you would have to use XQuery to do this. Here's the article that gets into XQuery syntax, but here's one of the articles in the series on here:

http://www.sqlservercentral.com/articles/Stairway+Series/+Querying+XML+Data/92784/

Eirikur Eiriksson SSC Guru Points: 182951 More actions · Answer 2

Quick suggestion on parsing the XML

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @INPUT XML = '<input>

<pair>

<acct_no>23423</acct_no>

<tran_dt>9-11-2015</tran_dt>

</pair>

<pair>

<acct_no>13223423</acct_no>

<tran_dt>7-12-2015</tran_dt>

</pair>

</input>';

SELECT

PAIR.DATA.value('(acct_no/text())[1]','INT') AS acct_no

,PAIR.DATA.value('(tran_dt/text())[1]','DATE') AS tran_dt

FROM @INPUT.nodes('input/pair') AS PAIR(DATA);

Results

acct_no tran_dt

----------- ----------

23423 2015-09-11

13223423 2015-07-12

pietlinden SSC Guru Points: 63621 More actions · Answer 3

(See, told ya Eirikur could do it... even if I can't remember how to spell his name!)

Eirikur Eiriksson SSC Guru Points: 182951 More actions · Answer 4

pietlinden (8/12/2015)
(See, told ya Eirikur could do it... even if I can't remember how to spell his name!)

He he, if I had a penny for every mis-spelling of my name I would be ....:-D

😎

pietlinden SSC Guru Points: 63621 More actions · Answer 5

pietlinden

SSC Guru

Points: 63621

August 17, 2015 at 12:57 pm

#1820554

Yeah, we silly Anglos can't read that well...

Pagan DBA Right there with Babe Points: 794 More actions · Answer 6

Many thanks guys! Now I know my decision for using XML string to provide input was not haphazard.

So back to my main question then.

If I have the inputs in a rowset extracted as (A, D) for (Account, Date) with values (A1, D1), (A2, D2), etc. etc. Can I then do a JOIN as follows :

JOIN ON MainTable.A = My.A and MainTable.D > My.D

then,

ORDER BY A, D.

Will above give me all rows for each A such that each row for a given A has a date later than corresponding D?

I'm going to try it, but didn't want to be wasting my time if it was never going to work.

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 7

Pagan DBA (8/12/2015)
It's been going well processing the transaction delta[font="Arial Black"] one account number at a time
[/font]

That would be the real problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pietlinden SSC Guru Points: 63621 More actions · Answer 8

I would probably start by reading this article[/url] by Pinal Dave on Simulating LAG in SQL Server 2008.

If possible I would persist the shredded XML to a table and then you could index it and this query would be pretty easy... well, given the code at SQL Authority (link above).

Pagan DBA Right there with Babe Points: 794 More actions · Answer 9

Thanks guys, but like I said in my original post I already know processing account transaction delta one at a time is slow. Hence this post.

And, I can look into making things efficient like storing in table and indexing (while I am not seeing how this is appropriate given my input changes on demand for only that set of transactions I need to get deltas for say 5 at a time)

I will look at link to article from Pinal Dave. However appreciate if someone could answer my last question. Basically, will JOIN as I have stated work? FWIW, I'm using SQL Server 2008.

Thanks in advance.

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 10

Pagan DBA (8/18/2015)
Thanks guys, but like I said in my original post I already know processing account transaction delta one at a time is slow. Hence this post.
And, I can look into making things efficient like storing in table and indexing (while I am not seeing how this is appropriate given my input changes on demand for only that set of transactions I need to get deltas for say 5 at a time)
I will look at link to article from Pinal Dave. However appreciate if someone could answer my last question. Basically, will JOIN as I have stated work? FWIW, I'm using SQL Server 2008.
Thanks in advance.

At this point and if it were me, I'd just try it. One test is worth a thousand expert opinions. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)