Link to home
Start Free TrialLog in
Avatar of jturkington
jturkington

asked on

Dynamic Table Outputting Weeks For A Specificied Fiscal Year

Looking the best way to create a dynamic table to display the week numbers starting from the Fiscal Year 01st April to 31st March for a specified year, and to display data that have a date value that falls within each of these weeks:  -

eg.. for 2005

                             Table Header (Week Numbers for 2005 Starting From April)

                        02nd April  | 09th April | 16th April | 23rd April | 30th April |07th May   etc...
Row Header1     datecount    datecount     etc..
RowHeader2      datecount    datecount     etc..
Rowheader3      datecount    datecount   etc..
etc...

Any advice on how to accomplish this would be much appreciated

JT
Avatar of INSDivision6
INSDivision6

<cfset startDate="04/02/2005">
<cfset startWeekNum=DatePart("ww", startDate)-1>

Loop fom start date to end date (currentDate) by day, week, etc, as needed.
Output week number as follows:

<cfset weekNum=DatePart("ww", currentDate)-startWeekNum>
<cfif weekNum LTE 0>
   <!--- New calendar year --->
   <cfset weekNum=weekNum+52>
</cfif>
<cfoutput>#weekNum#</cfoutput>

I think this is what you are trying to do, lemme know...

Try this... name the page: question.cfm


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<style type="text/css">
TD {text-align: center; font-size: 10pt; font-variant: small-caps; font: arial; border-right: 1px dashed #000000;}
TH {text-align: center; font-size: 10pt; font-variant: small-caps;
      font: arial; border-bottom: thin dashed #000000; border-right: thin dashed #000000;}
TABLE {
      font-family: Arial, Helvetica, sans-serif;
      font-size: 10px;
      font-variant: small-caps;
      text-transform: uppercase;
      color: #000000;
      border: thin solid #666666;
      width: 95%;
}
  .myBorder {border-right: none;}
  .myTable {width: 700px;}
  .myTD { border-bottom: none; border-right: none;}
  .Text1 { color: #CCCCCC;}
  .Text2 { color: #000000;}
CAPTION {font-size: 16px;}
</style>

<cfset monthList = "January,February,March,April,May,June,July,August,September,October,November,December">
<cfparam name="currentYear" default="#DateFormat(Now(),"YYYY")#">
<cfparam name="form.YearCount" default="#currentYear#">
<cfset currentyear = "#form.YearCount#">
<cfparam name="currentMonth" default="04">
<cfparam name="form.MonthCount" default="#currentMonth#">
<cfset currentMonth = "#form.MonthCount#">
<cfset startDate= #DateFormat("#currentMonth#/02/#currentYear#", "MM/DD/YYYY")#>
<cfif isdefined("url.start")><cfset startdate = url.start></cfif>
<cfset currentLast = currentYear + 1>
<cfset end = #DateFormat(DateAdd("D", -44, "#currentMonth#/02/#currentLast#"), "MM/DD/YYYY")#>
<cfset endDate  = #DateFormat(DateAdd("Y", +1, end), "MM/DD/YYYY")#>
<cfset DateNext = #DateFormat(DateAdd("D", +1, startDate), "MM/DD/YYYY")#>
<cfset DateBack = #DateFormat(DateAdd("D", -1, startDate), "MM/DD/YYYY")#>

<form name="MyForm" action="" method="post">
<br>
<table>
<CAPTION>Week Numbers for <cfoutput>#currentYear#</cfoutput> Starting From April<BR><BR></CAPTION>
<TR><TH>&nbsp;</TH>
      <cfloop from="0" to="6" index="a">
            <cfset DateList = Ucase(DateFormat(DateAdd("D", +(#a#*7), startDate), "MMM DD, YYYY"))>
                  <cfoutput><TH <cfif a IS "6">class="myBorder"</cfif>>#DateList#</TH></cfoutput>
      </cfloop>
</TR><cfloop from="2" to="8" index="i">
<TR>
<cfset count = i -1>
<TD><cfoutput>Row Header #count#</cfoutput></TD>
      <cfloop from="0" to="6" index="b">
            <cfset MatchDate = Ucase(DateFormat(DateAdd("D", +(#b#*7), startDate), "MMM DD"))>
                  <cfoutput><TD <cfif b IS 6>class="myBorder"</cfif>>#MatchDate#</TD></cfoutput>
      </cfloop>
</TR>
</cfloop>
</table>
<br>
<Table class="myTable">
<TR>
<cfoutput>
<TD width="278" class="myTD">Jump to Month:
            <select name="MonthCount" size="1">
            <cfloop from="1" to="#ListLen(monthList)#" index="mon">
                  <option value="#mon#"  <cfif form.MonthCount IS #mon#>selected</cfif>>#ListGetAt(monthList, mon)#</option>            
            </cfloop></select>&nbsp;
            <input type="submit" value="GO"  onClick="this.form.action = 'question.cfm';">
</TD>
<TD width="139" class="myTD">Year:
          <input name="YearCount" type="text"
               value="<cfif YearCount><cfoutput>#YearCount#</cfoutput><cfelse></cfif>" size="3" maxlength="4">&nbsp;
            <input type="submit" value="GO"  onClick="this.form.action = 'question.cfm';"></TD>         
<TD width="62" class="myTD">
      <cfif startDate EQ #DateFormat("04/02/#currentYear#", "MM/DD/YYYY")#>
            <input type="button" name="First" Value="First" disabled><cfelse>
            <input type="submit" name="First" Value="First" onClick="this.form.action='question.cfm?start=#DateFormat("04/02/#currentYear#", "MM/DD/YYYY")#';">
      </cfif>
</TD>
<TD width="62" class="myTD">
      <cfif startDate LTE #DateFormat("04/02/#currentYear#", "MM/DD/YYYY")#>
            <input type="button" name="Back" Value="Back" disabled><cfelse>
            <input type="submit" name="Back" Value="Back" onClick="this.form.action = 'question.cfm?start=#DateBack#';">
      </cfif>
</TD>
<TD width="62" class="myTD">
      <cfif startDate EQ endDate>
            <input type="button" name="Next" Value="Next" disabled><cfelse>
            <input type="submit" name="Next" Value="Next" onClick="this.form.action = 'question.cfm?start=#DateNext#';">
      </cfif>
</TD>
<TD width="62" class="myTD">
      <cfif startDate EQ endDate>
            <input type="button" name="Last" Value="Last" disabled><cfelse>
            <input type="submit" name="Last" Value="Last" onClick="this.form.action = 'question.cfm?start=#endDate#';">
      </cfif>
</TD>
</cfoutput>
</TR>
</Table>
</form>

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

I know you are looking to insert your query values to the table based on dates, if I had a query to go from I could show you an example, but in your query make your where statement match the #currentrow# and date field, and you should be set.

Lemme know if you need help...
I tossed this form together so it may look a little rough, but you can add the finishing touches and play with it some to get it to do what you want exactly.

Try adding your query to it and play with it some to get it to display how you want, but lemme know if you have some questions.. :o)

Regards,
~trail

I know you were looking for a datecount or week number in each field, but I think it'd be easier to compare dates with your query rather than week numbers

Here's a sample query... may be a bit rough.. :o)


in the second loop which populates your rows do this..

Sample query:
----------------------------------------------------------
<cfquery name="THEQ" datasource="#DSN#">
SELECT MyTableFiller, Date
FROM YourTable
</cfquery>
----------------------------------------------------------

Then edit this loop like this:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<cfloop from="0" to="6" index="b">
     <cfset MatchDate = Ucase(DateFormat(DateAdd("D", +(#b#*7), startDate), "MMM DD"))>
         <cfoutput query="THEQ">
              <TD <cfif b IS 6>class="myBorder"</cfif>><cfif #DateCompare(DateFormat(date, "MM/DD/YYYY"), MatchDate)# IS 0>#TableFiller#</TD>
         </cfoutput>
</cfloop>
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

This may work for you, again I don't have anything to test it with, so give it a try...

hope this helps :o)

~trail

oops slight correction

make the loop like this, notice the matchdate format and compare format

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<cfloop from="0" to="6" index="b">
     <cfset MatchDate = Ucase(DateFormat(DateAdd("D", +(#b#*7), startDate), "MM/DD/YYYY"))>
         <cfoutput query="THEQ">
              <TD <cfif b IS 6>class="myBorder"</cfif>><cfif #DateCompare(DateFormat(date, "MM/DD/YYYY"), MatchDate)# IS 0>#TableFiller#</TD>
         </cfoutput>
</cfloop>
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Avatar of jturkington

ASKER

Thanks trailblazzyr55, i am starting to realise that trying to simulate tables like MS Excel is harder than i thought and maybe not the job for a database.

I am looking to sum up the totals for each week of a fiscal year and display them in there appropraite row header

EG..

ITEM TABLE
ITEMID          INT  (PK)
ITEMNAME     VARCHAR
DATEADDED   DATE
ITEMCOST     INT
CATEGORY    VARCHAR   (Row Header Column)
You know cfgrid would probably be a lot closer to what you are looking to do :o)

What totals are you looking to sum up? Is it the item cost? Lemme know and we'll get the totals added up for each row.

~trail
ASKER CERTIFIED SOLUTION
Avatar of trailblazzyr55
trailblazzyr55

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial