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.
A
Anonymous
November 3, 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 ..


ah! Thanks again. That will do it.
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