Skip to main content
M
Mi-ul-vmijiI
Known Participant
December 22, 2008
Answered

Help / pointers needed to create a results page

  • December 22, 2008
  • 8 replies
  • 1045 views
Hi

I'm after a few pointers to get me going in the right direction.

I have plenty of data but now need to get some of it into a useful report. I guess I need to be using GROUP BY but at the moment I'm just getting rows of data rather than a useful table.

My mySql db has the following (relevant) fields

bookingID, bookingDate, stats

bookingID is an auto int (and not particularly relevant at the moment)
bookingDate is dateFormat
stats is a char(1) either N, X, U, A, F or P


I'm after a table
that will give me

#Week# | #total number of bookings this week# | #number of F bookings# | #number of U bookings#

(i'm happy not to worry about the week value and do it on a daily basis to start with)


Any help gratefully appreciated

Mi-ul

This topic has been closed for replies.
Correct answer Newsgroup_User
this works very well for me:

<cfset statsList = "X|F|P|A|U|N">
<cfquery name="q1" datasource="...">
SELECT WEEK(b.bookingDate, 5) AS bookingWeek, COUNT(b.bookingID) AS
totalBookings
<cfloop list="#statsList#" index="stat" delimiters="|">
, COUNT(sq_#stat#.bookingID) AS totalBookings_#stat#
</cfloop>
FROM bookings b
<cfloop list="#statsList#" index="stat" delimiters="|">
LEFT JOIN (SELECT b#stat#.bookingID FROM bookings b#stat# WHERE stats =
'#stat#') sq_#stat# ON b.bookingID = sq_#stat#.bookingID
</cfloop>
GROUP BY bookingWeek
ORDER BY bookingWeek
</cfquery>
<cfoutput query="q1">
Week: #bookingWeek# | Total Bookings: #totalBookings#<cfloop
list="#statsList#" index="stat" delimiters="|"> | #stat#:
#q1['totalBookings_' & stat][currentrow]#</cfloop><br>
</cfoutput>


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

8 replies

M
Mi-ul-vmijiIAuthor
Known Participant
December 23, 2008
THanks Dan and Azadi!

I've now got plenty to play with!

Azadi - You were 99.9% spot on - I've changed it to get YEARWEEK rather than just WEEK.

Now to try and get the other few similar bits working.

Cheers Guys
N
Newsgroup_UserCorrect answer
Inspiring
December 23, 2008
this works very well for me:

<cfset statsList = "X|F|P|A|U|N">
<cfquery name="q1" datasource="...">
SELECT WEEK(b.bookingDate, 5) AS bookingWeek, COUNT(b.bookingID) AS
totalBookings
<cfloop list="#statsList#" index="stat" delimiters="|">
, COUNT(sq_#stat#.bookingID) AS totalBookings_#stat#
</cfloop>
FROM bookings b
<cfloop list="#statsList#" index="stat" delimiters="|">
LEFT JOIN (SELECT b#stat#.bookingID FROM bookings b#stat# WHERE stats =
'#stat#') sq_#stat# ON b.bookingID = sq_#stat#.bookingID
</cfloop>
GROUP BY bookingWeek
ORDER BY bookingWeek
</cfquery>
<cfoutput query="q1">
Week: #bookingWeek# | Total Bookings: #totalBookings#<cfloop
list="#statsList#" index="stat" delimiters="|"> | #stat#:
#q1['totalBookings_' & stat][currentrow]#</cfloop><br>
</cfoutput>


Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/
D
Dan_Bracuk
Inspiring
December 22, 2008
Something like this.

statsList = "X|F|P|A|U|N";

<cfoutput>Date|#statsList#<br><cfoutput>
<cfoutput query="yourquery" group="bookingDate">
#bookingdate#
<cfloop list="#statsList#" delimters="|" index="idx">
<cfquery name="q2" dbtype="query">
select thecount
from yourquery
where bookingdate=#bookingdate#
and stats = '#idx#"
</cfquery>
| #q2.thecount#
</cfloop>
<br>
</cfoutput>
M
Mi-ul-vmijiIAuthor
Known Participant
December 22, 2008
OK that plan didn't work quite so well as the QoQ doesn't support the joins i was trying to make.

I've now got a query that gives me the following

Date | Count | stats

1/12 | 3 | F
1/12 | 6 | N
2/12 | 3 | F
2/12 | 4 | X

where stats is one of 6 characters.

How can I transform this into a table with a single row for date and 6 columns for the stats count rather than a row for each date / stats combination?

Date | X | F | P | A | U | N
1/12 | 3 | 0 | 0 | 0 | 2 | 1

Cheers Michael
M
Mi-ul-vmijiIAuthor
Known Participant
December 22, 2008
I think I'm going to start just getting the results by date.

My current plan is to group by bookingDate and have individual queries for the stats - where stats='N', stats='F' stats='U' etc.

Then probably a QoQ to match up the bookingDate across each of the above queries.

I hope this way will work but may be excessive work on the db.

Having a seperate db table for weeks might be useful to match them with our naming convention.
D
Dan_Bracuk
Inspiring
December 22, 2008
If all your booking dates have 0s for the time part, the query is simple.

Getting the week portion is tough. You might consider a database table for this. We have one. The primary key is the date, and other fields include fiscal year, fiscal period, and week beginning.
M
Mi-ul-vmijiIAuthor
Known Participant
December 22, 2008
bookingDate is a date stamp (at 00:00:0 hours)

I've got the ben forta book and glanced at a few bits and I'll be able to work out the query (hopefully, with a few pointers) but I'm not so sure about getting it the right way to link it with the CF
D
Dan_Bracuk
Inspiring
December 22, 2008
Booking date is what datatype?

Also, do you know how to select counts? If not, I've heard good things about the book Teach Yourself SQL in 10 Minutes by Ben Forta.
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices