Find how many week day crossed in a given date

  • Rock

    Old Hand

    Points: 346

    Team

    I have a scenario in SQL Query. If I Give a day, The output should say which day and how many days crossed in a given month

    For Ex

    1. If I give 04th Dec 2019 as input > It should say, It is WED and this date is first wed
    2. If the Input is 17th Dec 2019 > The output is Tue, Its 3rd Tuesday in this month. (3rd Dec - first tue, 10th Dec is 2nd Tue and 17 Dec is 3rd Wed in the month of December)
    3. It applies for all the dates given.

    Please suggest some solution.

    Regards

    Janu

    • This topic was modified 3 months, 3 weeks ago by  Rock.
    • This topic was modified 3 months, 2 weeks ago by  Rock.
  • DesNorton

    SSC-Insane

    Points: 22967

    Something like this

    DECLARE @TheDate date = '2019-12-17';

    SELECT TheDay = CASE DATEDIFF(dd, 0, @TheDate) %7
    WHEN 0 THEN 'Monday'
    WHEN 1 THEN 'Tuesday'
    WHEN 2 THEN 'Wednesday'
    WHEN 3 THEN 'Thursday'
    WHEN 4 THEN 'Friday'
    WHEN 5 THEN 'Saturday'
    WHEN 6 THEN 'Sunday'
    END
    , NumDays = DAY(@TheDate) /7 + CASE WHEN DAY(@TheDate) %7 = 0 THEN 0 ELSE 1 END

    • This reply was modified 3 months, 3 weeks ago by  DesNorton. Reason: Fixed the offset for Day1
  • DesNorton

    SSC-Insane

    Points: 22967

    You can turn the code into a function

    CREATE FUNCTION dbo.itvfGetDays(@TheDate date)
    RETURNS TABLE WITH SCHEMABINDING AS
    RETURN SELECT TheDay = CASE DATEDIFF(dd, 0, @TheDate) %7
    WHEN 0 THEN 'Monday'
    WHEN 1 THEN 'Tuesday'
    WHEN 2 THEN 'Wednesday'
    WHEN 3 THEN 'Thursday'
    WHEN 4 THEN 'Friday'
    WHEN 5 THEN 'Saturday'
    WHEN 6 THEN 'Sunday'
    END
    , NumDays = DAY(@TheDate) /7 + CASE WHEN DAY(@TheDate) %7 = 0 THEN 0 ELSE 1 END
    GO

    It can be used to check a single date

    DECLARE @TheDate date = '2019-12-17';

    SELECT @TheDate AS TheDate, d.TheDay, d.NumDays
    FROM dbo.itvfGetDays(@TheDate) AS d;

    Or to check against a table

    CREATE TABLE #SampleData (TheDate date);

    INSERT INTO #SampleData ( TheDate )
    VALUES ('2019-12-01'), ('2019-12-02'), ('2019-12-03'), ('2019-12-04'), ('2019-12-05'), ('2019-12-06'), ('2019-12-07')
    , ('2019-12-08'), ('2019-12-09'), ('2019-12-10'), ('2019-12-11'), ('2019-12-12'), ('2019-12-13'), ('2019-12-14')
    , ('2019-12-15'), ('2019-12-16'), ('2019-12-17'), ('2019-12-18'), ('2019-12-19'), ('2019-12-20'), ('2019-12-21')
    , ('2019-12-22'), ('2019-12-23'), ('2019-12-24'), ('2019-12-25'), ('2019-12-26'), ('2019-12-27'), ('2019-12-28')
    , ('2019-12-29'), ('2019-12-30'), ('2019-12-31');


    SELECT src.TheDate, d.TheDay, d.NumDays
    FROM #SampleData AS src
    CROSS APPLY dbo.itvfGetDays(src.TheDate) AS d;

     

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    if you look at recursive CTEs you can build a list of dates within your boundaries

    then use SELECT DATEPART (DAY,GETDATE()) and exclude the Saturday and sunday values then count...

    i iknow i haven't given you the full code, but I hope it's a start

    MVDBA

  • drew.allen

    SSC Guru

    Points: 76712

    MVDBA (Mike Vessey) wrote:

    if you look at recursive CTEs you can build a list of dates within your boundaries

    then use SELECT DATEPART (DAY,GETDATE()) and exclude the Saturday and sunday values then count...

    i iknow i haven't given you the full code, but I hope it's a start

    A recursive CTE is a horrible way to build a list of dates.  You're much better off using a tally table.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • schleep

    SSChampion

    Points: 12540

    DECLARE @dDate DATE = '11 Dec 2019'
    SELECT DATENAME(DW, @dDate), ((DAY(@dDate)-1) / 7) + 1

    • This reply was modified 3 months, 2 weeks ago by  schleep. Reason: Edited to correct formula per Jeff's observation below. (Thanks Jeff)
  • Jonathan AC Roberts

    SSCoach

    Points: 17204

    Rock wrote:

    Team

    I have a scenario in SQL Query. If I Give a day, The output should say which day and how many days crossed in a given month

    For Ex

      <li style="list-style-type: none;">

    1. If I give 04th Dec 2019 as input > It should say, It is Tuesday and this date is first tuesday
    2. If the Input is 17th Dec 2019 > The output is Wednesday, Its 3rd Tuesday in this month. (3rd Dec - first tue, 10th Dec is 2nd Tue and 17 Dec is 3rd Wed in the month of December)
    3. It applies for all the dates given.
      <li style="list-style-type: none;">

    Please suggest some solution.

    Regards

    Janu

    I'm confused by your question:

    4th December was a Wednesday but you want it to say Tuesday?

    17th December is going to be a Tuesday but you want it to say Wednesday?

  • scdecade

    Say Hey Kid

    Points: 693

    To see if DesNorton's solution worked (it does) and to see if the interval could be created in Sql 2012 I tried with this sql although I don't have any way of knowing if it actually works in Sql 2012.  It does work in Azure Sql compatibility level 140.  It doesn't use any newer date functions and I copied (from the internet) a function that returns the date from parts in 2012.  As long as the sys.objects table has more then 31 rows

    create function dbo.fn_datefromparts(@year int, @month int, @day int)
    returns datetime
    as
    begin
    declare @d datetime;

    select @d=cast(convert(varchar, @year) + '-' + convert(varchar, @month) + '-' + convert(varchar, @day) as datetime)
    return @d
    end
    go

    declare
    @dt datetime='2019-12-17';
    declare
    @input_year int=datepart(year, @dt),
    @input_month int=datepart(month, @dt);
    declare
    @test_month date=dbo.fn_datefromparts(@input_year,@input_month,1)
    declare
    @days_in_month int=(select datepart(day, (select dateadd(day,-1,dateadd(month,1,@test_month)))));

    with
    mon_cte(dt) as (
    select top(@days_in_month)
    dateadd(day, (row_number() over (order by (select null)))-1, @test_month)
    from
    sys.objects)
    select
    dt,
    day(dt)/7 div_sev,
    day(dt)%7 mod_sev
    from
    mon_cte;

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Jeff Moden

    SSC Guru

    Points: 995976

    drew.allen wrote:

    MVDBA (Mike Vessey) wrote:

    if you look at recursive CTEs you can build a list of dates within your boundaries

    then use SELECT DATEPART (DAY,GETDATE()) and exclude the Saturday and sunday values then count...

    i iknow i haven't given you the full code, but I hope it's a start

    A recursive CTE is a horrible way to build a list of dates.  You're much better off using a tally table.

    Drew

    Mike,

    I agree with Drew and will state it a slightly different way... never use rCTEs (Recursive CTEs) to do anything that provides a series of numbers... in other words, never use them to count.  Please see the following article for why.  As Drew says, they're "horrible"!

    https://www.sqlservercentral.com/articles/hidden-rbar-counting-with-recursive-ctes

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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: 995976

    Rock wrote:

    Team

    I have a scenario in SQL Query. If I Give a day, The output should say which day and how many days crossed in a given month

    For Ex

      <li style="list-style-type: none;">

    1. If I give 04th Dec 2019 as input > It should say, It is Tuesday and this date is first tuesday
    2. If the Input is 17th Dec 2019 > The output is Wednesday, Its 3rd Tuesday in this month. (3rd Dec - first tue, 10th Dec is 2nd Tue and 17 Dec is 3rd Wed in the month of December)
    3. It applies for all the dates given.

    Please suggest some solution.

    Regards

    Janu

    Here's my take on it...

    First, here are the two test dates from the original post but in the form of a test table...

    --===== Create and populate the test table.
    CREATE TABLE #TestTable
    (SomeDT DATETIME)
    ;
    INSERT INTO #TestTable
    (SomeDT)
    VALUES (' 4 Dec 2019')
    ,('17 Dec 2019')
    ;

    And here's my solution to the problem...

    --===== My take on the solution
    SELECT SomeDT
    ,DoW = DATENAME(dw,SomeDT)
    ,DoW# = (DAY(SomeDT)-1)/7+1
    FROM #TestTable
    ;

    The DoW column (DoW = Day of Week) is simple to come by.  SQL Server has the DATENAME function to do it and it auto-magically adjusts to the current language if it's supported.  A lot of people simply don't know this function exists.

    For the DoW# column (Day of Week number for the month), it's also easy.  Just do a proper "modulus" (we're using the quotient of the base division rather than the remainder) on the DAY of the month.  A "proper" modulus  works on a zero base so we get the day of the month, subtract 1 from that, divide that by 7 to come up with the grouping for each day of the month (and it's all integer math so no decimals involved), and then add 1 back to that.

    To be honest, the formula for both columns is easy enough to remember, I wouldn't waste the time to write a function for either column.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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: 995976

    schleep wrote:

    DECLARE @dDate DATE = '11 Dec 2019'
    SELECT DATENAME(DW, @dDate), (DAY(@dDate) / 7) + 1

    Try the date of '7 Dec 2019' and see that it comes up as the 2nd Saturday, which is impossible because it's only the 7th day.  You need to subtract 1 from the day to form a proper modulus, which is zero based.  Day is unit based.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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: 995976

    Jonathan AC Roberts wrote:

    Rock wrote:

    Team

    I have a scenario in SQL Query. If I Give a day, The output should say which day and how many days crossed in a given month

    For Ex

     

     

      <li style="list-style-type: none;">
      <li style="list-style-type: none;">

    1. If I give 04th Dec 2019 as input > It should say, It is Tuesday and this date is first tuesday
    2. If the Input is 17th Dec 2019 > The output is Wednesday, Its 3rd Tuesday in this month. (3rd Dec - first tue, 10th Dec is 2nd Tue and 17 Dec is 3rd Wed in the month of December)
    3. It applies for all the dates given.
      <li style="list-style-type: none;">
      <li style="list-style-type: none;">

    Please suggest some solution.

    Regards

    Janu

    I'm confused by your question:

    4th December was a Wednesday but you want it to say Tuesday?

    17th December is going to be a Tuesday but you want it to say Wednesday?

    Heh... it is confusing and I think he may have just been using the wrong calendar somewhere along the line or had a physical calendar that maybe started on a Monday and then things went to hell from there.  So, like a lot of the others, I may have made a mistake by assuming I knew what the OP really wanted.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

  • jcelko212 32090

    SSCrazy Eights

    Points: 8946

     

    In your case, I would use the ISO-8601 week-date format. You can downoad such a calendar from the internet. The format is yyyyW[0-5][0-9]-[1-7] where the week is 01-53, depending on the year and day of the week is (1= Monday,  7= Sunday).

    Build a calendar table with one column for the calendar data and other columns to show whatever your business needs in the way of temporal information. Do not try to calculate holidays in SQL -- Easter alone requires too much math and there are two of them (Orthodox and Catholic).

    The Ordinal business day is a good trick. Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday.

    CREATE TABLE Calendar

    (cal_date DATE NOT NULL PRIMARY KEY,

    ordinal_business_nbr INTEGER NOT NULL,

    ...);

    INSERT INTO Calendar

    VALUES ('2007-04-05', 42);

    ('2007-04-06', 43); -- Good Friday

    ('2007-04-07', 43);

    ('2007-04-08', 43); -- Easter Sunday

    ('2007-04-09', 44);

    ('2007-04-10', 45); -- Tuesday, back to work

    To compute the business days from '2007-04-05', Thursday before Easter in 2007 to '2007-04-10', the next Tuesday:

     

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • Jeff Moden

    SSC Guru

    Points: 995976

    jcelko212 32090 wrote:

    In your case, I would use the ISO-8601 week-date format. You can downoad such a calendar from the internet. The format is yyyyW[0-5][0-9]-[1-7] where the week is 01-53, depending on the year and day of the week is (1= Monday,  7= Sunday).

    I don't see how that will provide any benefit to solving the OP's posted  requirements.   Please explain why you think it will.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

Viewing 14 posts - 1 through 14 (of 14 total)

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