• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 51
  • Last Modified:

Convert Excel Formula to WHERE clause in SQL

I need to convert this Excel formula and replace my WHERE clause in my SQL query with it.

The current WHERE clause:

(C is CLOSED,
date_changed is STATUS DATE,
date_entered is START DATE,
@cutoffdate is CUTOFF DATE)


a.[status] = 'C' AND ISNULL(a.date_changed, '1/1/1900') > @cutoffDate
OR
a.[status] <> 'C' and ISNULL(a.date_entered, '1/1/1900') <= @cutoffDate

Excel formula is
=IF(A5="C",IF(B5>D5,"YES","NO"),IF(C5<=D5,"YES","NO"))

I have attached the Excel file.Unbilled-cost-report-select-logic.xlsx
0
Kelly Martens
Asked:
Kelly Martens
  • 9
  • 9
1 Solution
 
_agx_Commented:
Did you forget to attach the file?
0
 
Kelly MartensAuthor Commented:
I must have. I will try this again.Unbilled-cost-report-select-logic.xlsx
0
 
Kelly MartensAuthor Commented:
Come through now?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
_agx_Commented:
Yep, I see it.  

This would produce the desired results, but ... how do you want to use the value?  Only return records where "Display on Report" == 'Yes'?

Setup:
declare @yourTable table
(
status char(1)
, statusDate datetime
, startDate datetime
, cutoffDate datetime
, expectedResult varchar(5)
)
insert into @yourTable
values 
('C','6/20/2017','6/19/2017','6/20/2017','NO')
, ('C','6/21/2017','6/1/2017','6/20/2017','YES')
, ('C','6/21/2017','6/1/2017','5/28/2017','YES')
, ('O',NULL,'6/1/2017','6/1/2017','YES')
, ('O',NULL,'6/15/2018','6/1/2017','NO')

Open in new window

Query:

SELECT *
	, CASE WHEN [status] = 'C' THEN
		CASE WHEN statusDate > cutoffDate THEN 'YES' ELSE 'NO' END
		ELSE 
			CASE WHEN startDate <= cutoffDate THEN 'YES' ELSE 'NO' END
		END AS DisplayOnReport
FROM  @yourTable

Open in new window


Results:

status	statusDate	startDate	cutoffDate	expectedResult	DisplayOnReport
C	2017-06-20 00:00:00.000	2017-06-19 00:00:00.000	2017-06-20 00:00:00.000	NO	NO
C	2017-06-21 00:00:00.000	2017-06-01 00:00:00.000	2017-06-20 00:00:00.000	YES	YES
C	2017-06-21 00:00:00.000	2017-06-01 00:00:00.000	2017-05-28 00:00:00.000	YES	YES
O	NULL	2017-06-01 00:00:00.000	2017-06-01 00:00:00.000	YES	YES
O	NULL	2018-06-15 00:00:00.000	2017-06-01 00:00:00.000	NO	NO

Open in new window

0
 
Kelly MartensAuthor Commented:
It would show up in the result set. If not it would be excluded. Trying your suggestion now to see if it will work. Thank you so much. I don't know where I am going wrong in how I understand the spec.
0
 
_agx_Commented:
Okay. I guess I was saying if you want to show all records, regardless of whether "Display on Report" is yes or no, then you probably wouldn't need a WHERE clause.  You'd just add a CASE to your SELECT query to return the "yes/no" as a column.
0
 
Kelly MartensAuthor Commented:
NO I have to exclude the items that would appear as a no in the excel formula. So I am trying the add column approach you suggested and then do my WHERE clause on that column (column = "YES")). Any more thoughts feel free to reach out. I am grateful.
0
 
_agx_Commented:
Okay, then I might approach it differently. Couple questions.

1. Are there any other statuses besides C an O?  
2. The actual cutoffDate is a @variable, correct?

IF CLOSED, WAS JOB OPEN ON OR BEFORE CUTOFF DATE?

