Indexing Dates

Question

Post reply

Indexing Dates

Viewing 10 posts - 16 through 24 (of 24 total)

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

Y.B. SSChampion Points: 11572 More actions · Answer 1

This should work:

NOTICE - I purposely created a gap for CBA to demonstrate.

DECLARE @test-2 TABLE ([Index] INT, FactorS NVARCHAR(3), Value DECIMAL(2,1), [Date] DATE)



INSERT INTO @test-2 ([Index], FactorS, Value, [Date])

VALUES (1, 'XYZ', 2.3, '12/31/2014'),

(2, 'XYZ', 1.4, '12/30/2014'),

(3, 'XYZ', 3.3, '12/29/2014'),

(4, 'ABC', 1.8, '12/31/2014'),

(5, 'ABC', 2.2, '12/30/2014'),

(6, 'CBA', 1.7, '12/31/2014'),

(7, 'CBA', 1.8, '12/30/2014'),

(8, 'CBA', 1.9, '12/29/2014'),

(9, 'CBA', 2.1, '12/27/2014');

WITH working (FactorS, MaxDate)

AS

(

SELECT FactorS, MAX([Date]) FROM @test-2 GROUP BY FactorS

)

SELECT

t.[Index],

t.FactorS,

t.Value,

t.[Date],

DATEDIFF(dd,t.Date, w.MaxDate) + 1 AS Lag

FROM

@test-2 t

JOIN working w ON w.FactorS = t.FactorS

ORDER BY

t.[Index]

SELECT quote FROM brain WHERE original = 1
0 rows returned

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

SQL 2016

SSC-Addicted

Points: 427

June 16, 2015 at 1:48 pm

#1805876

awesome..thank you for the great help!!

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

Hey all, thanks for the feedback, i'm not quite there yet... Trying to simplify how I'm stating my need:

This is what my table looks like:



Customers

Name, Date

‘John’, ‘2015-01-01’

‘John’, ‘2015-01-02’

‘John’, ‘2015-01-05’

‘John’, ‘2015-01-06’

‘John’, ‘2015-01-07’

‘Mike’, ‘2015-01-01’

‘Mike’, ‘2015-01-02’

‘Mike’, ‘2015-01-03’

You’ll notice that there is no data for January 3 or 4 since they were weekends.

What I would like is to add a column that counts “non-holiday weekdays” for each person, so the data will look like this:



Customers

Name, Date, Weekdays

‘John’, ‘2015-01-01’, 1

‘John’, ‘2015-01-02’, 2

‘John’, ‘2015-01-05’, 3

‘John’, ‘2015-01-06’, 4

‘John’, ‘2015-01-07’, 5

‘Mike’, ‘2015-01-01’, 1

‘Mike’, ‘2015-01-02’, 2

‘Mike’, ‘2015-01-05’, 3

I can get partial results with the following query:

SELECT *, ROW_NUMBER() OVER (ORDER BY Date) as Weekdays INTO Customers_1 FROM Customers WHERE Name='John'

However, this must be done one-by-one for each customer, and creates a duplicate of the table in order to accomplish it.

What I need is something roughly like:

UPDATE Customers SET Weekdays=__row-number-on-a-per-customer-basis-when-date-sorted__

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

Is January 1st a holiday for you?

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

p.s. The ROW_NUMBER method I mentioned previously will still work for this. 😉

--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)
Intro to Tally Tables and Functions

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 6

pederson1234 (6/16/2015)

Hey all, thanks for the feedback, i'm not quite there yet... Trying to simplify how I'm stating my need:

This is what my table looks like:

Customers Name, Date ‘John’, ‘2015-01-01’ ‘John’, ‘2015-01-02’ ‘John’, ‘2015-01-05’ ‘John’, ‘2015-01-06’ ‘John’, ‘2015-01-07’ ‘Mike’, ‘2015-01-01’ ‘Mike’, ‘2015-01-02’ ‘Mike’, ‘2015-01-03’

You’ll notice that there is no data for January 3 or 4 since they were weekends.

What I would like is to add a column that counts “non-holiday weekdays” for each person, so the data will look like this:

Customers Name, Date, Weekdays ‘John’, ‘2015-01-01’, 1 ‘John’, ‘2015-01-02’, 2 ‘John’, ‘2015-01-05’, 3 ‘John’, ‘2015-01-06’, 4 ‘John’, ‘2015-01-07’, 5 ‘Mike’, ‘2015-01-01’, 1 ‘Mike’, ‘2015-01-02’, 2 ‘Mike’, ‘2015-01-05’, 3

