3 Replies - 4002 Views - Last Post: 22 June 2007 - 08:49 AM Rate Topic: -----

#1 b21playa   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 10-November 05

comapring 2 sets of dates

Posted 06 April 2006 - 08:43 AM

I need to compare a list of dates i have in a database with dates of a month, to the output the dates that do not appear in the DB.

Im creating a timesheet for work, and each user needs to add the amount of hours worked in different categories each day. I need to show them on a page which dates they have not entered any dat for, so they can enter this before the end of the month.

Has to be dynamic, so as soon as they enter data, is it not then listed on the page.

i was thinking of putting the DB dates in an array, and also a list of dates from the start of the month to the current date and comparing them, and outputing any dates that are not matched

can this be done and if so any ideaS?

Is This A Good Question/Topic? 0
  • +

Replies To: comapring 2 sets of dates

#2 skyhawk133   User is offline

  • Head DIC Head
  • member icon

Reputation: 1962
  • View blog
  • Posts: 20,414
  • Joined: 17-March 01

Re: comapring 2 sets of dates

Posted 06 April 2006 - 10:09 AM

I'm doing something similar in an application I wrote recently. I'm looping over days in the month (cfloop supports dates), then using a query of queries to check if the index of my loop was in my original query.

Here's the code I used:
	<cfloop from="#startDate#" to="#endDate#" index="i">
	
  <cfquery name="thisday" dbtype="query">
  SELECT * FROM activity WHERE startdate = '#DateFormat(i, 'MM/DD/YYYY')#'
  </cfquery>
  
  <cfif thisday.tasks EQ "">
 	 <cfset count = "0">
  <cfelse>
 	 <cfset count = #thisday.tasks#>
  </cfif>
  
	
  <cfchartdata item="#DateFormat(i, 'MM/DD/YYYY')#" value="#count#">
	</cfloop>


Please keep in mind this was to build a chart so this won't work just copy and pasting.

For your situation I would suggest doing something like this:
<cfquery name="dates" datasource="YOURDATASOURCE">
SELECT date_column FROM table_with_dates
</cfquery>

<cfloop from="#startdate#" to="#enddate#" index="i">

     <cfset temp = #ListFind(ValueList(dates.date_column), i)#>

     <cfif temp>
          FOUND IT!
     <cfelse>
          Didn't Find it
     </cfif>

</cfloop>



I think this may be a more efficient way of doing what I did useing query of queries... might actually convert my code :). But anyway, you run your query to pull back all the dates, then loop from a startdate to an enddate (1st of the month to the last of the month), then you set a temp variable to true/false using ListFind... ListFind looks at the list of dates you pulled back from the query and checks if i (the current date in the loop) is in the list. If it is, it returns true, if not false... then you can do an if statement to handle it however you want.
Was This Post Helpful? 0
  • +
  • -

#3 KClark   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 29-May 07

Re: comapring 2 sets of dates

Posted 29 May 2007 - 08:02 AM

I have the task of creating a timesheet application. I only want the user to enter data if it is not the norm. For example, I want regular hours to stay @ 8 hrs unless the user worked hours other than 8. Any suggestions on how to get started. I am a newbie.

Thanks!




View Postb21playa, on 6 Apr, 2006 - 08:43 AM, said:

I need to compare a list of dates i have in a database with dates of a month, to the output the dates that do not appear in the DB.

Im creating a timesheet for work, and each user needs to add the amount of hours worked in different categories each day. I need to show them on a page which dates they have not entered any dat for, so they can enter this before the end of the month.

Has to be dynamic, so as soon as they enter data, is it not then listed on the page.

i was thinking of putting the DB dates in an array, and also a list of dates from the start of the month to the current date and comparing them, and outputing any dates that are not matched

can this be done and if so any ideaS?

Was This Post Helpful? 0
  • +
  • -

#4 kenham40   User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 14
  • Joined: 22-June 07

Re: comapring 2 sets of dates

Posted 22 June 2007 - 08:49 AM

View Postskyhawk133, on 6 Apr, 2006 - 10:09 AM, said:

I'm doing something similar in an application I wrote recently. I'm looping over days in the month (cfloop supports dates), then using a query of queries to check if the index of my loop was in my original query.

Here's the code I used:
	<cfloop from="#startDate#" to="#endDate#" index="i">
	
  <cfquery name="thisday" dbtype="query">
  SELECT * FROM activity WHERE startdate = '#DateFormat(i, 'MM/DD/YYYY')#'
  </cfquery>
  
  <cfif thisday.tasks EQ "">
 	 <cfset count = "0">
  <cfelse>
 	 <cfset count = #thisday.tasks#>
  </cfif>
  
	
  <cfchartdata item="#DateFormat(i, 'MM/DD/YYYY')#" value="#count#">
	</cfloop>


Please keep in mind this was to build a chart so this won't work just copy and pasting.

For your situation I would suggest doing something like this:
<cfquery name="dates" datasource="YOURDATASOURCE">
SELECT date_column FROM table_with_dates
</cfquery>

<cfloop from="#startdate#" to="#enddate#" index="i">

	 <cfset temp = #ListFind(ValueList(dates.date_column), i)#>

	 <cfif temp>
		  FOUND IT!
	 <cfelse>
		  Didn't Find it
	 </cfif>

</cfloop>



I think this may be a more efficient way of doing what I did useing query of queries... might actually convert my code :). But anyway, you run your query to pull back all the dates, then loop from a startdate to an enddate (1st of the month to the last of the month), then you set a temp variable to true/false using ListFind... ListFind looks at the list of dates you pulled back from the query and checks if i (the current date in the loop) is in the list. If it is, it returns true, if not false... then you can do an if statement to handle it however you want.


I think first you have to decide what your regular hours are. 8-5? Then from there you can choose what is outside the scope. Do your workers work outside normal business hours?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1