+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Working days calendar in T-SQL?

  1. Working days calendar in T-SQL?

    My company working days is from Monday to Friday.
    I would like to generate a result set which show all
    consecutive working days (Monday to Friday excluding the
    weekends) that is, for June 2007, it show me 1st, 4th, 5th,
    6th, 8th, 11th etc... until 29th (last working day of June). Is
    it possible to do this with T-SQL?

    Thank you very much.

    --
    Message posted via SQLMonster.com
    http://www.sqlmonster.com/Uwe/Forums...mming/200706/1


  2. Re: Working days calendar in T-SQL?

    Hi
    http://classicasp.aspfaq.com/date-ti...two-dates.html




    "pedestrian via SQLMonster.com" wrote in message
    news:73eef10337f0a@uwe...
    > My company working days is from Monday to Friday.
    > I would like to generate a result set which show all
    > consecutive working days (Monday to Friday excluding the
    > weekends) that is, for June 2007, it show me 1st, 4th, 5th,
    > 6th, 8th, 11th etc... until 29th (last working day of June). Is
    > it possible to do this with T-SQL?
    >
    > Thank you very much.
    >
    > --
    > Message posted via SQLMonster.com
    > http://www.sqlmonster.com/Uwe/Forums...mming/200706/1
    >




  3. Re: Working days calendar in T-SQL?

    On Jun 19, 8:22 am, "pedestrian via SQLMonster.com"
    wrote:
    > My company working days is from Monday to Friday.
    > I would like to generate a result set which show all
    > consecutive working days (Monday to Friday excluding the
    > weekends) that is, for June 2007, it show me 1st, 4th, 5th,
    > 6th, 8th, 11th etc... until 29th (last working day of June). Is
    > it possible to do this with T-SQL?
    >
    > Thank you very much.
    >
    > --
    > Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/2007...


    Yes, google up "calendar table".


  4. RE: Working days calendar in T-SQL?

    Yes, consider using a calendar table. See this article for details:
    http://sqlserver2000.databases.aspfa...dar-table.html


    ML

    ---
    http://milambda.blogspot.com/

  5. Re: Working days calendar in T-SQL?

    On 19 Jun, 14:22, "pedestrian via SQLMonster.com" wrote:
    > My company working days is from Monday to Friday.


    Is that always the case though? What about public holidays?


    > I would like to generate a result set which show all
    > consecutive working days (Monday to Friday excluding the
    > weekends) that is, for June 2007, it show me 1st, 4th, 5th,
    > 6th, 8th, 11th etc... until 29th (last working day of June). Is
    > it possible to do this with T-SQL?


    Use a calendar table:

    SELECT dt
    FROM Calendar
    WHERE dt > '20070601'
    AND dt < '20070701'
    AND WorkingDay = 'Y';

    --
    David Portas, SQL Server MVP

    Whenever possible please post enough code to reproduce your problem.
    Including CREATE TABLE and INSERT statements usually helps.
    State what version of SQL Server you are using and specify the content
    of any error messages.

    SQL Server Books Online:
    http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
    --


  6. Re: Working days calendar in T-SQL?

    Hi, Uri, thanks for the link for useful information...

    Uri Dimant wrote:
    >Hi
    >http://classicasp.aspfaq.com/date-ti...two-dates.html
    >
    >> My company working days is from Monday to Friday.
    >> I would like to generate a result set which show all

    >[quoted text clipped - 4 lines]
    >>
    >> Thank you very much.


    --
    Regards,
    Pedestrian, Penang.

    Message posted via SQLMonster.com
    http://www.sqlmonster.com/Uwe/Forums...mming/200706/1


  7. Re: Working days calendar in T-SQL?

    Hi, Uri, thanks for the link for useful information...

    Uri Dimant wrote:
    >Hi
    >http://classicasp.aspfaq.com/date-ti...two-dates.html
    >
    >> My company working days is from Monday to Friday.
    >> I would like to generate a result set which show all

    >[quoted text clipped - 4 lines]
    >>
    >> Thank you very much.


    --
    Regards,
    Pedestrian, Penang.

    Message posted via http://www.sqlmonster.com


  8. RE: Working days calendar in T-SQL?

    Thanks for the suggestion, ML.
    Thank you Alex.

    ML wrote:
    >Yes, consider using a calendar table. See this article for details
    >http://sqlserver2000.databases.aspfa...dar-table.html
    >
    >ML
    >
    >---
    >http://milambda.blogspot.com/


    --
    Regards,
    Pedestrian, Penang.

    Message posted via http://www.sqlmonster.com


  9. Re: Working days calendar in T-SQL?

    Ya.... I have to take care the holiday as well (which is stored in a table)
    FYI, I have decided to create an Windows app that will be scheduled daily...

    David Portas wrote:
    >> My company working days is from Monday to Friday.

    >
    >Is that always the case though? What about public holidays?
    >
    >> I would like to generate a result set which show all
    >> consecutive working days (Monday to Friday excluding the
    >> weekends) that is, for June 2007, it show me 1st, 4th, 5th,
    >> 6th, 8th, 11th etc... until 29th (last working day of June). Is
    >> it possible to do this with T-SQL?

    >
    >Use a calendar table:
    >
    >SELECT dt
    >FROM Calendar
    >WHERE dt > '20070601'
    > AND dt < '20070701'
    > AND WorkingDay = 'Y';
    >
    >--
    >David Portas, SQL Server MVP
    >
    >Whenever possible please post enough code to reproduce your problem.
    >Including CREATE TABLE and INSERT statements usually helps.
    >State what version of SQL Server you are using and specify the content
    >of any error messages.
    >
    >SQL Server Books Online:
    >http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
    >--


    --
    Regards,
    Pedestrian, Penang.

    Message posted via SQLMonster.com
    http://www.sqlmonster.com/Uwe/Forums...mming/200706/1


  10. Re: Working days calendar in T-SQL?

    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.

    CREATE TABLE Calendar
    (cal_date DATE NOT NULL PRIMARY KEY,
    fiscal_year INTEGER NOT NULL,
    fiscal_month INTEGER NOT NULL,
    week_in_year INTEGER NOT NULL, -- SQL server is not ISO standard
    holiday_type INTEGER NOT NULL
    CHECK(holiday_type IN ( ..), --
    day_in_year INTEGER NOT NULL,
    julian_business_day INTEGER NOT NULL,
    ...);

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

    A calendar table for US Secular holidays can be built from the data at
    this website, so you will get the three-day weekends:

    http://www.smart.net/~mmontes/ushols.html

    Time zones with fractional hour displacements
    http://www.timeanddate.com/worldclock/city.html?n=246
    http://www.timeanddate.com/worldclock/city.html?n=176
    http://www.timeanddate.com/worldclock/city.html?n=5
    http://www.timeanddate.com/worldclock/city.html?n=54

    But the STRANGE ones are:
    http://www.timeanddate.com/worldclock/city.html?n=63
    http://www.timeanddate.com/worldclock/city.html?n=5


+ Reply to Thread
Page 1 of 2 1 2 LastLast