Indexing Dates

Question

Post reply

Indexing Dates

SQL 2016

SSC-Addicted

Points: 427
More actions
June 16, 2015 at 12:35 pm

#398604

Hello SQL ServerCentral,
I have a quick question. I am trying to index dates to numbers with a large data set.
The first colums is index, the next is FactorsS, the next is value and the next is Date and the last is Lag.
Would it be difficult to write code that would determine the lag values. The lag value is based on the date value.
Index FactorS Value Date Lag
1 XYZ 2.3 12/31/2014 1
2 XYZ 1.4 12/30/2014 2
3 XYZ 3.3 12/29/2014 3
4 ABC 1.8 12/31/2014 1
5 ABC 2.2 12/30/2014 2
6 CBA 1.7 12/31/2014 1
7 CBA 1.8 12/30/2014 2
8 CBA 1.9 12/29/2014 3
9 CBA 2.1 12/28/2014 4

Viewing 15 posts - 1 through 15 (of 24 total)

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

Robert Klimes SSCoach Points: 17865 More actions · Answer 1

you are not very clear in what your requirements are? you want to determine the lag values in reference to what?

read the post at the link in my signature on how to post questions to get better answers.

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

SQL 2016 SSC-Addicted Points: 427 More actions · Answer 2

The "Lag" value would be based in the "date" column. "1" would be 1 day back in time, 2 would be 2nd day back in time.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

pederson1234 (6/16/2015)
Hello SQL ServerCentral,
I have a quick question. I am trying to index dates to numbers with a large data set.
The first colums is index, the next is FactorsS, the next is value and the next is Date and the last is Lag.
Would it be difficult to write code that would determine the lag values. The lag value is based on the date value.
Index FactorS Value Date Lag
1 XYZ 2.3 12/31/2014 1
2 XYZ 1.4 12/30/2014 2
3 XYZ 3.3 12/29/2014 3
4 ABC 1.8 12/31/2014 1
5 ABC 2.2 12/30/2014 2
6 CBA 1.7 12/31/2014 1
7 CBA 1.8 12/30/2014 2
8 CBA 1.9 12/29/2014 3
9 CBA 2.1 12/28/2014 4

No problem at all. Use ROW_NUMBER() OVER (PARTITION BY FactorS ORDER BY Date DESC) to create the "Lag" column during a query.

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

p.s. You're brand new here. Welcome aboard! 🙂

The people on these forums generally like to test their answers before they post them. Please help those that want to help you by making it easy on them. Please see both articles at the Blue links in my signature line below.

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

SQL 2016 SSC-Addicted Points: 427 More actions · Answer 5

great thanks...Also what is the maximum number of columns that can be used in a large data set? "Large" meaning 5 columns by 20 million rows?

Y.B. SSChampion Points: 11642 More actions · Answer 6

Robert klimes (6/16/2015)
you are not very clear in what your requirements are? you want to determine the lag values in reference to what?
read the post at the link in my signature on how to post questions to get better answers.

Will you ever have any holes in your dates...meaning is it possible you will get dates more than 1 day apart?

If so than you could (ironically) make use of the LAG function. Otherwise Jeff's solution will work just fine.

SELECT quote FROM brain WHERE original = 1
0 rows returned

SQL 2016 SSC-Addicted Points: 427 More actions · Answer 7

Thanks for the blue links..will review and adjust

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

pederson1234 (6/16/2015)
great thanks...Also what is the maximum number of columns that can be used in a large data set? "Large" meaning 5 columns by 20 million rows?

Errr????

The limit on columns in a SELECT statement is 4096.

Not really sure what you're asking.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL 2016 SSC-Addicted Points: 427 More actions · Answer 9

yes, weekend dates will be excluded so there will be gaps in the date ranges

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

In that case, can you please post some sample data with the gaps that you expect and show what the results you want are?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

yb751 (6/16/2015)
If so than you could (ironically) make use of the LAG function. Otherwise Jeff's solution will work just fine.

Unless I'm missing something, LAG won't produce the results he wants. LAG just gets the value from a fixed number of rows prior to the current one.

FIRST_VALUE along with a DATEDIFF might, maybe.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL 2016 SSC-Addicted Points: 427 More actions · Answer 12

Hi Gail,

My question was referring to converting row data to column data. I am trying to get a sense of how "wide" SQL can go without it crashing. Your answer was very helpful.

Thank you

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

It won't crash. If you exceed the limit, you'll get an error.

That said, you probably want to do that kind of crosstab in the application tier, the built in PIVOT isn't that great.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Y.B. SSChampion Points: 11642 More actions · Answer 14

GilaMonster (6/16/2015)
yb751 (6/16/2015)
If so than you could (ironically) make use of the LAG function. Otherwise Jeff's solution will work just fine.
Unless I'm missing something, LAG won't produce the results he wants. LAG just gets the value from a fixed number of rows prior to the current one.
FIRST_VALUE along with a DATEDIFF might, maybe.

I know I was just rethinking that myself.

SELECT quote FROM brain WHERE original = 1
0 rows returned