IF OPEN, WAS JOB CREATED AFTER CUTOFF DATE
3.  I interpreted the Excel formula a little differently.
* IF CLOSED and StatusDate is after CutoffDate THEN 'YES' ELSE 'NO'
* IF OPEN and StartDate is equal to or before the CutoffDate THEN 'YES' ELSE 'NO'
0
 
Kelly MartensAuthor Commented:
1. Yes there are other statuses besides C and O. But C was only spec given to me originally
2. Cutoff Date is variable supplied by user to the stored procedure
3. Your guess is as good as mine at this point. I thought I had it with the SQL code I currently had shown at the beginning.
0
 
_agx_Commented:
> I thought I had it with the SQL code I currently had shown at the beginning.
Hm... actually you might be right. Let me run a few comparisons between Excel and SQL.
0
 
_agx_Commented:
Hm... in Excel if you enter a status code, and leave all the dates blank, the result is "YES". Is that what you want?
0
 
Kelly MartensAuthor Commented:
NO. He is trying to simulate how he wants the records filtered using dates as an example. He is a stakeholder who is trying to communicate specs in a technical way without understanding how we do that in SQL.
0
 
_agx_Commented:
Okay. Assuming the date columns only store dates (no time) then your original SQL code is fine. Though technically the ISNULL's aren't  needed.  NULL isn't considered equal to anything, including another NULL, so it's the same as doing this:

WHERE ([status] = 'C' AND statusDate > @cutoffDate)
OR    ([status] <> 'C' AND startDate <= @cutoffDate )

Just to clarify, here's what it'll capture

-  If the status is completed AND StatusDate is AFTER the CutoffDate
-  If the status is anything OTHER than completed AND startDate is ON or BEFORE the CutoffDate

.. and here's what it'll exclude

-  If the status is NULL
-  If the statusDate is EQUAL to CutoffDate
-  If the statusDate is NULL
-  If the startDate is NULL
0
 
Kelly MartensAuthor Commented:
SO therein lies the problem. He insists the excel formula and what I am coding are different.
0
 
Kelly MartensAuthor Commented:
I replaced the WHERE clause with your

WHERE
a.[status] = 'C' AND ISNULL(convert(varchar(10),cast(a.date_changed as date), 101),'') > @cutoffDate
            OR
a.[status] <> 'C' AND ISNULL(convert(varchar(10),cast(a.date_entered as date), 101),'')  <= @cutoffDate

And the results have changed. I don't know if it is acceptable yet but it clearly produced a different result. I am going to give you the points as soon as I figure out what answer is to give it. By the way, any guesses why your WHERE clause produced a different result then the WHERE clause I had to to begin with

a.[status] = 'C' AND ISNULL(a.date_changed, '1/1/1900') > @cutoffDate
OR
a.[status] <> 'C' and ISNULL(a.date_entered, '1/1/1900') <= @cutoffDate
0
 
_agx_Commented:
EDIT: Sorry, I was putting together a better example to show the differences of all 3 methods, side by side... See attached file.

I replaced the WHERE clause with your

Not sure if you saw my earlier comment, but ... I mentioned it works only IF the columns contain a date (not a date and time portion). If you're using convert() then unlike the sample data, the real columns must actually contain a "time", not just a date.  Obviously that makes a big difference, because the comparison will behave differently than expected.

--------------------------------------------------------------
Original post:
trying to communicate specs in a technical way without understanding how we do that in SQL.

What I like to do is create a sample table containing all possible combinations. Print out the results of the proposed logic. Let the user review it and see if they agree. Usually concrete values makes more sense to them. Makes it easier to provide specifics if they disagree with something ie "oh no, I would've expected X here because of Y".  If nothing else, it ensures I consider all possibilities.



