Calculate number of moths between two days?

Question

Post reply

Calculate number of moths between two days?

adonetok

SSCrazy Eights

Points: 8395
More actions
November 3, 2010 at 6:56 am

#228714

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)

Viewing 11 posts - 1 through 10 (of 10 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

There's DATEDIFF for that kind of thing:

http://msdn.microsoft.com/it-it/library/ms189794.aspx

-- Gianluca Sartori

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

See here.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Chris Morris-439714 (11/3/2010)
See here.

:-D:hehe:

LOL! I'm sure DATEDIFF can do nothing with them!

-- Gianluca Sartori

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

select

DT1,

DT2,

MonthDiff =

datediff(mm,dateadd(dd,-14,a.DT1),dateadd(dd,-14,b.DT2))

from

( -- Test Data

select DT1=convert(datetime,'20100214')union all

select DT1=convert(datetime,'20100215')

) a

cross join

( -- Test Data

select DT2=convert(datetime,'20101114')union all

select DT2=convert(datetime,'20101115')

) b

order by

DT1,

DT2

Results:

DT1 DT2 MonthDiff

----------------------- ----------------------- ---------

2010-02-14 00:00:00.000 2010-11-14 00:00:00.000 9

2010-02-14 00:00:00.000 2010-11-15 00:00:00.000 10

2010-02-15 00:00:00.000 2010-11-14 00:00:00.000 8

2010-02-15 00:00:00.000 2010-11-15 00:00:00.000 9

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 5

I believe everyone is missing the requirements a bit on this one. The op wants to know how many months are in each year of a multi-year range of dates.

--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.

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)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

DECLARE @startDate DATETIME,

@EndDate DATETIME

SET @StartDate = '09/01/2007'

SET @EndDate = '05/20/2009'

SELECT

N AS [Year],

CASE WHEN N = YEAR( @StartDate)

THEN DATEDIFF( mm, @startdate, DATEADD( yy, YEAR( @StartDate) - 1900 + 1, 0))

WHEN N = YEAR( @EndDate)

THEN DATEDIFF( mm, DATEADD( yy, YEAR( @EndDate) - 1900 , 0) - 1, @EndDate)

ELSE 12

END AS Moths

FROM

Tally

WHERE

N BETWEEN YEAR( @startDate) AND YEAR ( @EndDAte)

EDIT: Heh, sorry about that. If you need help with creating/using the tally table, see the link in my sig.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

Jeff Moden (11/3/2010)
I believe everyone is missing the requirements a bit on this one. The op wants to know how many months are in each year of a multi-year range of dates.

I actually answered the OP's first requirement: "How to calculate number of moths between two days?", and just ignored the second, different requirement for a result set for each year in between.

I mainly wanted to show the logic for how to handle this: "(If date of month >=15 will count as whole month, if <15 will be ignored) ". None of the other solutions seem to have attempted to address this.

Question posted and answered here also:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=152512

OP was happy with solution posted there, even though it produces the same answer when the day of month is 13 or 20. "It works, thank you." :ermm:

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 8

Michael Valentine Jones (11/3/2010)
I mainly wanted to show the logic for how to handle this: "(If date of month >=15 will count as whole month, if <15 will be ignored) ". None of the other solutions seem to have attempted to address this.

Heh, missed that for some reason, in a four line post. Guess it went with the day. But I guess the OP's happy so it's all good. 🙂

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 9

adonetok (11/3/2010)
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

Hi

Craig Farrell has used a table you can avoid the physical table you can have temp table that will do the same work and you can reduce count for your requirment

declare @table table (ivalue int)

declare @I int

set @I= 1900

while @I<= 2100 begin

insert into @table (ivalue)

values (@I)

set @I = @I+ 1

end

DECLARE @startDate DATETIME,

@EndDate DATETIME

SET @StartDate = '09/01/2007'

SET @EndDate = '05/20/2009'

SELECT

Ivalue AS [Year],

CASE WHEN Ivalue = YEAR( @StartDate)

THEN DATEDIFF( mm, @startdate, DATEADD( yy, YEAR( @StartDate) - 1900 + 1, 0))

WHEN Ivalue = YEAR( @EndDate)

THEN DATEDIFF( mm, DATEADD( yy, YEAR( @EndDate) - 1900 , 0) - 1, @EndDate)

ELSE 12

END AS Moths

FROM @table

WHERE Ivalue BETWEEN YEAR( @startDate) AND YEAR ( @EndDAte)

If you wish you can keep table to retrive the data instead of temp table used every time.If it is less process you can go with temp table or you can have a permanent table

Thanks

Parthi

Thanks
Parthi

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 10

parthi-1705 (11/4/2010)
adonetok (11/3/2010)
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
Hi
Craig Farrell has used a table you can avoid the physical table you can have temp table that will do the same work and you can reduce count for your requirment
declare @table table (ivalue int)
declare @I int
set @I= 1900
while @I<= 2100 begin
insert into @table (ivalue)
values (@I)
set @I = @I+ 1
end
DECLARE @startDate DATETIME,
@EndDate DATETIME
SET @StartDate = '09/01/2007'
SET @EndDate = '05/20/2009'
SELECT
Ivalue AS [Year],
CASE WHEN Ivalue = YEAR( @StartDate)
THEN DATEDIFF( mm, @startdate, DATEADD( yy, YEAR( @StartDate) - 1900 + 1, 0))
WHEN Ivalue = YEAR( @EndDate)
THEN DATEDIFF( mm, DATEADD( yy, YEAR( @EndDate) - 1900 , 0) - 1, @EndDate)
ELSE 12
END AS Moths
FROM @table
WHERE Ivalue BETWEEN YEAR( @startDate) AND YEAR ( @EndDAte)
If you wish you can keep table to retrive the data instead of temp table used every time.If it is less process you can go with temp table or you can have a permanent table
Thanks
Parthi

Ummm... that must mean you have test code that shows the While Loop method you've used is actually more efficient, faster, and uses less resources. I'd love to see that code, Parthi.

--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.

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)