determining how many Tuesdays there are in a month

[Guest]

I have a date/time column named date_entered. I am trying to query hom many Tuesdays form any given month that have been entered, such as determining how many Tuesdays are in the month of April.

I tried using

<CFQUERY NAME="update" datasource="#application.database#">
select date_entered
from quarterly
where Month(date_entered) = 4 and dayofweek(date_entered) = 3
</cfquery>

Then I was trying to use #update.recordcount# to give me the amount of Tuesdays for that month.

but I keep getting an error that date_entered is undefined.

Any ideas why?

[-__cfSearching__-]

Post the full error message.

[Guest]

My bad, here is the error message:

[Macromedia][SequeLink JDBC Driver][ODBC  Socket][Microsoft][ODBC Microsoft Access Driver] Undefined function  'dayofweek' in expression.

The error occurred in wwwroot\forms\test-date.cfm Line 2

[Dan_Bracuk]

You appear to be attempting to use ColdFusion functions as part of your sql.  You have to use database functions instead.

Also, your query won't necessarily count the number of Tuesdays in that month.

[Guest]

So, how do I determine the amount of Tuesdays in a month?

[Dan_Bracuk]

I'd do it strictly with ColdFusion date functions.  I'd start with creating a date for the 1st of the month.  Then I'd be looking at the DayOfWeek() for that date, and the DaysInMonth for that date.  You should be able to take it from there.

[Dan_Bracuk]

Upon further review, I'd do it like this:

Find the first day of the month.

Add days until you come to the first Tuesday.

Add 28 days.

If you are still in the same month, there are 5 Tuesdays.  Otherwise there are 4.

[Guest]

Maybe it would help more if I explained what I am trying to do. What I  am trying to accomplish is giving an account of quarterly attendance  for our Tuesday morning meetings.
As an example for the month of April, there are 80 entries with dates of  4/5/2011, 4/12/2011, 4/19/2011, and 4/26/2011 with 20  entries(attendies) for each date. I am using the sum query to determine  how many people have participated in our meetings on a weekly basis. I  wanted to count the tuesdays in the month so I could give a total like  "You have attended 9 out of 12 meetings this quarter. That is why I have  been trying to count the Tuesdays in the month. Does this help explain  what I am trying to accomplish better?

