Welcome to the Lounge

RUs12318-Feb-16 0:51

RUs123

18-Feb-16 0:51

Ignore him Naughtius, he thinks he is the messiah but he is a very naughty boy. Might be your son. Laugh | :laugh:

OriginalGriff18-Feb-16 0:53

18-Feb-16 0:53

Yeah, they said I hadn't done anything and I could go and live on an island somewhere.

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

Johnny J.18-Feb-16 1:06

Johnny J.

18-Feb-16 1:06

Oh I say, that's very nice. Well, off you go then...

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

RUs12318-Feb-16 0:31

RUs123

18-Feb-16 0:31

The Terminator: Relapse.

Turned to the dark side?

Kornfeld Eliyahu Peter17-Feb-16 23:20

All this effort, all this skill, for this? | CommitStrip[^]

17-Feb-16 23:20

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

Re: Turned to the dark side?

HobbyProggy17-Feb-16 23:40

HobbyProggy

17-Feb-16 23:40

Living life on the Dark Side!

Rules for the FOSW ![^]

if(this.signature != "")
{
    MessageBox.Show("This is my signature: " + Environment.NewLine + signature);
}
else
{
    MessageBox.Show("404-Signature not found");
}

Re: Turned to the dark side?

Chris Quinn18-Feb-16 2:30

Chris Quinn

18-Feb-16 2:30

My life is more like The Far Side than the dark side.

=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================

I know I'm behind the times sometimes...

OriginalGriff17-Feb-16 22:26

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

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

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:

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...

CCC WSO 18, February 2016 - Solved

Richard Deeming18-Feb-16 3:22

Richard Deeming

18-Feb-16 3:22

OriginalGriff wrote:
it does make a lot of sense in terms of avoiding the "missing" or "leftover" comma.

Until you remove the first column from the list.

Or add a column before it.

Basically, it just moves the problem from the end of the column list to the start.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Kornfeld Eliyahu Peter17-Feb-16 22:01