I can get partial results with the following query:

SELECT *, ROW_NUMBER() OVER (ORDER BY Date) as Weekdays INTO Customers_1 FROM Customers WHERE Name='John'

However, this must be done one-by-one for each customer, and creates a duplicate of the table in order to accomplish it.

What I need is something roughly like:

UPDATE Customers SET Weekdays=__row-number-on-a-per-customer-basis-when-date-sorted__

A calendar or "dimDate" table will make this a much simpler task.

Don Simpson

I'm not sure about Heisenberg.

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

DonlSimpson (6/16/2015)

pederson1234 (6/16/2015)

Hey all, thanks for the feedback, i'm not quite there yet... Trying to simplify how I'm stating my need:

This is what my table looks like:

Customers Name, Date ‘John’, ‘2015-01-01’ ‘John’, ‘2015-01-02’ ‘John’, ‘2015-01-05’ ‘John’, ‘2015-01-06’ ‘John’, ‘2015-01-07’ ‘Mike’, ‘2015-01-01’ ‘Mike’, ‘2015-01-02’ ‘Mike’, ‘2015-01-03’

You’ll notice that there is no data for January 3 or 4 since they were weekends.

What I would like is to add a column that counts “non-holiday weekdays” for each person, so the data will look like this:

Customers Name, Date, Weekdays ‘John’, ‘2015-01-01’, 1 ‘John’, ‘2015-01-02’, 2 ‘John’, ‘2015-01-05’, 3 ‘John’, ‘2015-01-06’, 4 ‘John’, ‘2015-01-07’, 5 ‘Mike’, ‘2015-01-01’, 1 ‘Mike’, ‘2015-01-02’, 2 ‘Mike’, ‘2015-01-05’, 3

I can get partial results with the following query:

SELECT *, ROW_NUMBER() OVER (ORDER BY Date) as Weekdays INTO Customers_1 FROM Customers WHERE Name='John'

However, this must be done one-by-one for each customer, and creates a duplicate of the table in order to accomplish it.

What I need is something roughly like:

UPDATE Customers SET Weekdays=__row-number-on-a-per-customer-basis-when-date-sorted__

A calendar or "dimDate" table will make this a much simpler task.

How? He already supposedly has the dates he wants and just wants to number the output. How would a Calendar table make that any easier than a single SELECT with a ROW_NUMBER column in it?

--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)
Intro to Tally Tables and Functions

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 8

Jeff Moden (6/16/2015)

DonlSimpson (6/16/2015)

A calendar or "dimDate" table will make this a much simpler task.

How? He already supposedly has the dates he wants and just wants to number the output. How would a Calendar table make that any easier than a single SELECT with a ROW_NUMBER column in it?

I guess I assumed that dates would continue in the future, and I was focusing on this:

UPDATE Customers SET Weekdays=__row-number-on-a-per-customer-basis-when-date-sorted__

I use a calendar table that includes things like "calendarYearWorkdayNumber" and "fiscalYearWorkdayNumber."

Don Simpson

I'm not sure about Heisenberg.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

DonlSimpson (6/17/2015)

Jeff Moden (6/16/2015)

DonlSimpson (6/16/2015)

A calendar or "dimDate" table will make this a much simpler task.

How? He already supposedly has the dates he wants and just wants to number the output. How would a Calendar table make that any easier than a single SELECT with a ROW_NUMBER column in it?

I guess I assumed that dates would continue in the future, and I was focusing on this:

UPDATE Customers SET Weekdays=__row-number-on-a-per-customer-basis-when-date-sorted__

I use a calendar table that includes things like "calendarYearWorkdayNumber" and "fiscalYearWorkdayNumber."

Ah... got it. Thanks for the feedback.

--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)
Intro to Tally Tables and Functions

sgmunson SSC Guru Points: 110588 More actions · Answer 10

For the original poster, the ROW_NUMBER() function you're looking for would look like this:



ROW_NUMBER() OVER(PARTITION BY [Name] ORDER BY [Date]) AS Weekdays

The PARTITION BY portion will start the numbering over with each different Name value, and is considerd a "Window" function, because it doesn't require a GROUP BY, but can still see values from other records than the current one.

Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)