Skip to main content
A
Anonymous
October 28, 2008
Answered

Update dates

  • October 28, 2008
  • 2 replies
  • 1474 views
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.
This topic has been closed for replies.
Correct answer -__cfSearching__-
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.
> 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 ..

2 replies

-
-__cfSearching__-
Inspiring
October 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?
A
Anonymous
October 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.
-
-__cfSearching__-
Inspiring
October 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.
N
Newsgroup_User
Inspiring
October 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.
A
Anonymous
October 28, 2008
Currently I am using MS Access. Eventually we will switch to MSSQL Server.
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices