Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2014

    Unanswered: Syntax correct to handle same months for different years?

    Anyone out there take a look at the code and let me know how to fix it to handle the years for the same months?
    May and June. May 2014, Jun 2014, May 2015, Jun 2014

    The code is working, I think EXCEPT which year it needs to total for May and June since May and June totals twice.
    In that I think it’s confused on what year the May and June totals are.
    If you look at the 02_Collegiate query I’m not sure how it knows which year to get the figures for.
    It is cumulating and the correct months but not sure if it’s cumulating the correct year for the month.

    1) Fiscal year is July – June
    a. So right now fiscal year is officially July 2014 – June 2015
    b. But needs to show cumulative totals from May 2014 and total it each month until the end of June 2015.
    i. Because payments for future season (enddate field) are received starting May and they need to show those invoice counts in the next fiscal year although the payments are received in the current fiscal year or now that it’s August, last fiscal year.
    ii. That said you can see how it has to deal with May 2014 and June 2014 AND May 2015 and June 2015 when those months and year come.
    iii. May 2014, Jun 2014, Jul 2014 into Jul column
    iv. May 2014-Aug 2014 in Aug column
    v. May 2014-Sep 2014 in Sep column, etc … until
    vi. May 2014-May 2015 in May column <-- this is where I need to verify the code below is correct on handling the years for the month of May
    vii. May 2014-Jun 2015 in Jun column <-- this is where I need to verify the code below is correct on handling the years for the month of May

    So when looking at the first month Jul column: The code needs to total May and Jun and Jul of last FISCAL year if it’s < then July 2014, if it’s July 2014 or greater it should cumulate the total of this year May and June.
    IIf(Sum(IIf((DatePart("m",[PaymentDate])>=5 Or DatePart("m",[PaymentDate])<=7) And DatePart("yyyy",[paymentdate])=(DatePart("yyyy",Date())-1),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And DatePart("m",[PaymentDate])<=7,1,0))) AS Jul
    And the last two columns May and Jun: The code needs to total May – May (1 year span, last May through this May which is May 2014 – May 2015 if it’s July this year or greater.
    IIf(Sum(IIf(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])=6,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=6,1,0))) AS Jun
    With the payment date parameter of:
    ((dbo_v030mbrshp02Collegiates.EndDate) Between IIf(Month(Date())<=7,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+1,6,30)) And ((dbo_v030mbrshp02Collegiates.PaymentDate) Between IIf(Month(Date())<=7,DateSerial(Year(Date())-4,5,1),DateSerial(Year(Date())-3,5,1)) And IIf(Month(Date())<=7,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+3,6,30))))
    NOTE: the EndDate (Season) for the Collegiate is 4 year memberships, so it would show, now that it's August and in the new fiscal year, 2015-2018 for the season column as can be seen in the example.
    And payments are each year.

    So come next July (July 2015) it would flip into the next fiscal year and show seasons 2016-2019.

    I’m not sure if it’s correct because, if you look at Season ending in 2018 (Year([EndDate])), it has Null value in the 1st month Jul (which is 2014 but having the totals from May 2014-Jul-2014) and a value in the last month Jun (which is for Jun 2015 and would be totals from May 2014-June 2015)
    MemberType Season Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun
    Collegiate 2015 553 664 735 818 860 888 916 934 948 953 958 958
    Collegiate 2016 523 620 721 800 825 839 859 877 890 892 894 894
    Collegiate 2017 539 624 695 816 852 866 898 918 928 938 944 944
    Collegiate 2018
    648 648 648 648 648 648 648

    I don't know why the Season ending 2018 has the 1st column Jul blank and has the value in the last column Jun?

    Since the 1st column Jul should be from may 2014 - july 2014 and the last column is the total from May 2014 - Jun 2015

  2. #2
    Join Date
    Apr 2014
    I think I've got it.

    IF anyone can see an error, please let me know, would appreciate feedback.

    And to those that might need something like this. I see a lot of rolling months questions

    SELECT dbo_v030mbrshp02Collegiates.MemberType, dbo_v030mbrshp02Collegiates.EndYear AS 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 dbo_v030mbrshp02Collegiates
    WHERE (((dbo_v030mbrshp02Collegiates.MemberTypeID)=3) AND ((dbo_v030mbrshp02Collegiates.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 ((dbo_v030mbrshp02Collegiates.PaymentDate) Between IIf(Month(Date())<=7,DateSerial(Year(Date())-4,5,1),DateSerial(Year(Date())-3,5,1)) And IIf(Month(Date())<=7,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+3,6,30))))
    GROUP BY dbo_v030mbrshp02Collegiates.MemberType, dbo_v030mbrshp02Collegiates.EndYear;

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    Takign a step back from the detail...

    what Id normally expect for this sort of reequirement is a tabke that defines financial periods, usually its something like:-
    FYear Integer   } composite PK of Fyear and FPeriod
    FPeriod Integer }
    PeriodStart date
    PeriodEnd date dont need both, but if you do then you create a task to make certain that you don't having missing days or overlapping days. a single date means you don't need to worry about such things

    storing data in the spreadhseet way (ie 12 columns each representing a month) isn't the 'database way'. it can cause problems down the line. instead store the value against a specific year/month key.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2014
    I'm not storing anything.

    I'm trying to pull the data in the database to report it for the fiscal year however actually reporting 15 months but in 12 month columns

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts