?
Solved

Dynamic Table Outputting Weeks For A Specificied Fiscal Year

Posted on 2005-05-01
7
Medium Priority
?
206 Views
Last Modified: 2013-12-24
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
0
Comment
Question by:jturkington
  • 5
7 Comments
 
LVL 7

Expert Comment

by:INSDivision6
ID: 13905023
<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>

0
 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 13916077
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

0
 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 13916137
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

0
Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 13916150
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>
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0
 

Author Comment

by:jturkington
ID: 13916169
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)
0
 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 13920295
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
0
 
LVL 20

Accepted Solution

by:
trailblazzyr55 earned 2000 total points
ID: 13980792
I think this is more what you're looking for... ofcourse you'd have to change the query to your table name and column names, but this will do what you are looking for.. it doesn't give for fiscal year because that would be more scripting and leave no space for customizing,

but here's something I came up with for you.. have a look and feel free to change it how you want... remeber to change the queries... there's a couple of them but they query the same database, right now inthe code it's set to my testing database, but it should be simple enough to edit...

start
________________________________________________________________________________________________________
<style type="text/css">
.whiteText {color: white; font-weight: bold}
table {
 font-family: Arial, Helvetica, sans-serif;
 font-size: 11px;
 font-variant: small-caps;
 text-transform: uppercase;
 color: #000000;
 border: thin solid #666666;
 width: 800px;}
.tableOne {background-color: 66CCFF;}
</style>
<cfif isdefined("URL.I")>
      <cfset Form.IncrementSelect = URL.I>
</cfif>
<cfparam name="url.step" default="1">
<cfparam name="form.incrementselect" default="1">
<cfif isdefined("form.incrementselect")>
      <cfset step = form.incrementselect>
