Cumulative Totals over a period of time?

[aellistechsupport]

I need to modify this code I received help on last year to show ALL of the prior years payments for the current Seasons.
It seems to be leaving out the current month for the prior years and it should include it since it's already past.

SELECT [02_Collegiate_Details].MemberType, [02_Collegiate_Details].Season, IIf(Sum(IIf((DatePart("m",[PaymentDate])>=5 Or DatePart("m",[PaymentDate])<=7) And DatePart("m",[paymentdate])<=7,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And DatePart("m",[PaymentDate])<=7,1,0))) AS Jul, IIf(Sum(IIf(DatePart("m",[paymentdate])>=5 And 7 And DatePart("m",[paymentdate])<=8,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=5 And 7 And DatePart("m",[paymentdate])<=8,1,0))) AS Aug, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=9,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=9,1,0))) AS Sep, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=10,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=10,1,0))) AS Oct, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=11,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=11,1,0))) AS Nov, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7,1,0))) AS [Dec], IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=1,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=1,1,0))) AS Jan, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=2,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=2,1,0))) AS Feb, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=3,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=3,1,0))) AS Mar, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=4,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=4,1,0))) AS Apr, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=5,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=5,1,0))) AS May, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=6,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=6,1,0))) AS Jun
FROM 02_Collegiate_Details
GROUP BY [02_Collegiate_Details].MemberType, [02_Collegiate_Details].Season;

Here is the summary. So All payments from Jun 2011 from Season 2015 through Mar 2015 to Season 2018 where the Mar column totals 3834. But only shows 3817.

Please see attached details. Collegiate.zip

MemberType	Season	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Collegiate	2015	549	660	731	814	861	889	917	935	949	954	954	954
Collegiate	2016	520	617	716	795	822	836	856	874	887	889	889	889
Collegiate	2017	537	622	693	813	855	869	901	921	931	941	941	941
Collegiate	2018	628	721	826	888	936	972	1020	1042	1050	1050	1050	1050

It seems, when I looked at the details and all the Apr totals for the prior years are being excluded. These 17 out of the 3834 and while it only says there are 3817. However, these 17 records, since it's past, should be included.

MemberType	GrandTotal	ReportingMonth
Collegiate	3834	Mar

SELECT [02_Collegiate_Details].MemberType, Count([02_Collegiate_Details].membershipnumber) AS ReportingMonth, DateSerial(Year(Date()),Month(Date()),0) AS LastMonth
FROM 02_Collegiate_Details
WHERE ((([02_Collegiate_Details].EndDate) Between IIf(Month(Date())<=7,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+1,6,30)) And IIf(Month(Date())<=7,DateSerial(Year(Date())+3,6,30),DateSerial(Year(Date())+4,6,30))) AND (([02_Collegiate_Details].PaymentDate) Between IIf(Month(Date())<=7,DateSerial(Year(Date())-4,6,1),DateSerial(Year(Date()),6,1)) And IIf(Month(Date())<=7,DateSerial(Year(Date()),Month(Date()),0),DateSerial(Year(Date())+3,Month(Date()),0))))

Please advise.

Collegiate	2015										1	1	1	01-Apr-12	942501
Collegiate	2015										1	1	1	06-Apr-12	1395682
Collegiate	2015										1	1	1	08-Apr-12	1486428
Collegiate	2015										1	1	1	17-Apr-12	6007168
Collegiate	2015										1	1	1	27-Apr-12	8762208
Collegiate	2016										1	1	1	20-Apr-13	1040804
Collegiate	2016										1	1	1	30-Apr-13	924553
Collegiate	2017										1	1	1	03-Apr-14	1482073
Collegiate	2017										1	1	1	03-Apr-14	6303630
Collegiate	2017										1	1	1	11-Apr-14	1438341
Collegiate	2017										1	1	1	12-Apr-14	1537156
Collegiate	2017										1	1	1	13-Apr-14	1126299
Collegiate	2017										1	1	1	14-Apr-14	6323641
Collegiate	2017										1	1	1	23-Apr-14	1063631
Collegiate	2017										1	1	1	23-Apr-14	1063633
Collegiate	2017										1	1	1	23-Apr-14	1063707
Collegiate	2017										1	1	1	23-Apr-14	1063708

Otherwise perhaps change the detail query

SELECT dbo_v030mbrshp02Collegiates.MemberType, dbo_v030mbrshp02Collegiates.EndYear AS Season, dbo_v030mbrshp02Collegiates.MembershipNumber, dbo_v030mbrshp02Collegiates.PaymentDate, dbo_v030mbrshp02Collegiates.EndDate
FROM dbo_v030mbrshp02Collegiates
WHERE (((dbo_v030mbrshp02Collegiates.MemberTypeID)=3))
GROUP BY dbo_v030mbrshp02Collegiates.MemberType, dbo_v030mbrshp02Collegiates.EndYear, dbo_v030mbrshp02Collegiates.MembershipNumber, 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())+3,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;

to exclude the prior years current month data set? Since we're reporting up to the months prior to the current month?

[ranman256]

