how to get customized day and week numbers for a given date range.

  • Hi,

    I have a table with Date ranges for a Plan every year as shown below., I need to generate another table with a customized weekofyear and dayofyear between those date ranges.

    could you please help me with this.

    Actual data:

    Plan Year StartDate EndDate

    PlanA2014 2014020520150204

    PlanB2014 2014021820150217

    PlanA2015 2015021520150214

    PlanB2015 2015021520150214

    Expected sample Result:

    PlanYearDateCustomised Dayof yearCustomised week of year

    PlanA2014201402051W1

    PlanA2014201402062W1

    PlanA2014201402073W1

    PlanA2014201402084W1

    PlanA2014201402095W1

    PlanA2014201402106W1

    PlanA2014201402117W1

    PlanA2014201402128W2

    PlanA2014201402139W2

    PlanA20142014021410W2

    PlanA20142014021511W2

    PlanA20142014021612W2

    PlanA20142014021713W2

    PlanA20142014021814W2

    PlanA20142014021915W3

    PlanA20142014022016W3

    PlanA20142014022117W3

    PlanA20142014022218W3

    PlanA20142014022319W3

    PlanA20142014022420W3

    PlanA20142014022521W3

    PlanB2014201402181W1

    PlanB2014201402192W1

    PlanB2014201402203W1

    PlanB2014201402214W1

    PlanB2014201402225W1

    PlanB2014201402236W1

    PlanB2014201402247W1

    PlanB2014201402258W2

    PlanB2014201402269W2

    PlanB20142014022710W2

    PlanB20142014022811W2

    PlanB20142014030112W2

    PlanB20142014030213W2

    PlanB20142014030314W2

    PlanB20142014030415W3

    PlanB20142014030516W3

    PlanB20142014030617W3

    PlanB20142014030718W3

    PlanB20142014030819W3

    PlanB20142014030920W3

    PlanB20142014031021W3

  • Please explain how the 'customized' figures are determined.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • possible solution ??

    USE TEMPDB;

    IF OBJECT_ID('TEMPDB..yourtable', 'U') IS NOT NULL DROP TABLE TEMPDB..yourtable ;

    CREATE TABLE yourtable(

    PlanName VARCHAR(5) NOT NULL

    ,YearID INTEGER NOT NULL

    ,StartDate DATEtime NOT NULL

    ,EndDate DATEtime NOT NULL

    );

    INSERT INTO yourtable(PlanName,YearID,StartDate,EndDate) VALUES ('PlanA',2014,'20140205','20150204');

    INSERT INTO yourtable(PlanName,YearID,StartDate,EndDate) VALUES ('PlanB',2014,'20140218','20150217');

    INSERT INTO yourtable(PlanName,YearID,StartDate,EndDate) VALUES ('PlanC',2015,'20150215','20160214');

    INSERT INTO yourtable(PlanName,YearID,StartDate,EndDate) VALUES ('PlanD',2015,'20150215','20160214');

    -- use a 'Tally' or 'Numbers' table...there ar many ways to do this (Google will help)

    WITH

    L0 AS(SELECT 1 AS C UNION ALL SELECT 1 AS O),

    L1 AS(SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B),

    L2 AS(SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B),

    L3 AS(SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B),

    L4 AS(SELECT 1 AS C FROM L3 AS A CROSS JOIN L3 AS B),

    Tally AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L4)

    -- one solution maybe?

    SELECT yt.PlanName,

    yt.YearId,

    t.N - 1 + yt.StartDate as Plan_Date,

    t.n as Plan_DOY,

    'W'+ CAST(((t.n - 1) / 7 + 1) AS VARCHAR(2)) as Plan_WOY

    FROM Tally t

    CROSS JOIN yourtable yt

    WHERE t.N - 1 + yt.StartDate <= yt.EndDate

    ORDER BY yt.PlanName,t.N;

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Thank you Livingston for the answer.

Viewing 4 posts - 1 through 3 (of 3 total)

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