SQLTeam.com | Weblogs | Forums

CASE WHEN result?


#1

Hello,
Can someone tell me what result this produces?
I am getting no records.

CASE WHEN MONTH(GETDATE()) <= 7 THEN DATEFROMPARTS(YEAR(GETDATE()) , 6 , 30) ELSE DATEFROMPARTS(YEAR(GETDATE()) + 1 , 6 , 30) END OR IS NULL

I want to see all records with EndDates as specified in the criteria but it is returning no results.
However, before 2017 I was getting results.

I think that it's saying to display records where the EndDates are less than July ending 6/30/ of current year otherwise display where the EndDates are in the future year ending 6/30/ next year
So if today is less than the month of July it looks at EndDates with 6/30/2017 otherwise looks at EndDates with 6/30/2018 or if it’s NULL


#2

The fragment you posted is doing what you described.

If you are using that expression in a filter condition such as a where clause, you are not getting any records likely because the filter condition is removing any records that may be there. Without seeing your full query, hard to tell or be more specific.


#3

Hi JamesK,
It's against a field called EndDate (expiration date of a membership)

Memberships are from 7/1/## to 6/30/##, which is a one year membership.

What I don't understand is, it was working in 2016 and now that it's 2017, it's not returning any records when there are many records (memberships) ending in 6/30/2018.

Expectation is to see all records with a payment date range of whatever the user inputs in Excel or CRW or whatever external file linked to the SQL View having the EndDate clause of membership records.

Example: payment date range of 12/1/2016 through 1/5/2017 should be producing membership records with EndDates on 6/30/2017. And once July 2017 comes it'll produce records with EndDates on 6/30/2018.