Count days per month per year between 2 dates

  • pnomadewolf

    SSC-Addicted

    Points: 412

    Hi!

    The problem is simple, but the answer doesn't seem so...

    I'm trying to count the number of days per month per year between two dates.

    So for the following 2 dates:

    • 2013.12.15
    • 2014.02.18

    I would have something like:

    Year | Month | Days

    2013 12 16

    2014 01 31

    2014 02 18

    So, with 2 dates, i would get as many rows as months between then.

    I would like to achieve this using only a Select statement, but am completelu lost.

    Anyone one out there can crack this? 😀

  • Luis Cazares

    SSC Guru

    Points: 183638

    With a calendar table you could count the rows without a problem. 🙂

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Eugene Elutin

    SSC Guru

    Points: 59322

    or without calendar table:

    declare @dtFrom date

    declare @dtTo date

    select @dtFrom = '2013-12-15'

    ,@dtTo = '2014-02-18'

    select year(dt) [Year], month(dt) [Month], count(*)

    from (select top(datediff(d, @dtFrom, @dtTo)) dateadd(d, row_number() over (order by (select null)), @dtFrom) dt

    from sys.columns) q

    group by year(dt), month(dt)

    order by [Year], [Month]

    Please note! sys.columns is used as a tally table, you nca use a proper one or in-line-built version if you like.

    You can search for tally table examples on this forum...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • pnomadewolf

    SSC-Addicted

    Points: 412

    Eugene Elutin (3/11/2014)


    or without calendar table:

    declare @dtFrom date

    declare @dtTo date

    select @dtFrom = '2013-12-15'

    ,@dtTo = '2014-02-18'

    select year(dt) [Year], month(dt) [Month], count(*)

    from (select top(datediff(d, @dtFrom, @dtTo)) dateadd(d, row_number() over (order by (select null)), @dtFrom) dt

    from sys.columns) q

    group by year(dt), month(dt)

    order by [Year], [Month]

    Please note! sys.columns is used as a tally table, you nca use a proper one or in-line-built version if you like.

    You can search for tally table examples on this forum...

    Your code works like a charm. Thanks a lot Eugene! 😎 Much elaborate 😎

    Your answer was good too Luis. But since the start and end dates actually result from select and vary all the time, i can avoid having to drop/create the table all the time or use a larger time frame than i actually need.

  • Luis Cazares

    SSC Guru

    Points: 183638

    Actually, you don't have to create and drop the calendar table. Calendar tables are meant to make date calculations easier and should be permanent tables. With the proper indexing, they can make things pretty fast and they only take 365 rows per year (about 36,525 for 100 years which seems like a small table for me).

    In the end, Eugene is building the calendar table on the fly with the needed range. This is great too, but implies more work every time (and I get lazy :-D), unless you convert it to an inline Table-Valued Function.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • pnomadewolf

    SSC-Addicted

    Points: 412

    Luis Cazares (3/14/2014)


    Actually, you don't have to create and drop the calendar table. Calendar tables are meant to make date calculations easier and should be permanent tables. With the proper indexing, they can make things pretty fast and they only take 365 rows per year (about 36,525 for 100 years which seems like a small table for me).

    In the end, Eugene is building the calendar table on the fly with the needed range. This is great too, but implies more work every time (and I get lazy :-D), unless you convert it to an inline Table-Valued Function.

    Yup.

    After testing, the calendar table actually might work a bit faster.

    But i have so many rows that when i compare dates, the query is getting so slow...

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

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