Query:
declare @yourTable table
(
status char(1)
, statusDate datetime
, startDate datetime
, cutoffDate datetime
--, expectedResult varchar(5)
, notes varchar(500)
)
insert into @yourTable
values 
('C','6/20/2017','6/19/2017','6/20/2017', 'Completed. StatusDate EQUALS CutoffDate')
, ('C','6/21/2017','6/1/2017','6/20/2017','Completed. StatusDate LATER than CutoffDate')
, ('C','6/18/2017','6/1/2017','6/20/2017','Completed. StatusDate is BEFORE CutoffDate')
, ('C',NULL,'6/1/2017','6/20/2017','Completed. StatusDate is UNKNOWN')

, ('O',NULL,'6/1/2017','6/1/2017', 'NOT Completed. StartDate EQUALS CutoffDate')
, ('O',NULL,'6/15/2018','6/1/2017','NOT Completed. StartDate LATER than CutoffDate')
, ('O',NULL,'5/15/2018','6/1/2017','NOT Completed. StartDate BEFORE CutoffDate')
, ('O',NULL, NULL,'6/1/2017', 'NOT Completed. StartDate is UNKNOWN')

, ('D',NULL,'6/1/2017','6/1/2017', 'Random Status. StartDate EQUALS CutoffDate')
, ('D',NULL,'6/15/2018','6/1/2017', 'Random Status. StartDate LATER than CutoffDate')
, ('D',NULL,'5/15/2018','6/1/2017', 'Random Status. StartDate BEFORE CutoffDate')
, ('D',NULL, NULL,'6/1/2017', 'Random Status. StartDate is UNKNOWN')

, (NULL,'6/20/2017','6/19/2017','6/20/2017', 'UNKNOWN Status. All dates are known')
, (NULL,NULL,'6/19/2017','6/20/2017', 'UNKNOWN Status. StatusDate is UNKNOWN')
, (NULL,'6/20/2017',NULL,'6/20/2017', 'UNKNOWN Status. StartDate is UNKNOWN')


SELECT *
	, CASE WHEN ([status] = 'C' AND statusDate > cutoffDate) OR ([status] <> 'C' AND startDate <= cutoffDate ) THEN 'YES'  ELSE 'NO' END AS [WITHOUT NULL Handling]
	, CASE WHEN ([status] = 'C' AND ISNULL(statusDate, '1/1/1900') > cutoffDate) OR ([status] <> 'C' and ISNULL(startDate, '1/1/1900') <= cutoffDate) THEN 'YES' ELSE 'NO' END AS [WITH NULL Handling]
FROM  @yourTable

--- results without null handling
SELECT * 
FROM   @yourTable
WHERE ([status] = 'C' AND statusDate > cutoffDate)
OR    ([status] <> 'C' AND startDate <= cutoffDate )

--- results WITH null handling
SELECT * 
FROM   @yourTable a
WHERE (a.[status] = 'C' AND ISNULL(a.statusDate, '1/1/1900') > cutoffDate)
OR    (a.[status] <> 'C' and ISNULL(a.startDate, '1/1/1900') <= cutoffDate)

Open in new window


Result:
SQL ResultExcel_Results.xlsx
0
 
Kelly MartensAuthor Commented:
Remember to convert Datetime to date prior to comparison. Thanks so much.
0
 
_agx_Commented:
I replaced the WHERE clause with your
WHERE
a.[status] = 'C' AND ISNULL(convert(varchar(10),cast(a.date_changed as date), 101),'') > @cutoffDate
            OR
a.[status] <> 'C' AND ISNULL(convert(varchar(10),cast(a.date_entered as date), 101),'')  <= @cutoffDate

Does the real query have parenthesis? They're needed when mixing AND / OR operators or the results are unpredictable.

any guesses why your WHERE clause produced a different result then the WHERE clause I had to to begin with

It's because of nulls.  NULL is a special value. It means the value isn't known. A NULL doesn't equal anything - even itself.  So when you use it in a comparison like >=, =, etc... it never evaluates to true.  So my query excludes records where the date being compared is NULL.   Yours won't, because of the ISNULL.

Say "date_entered" is null.  My query essentially does this (never true)

     WHERE NULL <= @cutoffDate

Yours converts the null to a valid date, so does this (probably always true)

     WHERE '1900-01-01' <= @cutoffDate
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now