Syntax? Sum on Iif statement based on date?

[June7]

Post the exact full SQL statement you are using. Or provide db for analysis.

[aellistechsupport]

Think I have to do this:

TRANSFORM Sum(Comparison.ReportingMonth) AS Totals
SELECT Comparison.SortOrder, Comparison.MemberGroup, Comparison.Type
FROM Comparison
WHERE (((Comparison.CreateDate)=DateSerial(Year(Date())-1,Month(Date()),0) Or (Comparison.CreateDate)=DateSerial(Year(Date()),Month(Date()),0)))
GROUP BY Comparison.SortOrder, Comparison.MemberGroup, Comparison.Type
ORDER BY Comparison.SortOrder
PIVOT IIf(Year([CreateDate])=Year(Date()),"ThisYear","LastYear");

Just NOT sure how

 PIVOT IIf(Year([CreateDate])=Year(Date()),"ThisYear","LastYear")

will handle when there's more than 2 years in the table?

[aellistechsupport]

I think this should work?

DateSerial(Year(Date())-1,Month(Date()),0) Or DateSerial(Year(Date()),Month(Date()),0)

Limits last year last month and this year this month.

I'm not sure if it'll handle it right when January comes?

[June7]

For the WHERE?

Your expression works for me. The return is:

4/30/2013 Or 4/30/2014

But shouldn't that use BETWEEN AND operator? Or format the criteria to YYYYMM?

I commented about the PIVOT in post 11.

[aellistechsupport]

Ok good, yes for the WHERE

DateSerial(Year(Date())-1,Month(Date()),0) Or DateSerial(Year(Date()),Month(Date()),0)

If this addresses December 2013 and December 2014 in January 2015 I'm good

[June7]

It should but the criteria is limited to a single day for each year:

4/30/2013
4/30/2014

[aellistechsupport]

?
I thought DateSerial(Year(Date()),Month(Date()),0), meant last month of this year.
All of last month?

If not is there a simple way to write entire month of last month of this year and entire month of last month of last year?

I've been trying to figure out how to say this month in any year or rather last month in any year.

Instead of Between 4/1/13 and 4/30/13 and Between 4/1/14 and 4/30/14.

[June7]

Your criteria as structured is to return all records dated 4/30/2013 OR 4/30/2014, only for those exact days.

That is why my suggestions all used Format function to construct YYYYMM criteria.

If you want to pull for all years then just use criteria of the month

Month([field]) = Month(DateSerial(Year(Date()),Month(Date()),0)

[aellistechsupport]

How about on the queries where there is no date field in the table to use and is using system date or "live" date.

So Now() or Date() or Month(Date())?

Will

 DateSerial(Year(Date()),Month(Date()),0)

show December 2013 in January 2014?

[June7]

If there is no date field then there is no way to filter records based date parameters.


Yes, that calculation should work for crossing years. Can test it in the VBA immediate window by typing:

?DateSerial(Year(#1/1/2015#),Month(#1/1/2015#),0)

[aellistechsupport]

Great! Did not know that Re: testing in VBA immediate window. Sadly I don't know how to do that. eh.
Unfortunately that's the level I'm at...



Do you know what happens with this code when this June and July comes around?

Between DateAdd("yyyy",-1,DateSerial(Year(Date()),6,1)) And DateSerial(Year(Date()),6,30)

Full code:

SELECT dbo_v030mbrshp01PdMembers.MemberGroup, dbo_v030mbrshp01PdMembers.MemberType, IIf(Sum(IIf((DatePart("m",[PaymentDate])=6 Or DatePart("m",[PaymentDate])=7) And DatePart("yyyy",[paymentdate])=(DatePart("yyyy",Date())-1),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And DatePart("m",[PaymentDate])<=7,1,0))) AS Jul, IIf(Sum(IIf(DatePart("m",[paymentdate])>=6 And 7 And DatePart("m",[paymentdate])<=8,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=6 And 7 And DatePart("m",[paymentdate])<=8,1,0))) AS Aug, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=9,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=9,1,0))) AS Sep, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=10,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=10,1,0))) AS Oct, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=11,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=11,1,0))) AS Nov, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7,1,0))) AS [Dec], IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=1,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=1,1,0))) AS Jan, IIf(Sum(IIf(DatePart("m",[PaymentDate])=2,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=2,1,0))) AS Feb, IIf(Sum(IIf(DatePart("m",[PaymentDate])=3,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=3,1,0))) AS Mar, IIf(Sum(IIf(DatePart("m",[PaymentDate])=4,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=4,1,0))) AS Apr, IIf(Sum(IIf(DatePart("m",[PaymentDate])=5,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=5,1,0))) AS May, IIf(Sum(IIf(DatePart("m",[PaymentDate])=6 And DatePart("yyyy",[paymentdate])=DatePart("yyyy",Date()),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=6,1,0))) AS Jun
FROM dbo_v030mbrshp01PdMembers
WHERE (((dbo_v030mbrshp01PdMembers.PaymentDate) Between DateAdd("yyyy",-1,DateSerial(Year(Date()),6,1)) And DateSerial(Year(Date()),6,30)) AND ((dbo_v030mbrshp01PdMembers.MemberTypeID)=1 Or (dbo_v030mbrshp01PdMembers.MemberTypeID)=2 Or (dbo_v030mbrshp01PdMembers.MemberTypeID)=4) AND ((dbo_v030mbrshp01PdMembers.EndYear)>=Year(Date())))
GROUP BY dbo_v030mbrshp01PdMembers.MemberGroup, dbo_v030mbrshp01PdMembers.MemberType, dbo_v030mbrshp01PdMembers.MemberTypeID
ORDER BY dbo_v030mbrshp01PdMembers.MemberTypeID;

[June7]

That should construct criteria:

BETWEEN 6/1/2013 AND 6/30/2014

Shouldn't that be 7 in the first date?

[aellistechsupport]

Ok, that's what I was afraid of as I'm getting more familiar with what these date functions are doing.

Would you know how to change the date functions (see entire code) plus that WHERE part so that it will dynamically update the fiscal year columns JUL-JUN?

1. Fiscal year is prior year July through current year June
2. HOWEVER prior year July will include prior year June in the July column "JUL", as you might see in the code.
3. Right now the codes in place works for the current fiscal year. HOWEVER when this July comes it needs to start again for the new fiscal year. Which is July 2014 - June 2015.

So right now my view looks like this from the current code:

MemberGroup	MemberType	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Regular Member	First Family	23551	25862	27507	29278	30125	30454	30834	31167	31374	31517	31536
Regular Member	Subsequent Family	11145	12354	13071	13942	14386	14561	14754	14945	15098	15216	15230
Regular Member	Introductory	1736	2254	2687	3194	3505	3670	3944	4181	4413	4569	4592

4. But when July comes in the current year it should ALL be blank.
5. That said, how to change it also to not show May yet. In the current month it should only report from prior June data accumulating each month until prior month. In today's case report only up until May?

[June7]

I am confused by what you want to produce. Seems originally the requirement was for only the previous month of data and the same month of prior year. But do you actually want the fiscal year to date?

Not sure what else I can offer. Use date/time functions to calculate the desired date parameters.

If you don't want May to show then don't include that expression in query or adjust the date range filter. Or build report and in textbox have an expression that suppresses display of data for current month.

[aellistechsupport]

Yes, it's a different question.

Original post was to find the statement to only produce the prior month of the current year and same month in the prior year.

For instance we're in May and I need all of last month, April and all of last year's April.

I started a new one for the last question I added here. sorry about that.