(instead of 1 single query)
I have a macro, that runs a series of queries, based on a form's date range I select.
Each query fills data into a 'report' table
The Last query is a crosstab, that pulls the data from my report table,into the one like yours shown.

[aellistechsupport]

How does that resolve the issue with the dates?

[aellistechsupport]

I can change the code to this, for example Mar to include Apr? Or will this include the current year Apr? As that still needs to be excluded, only that the prior year Apr SHOULD be included.

Though not sure IF this is the correct way of fixing this issue?

Mar: IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=4,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=4,1,0)))

[ItsMe]

I don't think I have any SQL in any of my DB's that looks similar to that.

[aellistechsupport]

How about, how to modify this to EXCLUDE current month in any year with the parameter?

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())+3,Month(Date()),0)))

OR leave this code as is but another to say the PaymentDate is not equal to current month?

[ItsMe]

For me, I do not approach Periods this way. I use tables that assign integers to each period and User Defined Functions to refer to the tables. Whatever that period may be (Month, Week, whatever) the function will use an integer to calculate. With this, calculating 15 vs. 12 or 4 vs. 5 is not an issue. I have not found a built in function that provides the flexibility required.

[rpeare]

I remember having a lot of reservations when you originally posted a year ago precisely because you were trying to display a ton of prior year data on a 'current' year record.

With the query I gave you you wanted a cumulative total as the months progressed leading to the total number of tickets sold that would be in your JUN column. If you total those they equal your 3834 records.

I remember thinking at the time this was a fundamentally flawed approach because you are compressing multiple years into a single year based on the month so if you were to look at this data based on not only season but year of purchase you would see this (just for season 2015):

MemberType	Season	PayYear	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Collegiate	2015	2011	0	0	0	0	0	265	272	101	67	83	47	28
Collegiate	2015	2012	28	18	14	5	3	1	1	8	3	0	0	0
Collegiate	2015	2013	0	0	0	0	2	2	3	2	1	0	0	0

Note the sales go from a fiscal year (I think your original problem was your fiscal year was from july to june) 2011 (sales in jun of 2011) through fiscal year 2014 (september 2013)

This is really the accurate representation of your sales by month but it is not really workable to expect to have 36 columns of activity (most of your data is spread over 3 years or slightly more)

I would recommend you show THIS query with a summation at the bottom on a report showing the total sales by month.

This is what your query is currently giving for the 2015 season:

MemberType	Season	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Collegiate	2015	549	660	731	814	861	889	917	935	949	954	954	954

These are the numbers your query is currently giving, and though the final number is right (954 in june) the formulas within each of these is really screwed because you are using >= with an AND (>= 5 and 7) operator when the original query I gave you did not. On top of that the 'starting' month (july) does not match the actual starting date of sales (june) because it's in a prior fiscal year.

This is the query I used:

SELECT [02_Collegiate_Details].MemberType, [02_Collegiate_Details].Season, Sum(IIf(DatePart("m",[paymentdate])=7,1,0)) AS Jul, Sum(IIf(DatePart("m",[paymentdate]) Between 7 And 8,1,0)) AS Aug, Sum(IIf(DatePart("m",[paymentdate]) Between 7 And 9,1,0)) AS Sep, Sum(IIf(DatePart("m",[paymentdate]) Between 7 And 10,1,0)) AS Oct, Sum(IIf(DatePart("m",[paymentdate]) Between 7 And 11,1,0)) AS Nov, Sum(IIf(DatePart("m",[paymentdate]) Between 7 And 12,1,0)) AS [Dec], Sum(IIf(DatePart("m",[paymentdate]) Between 7 And 12 Or DatePart("m",[paymentdate])=1,1,0)) AS Jan, Sum(IIf(DatePart("m",[paymentdate]) Between 7 And 12 Or DatePart("m",[paymentdate]) Between 1 And 2,1,0)) AS Feb, Sum(IIf(DatePart("m",[paymentdate]) Between 7 And 12 Or DatePart("m",[paymentdate]) Between 1 And 3,1,0)) AS Mar, Sum(IIf(DatePart("m",[paymentdate]) Between 7 And 12 Or DatePart("m",[paymentdate]) Between 1 And 4,1,0)) AS Apr, Sum(IIf(DatePart("m",[paymentdate]) Between 7 And 12 Or DatePart("m",[paymentdate]) Between 1 And 5,1,0)) AS May, Sum(IIf(DatePart("m",[paymentdate]) Between 7 And 12 Or DatePart("m",[paymentdate]) Between 1 And 6,1,0)) AS Jun
FROM 02_Collegiate_Details
GROUP BY [02_Collegiate_Details].MemberType, [02_Collegiate_Details].Season;

this is my result:

MemberType	Season	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Collegiate	2015	276	387	458	541	588	616	644	662	676	681	686	954
Collegiate	2016	239	336	435	514	541	555	575	593	606	608	610	889
Collegiate	2017	243	328	399	519	561	575	607	627	637	647	653	941
Collegiate	2018	269	362	467	529	577	613	661	683	691	691	691	1050

Again note the last column equals the correct number of records.

[aellistechsupport]

Yes and no ...
so the Jun column should really show in Mar.