To me it makes  sense (which isn't always the case) to get a count on all of the  different Tuesdays in the month, using coldfusion date functions and  pulling out say all of the Tuesdays that fall on the 5th, 12th, 19th,  and 26th.

[Dan_Bracuk]

Once you have your query that includes dates and attendees, you can do a query of queries (select count(distinct date)  from yourquery) to get the number of meetings during that date range. 

However, this has nothing to do with how many Tuesdays are in any given month, unless your date range is for that month.  If it's for the quarter, don't you want the number of meeting days in the quarter?

[Guest]

Yes, you are right. I was trying to get it to work for month to see if it would work on a smaller scale. I do want to know how many Tuesdays there are in the entire quarter.

[Guest]

After experimenting with your query of queries suggestion, I was able to get it to work. Thanks. Here is the code I came up with. I don't know if there is a better way to optimize it, but it is working!!! Again, thanks for your patience.

<CFQUERY NAME="activity" datasource="#application.database#">
select date_entered
from quarterly
WHERE ((date_entered BETWEEN #CreateODBCDate(form.StartDate)# AND #CreateODBCDate(form.EndDate)#))
</cfquery>

<cfquery dbtype="query" name="GetInfo">
select count(distinct date_entered) AS qty2
from activity
</cfquery>

[-__cfSearching__-]

I have only skimmed the thread, but I do not see the need for a QoQ. Just do the COUNT(DISTINCT ..) in your original query.

Also, as already mentioned, that total has nothing to do with Tuesdays 😉 It is just a count of whatever distinct meeting dates are entered. If that is what you need, then you are good to go. But if you really need Tuesdays only, you will need to refine the query.

Message was edited by: -==cfSearching==-

Message was edited by: -==cfSearching==-

[Dan_Bracuk]

Shouldn't the first query have something about attendance?

[Guest]

Actually I had another queary pull the info for the attendance that give me the count of attendance.

<CFQUERY NAME="update" datasource="#application.database#">
select contact_first, contact_last, sum(attended) as QTY
from quarterly
WHERE ((date_entered BETWEEN #CreateODBCDate(form.StartDate)# AND #CreateODBCDate(form.EndDate)#))
group by contact_last, contact_first
</cfquery>

I then used the query we have been discussing:

<CFQUERY NAME="activity" datasource="#application.database#">
select date_entered
from quarterly
WHERE ((date_entered BETWEEN #CreateODBCDate(form.StartDate)# AND #CreateODBCDate(form.EndDate)#))
</cfquery>

<cfquery dbtype="query" name="GetInfo">
select count(distinct date_entered) AS qty2
from activity
</cfquery>

A previous post suggested I should have used the following code in the first query: select count(distinct date_entered) AS qty2

<CFQUERY NAME="activity" datasource="#application.database#">
select count(distinct date_entered) AS qty2
from quarterly
WHERE ((date_entered BETWEEN #CreateODBCDate(form.StartDate)# AND #CreateODBCDate(form.EndDate)#))
</cfquery>

Unfortunately, I get the following error:

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC  Microsoft Access Driver] Syntax error (missing operator) in query  expression 'count(distinct date_entered)'.

[-__cfSearching__-]

[Microsoft][ODBC  Microsoft Access Driver]

That explains it.  MS Access is a desktop database. So it does not support all the features of an enterprise database like MS SQL.  COUNT(DISTINCT...) is not supported in MS Access.  But you could probably simulate it with a derived query.

          SELECT COUNT(*) AS DistinctCount

          FROM   (

                            SELECT DISTINCT date_entered
                            FROM   quarterly
                            WHERE date_entered BETWEEN #CreateODBCDate(form.StartDate)# AND #CreateODBCDate(form.EndDate)#

                       ) q

[Guest]

Yes, I started out with a standalone version of Coldfusion on my machine at home. So access was what I had to work with. I just haven't been comfortable enough to graduate to my_sql yet. Basically because I am not sure how to set up my_sql on my test machine at home.

[Owainnorth]

I just haven't been comfortable enough to graduate to my_sql yet. Basically because I am not sure how to set up my_sql on my test machine at home.

Genuinely, I would strongly advise sacking off Access - it's an ancient, very limited technology and you're wasting your time learning it when you could be learning something more useful.

What OS are you running? If it's Windows I could easily knock you up an install howto in the morning.

[Guest]

That would be great. Currently, I am running windows XP and CF8. I am thinking of installing windows 7 next week though. How well does windows 7 play with CF8. I'm using 8 because my hosting company uses 8. Although by now they probably have 9 installed..

[Owainnorth]

How well does windows 7 play with CF8. I'm using 8 because my hosting company uses 8. Although by now they probably have 9 installed..

Honestly I can't say, I've never tried - but I'm sure it'll be fine. I'll do you a MySQL runthrough in the morning, it's really very easy.

[Dan_Bracuk]

In this query,

<CFQUERY NAME="update" datasource="#application.database#">
select contact_first, contact_last, sum(attended) as QTY
from quarterly
WHERE ((date_entered BETWEEN #CreateODBCDate(form.StartDate)# AND #CreateODBCDate(form.EndDate)#))
group by contact_last, contact_first
</cfquery>

What are contact_first and contact last?

[Guest]

Contact_first and contact_last are the first name and last name pulled from a contacts table. They were pulled from the contacts table and placed into the quarterly table along with the weekly attendance. Contact_first, conact_last, attended and date_entered make up the quarterly table.

[Owainnorth]

They were pulled from the contacts table and placed into the quarterly table along with the weekly attendance.

Naughty! Why the copying of data? If you're copying data, it normally means your database design is wrong.

[Guest]

I'm not sure I follow you. I originally set up the database to hold all of the data on membership, name, phone company, website, etc. It doesn't make sense to me to bloat the contact inormation with a year's worth of attendance data on top of it. Actually, I guess I should have just grabbed the contact_id instead and joined the info on output.

[Owainnorth]

Actually, I guess I should have just grabbed the contact_id instead and joined the info on output.

Spot on, that's *exactly* what you do

Duplication of data is databasing's #1 no-no. The whole point of relational databases (MySQL, Oracle, SQL Server, Access etc) is that the data is only ever stored once, then you do a join when selecting it back.

What happens if someone in your company gets married and changes their name? You'd have to run bulk updates of several tables to update it. Miss a table? you're stuck with a person's name in that table that you cannot reference back to a person - you've effectively orphaned all your data about their attendance. If you only store their name in the Contacts table with an ID then just join when you select it back, you only have to store it once. Far smaller database, far quicker database, far more maintainable database.

The only exception I've ever found is with invoicing and financial data - when storing things like Invoices in a database, you need to store a snapshot of the details of the person or company *at the time*; i.e. if they change their company name you do *not* want to update past data, so you have to store it again. Anything other than that, you should be storing it once and selecting it back in a join.

[Guest]

That makes sense. It is a practice I need to get into the habit of. I didn't think long term because most of the members don't last over a year.