Date Range Parameter - Confused

[aellistechsupport]

Currently have this:

SELECT dbo_v030mbrshp02Collegiates.MemberType, dbo_v030mbrshp02Collegiates.MembershipNumber, dbo_v030mbrshp02Collegiates.EndYear AS Season, dbo_v030mbrshp02Collegiates.PaymentDate, dbo_v030mbrshp02Collegiates.EndDate
FROM dbo_v030mbrshp02Collegiates
WHERE (((dbo_v030mbrshp02Collegiates.MemberTypeID)=8))
GROUP BY dbo_v030mbrshp02Collegiates.MemberType, dbo_v030mbrshp02Collegiates.MembershipNumber, dbo_v030mbrshp02Collegiates.EndYear, dbo_v030mbrshp02Collegiates.PaymentDate, dbo_v030mbrshp02Collegiates.EndDate
HAVING (((dbo_v030mbrshp02Collegiates.PaymentDate) Between IIf(Month(Date())<=7,DateSerial(Year(Date())-4,6,1),DateSerial(Year(Date())-3,6,1)) And IIf(Month(Date())<=7,DateSerial(Year(Date()),Month(Date()),0),DateSerial(Year(Date()),Month(Date()),0))) AND ((dbo_v030mbrshp02Collegiates.EndDate) Between IIf(Month(Date())<=7,DateSerial(Year(Date()),6,1),DateSerial(Year(Date())+3,6,1)) And IIf(Month(Date())<=7,DateSerial(Year(Date())+3,6,30),DateSerial(Year(Date())+4,6,30))))
ORDER BY dbo_v030mbrshp02Collegiates.EndYear, dbo_v030mbrshp02Collegiates.PaymentDate, dbo_v030mbrshp02Collegiates.EndDate;

Which is to show total of memberships where the memberships end next year and 4 years out.

It was working until August report.
Now it's only showing memberships ending in 2018 and 2019. It should be also be showing 2016 and 2017.

In July when the report was ran it showed
2015
2016


2017
2018

which is what it should do. Now that it's August it should show
2016
2017
2018
2019

Anyone know what I have incorrect?

[June7]

So you want to filter based on a fiscal year, not calendar year?

What is EndYear field for? Why don't you apply filter to that field?

Why are you using GROUP BY when there are no aggregate calcs?

[aellistechsupport]

EndYear is the year their membership ends

That is what needs to show for four years.

These particular memberships are good for 4 years.

They want to see how many memberships ending in the current year + 4.
Since memberships are good for 4 years the PaymenDate would go back a few years for the memberships ending this year and so on.

Not sure why it is only showing 2018 and 2019 since the fiscal year started.
Last month it showed all four years like it should.

So something in the formula isn't quite right that it would show only 2 years after the new fiscal year started whereas it was showing 4 years as it should before the new fiscal year month started.

Hope that made sense.

[June7]

Why are you filtering on PaymentDate?

Is EndYear the fiscal year?

EndYear BETWEEN Year(Date()) + IIf(Month(Date())>7, 1, 0) AND Year(Date()) + IIf(Month(Date())>7, 1, 0) + 3

Why using GROUP BY when there are no aggregate calcs?

[aellistechsupport]

Need to limit the PaymentDate from 4 years ago as well from the start of the fiscal year 4 years ago.

That said, PaymentDate filter is the fiscal year.

The EndYear is only when those PAID memberships end.

I'll try your formula, thank you.

The Group is because the table with the data set has membership numbers and need the total for each month.

[June7]

Is EndYear the fiscal year or calendar year? So if their membership ends in August 2015, would EndYear show 2015 or 2016?

But there are no aggregate calcs in that query.

Consider building a report and doing aggregate calcs in report design.

[aellistechsupport]

Hi june7,
sonyiur formula did produce 4 years as hoped.
however what happens in Jan 2016?

it says greater than July?

thank you

oh Report is not an option unfortunately. This is one part of 8 other parts

[June7]

> July because I thought the fiscal year break is July 31.

Don't see any issue with January 2016. If you run query on January 1 then you should get fiscal years 2016-2019, just as if you had run the query on December 31.

The criteria filters on the EndYear value, month is irrelevant.

[aellistechsupport]

 Month(Date())>7, 1, 0)

I get that the 1st part is saying greater than month 7 (July), what is the 1, 0

[June7]

Those are the two options the IIf() will return depending on the condition being true or false. The IIf will add either 1 or 0 to the current year to arrive and the fiscal year.

Year(Date()) + IIf(Month(Date())>7, 1, 0)

[aellistechsupport]

Ok I think I understand, so if it's greater than July it'll be this year (1) otherwise it'll show future year (0)?

[June7]

Not quite, you have it backwards.

If month is greater than July it will ADD 1 to the current year, otherwise ADD 0. This is to determine the fiscal year. So 8/1/2014 through 7/31/2015 will be captured as fiscal year 2015. If you prefer it to be considered fiscal year 2014, then swap the positions of 1 and 0 in the expression.

[aellistechsupport]

Thank you so much for clarifying and your help!

[aellistechsupport]

Hi June7,
Is there another way to write your suggestion in SQL View?

I tried this but getting an Invalid or missing expression:

BETWEEN Year(GetDate()) + IIf(Month(GetDate())>7, 1, 0) AND Year(GetDate()) + IIf(Month(GetDate())>7, 1, 0) + 3

[June7]

Why are you using GetDate()? Why not Date()?