Although the actual fiscal year is Jul - Jun the first column is a total of payments received for the future season starting in May, why the >=5 And 7 came into play. All payments starting in May for future EndDate (season) through July needs to show in Jul (1st month of the reporting fiscal year) then cumulative total the following months.

Thanks again for all your help last year. With what you provided and some modifications, everything is actually working pretty well.

Only issue now is, should the cumulative totals exclude prior years current month data?

For instance, if you look at details, all of the Apr data from the prior years are not included.
I'm trying to see if that is right. One sense it is, if we're saying I want to see the cumulative totals upto last month in all the 4 years. If not then the current months of the prior years should in fact be included in the cumulative totals.

Thoughts?

[aellistechsupport]

Also, by chance would you know why the change in the date format on the SQL View, being now ####-##-## is no longer running on the MS Access side. So I'm getting nothing now with the parameters from the refreshed link to the SQL View.

Was working all of last year til last night. But after refreshing the links it's no longer working

[rpeare]

Yes and no ...
so the Jun column should really show in Mar.

Although the actual fiscal year is Jul - Jun the first column is a total of payments received for the future season starting in May, why the >=5 And 7 came into play. All payments starting in May for future EndDate (season) through July needs to show in Jul (1st month of the reporting fiscal year) then cumulative total the following months.

Thanks again for all your help last year. With what you provided and some modifications, everything is actually working pretty well.

Only issue now is, should the cumulative totals exclude prior years current month data?

For instance, if you look at details, all of the Apr data from the prior years are not included.
I'm trying to see if that is right. One sense it is, if we're saying I want to see the cumulative totals up to last month in all the 4 years. If not then the current months of the prior years should in fact be included in the cumulative totals.

Thoughts?

This doesn't make sense in terms of your query if you want to continue to show jul as the first column. If you want to alter the formula so that may starts first you can do that but be aware that in the example I gave you you would show 5 sales in may, but they would both be from SUBSEQUENT calendar years NOT the initial sales year. That is the problem with this query you are trying to compress multiple years into a single year but you somehow expect to have 'special' formulas for the first year (i.e. summing the first year's july total to include may and june but not from subsequent years)

So for this example, assuming you want to keep the ORDER of the months the same (show july first) for the given SEASON (2015) you want to show may, june and july of 2011 in the JULY column, and exclude the may and june 2011 sales from the may and june columns?

[aellistechsupport]

Yes, that's a ... pickle.

Though I thought that was resolved

So the 4 seasons needing to report on:
1. All payments from May 2011 - July 2011 should be in the Jul column with Season ending in 2015
2. All payments from May 2012 - July 2012 should be in the Jul column with Season ending in 2016
3. All payments from May 2013 - July 2014 should be in the Jul column with Season ending in 2017
4. All payments from May 2014 - July 2015 should be in the Jul column with Season ending in 2018

Seemingly this is correct where the cumulative total in the Jun column here starts from Apr. Apr, May, Jun are the same numbers until beginning of next month, May, when the data set for up to Apr is ran.

MemberType	Season	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Collegiate	2015												954
Collegiate	2016												889
Collegiate	2017												941
Collegiate	2018												1050

NOTE: I think that there is minimal if any payments in May and June for the current Season. It's typically for the future Season and why, if there are any payments, won't show because those payments would be for the future Season anyway.

[rpeare]

why are your critera for 1 and 2 (may through july of the same year) different than the criteria for 3 and 4 (may of one year through july of the subsequent year) or is this a typo.

Should it be may - july of the (season - 4) year?

[aellistechsupport]

Sorry, typo

So the 4 seasons needing to report on:
1. All payments from May 2011 - July 2011 should be in the Jul column with Season ending in 2015
2. All payments from May 2012 - July 2013 should be in the Jul column with Season ending in 2016
3. All payments from May 2013 - July 2014 should be in the Jul column with Season ending in 2017
4. All payments from May 2014 - July 2015 should be in the Jul column with Season ending in 2018

Where it's May of last year to Jun of this year for the future Season. May 2011 - Jun 2012 ending in 2015 reporting in columns Jul - Jun where Jul consists of May and Jun 2011. Typically won't be any payments in May and Jun 2012 for Season ending in 2015. Those payments would be for Season ending in 2016.

[rpeare]

Typically won't be any payments in May and Jun 2012

This is false, every year you have in your example data has sales in the year after the initial burst in may or june or both, that's what I've been saying. You don't have many, that's for sure, but you do have some and to dump everything into the month of sale is not necessarily accurate nor does your data always bear out the way you think.

For instance for season 2017 you have a sales record in november of 2012 which is far prior to your May 2013 expected date.

You would be better off saying

If the sales date is prior to 7/1/(season - 4) put the sales in july
Otherwise
put the sale in the appropriate month

to do that the formula would be:

Jul: Sum(IIf((DatePart("m",[paymentdate])=7 And [paymentdate]>=DateSerial(([season]-4),7,1)) Or ([paymentdate]<DateSerial(([season]-4),7,1)),1,0))

then switch the bold portion of the formula to the 'between' statement for the remaining months