Update dates

Report · Oct 28, 2008

I have a table of records that are identified by Week Ending Dates. The dates for each of these records are 7 days apart. Each record also has a Start Date. These dates are the same. The first record have the same value for StartDate and WeekEndingDate. What I am having trouble doing is changing these dates. Here is a couple of scenerios:

1. A user decides to change the StartDate to an earlier or later date. Once the StartDate is changed the WeekEndingDate needs to change for each record in the record set.

2. A user decides to add more weeks to the record set. After the insert, a query needs to update those dates, which have a default value of 01-01-1900, to the appropriate WeekEndingDate in sequence.

With the help of ===cfsearching===, I have been experimenting with a query that he recomended for a similar issue. Here it is:

<CFQUERY Name="UpdateDate" DataSource="xxx">
UPDATE Productivity AS p INNER JOIN Productivity AS R
ON p.WeekEndingDate = DateAdd('d', 7, R.StartDate)
SET p.WeekEndingDate = DateAdd('d', 7, R.WeekEndingDate)
WHERE p.JobNumber = '#URL.JobNumber#'
AND p.SubJobNumber = '#URL.SubJobNumber#'
AND p.SubcontractID IS NULL
</CFQUERY>

I do have an insert that is working for adding records.

Report · Oct 28, 2008

chrispilie wrote:
>
> I do have an insert that is working for adding records.
>

What database management system are you using?

My first thought was this could easily be handled in the database.
Fully featured, enterprise capable database management systems have the
ability to program triggers.

It would be fairly trivial to program a trigger in such a database that
would calculate and store the correct weekEndingDate anytime a record is
inserted or updated in the table.

But exactly how that is done will depend on your exact database and what
access you have to programming it.

Report · Oct 28, 2008

Currently I am using MS Access. Eventually we will switch to MSSQL Server.

Report · Oct 29, 2008

Is there anything you can add to this?

Report · Oct 29, 2008

I can:
MS Access is NOT on the list of "Fully featured, enterprise capable
database management systems".

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

Report · Oct 29, 2008

Would there be a way of doing this with a loop of some sort? I will shed a little light on what I am trying to do.

Initial Project

StartDate WeekEndingDate
10-12-08 10-12-08
10-12-08 10-19-08
10-12-08 10-26-08

Now change the start date,

StartDate WeekEndingDate
10-26-08 10-26-08
10-26-08 11-2-08
10-26-08 11-9-08

Report · Oct 29, 2008

is start date always a Sunday? or can a user change it to any week day?
is week end date always a Sunday? or is it always 7 days after the start
date/previous week end date?
why is the first week end date same as start date? is it always this way?

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

Report · Oct 29, 2008

right now there is only the option of 7 days from the start date. The start date can be any date set by the user but our company's protocal is to always have it on Sunday. FYI:There will be a need eventually to deploy a daily options rather than weekly. For now we can concentrate on 7 day blocks. The reason for start day is for several reasons. It is mostly for task scheduling and sorting value. I would like the first StartDate to be the same as the WeekEndingDate.

Report · Oct 29, 2008

> Currently I am using MS Access. Eventually we will switch to MSSQL Server.

As mentioned, one of the big problems with Access is that it lacks support for many key features (stored procedures, triggers, ..). So it is likely you will end up writing some dubious or inefficient sql to get around the limitations. SQL which may need to be re-written anyway when you switch to MSSQL Server, so why not switch now and do it right the first time? Assuming it is possible to switch now ..

> A user decides to change the StartDate

In addition to the questions above .. I think I understand the general idea, but the overall relationships are unclear.

If I am understanding correctly, it sounds as if a single "JobNumber" has a defined Start and End date. When a new job is created, your application pre-populates the Productivity table with a series of records for the job's start/end date range. One record per WeekEnding (Sunday). So what you are trying to do now is update the records in the Productivity table whenever the "JobNumber's" start or end date is changed. Is that correct?

> Initial Project (records for JobNumber = 1)
> StartDate WeekEndingDate
> 10-12-08 10-12-08
> 10-12-08 10-19-08
> 10-12-08 10-26-08

a) How does the user "change the StartDate to an earlier or later date" or "add more weeks to the record set"? Are you storing the overall Start and End dates with the "JobNumber" record, or just in the Productivity table?

b) What should happen to earlier records (10-12, 10-19) if the startDate is changed to a later date (10-26)? Should they be deleted?

Report · Oct 29, 2008

Thanks ===cfsearching===,
I can't switch over to SQL Server yet. I have to finish this application and launch a version one before I begin with SQL Server. You have a good idea of what I have. Let me re-iterate to make sure I cover your questions. A user sets up a job with a StartDate and a Duration (Weeks). After he sets up a job and opens a Productivity Page the "Productivity" table with records (records = Duration). NOTE: That Duration could change along with the StartDate. To change that, the user must go to a job edit screen and change the StartDate and Duration(either/or/both). What would be Ideal is when the user goes to the Productivity page, the change is recognized and the insert statement would run to update duration changes if there are any and then the update statement would progressively (possibly a loop incrementally 7 days) change the WeekEndingDates based on the new StartDate.

As far as the records, if I have to add weeks, the weeks will be added sequentially so they will follow the existing. When I change the StartDate, the first days records shouldn't change except for the WeekEnding Date. The user will have to make sure that the new StartDate and the first WeekEndingDate represent reality once he makes the changes. IF they don't he can go back and change it.

Report · Oct 30, 2008

chrispilie wrote:
> A user sets up a job with a StartDate and a Duration (Weeks).

