CASE statement in WHERE clause considering a fiscal year 2

[smatthews]

Hello,

I am trying to create a query with a case statement with a where clause to take into account a fiscal year from 10/1/yyyy to 09/30/yyyy. For example, If the getdate month is between 1 and 6 then I need to pull people with the paid thru of 9/30/ + the current year. If the getdate month is between 7 and 12 then I would need the selection to pull people with the paid thru of the 9/30 + the following year. Using SQL 2005. Any help would be greatly appreciated.

Thanks

Smatthews

 

[gmmastros]

This can certainly be done, but it will also be very messy and ultimately hard to debug.

Instead, I would suggest that you add another (very small) table to your database. This table could have something like:

ReportStartDate, ReportEndDate, FiscalStartDate, FiscalEndDate
1/1/2013,7/1/2013,10/1/2012,9/1/2013

Populate a bunch of rows in this table.

Then you can easily get your Fiscal Start and End dates from this table.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[smatthews]

Sorry I'm not sure how I would apply this. Can you explain further?

 

[gmmastros]

Can you show some sample data and expected results. Then I will show you how this would fit in.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[smatthews]

I think I am way off base here but I can't seem to wrap my head around this one; here is the code, paid thru date is currently always 09/30/2013 or 09/30/2014

select id, paid_thru, getdate()as today
from name
where datepart(mm,getdate())between 1 and 6 and datepart (yyyy,paid_thru)+1 = datepart(yyyy,getdate())
or
datepart(mm,getdate()) between 7 and 12 and datepart (yyyy,paid_thru) = datepart(yyyy,getdate())

This query is only giving me the 09/30/2013, I need the 09/30/2013 and 09/30/2014 paid thru.

Thanks!

 

[gmmastros]

Wait a minute. Let me make sure I understand this.

If today is greater than (or equal to) July 1, 2013 and less than or equal to June 30, 2014, you want rows where paid_thru is 9/30/2013.

If today is greater than or equal to July 1, 2014 and less than or equal to June 30, 2014, you want rows where pass_thru is 9/30/2014.

Is this correct?



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[smatthews]

Sorry for the confusion, I think I need to explain rather than using the hard dates. I was giving you our fiscal year which is 10/1 to 9/30 but our chapter year runs from 7/1 to 6/30 and that is what I should have told you originally. I understand now why the dates weren't making sense.

If someone joins between July 1st and December 31st then the paid thru date is in the next calendar year, for example it is still 2013 but they have a paid thru of 09/30/2014, I need this group.
If someone joins between January 1st and June 30th then the paid thru date is in the current calendar year, for example now it is 2014 and they have a paid thru of 09/30/2014, I also need this group.

I am trying to avoid hardcoding paid thru dates

I hope this makes more sense.

Thanks for hanging in on this one.

 

[smatthews]

Bumping this to the top again hoping that someone can help me or maybe someone has a better way to handle this issue.

Thanks,
Smatthews

 

[RyanEK]

This query is only giving me the 09/30/2013, I need the 09/30/2013 and 09/30/2014 paid thru."

Your query looks correct. Why are you expecting 09/30/2014 when by your definition, it should only return 09/30/2013 when the current month is 11?

 

[smatthews]

There was a flaw in my logic. I needed the current and next year if the month was between 7 and 12 so I changed the query to >= datepart(yyyy,getdate()) for that line.

Thanks RyanEK

Smatthews

 

[gk53]

try this

declare @fromDt as date, @thruDt as date
if DATEPART(month,getdate()) < 10
	select @fromDt = '1/1/' + cast(datepart(year, GETDATE()) as varchar(4)),
		   @thruDt = GETDATE()
else
	select @fromDt = '10/1/' + cast(datepart(year, GETDATE()) as varchar(4)),
		   @thruDt = GETDATE()

select id, paid_thru, getdate()as today
from name
where paid_thru  between @fromDt and @thruDt