Re: Aggregate query help please
Hi Barney Your answer was cut - would you mind resending please ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324690 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Aggregate query help please
I have no idea what that was. My sent mail has the same blank message, but I didn't reply. WITH ROLLUP is the clause you want though, if your DB supports it. cheers, barneyb -- Barney Boisvert bboisv...@gmail.com http://www.barneyb.com/ On Jul 17, 2009, at 4:09 PM, Seamus Campbell coldfus...@boldacious.com wrote: Hi Barney Your answer was cut - would you mind resending please ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324691 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Aggregate query help please
I've got some code that uses the JExcelAPI for converting queries to Excel spreadsheets. It's got some support for formulas, so theoretically you could wow the people you need to send it to by not only having the sums there, but having them update if they change the numbers in the various columns. Here's an example of its use: var workbook = variables.workbook; var sheet = workbook.createSheet(Test Sheet); var results = ; sheet.addFormula(evaluateat=row,columnname=Net,formula=sum(amt[currentrow])-((sum(amt[currentrow]) * 5) / 100)); sheet.setQuery(runQuery(SELECT *,'' as overhead FROM creadittransactions LIMIT 19,myDSN)); workbook.writeXLSFile(./test.xls); results = sheet.getQuery(); debug(results); Dunno if an approach like that would be easier or harder, but, well, it's yours for the asking. -- Permanence, perseverance and persistence in spite of all obstacle s, discouragement s, and impossibilities: It is this, that in all things distinguishes the strong soul from the weak. Thomas Carlyle On Thu, Jul 16, 2009 at 5:57 AM, Seamus Campbellcoldfus...@boldacious.com wrote: I have a database (access at the moment) for members of an organisation. The members are scattered throughout (mostly in one state) Australia (with a couple in the US) They all are one of three language groups. I need to export an excel spreadsheet with a count of members from each language group per town, region, state, country plus total count . Then another excel spreadsheet with the count per language group for the end of each financial year. (plus total here as well) I am just at my wits end trying to work this out. I have to have it ready in the next few days. Can anyone help - I think I'd need to forward the relevant tables and the spreadsheets to show the format needed. I'm willing to pay for someone to help me with this. (I presume that it's only 1-2 hours work for someone who knows what they are doing) Many thanks Seamus ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324692 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Aggregate query help please
I have a database (access at the moment) for members of an organisation. The members are scattered throughout (mostly in one state) Australia (with a couple in the US) They all are one of three language groups. I need to export an excel spreadsheet with a count of members from each language group per town, region, state, country plus total count . Then another excel spreadsheet with the count per language group for the end of each financial year. (plus total here as well) I am just at my wits end trying to work this out. I have to have it ready in the next few days. Can anyone help - I think I'd need to forward the relevant tables and the spreadsheets to show the format needed. I'm willing to pay for someone to help me with this. (I presume that it's only 1-2 hours work for someone who knows what they are doing) Many thanks Seamus ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324583 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Aggregate query help please
On Thu, Jul 16, 2009 at 4:57 AM, Seamus Campbellcoldfus...@boldacious.com wrote: I have a database (access at the moment) for members of an organisation. The members are scattered throughout (mostly in one state) Australia (with a couple in the US) They all are one of three language groups. I need to export an excel spreadsheet with a count of members from each language group per town, region, state, country plus total count . Then another excel spreadsheet with the count per language group for the end of each financial year. (plus total here as well) I am just at my wits end trying to work this out. I have to have it ready in the next few days. Can anyone help - I think I'd need to forward the relevant tables and the spreadsheets to show the format needed. I'm willing to pay for someone to help me with this. (I presume that it's only 1-2 hours work for someone who knows what they are doing) Many thanks Seamus ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324593 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query Help Please
select *, exists ( select * from orders where date between #date1# and #date2# ) as hasOrdered from customers order by name, id Use CFQUERYPARAM, of course. Another variation is a left join. Ditto on using cfqueryparam. SELECT c.ID, c.Name, c.Email, SUM(CASE WHEN o.Date IS NULL THEN 0 ELSE 1 END) ordersFound FROMtblCustomers AS c LEFT JOIN tblOrders AS o ON c.ID = o.UserID AND o.Date BETWEEN @startDate AND @endDate GROUP BY c.ID, c.Name, c.Email ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304381 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query Help Please
I dont believe this is working with Access DB SUM(CASE WHEN o.Date IS NULL THEN 0 ELSE 1 END) ordersFound C S [EMAIL PROTECTED] wrote: select *, exists ( select * from orders where date between #date1# and #date2# ) as hasOrdered from customers order by name, id Use CFQUERYPARAM, of course. Another variation is a left join. Ditto on using cfqueryparam. SELECT c.ID, c.Name, c.Email, SUM(CASE WHEN o.Date IS NULL THEN 0 ELSE 1 END) ordersFound FROM tblCustomers AS c LEFT JOIN tblOrders AS o ON c.ID = o.UserID AND o.Date BETWEEN @startDate AND @endDate GROUP BY c.ID, c.Name, c.Email ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304382 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query Help Please
I dont believe this is working with Access DB SUM(CASE WHEN o.Date IS NULL THEN 0 ELSE 1 END) ordersFound I think Access's version of CASE is IIF(..). Try using IIF instead and also try the query Barney suggested. One of them should work. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304392 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Query Help Please
I will try to make this as simple as possible. Any help would be greatly appreciated. I have 2 tables like below. tblCustomers === ID | Name | Email | 1, John, [EMAIL PROTECTED] 2, Bob, [EMAIL PROTECTED] 3, Steve, [EMAIL PROTECTED] === tblOrders === UserID | Date| 1, 12/22/2007 3, 1/2/2008 === I want to do a query like a scorecard view, that will list all CUSTOMERS and show yes or no if they had an order bewtween 2 dates. RESULTS WOULD BE LIKE THIS IF I SEARCH BETWEEN 12/01/2007 - 1/30/2008 - ID | Name | Email | 1, John, YES 2, Bob, NO 3, Steve, YES ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304374 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query Help Please
select *, exists ( select * from orders where date between #date1# and #date2# ) as hasOrdered from customers order by name, id Use CFQUERYPARAM, of course. cheers, barneyb On Mon, Apr 28, 2008 at 8:57 PM, Brian Sheridan [EMAIL PROTECTED] wrote: I will try to make this as simple as possible. Any help would be greatly appreciated. I have 2 tables like below. tblCustomers === ID | Name | Email | 1, John, [EMAIL PROTECTED] 2, Bob, [EMAIL PROTECTED] 3, Steve, [EMAIL PROTECTED] === tblOrders === UserID | Date| 1, 12/22/2007 3, 1/2/2008 === I want to do a query like a scorecard view, that will list all CUSTOMERS and show yes or no if they had an order bewtween 2 dates. RESULTS WOULD BE LIKE THIS IF I SEARCH BETWEEN 12/01/2007 - 1/30/2008 - ID | Name | Email | 1, John, YES 2, Bob, NO 3, Steve, YES ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304375 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
SOLVED: Need query help please
Here's my final solution for the query - a self-join. cfquery name=getimages datasource= SELECT TBIG.prodimagefilename AS FullSizeImageFilename, TLITTLE.prodimagefilename AS ThumbnailImageFilename FROM tblimages_rel TBIG, tblimages_rel TLITTLE WHERE TBIG.prodID = #URL.prodID# !--- Matches only rows of product ID you want in the full-size version of table --- AND TBIG.prodID = TLITTLE.prodID !--- Joins the two versions of the table --- AND TBIG.ImageTypeID = 2 !--- tells it to limit this table to full-size images --- AND TLITTLE.ImageTypeID = 1 !--- tells it to limit this table to thumbnail images --- /cfquery Thanks for the tips. And thanks matthieu for this sql. Will ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223165 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Need query help please
The way I do it on a couple of sites is to store large images in one folder and thumbs in another. I store one file name in the db table and just use: #application.photos#\#table.jpeg_name# or #application.photos_thumb#\#table.jpeg_name# Jenny ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223040 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Need query help please
I just needed help making my query work. Matthieu is helping me with a self join. Thanks, Will ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223148 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Need query help please
For my products, I have a tblImageTypes that looks like this ImageTypeID (PK) ImageTypeName 1Thumbnail 2Large I have a many/linking tblimages_rel that looks like this ImageID (PK) ImageTypeID (FK) prodID (FK) filename 11 25 shirtsmall.jpg 22 25 shirtlarge.jpg On my detail page I'd like to show the small image and link it to the larger image but I'm having trouble on how to run my query. cfquery blah blah SELECT imageID, prodID, imagetypeID, prodimagefilename FROM tblimages_rel WHERE prodID = #URL.prodID# ??? Thanks, Will ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222972 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Need query help please
why not just name the images something like img_large.jpg and img_thumb.jpg this way you can just store img in the database (in one record), and output #img#_large.jpg or #img#_thumb.jpg depending on which you need? On 11/2/05, Will Tomlinson [EMAIL PROTECTED] wrote: For my products, I have a tblImageTypes that looks like this ImageTypeID (PK) ImageTypeName 1Thumbnail 2Large I have a many/linking tblimages_rel that looks like this ImageID (PK) ImageTypeID (FK) prodID (FK) filename 11 25 shirtsmall.jpg 22 25 shirtlarge.jpg On my detail page I'd like to show the small image and link it to the larger image but I'm having trouble on how to run my query. cfquery blah blah SELECT imageID, prodID, imagetypeID, prodimagefilename FROM tblimages_rel WHERE prodID = #URL.prodID# ??? Thanks, Will ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222975 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Need query help please
yeah I agree with Charlie.. just add an extra column called thumbnail with the image name. On 11/2/05, Charlie Griefer [EMAIL PROTECTED] wrote: why not just name the images something like img_large.jpg and img_thumb.jpg this way you can just store img in the database (in one record), and output #img#_large.jpg or #img#_thumb.jpg depending on which you need? On 11/2/05, Will Tomlinson [EMAIL PROTECTED] wrote: For my products, I have a tblImageTypes that looks like this ImageTypeID (PK) ImageTypeName 1Thumbnail 2Large I have a many/linking tblimages_rel that looks like this ImageID (PK) ImageTypeID (FK) prodID (FK) filename 11 25 shirtsmall.jpg 22 25 shirtlarge.jpg On my detail page I'd like to show the small image and link it to the larger image but I'm having trouble on how to run my query. cfquery blah blah SELECT imageID, prodID, imagetypeID, prodimagefilename FROM tblimages_rel WHERE prodID = #URL.prodID# ??? Thanks, Will ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222981 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Mambo SQL query help.... Please
This looks like a job for OLAP. On Tue, 23 Nov 2004 15:31:07 -0600, Mark W. Breneman [EMAIL PROTECTED] wrote: John, I thought about that and also just caching the results, but that will not work in my case. I fear that I did not explain this very well. This is a dynamic query that can get the results for a different years, different ages and different school districts. The user can pick several items from several pull down list in a form. Any other ideas? Thanks. Mark W. Breneman Here is the full CFquery tag: cfquery name=getData datasource=#database# SELECT count(*) as totalRecords, (SELECT count(*) FROM CheckListData WHERE review_no = 1 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'no' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'no' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2No, (SELECT count(*) FROM CheckListData WHERE review_no = 1 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'no' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND TWO_ATTEND = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R2Yes,
Mambo SQL query help.... Please
I just inherited a project that has a very very large SQL count query. Now when I say very large I mean very large. What I have posted here is only 4 blocks of the 35 total blocks of SQL code in this one query. The total query takes about 120 seconds to run and often takes down the CF server. This query is made up of 203 in line sub queries and only returns a single row of values. Currently this query is not a stored procedure it is just a standard cfquery. So my question is where do I get started rewriting this query. This report page is on an administrative website where the traffic is very low. But never the less 2 mins is far too long to wait for a simple report. First off I can see that the yes, No and NA should be converted to a number. The DISTRICT also needs to be converted to a number. Then the whole thing needs to be converted into a stored procedure. Is there an EZ way to write this as a stored procedure. Currently the query is made by a Cfloop list that changes the query based on what options the users pick. What do I do next? Is there an EZer way to get this data then in line queries? What can I do first to get the biggest bang for my $. IOW is there something I can do quickly to get 40% shorter query run time? THANKS! Here is a small sample of the Query: SELECT count(*) as totalRecords, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q1R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'yes' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q1R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'no'AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q1R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q1R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'yes' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q1R2Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'no'AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q1R2No, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q2R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'yes'AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q2R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'no' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q2R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q2R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND TWO_ATTEND = 'yes'AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q2R2Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND TWO_ATTEND = 'no' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q2R2No, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q3R1,
RE: Mambo SQL query help.... Please
Maybe make it into some kind of scheduled job that runs every X hours or something and populates a table with the new values rather than doing real-time queries. Then the 2 minutes isn't as big of a deal. It's probably still a good idea to make it a stored procedure and then just execute that however often you want to populate the temporary table with the newest values. Then your code just does a select * from temporaryTable. John -Original Message- From: Mark W. Breneman [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 23, 2004 4:02 PM To: CF-Talk Subject: Mambo SQL query help Please I just inherited a project that has a very very large SQL count query. Now when I say very large I mean very large. What I have posted here is only 4 blocks of the 35 total blocks of SQL code in this one query. The total query takes about 120 seconds to run and often takes down the CF server. This query is made up of 203 in line sub queries and only returns a single row of values. Currently this query is not a stored procedure it is just a standard cfquery. So my question is where do I get started rewriting this query. This report page is on an administrative website where the traffic is very low. But never the less 2 mins is far too long to wait for a simple report. First off I can see that the yes, No and NA should be converted to a number. The DISTRICT also needs to be converted to a number. Then the whole thing needs to be converted into a stored procedure. Is there an EZ way to write this as a stored procedure. Currently the query is made by a Cfloop list that changes the query based on what options the users pick. What do I do next? Is there an EZer way to get this data then in line queries? What can I do first to get the biggest bang for my $. IOW is there something I can do quickly to get 40% shorter query run time? THANKS! Here is a small sample of the Query: SELECT count(*) as totalRecords, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q1R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'yes' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q1R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'no' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q1R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q1R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'yes' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q1R2Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'no' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q1R2No, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q2R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'yes' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q2R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'no' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q2R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q2R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND TWO_ATTEND = 'yes' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP15 AND DISTRICT = 'Black River Falls') as tot_Q2R2Yes
RE: Mambo SQL query help.... Please
John, I thought about that and also just caching the results, but that will not work in my case. I fear that I did not explain this very well. This is a dynamic query that can get the results for a different years, different ages and different school districts. The user can pick several items from several pull down list in a form. Any other ideas? Thanks. Mark W. Breneman Here is the full CFquery tag: cfquery name=getData datasource=#database# SELECT count(*) as totalRecords, (SELECT count(*) FROM CheckListData WHERE review_no = 1 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'no' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'no' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2No, (SELECT count(*) FROM CheckListData WHERE review_no = 1 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'no' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND TWO_ATTEND = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R2Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND TWO_ATTEND = 'no' cfif form.schoolyear is not allAND schoolyear =
RE: Mambo SQL query help.... Please
Not really, unless you did stored procedures or something. I guess you could also use a view for each type of data you want to look up. I'm not really sure what that would do for you performance-wise but it may help organize things some. I think that you're going to take a while because of all the data you're checking against and all of the queries you're doing. I don't really know that there is a way around it besides caching. cfqueryparam should improve performance some without having to go to a stored procedure. Out of curiosity, have you tried making each query separate? Maybe that would help by eliminating the subqueries? I'm not a SQL guru so that could be dead wrong, but it may be worth a try. At least that way, you could use CF to cache some of the queries and then if someone changes the search criteria, only the query with new criteria will need to access the database. John -Original Message- From: Mark W. Breneman [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 23, 2004 4:31 PM To: CF-Talk Subject: RE: Mambo SQL query help Please John, I thought about that and also just caching the results, but that will not work in my case. I fear that I did not explain this very well. This is a dynamic query that can get the results for a different years, different ages and different school districts. The user can pick several items from several pull down list in a form. Any other ideas? Thanks. Mark W. Breneman Here is the full CFquery tag: cfquery name=getData datasource=#database# SELECT count(*) as totalRecords, (SELECT count(*) FROM CheckListData WHERE review_no = 1 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'no' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'no' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2No, (SELECT count(*) FROM CheckListData WHERE review_no = 1 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'no' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision
Re: Mambo SQL query help.... Please
Mark W. Breneman wrote: So my question is where do I get started rewriting this query. If that is an option, start with optimizing the data model. First off I can see that the yes, No and NA should be converted to a number. To a BOOLEAN. The DISTRICT also needs to be converted to a number. In general, you need to normalize the data. Obviously the DISTRICT should be a foreign key to a district table, but for instance, I am wondering if STUDENT_AGE_AT_IEP isn't redundant with for instance some table with records from students which has a birthday. Then the whole thing needs to be converted into a stored procedure. Why? For performance? A stored procedure has a precompiled execution plan so it saves you the time to parse and plan the query. From the looks of it you are running multiple indexscans and possibly even seqscans on the table so that won't help you. It saves you 99.99% of the 1 second it takes to compile, and doesn't help with the 119 seconds it takes to run. Profile the query. What is the execution plan? Is your system I/O bound or CPU bound? What DBMS are you using? Jochem ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185260 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Mambo SQL query help.... Please
Jochem, Thank you for your reply. You have answered one of my questions I have never asked but, always wondered about. How a stored procedure improves performance. Is there a better way to get the end result then using the inline queries? I am running a MSSQL 2000 database. The execution plan in SQL Query Analyzer for this query is as large or larger then the query itself. Now how do I tell if the query is I/O bound or CPU bound? Thanks for your help. Mark W. Breneman -Cold Fusion Developer -Network Administrator Vivid Media [EMAIL PROTECTED] www.vividmedia.com 608.270.9770 -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 23, 2004 3:47 PM To: CF-Talk Subject: Re: Mambo SQL query help Please Mark W. Breneman wrote: So my question is where do I get started rewriting this query. If that is an option, start with optimizing the data model. First off I can see that the yes, No and NA should be converted to a number. To a BOOLEAN. The DISTRICT also needs to be converted to a number. In general, you need to normalize the data. Obviously the DISTRICT should be a foreign key to a district table, but for instance, I am wondering if STUDENT_AGE_AT_IEP isn't redundant with for instance some table with records from students which has a birthday. Then the whole thing needs to be converted into a stored procedure. Why? For performance? A stored procedure has a precompiled execution plan so it saves you the time to parse and plan the query. From the looks of it you are running multiple indexscans and possibly even seqscans on the table so that won't help you. It saves you 99.99% of the 1 second it takes to compile, and doesn't help with the 119 seconds it takes to run. Profile the query. What is the execution plan? Is your system I/O bound or CPU bound? What DBMS are you using? Jochem ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185263 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Mambo SQL query help.... Please
John, I never thought of running separate SQL statements VS the inlin. I'm not sure if that would be faster or not. I may mock up a quick test of that. Mark W. Breneman -Cold Fusion Developer -Network Administrator Vivid Media [EMAIL PROTECTED] www.vividmedia.com 608.270.9770 -Original Message- From: Burns, John D [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 23, 2004 3:36 PM To: CF-Talk Subject: RE: Mambo SQL query help Please Not really, unless you did stored procedures or something. I guess you could also use a view for each type of data you want to look up. I'm not really sure what that would do for you performance-wise but it may help organize things some. I think that you're going to take a while because of all the data you're checking against and all of the queries you're doing. I don't really know that there is a way around it besides caching. cfqueryparam should improve performance some without having to go to a stored procedure. Out of curiosity, have you tried making each query separate? Maybe that would help by eliminating the subqueries? I'm not a SQL guru so that could be dead wrong, but it may be worth a try. At least that way, you could use CF to cache some of the queries and then if someone changes the search criteria, only the query with new criteria will need to access the database. John -Original Message- From: Mark W. Breneman [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 23, 2004 4:31 PM To: CF-Talk Subject: RE: Mambo SQL query help Please John, I thought about that and also just caching the results, but that will not work in my case. I fear that I did not explain this very well. This is a dynamic query that can get the results for a different years, different ages and different school districts. The user can pick several items from several pull down list in a form. Any other ideas? Thanks. Mark W. Breneman Here is the full CFquery tag: cfquery name=getData datasource=#database# SELECT count(*) as totalRecords, (SELECT count(*) FROM CheckListData WHERE review_no = 1 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'no' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'no' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2No, (SELECT count(*) FROM CheckListData WHERE review_no = 1 cfif form.schoolyear is not allAND schoolyear = #form.schoolyear#/cfifcfif form.cesadivision is not allAND cesadivision = #form.cesadivision#/cfif #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'yes' cfif form.schoolyear is not allAND schoolyear = #form.schoolyear
Re: Mambo SQL query help.... Please
Mark W. Breneman wrote: Is there a better way to get the end result then using the inline queries? With a helicopter view of the problem I can think of various execution plans that could make sense in your situation. But it all depends on the schema, cardinality and distribution of the data. All of which would express themselves in the execution plan. Possible options I can think of on the SQL front are: - replace your COUNT queries by SUM + CASE SELECT SUM(CASE WHEN review_no = 1 THEN 1 ELSE 0 END) as tot_Q1R1, SUM(CASE WHEN review_no = 2 THEN 1 ELSE 0 END) as tot_Q1R2, SUM(CASE WHEN review_no = 1 AND ONE_INVITED = 'no' THEN 1 ELSE 0 END) as tot_Q1R1No, SUM(CASE WHEN review_no = 2 AND ONE_INVITED = 'no' THEN 1 ELSE 0 END) as tot_Q1R2No, etc. FROM CheckListData WHERE schoolyear = 2003 AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP 15 AND DISTRICT = 'Black River Falls' (The idea behind this query is to force the database to run just one scan of the table and do the rest in RAM. This should help a lot if you are running many scans (one for each subquery) and are I/O bound.) Or: - create a temp table with all the data that matches the primary predicates: schoolyear = 2003 AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP 15 AND DISTRICT = 'Black River Falls' - run your query on just that table - drop the temp table (Here we want to do away with possible seqscans due to low cardinality on the primary predicates.) But all of these are just (imperfect) means to an end: getting a good execution plan. I'll see if I can dig up some high level literature on database internals. Also, keep looking at the schema of your table. If you can get your fields in your schema to be BOOLEAN NOT NULL, you might be able to do away with half of the subqueries, because total = true + false. (In your case with MS SQL Server that would be BIT fields.) The execution plan in SQL Query Analyzer for this query is as large or larger then the query itself. Isn't it largely repetetive? (I would expect so.) Else put it up on a website. Now how do I tell if the query is I/O bound or CPU bound? Task Manager: is your CPU at 100% ? If not, you need the Windows equivalent of IOstat to determine if the disks are running at their maximum capacity. If you are CPU bound, indexes often help. If you are I/O bound, normalize more so you store the data more efficiently. Jochem ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185265 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Mambo SQL query help.... Please
Jochem van Dieten wrote: But all of these are just (imperfect) means to an end: getting a good execution plan. I'll see if I can dig up some high level literature on database internals. While Googling for an easy howto I had little success: most general reading material is database specific and doesn't really grow an understanding of execution plans and most scientific literature assumes you already know too much about them. Then I came across SQL Tuning by Dan Tow: http://www.oreilly.com/catalog/sqltuning/ It looks like the book I wanted to write :-) Jochem ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185269 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Query Help Please.
2.) Can someone please suggest a good book on SQL syntax that will clear thing up for me. Other beginner resources: 1) http://www.sqlcourse.com/ http://www.sqlcourse.com/ 2) http://sqlcourse2.com/ http://sqlcourse2.com/ 3) http://www.freeprogrammingresources.com/sql.html http://www.freeprogrammingresources.com/sql.html(links to many others) [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL Query Help Please.
Thanks Umer Others, You guys have been of great help. Will try the query today.. Regards, Mark - Original Message - From: Umer Farooq [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, October 11, 2004 8:17 PM Subject: Re: SQL Query Help Please. Here you.. go.. returns.. orderId, date, customerId, customer name, paymentReceived, amount due (per order)... , SELECT orders.orderId, order.orderDate, orders.customerID, customers.customerName, customerPayment.PaymentReceived (SELECT sum(unitPrice * quantity) FROM orderDetails WHERE orderDetails.orderID = orders.orderID ) AS amountDue, FROM (orders LEFT JOIN customerPayment ON customerPayment.orderID = orders.orderID) LEFT JOIN customer ON customer.customerID = orders.customerID Nomad wrote: The Database tables and fields are: Orders (orderid,customerid,Orderdate) OrderDetails(Orderid,UnitPrice,productid,quantity) Customer(Customerid, customername, address) CustomerPayments(orderid,PaymentReceived) My purpose is to track the account status of the customer for each order they have placed. The desired format of result obtained from the query is like this: Customername, Orderid, Orderdate,AmountReceived, AmountDue(this is a calculated column). -- Umer Farooq Octadyne Systems [EMAIL PROTECTED] +1 (519) 772-5424 voice +1 (519) 635-2795 mobile +1 (208) 275-3824 fax LOOKING FOR A USED CAR IN IOWA VISIT: http://www.IowaMotors.com WARNING: --- The information contained in this document and attachments is confidential and intended only for the person(s) named above. If you are not theintended recipient you are hereby notified that any disclosure, copying, distribution, or any other use of the information is strictly prohibited.If you have received this document by mistake, please notify the sender immediately and destroy this document and attachments without making any copy of any kind. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Query Help Please.
2.) Can someone please suggest a good book on SQL syntax Teach Yourself SQL in 10 Minutes by Ben Forta. ISBN 0-672-32128-9 ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
SQL Query Help Please.
Hello! I am trying to create a join of four tables to get data from a db in the format I want. The Database tables and fields are: Orders (orderid,customerid,Orderdate) OrderDetails(Orderid,UnitPrice,productid,quantity) Customer(Customerid, customername, address) CustomerPayments(orderid,PaymentReceived) My purpose is to track the account status of the customer for each order they have placed. The desired format of result obtained from the query is like this: Customername, Orderid, Orderdate,AmountReceived, AmountDue(this is a calculated column). I am using Access 2000 with CFusion. 1.)Can someonehelp me with the SQL syntax please. I have tried several permutations and combinations of sql (to the best of my knowledge) but without success. 2.) Can someone please suggest a good book on SQL syntax that will clear thing up for me. Many Thanks in Advance, Mark Taylor Admin [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL Query Help Please.
Hi, http://www.techonthenet.com/access/queries/joins1.htm For beginner I would suggest.. SAMS SQL in 21 Days.. and Google.. :-) Nomad wrote: Hello! I am trying to create a join of four tables to get data from a db in the format I want. The Database tables and fields are: Orders (orderid,customerid,Orderdate) OrderDetails(Orderid,UnitPrice,productid,quantity) Customer(Customerid, customername, address) CustomerPayments(orderid,PaymentReceived) My purpose is to track the account status of the customer for each order they have placed. The desired format of result obtained from the query is like this: Customername, Orderid, Orderdate,AmountReceived, AmountDue(this is a calculated column). I am using Access 2000 with CFusion. 1.)Can someonehelp me with the SQL syntax please. I have tried several permutations and combinations of sql (to the best of my knowledge) but without success. 2.) Can someone please suggest a good book on SQL syntax that will clear thing up for me. Many Thanks in Advance, Mark Taylor Admin [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL Query Help Please.
Here's a start, though you'll have to explain your tables and your calculations further. SELECT c.Customername, a.Orderid, a.Orderdate, ( where is this being stored? AmountReceived maybe this could be d.PaymentReceivedAS AmountReceived ? ), ( put your formula for calculating AmountDue here, possibly a subquery ) FROMOrders a, OrderDetails b, Customer c, CustomerPayments d WHERE a.orderid = b.orderid AND a.orderid = d.orderid AND c.customerid = a.customerid Hope that helps some... I would recomend the SQL book by forta, or just look online for a good tutorial. -Josh -- Exciteworks, Inc Expert Hosting for less! *Ask for a free 30 day trial!* http://exciteworks.com Plans starting at -$12.95- including MS SQL Server! Nomad wrote: Hello! I am trying to create a join of four tables to get data from a db in the format I want. The Database tables and fields are: Orders (orderid,customerid,Orderdate) OrderDetails(Orderid,UnitPrice,productid,quantity) Customer(Customerid, customername, address) CustomerPayments(orderid,PaymentReceived) My purpose is to track the account status of the customer for each order they have placed. The desired format of result obtained from the query is like this: Customername, Orderid, Orderdate,AmountReceived, AmountDue(this is a calculated column). I am using Access 2000 with CFusion. 1.)Can someonehelp me with the SQL syntax please. I have tried several permutations and combinations of sql (to the best of my knowledge) but without success. 2.) Can someone please suggest a good book on SQL syntax that will clear thing up for me. Many Thanks in Advance, Mark Taylor Admin [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL Query Help Please.
Hi Josh, Thanks for your reply. Your query is similar to what I have been trying. My query is: SELECT customer.Customercompanyname, orders.Orderid, orders.Orderdate, (Orderdetails.unitprice * Orderdetails.quantity) As OrderValue, CustomerPayments.PRIG As PaymentsReceived FROMOrders , OrderDetails, Customer, CustomerPayments WHERE 0=0 AND Orderdetails.orderid=Orders.orderid AND CustomerPayments.orderid=Orders.orderid AND Orders.customerid=Customer.customerid The result is something like this which is wrong: Notice the repetition of the OrderId field. Ideally there should be one row for each order. Customer Name OrderID, OrderDate, OrderValuePayments Received. Company /10/2004 55 500 Company /10/2004 31898 500 Company /10/20045678 500 Company5 411/10/20042320 400 Company5 411/10/20044740 400 Company5 411/10/2004 26400 400 Company5 411/10/2004544600 400 - Original Message - From: Josh [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, October 11, 2004 6:07 PM Subject: Re: SQL Query Help Please. Here's a start, though you'll have to explain your tables and your calculations further. SELECT c.Customername, a.Orderid, a.Orderdate, ( where is this being stored? AmountReceived maybe this could be d.PaymentReceivedAS AmountReceived ? ), ( put your formula for calculating AmountDue here, possibly a subquery ) FROMOrders a, OrderDetails b, Customer c, CustomerPayments d WHERE a.orderid = b.orderid AND a.orderid = d.orderid AND c.customerid = a.customerid Hope that helps some... I would recomend the SQL book by forta, or just look online for a good tutorial. -Josh -- Exciteworks, Inc Expert Hosting for less! *Ask for a free 30 day trial!* http://exciteworks.com Plans starting at -$12.95- including MS SQL Server! Nomad wrote: Hello! I am trying to create a join of four tables to get data from a db in the format I want. The Database tables and fields are: Orders (orderid,customerid,Orderdate) OrderDetails(Orderid,UnitPrice,productid,quantity) Customer(Customerid, customername, address) CustomerPayments(orderid,PaymentReceived) My purpose is to track the account status of the customer for each order they have placed. The desired format of result obtained from the query is like this: Customername, Orderid, Orderdate,AmountReceived, AmountDue(this is a calculated column). I am using Access 2000 with CFusion. 1.)Can someonehelp me with the SQL syntax please. I have tried several permutations and combinations of sql (to the best of my knowledge) but without success. 2.) Can someone please suggest a good book on SQL syntax that will clear thing up for me. Many Thanks in Advance, Mark Taylor Admin [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL Query Help Please.
Hi, Here you.. go.. returns.. orderId, date, customerId, customer name, paymentReceived, amount due per order... , SELECT orders.orderId, order.orderDate, orders.customerID, customers.customerName, customerPayment.PaymentReceived (SELECT sum(unitPrice * quantity) FROM orderDetails WHERE orderDetails.orderID = orders.orderID ) AS amountDue, FROM (orders LEFT JOIN customerPayment ON customerPayment.orderID = orders.orderID) LEFT JOIN customer ON customer.customerID = orders.customerID Nomad wrote: Hi Josh, Thanks for your reply. Your query is similar to what I have been trying. My query is: SELECT customer.Customercompanyname, orders.Orderid, orders.Orderdate, (Orderdetails.unitprice * Orderdetails.quantity) As OrderValue, CustomerPayments.PRIG As PaymentsReceived FROMOrders , OrderDetails, Customer, CustomerPayments WHERE 0=0 AND Orderdetails.orderid=Orders.orderid AND CustomerPayments.orderid=Orders.orderid AND Orders.customerid=Customer.customerid The result is something like this which is wrong: Notice the repetition of the OrderId field. Ideally there should be one row for each order. Customer Name OrderID, OrderDate, OrderValuePayments Received. Company /10/2004 55 500 Company /10/2004 31898 500 Company /10/20045678 500 Company5 411/10/20042320 400 Company5 411/10/20044740 400 Company5 411/10/2004 26400 400 Company5 411/10/2004544600 400 - Original Message - From: Josh [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, October 11, 2004 6:07 PM Subject: Re: SQL Query Help Please. Here's a start, though you'll have to explain your tables and your calculations further. SELECT c.Customername, a.Orderid, a.Orderdate, ( where is this being stored? AmountReceived maybe this could be d.PaymentReceivedAS AmountReceived ? ), ( put your formula for calculating AmountDue here, possibly a subquery ) FROMOrders a, OrderDetails b, Customer c, CustomerPayments d WHERE a.orderid = b.orderid AND a.orderid = d.orderid AND c.customerid = a.customerid Hope that helps some... I would recomend the SQL book by forta, or just look online for a good tutorial. -Josh -- Exciteworks, Inc Expert Hosting for less! *Ask for a free 30 day trial!* http://exciteworks.com Plans starting at -$12.95- including MS SQL Server! Nomad wrote: Hello! I am trying to create a join of four tables to get data from a db in the format I want. The Database tables and fields are: Orders (orderid,customerid,Orderdate) OrderDetails(Orderid,UnitPrice,productid,quantity) Customer(Customerid, customername, address) CustomerPayments(orderid,PaymentReceived) My purpose is to track the account status of the customer for each order they have placed. The desired format of result obtained from the query is like this: Customername, Orderid, Orderdate,AmountReceived, AmountDue(this is a calculated column). I am using Access 2000 with CFusion. 1.)Can someonehelp me with the SQL syntax please. I have tried several permutations and combinations of sql (to the best of my knowledge) but without success. 2.) Can someone please suggest a good book on SQL syntax that will clear thing up for me. Many Thanks in Advance, Mark Taylor Admin [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL Query Help Please.
Here you.. go.. returns.. orderId, date, customerId, customer name, paymentReceived, amount due (per order)... , SELECT orders.orderId, order.orderDate, orders.customerID, customers.customerName, customerPayment.PaymentReceived (SELECT sum(unitPrice * quantity) FROM orderDetails WHERE orderDetails.orderID = orders.orderID ) AS amountDue, FROM (orders LEFT JOIN customerPayment ON customerPayment.orderID = orders.orderID) LEFT JOIN customer ON customer.customerID = orders.customerID Nomad wrote: The Database tables and fields are: Orders (orderid,customerid,Orderdate) OrderDetails(Orderid,UnitPrice,productid,quantity) Customer(Customerid, customername, address) CustomerPayments(orderid,PaymentReceived) My purpose is to track the account status of the customer for each order they have placed. The desired format of result obtained from the query is like this: Customername, Orderid, Orderdate,AmountReceived, AmountDue(this is a calculated column). -- Umer Farooq Octadyne Systems [EMAIL PROTECTED] +1 (519) 772-5424 voice +1 (519) 635-2795 mobile +1 (208) 275-3824 fax LOOKING FOR A USED CAR IN IOWA VISIT: http://www.IowaMotors.com WARNING: --- The information contained in this document and attachments is confidential and intended only for the person(s) named above. If you are not theintended recipient you are hereby notified that any disclosure, copying, distribution, or any other use of the information is strictly prohibited.If you have received this document by mistake, please notify the sender immediately and destroy this document and attachments without making any copy of any kind. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
