Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Outputing Weeks Of Year And Comparing Date Ranges

Posted on 2005-05-01
3
Medium Priority
?
283 Views
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..
etc...

QUERY
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)
ITEMCATEGORYIDFK  FK
COST                       INT

ITEM Categorys TABLE
ITEMCATEGORYID      PK
ITEMCATEGORYNAME VARCHAR(50)

Any advice on how to accomplish this would be much appreciated

JT
0
Comment
Question by:jturkington
3 Comments
 
LVL 19

Assisted Solution

by:Melih SARICA
Melih SARICA earned 400 total points
ID: 13904679
Check this link

this link shows a SP to create Pivot tables

it works fine

http://www.itrain.de/knowhow/sql/tsql/pivot/sp_transform_v1_1.asp


Melih SARICa
0
 
LVL 28

Assisted Solution

by:rafrancisco
rafrancisco earned 800 total points
ID: 13906346
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 )
AS
BEGIN

    DECLARE @vWeekNumber            INT
    SET @vWeekNumber = 1
    WHILE @pWeekStart < @pWeekEnd
    BEGIN
        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
    END

    RETURN
END
GO

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

Hope this helps.
0
 
LVL 20

Accepted Solution

by:
trailblazzyr55 earned 800 total points
ID: 13980809
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...

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

810 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