Post reply

Indexing Dates

  • June 16, 2015 at 1:46 pm

    #1805874

    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

  • June 16, 2015 at 1:48 pm

    #1805876

    awesome..thank you for the great help!!

  • June 16, 2015 at 2:47 pm

    #1805899

    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__

  • June 16, 2015 at 2:54 pm

    #1805902

    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)

  • June 16, 2015 at 3:00 pm

    #1805904

    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)

  • June 16, 2015 at 4:25 pm

    #1805921

    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.

  • June 16, 2015 at 4:31 pm

    #1805923

    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)

  • June 17, 2015 at 11:15 am

    #1806164

    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.

  • June 17, 2015 at 3:12 pm

    #1806227

    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)

  • June 18, 2015 at 3:11 pm

    #1806554

    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)

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

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