Calculating Date Periods

[jakebur01]

I have a table with Territory Representative Sales.  It has columns that go back 26 months.  The columns are SALES_OLDEST_PD_1, SALES_PD_2, SALES_PD_3, ..... SALES_PD_25, SALES_LAST_PD_26, & SALES_CURR_PD_27.  SALES_CURR_PD_27 is the current month.

 

I want to build a table with "Last Year To Date Sales" (LYTD), "Current Year To Date Sales" (CYTD), (May 2011), and (May 2010).  I know that (May 2011) will be SALES_CURR_PD_27 and (May 2010) will be SALES_PD_15.

 

The trick is that the sales year starts in November.  So, I need to figure out a way to match the table columns with "Nov. 09 - May. 10 TOTAL" for (LYTD) and "Nov. 10 - May. 2011 TOTAL" for (CYTD). I am familiar with using mktime() to subtract months.  But, I am not sure where to start yet on this project.

 

Jake

while (odbc_fetch_row($rs))
  {
$M1 += odbc_result($rs,"SALES_OLDEST_PD1");
$M2 += odbc_result($rs,"SALES_PD2");
$M3 += odbc_result($rs,"SALES_PD3");
$M4 += odbc_result($rs,"SALES_PD4");
$M5 += odbc_result($rs,"SALES_PD5");
$M6 += odbc_result($rs,"SALES_PD6");
$M7 += odbc_result($rs,"SALES_PD7");
$mate += odbc_result($rs,"SALES_PD8");
$M9 += odbc_result($rs,"SALES_PD9");
$M10 += odbc_result($rs,"SALES_PD10");
$M11 += odbc_result($rs,"SALES_PD11");
$M12 += odbc_result($rs,"SALES_PD12");
$M13 += odbc_result($rs,"SALES_PD13");
$M14 += odbc_result($rs,"SALES_PD14");
$M15 += odbc_result($rs,"SALES_PD15");
$M16 += odbc_result($rs,"SALES_PD16");
$M17 += odbc_result($rs,"SALES_PD17");
$M18 += odbc_result($rs,"SALES_PD18");
$M19 += odbc_result($rs,"SALES_PD19");
$M20 += odbc_result($rs,"SALES_PD20");
$M21 += odbc_result($rs,"SALES_PD21");
$M22 += odbc_result($rs,"SALES_PD22");
$M23 += odbc_result($rs,"SALES_PD23");
$M24 += odbc_result($rs,"SALES_PD24");
$M25 += odbc_result($rs,"SALES_PD25");
$M26 += odbc_result($rs,"SALES_LAST_PD26");
$M27 += odbc_result($rs,"SALES_CURR_PD27");
  }

 

Note: By the way SMF is changing $M"8" to $M"ate". lol

[requinix]

Any chance you can restructure the database? Or even just create a view in it?

[jakebur01]

No. I can't do much of anything with it.  It was built in a language called ProvideX.  So, I do as much cool stuff as I can using PHP and the ProvideX ODBC.  I've been using wget with a scheduled task to generate e-mail reports.

[jakebur01]

Well just as a starting place, how do I find last November?

$Currmonth = mktime(0, 0, 0, date("m"), date("d"), date("y"));
echo date("m", $Currmonth); 

[jakebur01]

Or rather find the number of how many months it is back to November?

 

Example: If I could calculate that November is 6 months back.  Store it in $backtoNov.  I could subtract 6 from 27.  That would help me in knowing that last November would be column SALES_PD_21.  * The sum of the totals between SALES_CUR_PD and SALES_PD_21 would give me (CYTD)"current year to date sales".

 

I know that 27 - 12 is 15, giving me the current month a year ago as SALES_PD_15.  I could subtract $backtoNov from this, which is 6 in this case, giving me 9. So, last year to date's starting year in November would be SALES_PD_9.  * The sum of the totals between SALES_PD_15 and SALES_PD_9 would give me (LYTD)"last year to date sales".