Welcome to the Lounge

17-Feb-16 22:26

...but I've just discovered the SQL LAG function.
Wow, but that's a fwappy feature! Thumbs Up | :thumbsup:

Makes it so simple to get a running delta for users:

SQL

SELECT UserID
      ,EnterDate
      ,CurrentValue
      ,CurrentValue - LAG(CurrentValue, 1 , CurrentValue) OVER (PARTITION BY UserId ORDER BY EnterDate ASC) AS Delta
FROM MyTable

So, no more nasty multiple SELECT and JOINS, no more C# loops once the data is loaded - just a simple addition to the SQL SELECT.
Why didn't I find this a couple of years ago? D'Oh! | :doh:

Now the big decision: do I rewrite the code to use it? Don't have any mods planned for that software... Unsure | :~

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

Kornfeld Eliyahu Peter17-Feb-16 22:33

Kornfeld Eliyahu Peter

17-Feb-16 22:33

If you want to be ahead of time, check LEAD[^]...

IMHO, rewriting is only necessary if performance is extremely important...Also notice that LAG (and LEAD) can gain you performance only if you use hard coded offset values!

Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.

Jörgen Andersson17-Feb-16 23:16

17-Feb-16 23:16

Kornfeld Eliyahu Peter wrote:
If you want to be ahead of time, check LEAD[^]...

Or make the Offset negative.

Wrong is evil and must be defeated. - Jeff Ello

Kornfeld Eliyahu Peter17-Feb-16 23:20

Kornfeld Eliyahu Peter

17-Feb-16 23:20

MSDN:
offset cannot be a negative

Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.

Jörgen Andersson17-Feb-16 23:26

17-Feb-16 23:26

Oh, when I tested it on Oracle I could. Oh well...

Wrong is evil and must be defeated. - Jeff Ello

dan!sh 17-Feb-16 22:38

dan!sh

17-Feb-16 22:38

OriginalGriff wrote:
do I rewrite the code to use it?

If it ain't broke, don't fix it.

Unless you are a consultant. In which case, you fix it which breaks it then fix it again which does not work so revert back to old stuff. Then, go out for a vacation using all the big money you made from this.

"You'd have to be a floating database guru clad in a white toga and ghandi level of sereneness to fix this goddamn clusterfuck.", BruceN[^]

Johnny J.17-Feb-16 22:52

Johnny J.

17-Feb-16 22:52

d@nish wrote:
Unless you are a consultant. In which case, you fix it which breaks it then fix it again which does not work so revert back to old stuff. Then, go out for a vacation using all the big money you made from this.

I don't Think Griff's boss (Mrs. Griff) will go for that approach... D'Oh! | :doh:

Anything that is unrelated to elephants is irrelephant
Anonymous
-
The problem with quotes on the internet is that you can never tell if they're genuine
Winston Churchill, 1944
-
I'd just like a chance to prove that money can't make me happy.
Me, all the time

Sander Rossel17-Feb-16 22:58

Sander Rossel

17-Feb-16 22:58

You're LAGging behind, why don't you let me take the LEAD? Querying SQL Server 2012: Part I[^] Big Grin | :-D

Visit my blog at Sander's bits - Writing the code you need.
Or read my articles at my CodeProject profile.

Simplicity is prerequisite for reliability.
— Edsger W. Dijkstra

Regards,
Sander

OriginalGriff17-Feb-16 23:13

17-Feb-16 23:13

In my - slight - defence, I only upgraded to SQL Server 2012 about 6 months ago when I found my hosting service wasn't using 2008R2 any more... Blush | :O

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

Jörgen Andersson17-Feb-16 23:19

17-Feb-16 23:19

A small note, Lead and Lag really likes Composite indices. In your example on (UserId,EnterDate)

Wrong is evil and must be defeated. - Jeff Ello

Jörgen Andersson17-Feb-16 23:25

17-Feb-16 23:25

Note 2.

Remember the order of execution, analytic functions are the third last to happen in a query.
Joins, filtering, aggregation all happens before the analytic functions. Only Unions and Order By happens after.

Wrong is evil and must be defeated. - Jeff Ello

ZurdoDev18-Feb-16 1:44

ZurdoDev

18-Feb-16 1:44

OriginalGriff wrote:
,EnterDate
,CurrentValue
,CurrentValue

Nightmares of commas as the first character of a line are returning. How dare you!! Dead | X|

I wish SQL would not allow that to even compile. Should be called a syntax error. $Shucks | :-\$

There are only 10 types of people in the world, those who understand binary and those who don't.

OriginalGriff18-Feb-16 1:48

18-Feb-16 1:48

I know what you mean, but ... it's what SSMS does when you ask for a "Top 1000 rows" query, which I used to test the LAG function. And I can see why it does it; it does make a lot of sense in terms of avoiding the "missing" or "leftover" comma. But it does look bad.

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

kmoorevs18-Feb-16 2:42

kmoorevs

18-Feb-16 2:42

Well, at least you removed those horrible brackets! Smile | :)

"Go forth into the source" - Neal Morse

OriginalGriff18-Feb-16 2:49

18-Feb-16 2:49

They're easier to get rid of! Laugh | :laugh:

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...