In your sample data, the StartDate value is always the same. Which makes it seem like "StartDate" is an attribute of a Job. Since we cannot see your schema, I do not know if that is correct. But if it is, StartDate should be stored in the Job table (not Productivity). By duplicating the StartDate value in every Productivity record, the dates for a single "JobNumber" can easily get out-of-synch.

> What would be Ideal is when the user goes to the Productivity page, the change is
> recognized and the insert statement would run to update duration changes if there
> are any and then the update statement would progressively (possibly a loop
> incrementally 7 days) change the WeekEndingDates based on the new StartDate.

If you are _only_ dealing with 7 day blocks, then the general process comes down to two things:

1) Determine how existing record dates should be adjusted, due to a change in start date
2) Determine how many records should be added/removed, due to a change in duration

The first is just calculating the number of weeks difference between the old and new start dates. You could use the DateDiff function for this. Then adjust the existing dates using that offset value in the dateAdd function

... SET WeekEndingDate = dateAdd('ww', numberOfweeksOffset, WeekEndingDate)

To determine the number of weeks to add or remove, simply compare the old and new duration values. Then add or remove records as needed. The rest is just basic date calculations.

Bear in mind, this is all based on my very limited knowledge of Access. It is distinctly possible there are better ways to achieve this (even with MS Access). However, given its limitations I suspect the final code will end up being more convoluted than if you were using a fully featured database like MS SQL.

However, before you can write the sql, you need to nail down all of the possible scenarios. Then determine how each and every case should be handled according to your business rules. That is something only you can do. It often helps to write out the logic and individual steps in plain english first. Then convert it to code and/or sql.

> There will be a need eventually to deploy a daily options rather than weekly.

That is something you may want to consider that now, while you are still in the design phase. Think about how easy or difficult it would be to implement that change, given your current structure.

Report · Oct 30, 2008

===cfsearching=== Thanks for your reply. The StartDate actually has many purposes here. I do have it in the Job table and there is a SubJob table. There is also a Subcontract table which may be under the same job but Start on a different date. Not all Jobs have SubJobs and/or Subcontracts. Anyway. I think what I was most puzzled about was the best way to approach it. I was unaware if there was a way of doing this within a single update query or I was going to have to loop over one. I have everything set up except the code to incrementally update the WeekEndingDates. It appears that it needs to be a loop of some kind... maybe an index loop. This is a similar script that I wrote to display dates.


<CFQUERY DATASOURCE="db" name="PlannedQuery">
SELECT StartDate
FROM Table
WHERE Job = '80731'
</CFQUERY>

<cfset StartDate = "#PlannedQuery.StartDate#">
<cfset s.startdate = 8 - #DayOfWeek(startdate)#>


<cfset WE.StartDate = #DateFormat(startdate+s.startdate)#>
<cfset weekday = "1">

<cfoutput query="PlannedQuery">
#DateFormat(we.startdate, "medium")#
<cfset WE.StartDate = #DateFormat(DateAdd("d", 7, we.startdate), "medium")# +0>
<cfset weekday = #weekday# + 1>
</cfoutput>

I am thinking this is similar to what I need to do.

Report · Oct 30, 2008

> The StartDate actually has many purposes here.

If the Job StartDate is actually duplicated in the Productivity table, it still sounds questionable to me. But you know your business rules better than I do.

> incrementally update the WeekEndingDates

A single UPDATE with dateAdd should do it.

>Initial Project
>StartDate WeekEndingDate
> 10-12-08 10-12-08
> 10-12-08 10-19-08
> 10-12-08 10-26-08

Take your original example. The old StartDate is 10-12 and the new start date is 10-26. So the weeks offset is +2.


weeksOffset = dateDiff(ww, oldStartDate, newStartDate)
weeksOffset == 2 (weeks)

Calling DateAdd will shift all of the existing dates forward by two (2) weeks. Since they are already seven (7) days apart, the new values will be 10-26, 11-2 and 11-9. No further adjustments should be needed.

UPDATE Productivity
SET WeekEndingDate = dateAdd(ww, weeksOffset, WeekEndingDate )
.... etcetera ....

It also works in reverse. If the offset is negative, dateAdd will deduct x number of weeks from the existing dates.

Report · Oct 31, 2008

I tried this below and I am getting an error. It is a variable parameter error on WeekEndingDate in the SET.

<cfset weeksOffset = dateDiff("ww", Plan.StartDate, JobInfo.StartDate)>
<CFQUERY Name="WeekEnding" DataSource="db">
UPDATE Productivity
SET StartDate = #CreateODBCDate(GetSubJobInfo.StartDate)#,
WeekEndingDate = dateAdd("ww",#weeksOffset#,WeekEndingDate)
WHERE Job = '#URL.Job#'
</CFQUERY>

I tried what I have below and I get a varriable WeekEndingDate undefind error.

<cfset weeksOffset = dateDiff("ww", Plan.StartDate, JobInfo.StartDate)>
<CFQUERY Name="WeekEnding" DataSource="db">
UPDATE Productivity
SET StartDate = #CreateODBCDate(GetSubJobInfo.StartDate)#,
WeekEndingDate = #dateAdd("ww",weeksOffset,WeekEndingDate)#
WHERE Job = '#URL.Job#'
</CFQUERY>

I even tried a JOIN and I getting the same errors.

Report · Oct 31, 2008

> WeekEndingDate = dateAdd( "ww",#weeksOffset#,WeekEndingDate)

Remember when using the Access dateAdd function in a cfquery you must use single (not double) quotes around the interval:

BTW - You should get into the habit of using cfqueryparam on all query parameters now. There are many benefits: bind variables, limited sql injection protection, etcetera. It is a real pain to have to go back over all of your queries later ..

Report · Nov 03, 2008

ah! Thanks again. That will do it.

Update dates

1 Correct answer