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
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
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,Ap ril,May,Ju ne,July,Au gust,Septe mber,Octob er,Novembe r,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/#curr entYear#", "MM/DD/YYYY")#>
<cfif isdefined("url.start")><cf set startdate = url.start></cfif>
<cfset currentLast = currentYear + 1>
<cfset end = #DateFormat(DateAdd("D", -44, "#currentMonth#/02/#curren tLast#"), "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#</c foutput> Starting From April<BR><BR></CAPTION>
<TR><TH> </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 >>#DateLis t#</TH></c foutput>
</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></cf output>
</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>>#Lis tGetAt(mon thList, mon)#</option>
</cfloop></select>
<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>#YearC ount#</cfo utput><cfe lse></cfif >" size="3" maxlength="4">
<input type="submit" value="GO" onClick="this.form.action = 'question.cfm';"></TD>
<TD width="62" class="myTD">
<cfif startDate EQ #DateFormat("04/02/#curren tYear#", "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= #DateForma t("04/02/# currentYea r#", "MM/DD/YYYY")#';">
</cfif>
</TD>
<TD width="62" class="myTD">
<cfif startDate LTE #DateFormat("04/02/#curren tYear#", "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=#DateB ack#';">
</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=#DateN ext#';">
</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=#endDa te#';">
</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
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,Ap
<cfparam name="currentYear" default="#DateFormat(Now()
<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
<cfif isdefined("url.start")><cf
<cfset currentLast = currentYear + 1>
<cfset end = #DateFormat(DateAdd("D", -44, "#currentMonth#/02/#curren
<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#</c
<TR><TH> </TH>
<cfloop from="0" to="6" index="a">
<cfset DateList = Ucase(DateFormat(DateAdd("
<cfoutput><TH <cfif a IS "6">class="myBorder"</cfif
</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("
<cfoutput><TD <cfif b IS 6>class="myBorder"</cfif>>
</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>>#Lis
</cfloop></select>
<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>#YearC
<input type="submit" value="GO" onClick="this.form.action = 'question.cfm';"></TD>
<TD width="62" class="myTD">
<cfif startDate EQ #DateFormat("04/02/#curren
<input type="button" name="First" Value="First" disabled><cfelse>
<input type="submit" name="First" Value="First" onClick="this.form.action=
</cfif>
</TD>
<TD width="62" class="myTD">
<cfif startDate LTE #DateFormat("04/02/#curren
<input type="button" name="Back" Value="Back" disabled><cfelse>
<input type="submit" name="Back" Value="Back" onClick="this.form.action = 'question.cfm?start=#DateB
</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=#DateN
</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=#endDa
</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(da te, "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
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("
<cfoutput query="THEQ">
<TD <cfif b IS 6>class="myBorder"</cfif>>
</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(da te, "MM/DD/YYYY"), MatchDate)# IS 0>#TableFiller#</TD>
</cfoutput>
</cfloop>
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------
make the loop like this, notice the matchdate format and compare format
--------------------------
<cfloop from="0" to="6" index="b">
<cfset MatchDate = Ucase(DateFormat(DateAdd("
<cfoutput query="THEQ">
<TD <cfif b IS 6>class="myBorder"</cfif>>
</cfoutput>
</cfloop>
--------------------------
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)
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<cfset startWeekNum=DatePart("ww"
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#</cfout