March 11, 2014 at 10:07 am
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:
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? 😀
March 11, 2014 at 10:12 am
With a calendar table you could count the rows without a problem. 🙂
March 11, 2014 at 11:36 am
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...
March 14, 2014 at 6:52 am
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.
March 14, 2014 at 9:17 am
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.
March 14, 2014 at 11:26 am
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...
February 6, 2023 at 6:36 am
This was removed by the editor as SPAM
February 7, 2023 at 1:02 pm
This was removed by the editor as SPAM
February 7, 2023 at 6:43 pm
Congratulations on finding a relevant post that has the answer you need. Did you try it??
February 7, 2023 at 7:06 pm
Jeffery is a spammer - ignore
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply