Jump to content

Calculating Date Periods


Recommended Posts

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.



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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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".

Link to comment
Share on other sites


This topic is now archived and is closed to further replies.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.