RE: Adding Small sections of a query
Select Check_Num, Issued_To, sum(Amount) as Total, max(purDate_Purchased) as PurchaseDate From TableName Group by Check_Num, Issued_To Order by Check_Num, Issued_To Easy! Regards Dale Fraser http://learncf.com -Original Message- From: Travis Haley [mailto:[EMAIL PROTECTED] Sent: Tuesday, 2 October 2007 3:37 PM To: CF-Talk Subject: RE: Adding Small sections of a query The date part of the datetime is the same the time part differs but the time part is irrelevant to this report. I was able to get this to work by dropping the date out of the first query then running a second query in the cfoutput of the first query: cfquery name=rsGetPurchaseDate datasource=#application.dsn# username=#application.dsnUsername# password=#application.dsnPassword# maxrows=1 SELECT pur_Date_Purchased FROM tbl_HCFA_Purchase_Transactions WHERE Check_Num = #Check_Num# ORDER BY pur_Date_Purchased DESC /cfquery This just seems like a hack and it takes a lot of time for the page to run now from 130ms up to 1380ms now on 200 records. I am just looking for a way to cut the timestamp off of the date in the first query. I didn't realize in my first question that the GROUP BY needed exact matching entries and has to group everything in the original SELECT I am using an ACCESS database and am curious how I would use min( or max( to get the oldest date for the current group. I tried putting max(pur_Date_Purchased) in my GROUP BY clause and am told no aggregate functions are allowed in GROUP BY. Travis Haley -Original Message- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: Monday, October 01, 2007 7:38 PM To: CF-Talk Subject: Re: Adding Small sections of a query Which date do you want? You have multiple entries - which one should the query return? On 10/2/07, Travis Haley [EMAIL PROTECTED] wrote: But I need the date, should I run this in a second query? Travis Haley -Original Message- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: Monday, October 01, 2007 6:45 PM To: CF-Talk Subject: Re: Adding Small sections of a query Remove the date from the query. ~| Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289890 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Adding Small sections of a query
Works Perfect I was trying to put max(pur_Date_Purchased) in the GROUP BY also took it out and works exactly right. 123ms too :) Thanks for all the help everybody!! Travis -Original Message- From: Dale Fraser [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 02, 2007 12:26 AM To: CF-Talk Subject: RE: Adding Small sections of a query Select Check_Num, Issued_To, sum(Amount) as Total, max(purDate_Purchased) as PurchaseDate From TableName Group by Check_Num, Issued_To Order by Check_Num, Issued_To Easy! Regards Dale Fraser http://learncf.com -Original Message- From: Travis Haley [mailto:[EMAIL PROTECTED] Sent: Tuesday, 2 October 2007 3:37 PM To: CF-Talk Subject: RE: Adding Small sections of a query The date part of the datetime is the same the time part differs but the time part is irrelevant to this report. I was able to get this to work by dropping the date out of the first query then running a second query in the cfoutput of the first query: cfquery name=rsGetPurchaseDate datasource=#application.dsn# username=#application.dsnUsername# password=#application.dsnPassword# maxrows=1 SELECT pur_Date_Purchased FROM tbl_HCFA_Purchase_Transactions WHERE Check_Num = #Check_Num# ORDER BY pur_Date_Purchased DESC /cfquery This just seems like a hack and it takes a lot of time for the page to run now from 130ms up to 1380ms now on 200 records. I am just looking for a way to cut the timestamp off of the date in the first query. I didn't realize in my first question that the GROUP BY needed exact matching entries and has to group everything in the original SELECT I am using an ACCESS database and am curious how I would use min( or max( to get the oldest date for the current group. I tried putting max(pur_Date_Purchased) in my GROUP BY clause and am told no aggregate functions are allowed in GROUP BY. Travis Haley -Original Message- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: Monday, October 01, 2007 7:38 PM To: CF-Talk Subject: Re: Adding Small sections of a query Which date do you want? You have multiple entries - which one should the query return? On 10/2/07, Travis Haley [EMAIL PROTECTED] wrote: But I need the date, should I run this in a second query? Travis Haley -Original Message- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: Monday, October 01, 2007 6:45 PM To: CF-Talk Subject: Re: Adding Small sections of a query Remove the date from the query. ~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finderproductID=1522loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289891 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Adding Small sections of a query
Select Check_Num, Issued_To, sum(Amount) as Total From TableName Group by Check_Num, Issued_To Order by Check_Num, Issued_To Regards Dale Fraser http://learncf.com -Original Message- From: Travis Haley [mailto:[EMAIL PROTECTED] Sent: Monday, 1 October 2007 4:43 PM To: CF-Talk Subject: Adding Small sections of a query I am not sure the best way to phrase this question but I'll try here. I have a table that records checks I am breaking the check down in the database to show where each dollar goes so I have 3 lines with the same check number Example: Check_Num, Issued_To, Amount, Expense_Type 1234, Travis Haley, 100.00, 203 1234, Travis Haley, 6.00, 207 1234, Travis Haley, 21.00, 213 This is done to allow me to run a report on expense type and get what I need to pay in taxes. (I hate taxes lol) Anyway now that I'm trying to make a General Ledger I am trying to combine these back together using SQL I was wondering if there is a way to do this? I want the query to have one line with 1234, Travis Haley, 127.00 Expense_Type is not needed for this report Thanks in advance, Travis Haley ~| Check out the new features and enhancements in the latest product release - download the What's New PDF now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289842 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Adding Small sections of a query
Yes that works if there is only these three entries in the database however there are more than three here is a full sample of data Check_Num, Issued_To, Amount 1234, Travis, 100.00 1234, Travis, 1.00 1234, Travis, 6.00 1235, Kim, 300.00 1235, Kim, 6.00 1236, Adam, 100.00 1237, Travis, 200.00 How can i get the values returned to be Check_Num, Issued_To, Amount 1234, Travis, 107.00 1235, Kim, 306.00 1236, Adam, 100.00 1237, Travis, 200.00 Sorry for the lack of information earlier Travis From: Dale Fraser [EMAIL PROTECTED] Sent: Monday, October 01, 2007 12:56 AM To: CF-Talk cf-talk@houseoffusion.com Subject: RE: Adding Small sections of a query Select Check_Num, Issued_To, sum(Amount) as Total From TableName Group by Check_Num, Issued_To Order by Check_Num, Issued_To Regards Dale Fraser http://learncf.com -Original Message- From: Travis Haley [mailto:[EMAIL PROTECTED] Sent: Monday, 1 October 2007 4:43 PM To: CF-Talk Subject: Adding Small sections of a query I am not sure the best way to phrase this question but I'll try here. I have a table that records checks I am breaking the check down in the database to show where each dollar goes so I have 3 lines with the same check number Example: Check_Num, Issued_To, Amount, Expense_Type 1234, Travis Haley, 100.00, 203 1234, Travis Haley, 6.00, 207 1234, Travis Haley, 21.00, 213 This is done to allow me to run a report on expense type and get what I need to pay in taxes. (I hate taxes lol) Anyway now that I'm trying to make a General Ledger I am trying to combine these back together using SQL I was wondering if there is a way to do this? I want the query to have one line with 1234, Travis Haley, 127.00 Expense_Type is not needed for this report Thanks in advance, Travis Haley ~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289843 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Adding Small sections of a query
it should make no difference how many records you have in your table. Dale's code will return the results you want. Azadi Travis Haley wrote: Yes that works if there is only these three entries in the database however there are more than three here is a full sample of data Check_Num, Issued_To, Amount 1234, Travis, 100.00 1234, Travis, 1.00 1234, Travis, 6.00 1235, Kim, 300.00 1235, Kim, 6.00 1236, Adam, 100.00 1237, Travis, 200.00 How can i get the values returned to be Check_Num, Issued_To, Amount 1234, Travis, 107.00 1235, Kim, 306.00 1236, Adam, 100.00 1237, Travis, 200.00 Sorry for the lack of information earlier Travis From: Dale Fraser [EMAIL PROTECTED] Sent: Monday, October 01, 2007 12:56 AM To: CF-Talk cf-talk@houseoffusion.com Subject: RE: Adding Small sections of a query Select Check_Num, Issued_To, sum(Amount) as Total From TableName Group by Check_Num, Issued_To Order by Check_Num, Issued_To Regards Dale Fraser http://learncf.com -Original Message- From: Travis Haley [mailto:[EMAIL PROTECTED] Sent: Monday, 1 October 2007 4:43 PM To: CF-Talk Subject: Adding Small sections of a query I am not sure the best way to phrase this question but I'll try here. I have a table that records checks I am breaking the check down in the database to show where each dollar goes so I have 3 lines with the same check number Example: Check_Num, Issued_To, Amount, Expense_Type 1234, Travis Haley, 100.00, 203 1234, Travis Haley, 6.00, 207 1234, Travis Haley, 21.00, 213 This is done to allow me to run a report on expense type and get what I need to pay in taxes. (I hate taxes lol) Anyway now that I'm trying to make a General Ledger I am trying to combine these back together using SQL I was wondering if there is a way to do this? I want the query to have one line with 1234, Travis Haley, 127.00 Expense_Type is not needed for this report Thanks in advance, Travis Haley ~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289845 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Adding Small sections of a query
Thanks for all the help so far I know I'm almost there I figured out that all the items in the select need to be in the group by clause, however what is causing the problem is the date. When I run the query without the date it runs fine (actually I had to remove the FullName and break it into pat_First, Pat_Last) but I assume the GROUP BY is wanting everything the same before it adds them together. Well the date is the same but the time is different causing the entries to not match exactly and therefore will not add them together New Query is: cfquery name=rsGetPayments datasource=#application.dsn# username=#application.dsnUsername# password=#application.dsnPassword# SELECTsum(tbl_HCFA_Purchase_Transactions.pur_Amount) AS Total, tbl_HCFA_Purchase_Transactions.pur_Date_Purchased, tbl_HCFA_Purchase_Transactions.Check_Num, tbl_Patients.Pat_First, tbl_Patients.Pat_Last FROM tbl_HCFA_Purchase_Transactions, tbl_Patients WHEREtbl_HCFA_Purchase_Transactions.PatientID = tbl_Patients.PatientID AND pur_Date_Purchased BETWEEN #CreateODBCDateTime(cFullStartDate)# AND #CreateODBCDateTime(cFullEndDate)# GROUP BY Check_Num, pur_Date_Purchased, Pat_Last, Pat_First ORDER BY Check_Num /cfquery Output is: ID, Check_Num, pat_First, pat_Last, pur_Date_Purchased, Total 4, 8356, Dolores, Cannon, 2007-09-04 09:20:38.0, 100.000 5, 8357, Joseph, Manzanares, 2007-09-04 09:19:18.0, 204. 6, 8357, Joseph, Manzanares, 2007-09-04 09:19:55.0, 204. 7, 8358, Caesar, Marquez, 2007-09-04 09:31:02.0, 950. Records 5 and 6 should be added together but since the time stamp is 9:19:18 and 9:19:55 they are not equal and therefore the sum wont add them I tried taking the date out of the group by statement and get: You tried to execute a query that does not include the specified expression 'pur_Date_Purchased' as part of an aggregate function. Travis Haley -Original Message- From: Azadi Saryev [mailto:[EMAIL PROTECTED] Sent: Monday, October 01, 2007 2:56 AM To: CF-Talk Subject: Re: Adding Small sections of a query it should make no difference how many records you have in your table. Dale's code will return the results you want. Azadi ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289869 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Adding Small sections of a query
Remove the date from the query. On 10/2/07, Travis Haley [EMAIL PROTECTED] wrote: Thanks for all the help so far I know I'm almost there I figured out that all the items in the select need to be in the group by clause, however what is causing the problem is the date. When I run the query without the date it runs fine (actually I had to remove the FullName and break it into pat_First, Pat_Last) but I assume the GROUP BY is wanting everything the same before it adds them together. Well the date is the same but the time is different causing the entries to not match exactly and therefore will not add them together -- mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ ~| Check out the new features and enhancements in the latest product release - download the What's New PDF now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289877 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Adding Small sections of a query
But I need the date, should I run this in a second query? Travis Haley -Original Message- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: Monday, October 01, 2007 6:45 PM To: CF-Talk Subject: Re: Adding Small sections of a query Remove the date from the query. ~| Enterprise web applications, build robust, secure scalable apps today - Try it now ColdFusion Today ColdFusion 8 beta - Build next generation apps Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289883 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Adding Small sections of a query
Which date do you want? You have multiple entries - which one should the query return? On 10/2/07, Travis Haley [EMAIL PROTECTED] wrote: But I need the date, should I run this in a second query? Travis Haley -Original Message- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: Monday, October 01, 2007 6:45 PM To: CF-Talk Subject: Re: Adding Small sections of a query Remove the date from the query. ~| Check out the new features and enhancements in the latest product release - download the What's New PDF now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289884 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Adding Small sections of a query
depending on your database, you should have various functions available to you to extract just the date part from your datetime field. like DATE(yourdatefield) in mysql, for example... Travis Haley wrote: But I need the date, should I run this in a second query? Travis Haley -Original Message- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: Monday, October 01, 2007 6:45 PM To: CF-Talk Subject: Re: Adding Small sections of a query Remove the date from the query. ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289885 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Adding Small sections of a query
If you want the date, And the date is the same per record, then just add it to the group by. If the date is different per record then you need to either 1. Return multiple records 2. Use Max( or Min( on the date to get the oldest or newest Since your original sample had no dates, not sure what you are referring to. Regards Dale Fraser http://learncf.com -Original Message- From: Travis Haley [mailto:[EMAIL PROTECTED] Sent: Tuesday, 2 October 2007 11:28 AM To: CF-Talk Subject: RE: Adding Small sections of a query But I need the date, should I run this in a second query? Travis Haley -Original Message- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: Monday, October 01, 2007 6:45 PM To: CF-Talk Subject: Re: Adding Small sections of a query Remove the date from the query. ~| Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289888 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Adding Small sections of a query
The date part of the datetime is the same the time part differs but the time part is irrelevant to this report. I was able to get this to work by dropping the date out of the first query then running a second query in the cfoutput of the first query: cfquery name=rsGetPurchaseDate datasource=#application.dsn# username=#application.dsnUsername# password=#application.dsnPassword# maxrows=1 SELECT pur_Date_Purchased FROM tbl_HCFA_Purchase_Transactions WHERE Check_Num = #Check_Num# ORDER BY pur_Date_Purchased DESC /cfquery This just seems like a hack and it takes a lot of time for the page to run now from 130ms up to 1380ms now on 200 records. I am just looking for a way to cut the timestamp off of the date in the first query. I didn't realize in my first question that the GROUP BY needed exact matching entries and has to group everything in the original SELECT I am using an ACCESS database and am curious how I would use min( or max( to get the oldest date for the current group. I tried putting max(pur_Date_Purchased) in my GROUP BY clause and am told no aggregate functions are allowed in GROUP BY. Travis Haley -Original Message- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: Monday, October 01, 2007 7:38 PM To: CF-Talk Subject: Re: Adding Small sections of a query Which date do you want? You have multiple entries - which one should the query return? On 10/2/07, Travis Haley [EMAIL PROTECTED] wrote: But I need the date, should I run this in a second query? Travis Haley -Original Message- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: Monday, October 01, 2007 6:45 PM To: CF-Talk Subject: Re: Adding Small sections of a query Remove the date from the query. ~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289889 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
