Solved

get the number of months between start and end dates

Posted on 2014-03-20
7
219 Views
Last Modified: 2014-03-25
Hello.

I have to calculated fields:
,[Contract_NetStartDate] = CASE WHEN ContractStartDate <= @ReportStartDate THEN @ReportStartDate ELSE ContractStartDate END
,[Contract_NetEndDate] = CASE WHEN ContractEndDate >= @ReportEndDate THEN @ReportEndDate ELSE ContractEndDate END

Open in new window


I need to add a third calculated field:
[Contract_NetNumberOfPeriods]

Open in new window


The third field is the number of months between [Contract_NetStartDate] and [Contract_NetEndDate]

Is it possible that some one show me how to obtain that number from the two fields?

Thank you much.
0
Comment
Question by:metropia
7 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 250 total points
ID: 39943901
DATEDIFF(MONTH, ContractStartDate, ContractEndDate)

This depends on how you want to calculate the number of months.  In the case above...

DATEDIFF(MONTH, '1/31/2014', '2/1/2014') = 1
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39943945
Do you want to do the calculation on the original table columns (OR) on the results of the CASE expressions?

What do you mean by "month"?  That it crossed a calendar month, or that it was a minimum number of days for each "month"?
0
 

Assisted Solution

by:cshark247
cshark247 earned 250 total points
ID: 39943956
if you want to use original table columns you can use.

SELECT
             [Contract_NetStartDate] = CASE WHEN ContractStartDate <= @ReportStartDate THEN @ReportStartDate ELSE ContractStartDate END
            ,[Contract_NetEndDate] = CASE WHEN ContractEndDate >= @ReportEndDate THEN @ReportEndDate ELSE ContractEndDate END
             ,Contract_NetNumberOfPeriods =  datediff(m,CASE WHEN ContractStartDate <= @ReportStartDate THEN @ReportStartDate ELSE ContractStartDate END,CASE WHEN ContractEndDate >= @ReportEndDate THEN @ReportEndDate ELSE ContractEndDate END)


Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39944000
I wouldn't repeat the expressions twice, too hard to keep in sync later.
0
 

Assisted Solution

by:cshark247
cshark247 earned 250 total points
ID: 39944065
you are right, it is always good to avoid duplication. Another solution could be to wrap in outer query like this:

SELECT *,
 datediff(m,Contract_NetStartDate , Contract_NetEndDate ) as Contract_NetNumberOfPeriods
 FROM
(
	SELECT 
			 [Contract_NetStartDate] = CASE WHEN ContractStartDate <= @ReportStartDate THEN @ReportStartDate ELSE ContractStartDate END
			,[Contract_NetEndDate] = CASE WHEN ContractEndDate >= @ReportEndDate THEN @ReportEndDate ELSE ContractEndDate END
	
	   
) x

Open in new window

0
 

Author Comment

by:metropia
ID: 39945281
Thank you experts.

I have a question, why when I use:

DATEDIFF(m,Contract_NetStartDate , Contract_NetEndDate)

I get negative numbers, but if I switch to:

DATEDIFF(m, Contract_NetEndDate, Contract_NetStartDate)

Then the number of months is positive

??
0
 
LVL 32

Expert Comment

by:awking00
ID: 39945697
Because your Contract_NetEndDate must be earlier than your Contract_NetStartDate.
For syntax, see here -
http://www.w3schools.com/sql/func_datediff.asp
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now