</cfif>
<cfparam name="form.MonthSelect" default="#Month(Now())#">
<cfparam name="form.YearSelect" default="#Year(Now())#">
<cfif isdefined("URL.SetDate")>
<cfset DateStart = DateFormat(url.SetDate, "MM/DD/YYYY")>
<cfset Form.MonthSelect = DateFormat(DateStart, "MM")>
<cfset Form.YearSelect  = DateFormat(DateStart, "YYYY")>
<cfelse>
<cfparam name="CurrentDay" default="#Day(Now())#">
<cfparam name="CurrentMonth" default="#Form.MonthSelect#">
<cfparam name="CurrentYear" default="#Form.YearSelect#">
<cfparam name="MyDate" default="#CurrentMonth#/#CurrentDay#/#CurrentYear#">
<cfparam name="DateStart" default="#DateFormat(MyDate, "MM/DD/YYYY")#">
</cfif>
<cfif step EQ 1>
<cfset DateAdvance = DateFormat(DateAdd("D", +1, DateStart),"MM/DD/YYYY")>
<cfset DateBack    = DateFormat(DateAdd("D", -1, DateStart),"MM/DD/YYYY")>
<cfelseif step EQ 2>
<cfset DateAdvance = DateFormat(DateAdd("D", +7, DateStart),"MM/DD/YYYY")>
<cfset DateBack    = DateFormat(DateAdd("D", -7, DateStart),"MM/DD/YYYY")>
<cfelseif step EQ 3>
<cfset DateAdvance = DateFormat(DateAdd("M", +1, DateStart),"MM/DD/YYYY")>
<cfset DateBack    = DateFormat(DateAdd("M", -1, DateStart),"MM/DD/YYYY")>
<cfelseif step EQ 4>
<cfset DateAdvance = DateFormat(DateAdd("YYYY", +1, DateStart),"MM/DD/YYYY")>
<cfset DateBack    = DateFormat(DateAdd("YYYY", -1, DateStart),"MM/DD/YYYY")>
</cfif>
<cfoutput>
<br><br><br>
<form name="MyForm" action="newDate.cfm?step=#url.step#" method="post">
<table class="tableOne">
      <tr>
            <td width="187"> Start Month:
              <select name="monthSelect">
                  <cfloop from="1" to="12" index="Mon">
                        <cfset SelectMon = MonthAsString(#mon#)>
                        <option value="#mon#" <cfif FORM.monthSelect EQ #mon#>selected="selected"</cfif>>#SelectMon#</option>
                  </cfloop>
              </select>
            </td>
            <td width="178"> Start Year:
          <select name="yearSelect">
                <cfloop from="2003" to="2008" index="Yr">
              <option value="#Yr#" <cfif FORM.yearSelect EQ "#Yr#">selected="selected"</cfif>>#Yr#</option>
                  </cfloop>
          </select>
              &nbsp;<input type="submit" name="monthgo" value="Go" onClick="this.form.action='newDate.cfm';">
            </td>
        <td width="427">
                <div align="right">
              <a href="newDate.cfm?step=#step#&SetDate=#DateBack#&I=#Form.IncrementSelect#" style="{text-decoration: none}">&laquo;&laquo; Prev</a>
              <cfset SelectList = "Day,Week,Month,Year">
          <select name="IncrementSelect" onchange="form.submit()">
                <cfloop from="1" to="4" index="Count">
              <option value="#Count#" <cfif Form.IncrementSelect EQ Count>selected="selected"
                          </cfif>>#ListGetAt(SelectList,count)#</option>
                  </cfloop>
          </select>
              <a href="newDate.cfm?step=#step#&SetDate=#DateAdvance#&I=#Form.IncrementSelect#" style="{text-decoration: none}">Next &raquo;&raquo;</a>
              </div>
          </td>
      </tr>
      <input type="hidden" name="Submitted" value="1">
</table>
</form>
</cfoutput>
<table>
      <cfloop from="1" to="8" index="Row">
            <tr <cfif Row IS 1>bgcolor="##3300CC"<cfelse></cfif>>
                  <cfif Row IS 1>
                        <td width="140" class="whiteText"><cfoutput>Grid Start: #DateStart#</cfoutput></td>
                              <cfloop from="1" to="7" index="Column">
                                 <cfset DayInc = (Column - 1) * 7>
                                    <td align="center"  class="whiteText"><cfoutput>#DateFormat(DateAdd("D", + DayInc, DateStart),"MM/DD/YYYY")#<br>
                                    Week: #Week(DateFormat(DateAdd("D", + DayInc, DateStart),"MM/DD/YYYY"))#</cfoutput></td>
                              <cfset CurrentWeek[Column] = #DateFormat(DateAdd("D", + DayInc, DateStart),"MM/DD/YYYY")#>
                              </cfloop>
                  </cfif>
                  <cfif Row GT 1>
                        <cfset RCNT = Row - 2>
                        <cfset DayOutput = DateFormat(DateAdd("D",+RCNT,DateStart),"DDDD")>
                        <cfset LowDay = DateFormat(DateAdd("D",+RCNT+1,DateStart),"DDD")>
                        <td bgcolor="#CCCCCC"><cfoutput>#DayOutput#</cfoutput></td>
                              <cfloop from="1" to="7" index="Column">
                                 <cfset DayInc = (Column - 1) * 7>
                              <cfset Count = ((Row + 1) -2) - Column>
                                    <td bgcolor="#CCCCCC" width="120" align="center">            
                                          <cfset WeekInc = DateAdd("D",+(#Column# -1) + Count,DateStart)>
                                          <cfset CurrentDay = DateFormat(DateAdd("D", + DayInc, WeekInc),"M/D/YYYY")>
                                                <cfquery name="GetValues" datasource="testDB">
                                                      SELECT * FROM datahouse
                                                      WHERE TDate = #createODBCDate(DateFormat(CurrentDay,"M/D/YYYY"))#
                                                </cfquery>
                                          <cfoutput>#DollarFormat(GetValues.TTotal)#<!--- #CurrentDay#(#Week(CurrentDay)#) ---></cfoutput>
                                    </td>
                              </cfloop>
                  </cfif>
            </tr>
      </cfloop>
</table>
<table>
      <tr bgcolor="##66CCFF">
            <td align="center" colspan="8"><b>Weekly 7 Day Totals</b></td>
      </tr>
      <tr>
            <td width="160" bgcolor="#CCCCCC">Starting: <b><cfoutput>#LowDay#</cfoutput></b></td>
                  <cfloop from="1" to="7" index="i">
                        <cfloop from="1" to="7" index="ii">
                          <cfset Current = DateFormat(DateAdd("D",+((i-1)*ii),DateStart),"M/D/YYYY")>
                          <cfset Next = DateFormat(Current+CreateTimeSpan(6,0,0,0),"M/D/YYYY")>
                              <cfquery name="GetTotals" datasource="testDB">
                                    SELECT TTotal FROM datahouse
                                    WHERE TDate BETWEEN #createODBCDate(DateFormat(Current,"M/D/YYYY"))#
                                                          AND #createODBCDate(DateFormat(Next,"M/D/YYYY"))#
                              </cfquery>
                        </cfloop>
                              <cfset Values[i] = #ValueList(GetTotals.TTotal)#>
                              <cfset WeekTotal[i] = ArraySum(ListToArray(Values[i]))>
                        <td width="120" align="center" bgcolor="#CCCCCC"><cfoutput>#DollarFormat(WeekTotal[i])#</cfoutput></td>
                  </cfloop>
      </tr>
</table>

______________________________________________________________________________________________________________
end

Here are the values in the query that need to be changed...

first query...
                                                <cfquery name="GetValues" datasource="testDB">
                                                      SELECT TTotal AS TotalP FROM datahouse
                                                      WHERE TDate = #createODBCDate(DateFormat(CurrentDay,"M/D/YYYY"))#
                                                </cfquery>

CHANGE TTotal to your column where you store daily totals, TDate to your date column,
CHANGE GetValues to whatever you want to name the query, remember to change the query name in the variable just below the query as well :o)

Second query:
                              <cfquery name="GetTotals" datasource="testDB">
                                    SELECT TTotal AS TotalP FROM datahouse
                                    WHERE TDate BETWEEN #createODBCDate(DateFormat(Current,"M/D/YYYY"))#
                                                          AND #createODBCDate(DateFormat(Next,"M/D/YYYY"))#
                              </cfquery>

follow the same as above, selects the same, however it selects a date range so that's why for the second query...


Anyway, that should do the solve your question... if you have any other question or issues with this just lemme know, I'd be happy to answer ;o)

Regards,
~trail

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question