Solved

Converting Excel formula to SQL Case II

Posted on 2014-03-10
5
227 Views
Last Modified: 2014-03-11
I would like to ask again for your help converting an excel formula to sql syntax.

the excel formula goes as follows:
Remaining Contract Length: 

=IF(SalesOrderTypeCode="Blanket", ContractEndDate-IF(TodaysDate > ContractStartDate,IF(TodaysDate>ContractEndDate, ContractEndDate, TodaysDate),ContractStartDate)+1, 0)

Open in new window


I got help originally to convert the formula, but more logic was added to it this I need more help.

I currently have the formula in sql like this:

,	RemainingContractLength =
	CASE 
	        WHEN SalesOrderLineTypeCode = 'BLANKET' 
		THEN DATEDIFF(dd, @Today, ContractEndDate) + 1 
		ELSE 0 
	END

Open in new window


Thank you very much for your help.
0
Comment
Question by:metropia
  • 3
  • 2
5 Comments
 
LVL 6

Expert Comment

by:c1nmo
ID: 39918357
You can nest, not tested but something like:

CASE
WHEN SalesOrderTypeCode="Blanket"
THEN DATEDIFF(dd, ContractEndDate,
      CASE WHEN TodaysDate > ContractStartDate THEN
            CASE WHEN TodaysDate>ContractEndDate THEN ContractEndDate ELSE TodaysDate END
      ELSE ContractStartDate END) + 1 ELSE 0 END
0
 

Author Comment

by:metropia
ID: 39918608
thank you.

somehow the number of days is not coming up the same when i run it on sql server.

i am attaching the excel file, if you notice, for row 13, the number on column Remaining Contract Length if 214, but in sql server comes back as 212

I am also attaching sample data from sql server
Sales-Order-Proration-Sample.xlsx
sample-data.txt
0
 
LVL 6

Expert Comment

by:c1nmo
ID: 39918727
I get -295 using your sample data with:

CASE
WHEN SalesOrderTypeCode='Blanket'
THEN DATEDIFF(dd, ContractEndDate,
      CASE WHEN GETDATE() > ContractStartDate THEN
            CASE WHEN GETDATE()>ContractEndDate THEN ContractEndDate ELSE GETDATE() END
      ELSE ContractStartDate END) + 1 ELSE 0 END
0
 
LVL 6

Accepted Solution

by:
c1nmo earned 500 total points
ID: 39918862
214 for row 13 values now:

select CASE
WHEN SalesOrderTypeCode='Blanket'
THEN DATEDIFF(dd,
      CASE WHEN Today > ContractStartDate THEN
            CASE WHEN Today>ContractEndDate THEN ContractEndDate ELSE Today END
      ELSE ContractStartDate END, ContractEndDate) + 1 ELSE 0 END  from test
0
 

Author Closing Comment

by:metropia
ID: 39921758
excellent solution!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how the fundamental information of how to create a table.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

867 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

20 Experts available now in Live!

Get 1:1 Help Now