Indexing Dates

Question

Post reply

Indexing Dates

Viewing 9 posts - 16 through 25 (of 25 total)

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

SQL 2016 SSC-Addicted Points: 457 More actions · Answer 1

SQL 2016

SSC-Addicted

Points: 457

June 16, 2015 at 1:48 pm

#1805876

awesome..thank you for the great help!!

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

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: 1004664 More actions · Answer 3

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)

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

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)

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 5

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: 1004664 More actions · Answer 6

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)

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 7

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: 1004664 More actions · Answer 8

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)

sgmunson SSC Guru Points: 110651 More actions · Answer 9

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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)