Solved

get the number of months between start and end dates

Posted on 2014-03-20
7
222 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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:Scott Pletcher
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
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
LVL 69

Expert Comment

by:Scott Pletcher
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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

696 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