Calculate number of moths between two days

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Calculate number of moths between two days

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2010-11-03 : 10:20:55

How to calculate number of moths between two days?
For example, BeginingDt=09/01/2007, EndingDt=05/20/2009 should be list as below:
2007:4
2008:12
2009:5
(If date of month >=15 will count as whole month, if <15 will be ignored)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-03 : 11:40:22

Start with this

declare @BeginingDt datetime, @EndingDt datetime
select @BeginingDt ='09/01/2007', @EndingDt='05/20/2009'
select YEAR(dates),COUNT(*) from
(
select DATEADD(month,number,@BeginingDt) as dates from master..spt_values
where type='p' and number between 0 and DATEDIFF(month,@BeginingDt,@EndingDt)
) as t
group by YEAR(dates)

Madhivanan

Failing to plan is Planning to fail

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2010-11-03 : 13:14:30

It works, thank you.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-04 : 05:42:13

quote:
Originally posted by Sun Foster

It works, thank you.

You are welcome

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2010-11-04 : 05:59:19

quote:
(If date of month >=15 will count as whole month, if <15 will be ignored)

But I guess the above condition is being missed in the query

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-11-04 : 06:07:03

quote:
(If date of month >=15 will count as whole month, if <15 will be ignored)

Change the query to below to fulfill the above condition


declare @BeginingDt datetime, @EndingDt datetime
select @BeginingDt ='09/10/2007', @EndingDt='05/20/2009'
select YEAR(dates),COUNT(*) from
(
select number,DATEADD(month,number,@BeginingDt) as dates from master..spt_values
where type='p' and number  between case when datepart(dd,@BeginingDt)>=15 then 0 else 1 end and DATEDIFF(month,@BeginingDt,@EndingDt) 
) as t
group by YEAR(dates)

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources