Outputing Weeks Of Year And Comparing Date Ranges

Posted on 2005-05-01
Last Modified: 2010-03-19
Looking the best way to create a dynamic table to display the following, using COLDFUSION MX, with this question i am more concerned in working out the query for the date compares:  -

         Display Weeks Of Fiscal Year (April to March) as table header
         Perform Query To Count Total Costs Of Items That Fall In Each Week
         Week Starts On Monday and ends on Sunday ?, flexible with this...

eg.. Year 2005
                          Table Header (Week Numbers for 2005 Starting From April)

                        02nd April  | 09th April | 16th April | 23rd April | 30th April |07th May   etc...
Category1          2000            4500            etc..
Category2          3000            5000            etc..
Category3          4000            6000            etc..

Some type of count(*) but who do i compare with the 52 weeks in the specified FISCAL Year ??

ITEM Table
ITEMID                      PK
ITEMNAME                VARCHAR(50)
COST                       INT

ITEM Categorys TABLE

Any advice on how to accomplish this would be much appreciated

Question by:jturkington
    LVL 19

    Assisted Solution

    by:Melih SARICA
    Check this link

    this link shows a SP to create Pivot tables

    it works fine

    Melih SARICa
    LVL 28

    Assisted Solution

    Just a couple of questions:

    1.  In your sample table header, 02nd April, 2005 is a Saturday.  Should this be a Monday, April 4, 2005.
    2.  Neither of your tables, Items and Item Categories tables does not include any date fields.  How can you determine the total by week without a date field?

    >> who do i compare with the 52 weeks in the specified FISCAL Year ?? <<

    Here's a user-defined function that will give you the weeks given a start and end date:

    CREATE FUNCTION [dbo].[ufn_GetWeeks] ( @pWeekStart    DATETIME,
                                           @pWeekEnd      DATETIME )
    RETURNS @vWeekDates TABLE ( WeekNumber    INT,
                                WeekStart     DATETIME,
                                WeekEnd       DATETIME )

        DECLARE @vWeekNumber            INT
        SET @vWeekNumber = 1
        WHILE @pWeekStart < @pWeekEnd
            INSERT INTO @vWeekDates ( WeekNumber, WeekStart, WeekEnd )
            VALUES (@vWeekNumber,
                    DATEADD(D, -DATEPART(DW, CAST(CONVERT(VARCHAR(10), @pWeekStart, 101) AS DATETIME)) + 2,
                                     CAST(CONVERT(VARCHAR(10), @pWeekStart, 101) AS DATETIME)),
                    DATEADD(D, -DATEPART(DW, CAST(CONVERT(VARCHAR(10), @pWeekStart, 101) AS DATETIME)) + 8,
                                     CAST(CONVERT(VARCHAR(10), @pWeekStart, 101) AS DATETIME)))

            SET @pWeekStart = DATEADD(DD, 7, @pWeekStart)
            SET @vWeekNumber = @vWeekNumber + 1


    You can then use this function to join with your tables to determine the count by week.

    Hope this helps.
    LVL 20

    Accepted Solution

    Saw this question open as well, the same applies i think.... ;o)

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

    <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;}
    <cfif isdefined("URL.I")>
          <cfset Form.IncrementSelect = URL.I>
    <cfparam name="url.step" default="1">
    <cfparam name="form.incrementselect" default="1">
    <cfif isdefined("form.incrementselect")>
          <cfset step = form.incrementselect>
    <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")>
    <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 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")>
    <form name="MyForm" action="newDate.cfm?step=#url.step#" method="post">
    <table class="tableOne">
                <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>
                <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>
                  &nbsp;<input type="submit" name="monthgo" value="Go" onClick="this.form.action='newDate.cfm';">
            <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"
                  <a href="newDate.cfm?step=#step#&SetDate=#DateAdvance#&I=#Form.IncrementSelect#" style="{text-decoration: none}">Next &raquo;&raquo;</a>
          <input type="hidden" name="Submitted" value="1">
          <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")#>
                      <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"))#
                                              <cfoutput>#DollarFormat(GetValues.TTotal)#<!--- #CurrentDay#(#Week(CurrentDay)#) ---></cfoutput>
          <tr bgcolor="##66CCFF">
                <td align="center" colspan="8"><b>Weekly 7 Day Totals</b></td>
                <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"))#
                                  <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>


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

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

    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)


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now