Solved

get the number of months between start and end dates

Posted on 2014-03-20
7
218 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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 31

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

705 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

19 Experts available now in Live!

Get 1:1 Help Now