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

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

##### Share on other sites

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

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

##### Share on other sites

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); ```

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

#### Archived

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

×

• #### Activity

• Chat
×
• Create New...