Indexing Dates
-
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)SELECTt.[Index],t.FactorS,t.Value,t.[Date],DATEDIFF(dd,t.Date, w.MaxDate) + 1 AS LagFROM@test-2 tJOIN working w ON w.FactorS = t.FactorSORDER BYt.[Index]
SELECT quote FROM brain WHERE original = 1
0 rows returned -
awesome..thank you for the great help!!
-
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:
CustomersName, 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:
CustomersName, 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’, 3I 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__ -
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) -
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) -
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:
CustomersName, 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:
CustomersName, 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’, 3I 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. -
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:
CustomersName, 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:
CustomersName, 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’, 3I 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) -
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. -
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) -
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 WeekdaysThe 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)
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply