SQL Help, please ...
Hello, It's been a wile since I have had to do much in the way of SQL queries and I need some help. I have a table called installs that looks like this HostName, iDate, Package client1, 2007-06-01, Update1 client1, 2007-06-01, Update2 client1, 2007-06-01, Update3 client1, 2007-06-02, Update5 client1, 2007-06-02, NewApp client2, 2007-06-01, Update1 client2, 2007-06-01, Update2 client2, 2007-06-01, Update3 client2, 2007-06-02, Update5 client2, 2007-06-02, NewApp What I want is a report/summary like result that looks like this HostName, iDate, Installs client1, 2007-06-01, 3 client1, 2007-06-02, 2 client2, 2007-06-01, 3 client2, 2007-06-02, 2 I hope this makes sense and I know this is off topic but any hep would be great. Thanks, tom ~| CF 8 â Scorpio beta now available, easily build great internet experiences â Try it now on Labs http://www.adobe.com/cfusion/entitlement/index.cfm?e=labs_adobecf8_beta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282317 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Help, please ...
Let me get this straight, you want a report to summarize the number of installs by date and client. So client 1 ran 3 installs on 6/1 and 2 installs on 6/2. I believe you simply need to group by hostname, and then date and then use an aggregate function (count()) to add up the records in between like so: SELECT hostname, iDate, count(1) FROM installs GROUP BY hostname, iDate ORDER BY hostname, iDate The order by is optional, but I threw it in since your result set was ordered that way. Depending on whether or not your iDate column stores time or not, you may need to convert it to date only. (syntax depends on your DB). ~Brad -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 26, 2007 10:16 PM To: CF-Talk Subject: SQL Help, please ... Hello, It's been a wile since I have had to do much in the way of SQL queries and I need some help. I have a table called installs that looks like this HostName, iDate, Package client1, 2007-06-01, Update1 client1, 2007-06-01, Update2 client1, 2007-06-01, Update3 client1, 2007-06-02, Update5 client1, 2007-06-02, NewApp client2, 2007-06-01, Update1 client2, 2007-06-01, Update2 client2, 2007-06-01, Update3 client2, 2007-06-02, Update5 client2, 2007-06-02, NewApp What I want is a report/summary like result that looks like this HostName, iDate, Installs client1, 2007-06-01, 3 client1, 2007-06-02, 2 client2, 2007-06-01, 3 client2, 2007-06-02, 2 I hope this makes sense and I know this is off topic but any hep would be great. Thanks, tom ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282367 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help, please ...
[EMAIL PROTECTED] wrote: HostName, iDate, Package client1, 2007-06-01, Update1 client1, 2007-06-01, Update2 client1, 2007-06-01, Update3 client1, 2007-06-02, Update5 client1, 2007-06-02, NewApp client2, 2007-06-01, Update1 client2, 2007-06-01, Update2 client2, 2007-06-01, Update3 client2, 2007-06-02, Update5 client2, 2007-06-02, NewApp What I want is a report/summary like result that looks like this HostName, iDate, Installs client1, 2007-06-01, 3 client1, 2007-06-02, 2 client2, 2007-06-01, 3 client2, 2007-06-02, 2 SELECT HostName, iDate, COUNT(Package) FROM installs GROUP BY HostName, iDate ORDER BY HostName, iDate Jochem ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282372 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
SQL Help Please
I've got a query that I just can't get right. select * From companies where companyhide = 0 and companyid = (select distinct companyid From releases where date_entered = DATE_SUB(curdate (),INTERVAL 8 day) and date_entered = DATE_SUB (curdate(),INTERVAL 30 day) order by date_entered desc) order by rank, company Essentially, I've got a table of companies and anothr table with press releases. press releases are linked to companies by company id. What I'm trying to do is select only companies that have press releases within the last for weeks. What the above is giving me is a list of companuid's, whihc does not work. Any help or suggestion would be much appreciated. -jeff ~| 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:208501 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: SQL Help Please
and companyid in (select distinct companyid Matthew Small Web Developer American City Business Journals 704-973-1045 [EMAIL PROTECTED] -Original Message- From: Jeff Fongemie [mailto:[EMAIL PROTECTED] Sent: Friday, June 03, 2005 10:20 AM To: CF-Talk Subject: SQL Help Please I've got a query that I just can't get right. select * From companies where companyhide = 0 and companyid = (select distinct companyid From releases where date_entered = DATE_SUB(curdate (),INTERVAL 8 day) and date_entered = DATE_SUB (curdate(),INTERVAL 30 day) order by date_entered desc) order by rank, company Essentially, I've got a table of companies and anothr table with press releases. press releases are linked to companies by company id. What I'm trying to do is select only companies that have press releases within the last for weeks. What the above is giving me is a list of companuid's, whihc does not work. Any help or suggestion would be much appreciated. -jeff ~| 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:208505 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: SQL Help Please
I've got a query that I just can't get right. select * From companies where companyhide = 0 and companyid = (select distinct companyid From releases where date_entered = DATE_SUB(curdate (),INTERVAL 8 day) and date_entered = DATE_SUB (curdate(),INTERVAL 30 day) order by date_entered desc) order by rank, company Essentially, I've got a table of companies and anothr table with press releases. press releases are linked to companies by company id. What I'm trying to do is select only companies that have press releases within the last for weeks. What the above is giving me is a list of companuid's, whihc does not work. Any help or suggestion would be much appreciated. At first glance, my initial suggestion would be to use IN instead of = for your comparison: AND companyid IN (SELECT ... Also, there's no need to use ORDER BY in your subquery, and frankly I'd probably write this using a JOIN instead of a subquery anyway. SELECT c.* FROMcompanies c INNER JOIN releases r ON c.companyid = r.companyid WHERE c.companyhide = 0 AND r.date_entered BETWEEN DATE_SUB(curdate(), INTERVAL 8 day) AND DATE_SUB(curdate(), INTERVAL 30 day) ORDER BYc.rank, c.company Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| 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:208507 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 Help Please
and companyid in (select distinct companyid Matthew Small Web Developer American City Business Journals 704-973-1045 [EMAIL PROTECTED] That's it! I forget about in. I knew it was simple. Thanks! -jeff -Original Message- From: Jeff Fongemie [mailto:[EMAIL PROTECTED] Sent: Friday, June 03, 2005 10:20 AM To: CF-Talk Subject: SQL Help Please I've got a query that I just can't get right. select * From companies where companyhide = 0 and companyid = (select distinct companyid From releases where date_entered = DATE_SUB(curdate (),INTERVAL 8 day) and date_entered = DATE_SUB (curdate(),INTERVAL 30 day) order by date_entered desc) order by rank, company Essentially, I've got a table of companies and anothr table with press releases. press releases are linked to companies by company id. What I'm trying to do is select only companies that have press releases within the last for weeks. What the above is giving me is a list of companuid's, whihc does not work. Any help or suggestion would be much appreciated. -jeff ~| 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:208512 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
SQL Help Please?
Hi guys, I hope this isn't too far off topic, but I'm stuck on an SQL issue and really need some help. I have a table with these two fields: UserID(int) Referer (int) UserID is obviously my user's ID number.Referer refers to the UserID of the person who referred them. I need to run an SQL Query that returns to me the total number of records in which a UserID is in the Referer. For example, Ted is UserID 1 and John is UserID 2 Ted Refers, Jan, Jerry and Jack John Refers Mary and Bill I now have 7 records, all unique UserID's but the referer for Jan, Jerry and Jack is 1 and the referer for Mary and Bill is 2 I want to run an SQL Query that returns the following results: UserIDNumReferred 13 22 I know it's GOT to be possible to do it all in SQl.I know I can do it in CF with no problem by looping over my userid list, but that takes way too long. Any ideas? Thanks! Dave [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL Help Please?
Maybe I'm misunderstanding what you are trying to do, but try this Select count(Referer) as refcount From myTable Where Referer = #myUserID# Cutter Dave Phillips wrote: Hi guys, I hope this isn't too far off topic, but I'm stuck on an SQL issue and really need some help. I have a table with these two fields: UserID(int) Referer (int) UserID is obviously my user's ID number.Referer refers to the UserID of the person who referred them. I need to run an SQL Query that returns to me the total number of records in which a UserID is in the Referer. For example, Ted is UserID 1 and John is UserID 2 Ted Refers, Jan, Jerry and Jack John Refers Mary and Bill I now have 7 records, all unique UserID's but the referer for Jan, Jerry and Jack is 1 and the referer for Mary and Bill is 2 I want to run an SQL Query that returns the following results: UserIDNumReferred 13 22 I know it's GOT to be possible to do it all in SQl.I know I can do it in CF with no problem by looping over my userid list, but that takes way too long. Any ideas? Thanks! Dave [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Help Please?
Unfortunately, that will only give me the number of referer's for ONE UserID.I need the number of referer's for EACH of the User's returned in one SQL Statement. Thanks though. Sincerely, Dave Phillips 94percent.com [EMAIL PROTECTED] 615-746-3851 Why do 100% of the work when we'll do 94% of it for you? - http://honor.94percent.com _ From: Cutter (CF-Talk) [mailto:[EMAIL PROTECTED] Sent: Friday, July 30, 2004 8:31 PM To: CF-Talk Subject: Re: SQL Help Please? Maybe I'm misunderstanding what you are trying to do, but try this Select count(Referer) as refcount From myTable Where Referer = #myUserID# Cutter Dave Phillips wrote: Hi guys, I hope this isn't too far off topic, but I'm stuck on an SQL issue and really need some help. I have a table with these two fields: UserID(int) Referer (int) UserID is obviously my user's ID number.Referer refers to the UserID of the person who referred them. I need to run an SQL Query that returns to me the total number of records in which a UserID is in the Referer. For example, Ted is UserID 1 and John is UserID 2 Ted Refers, Jan, Jerry and Jack John Refers Mary and Bill I now have 7 records, all unique UserID's but the referer for Jan, Jerry and Jack is 1 and the referer for Mary and Bill is 2 I want to run an SQL Query that returns the following results: UserIDNumReferred 13 22 I know it's GOT to be possible to do it all in SQl.I know I can do it in CF with no problem by looping over my userid list, but that takes way too long. Any ideas? Thanks! Dave _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Help Please?
This is what you need ... SELECT DISTINCT(referer), COUNT(referer) FROM users GROUP BY referer -- Jeff _ From: Dave Phillips [mailto:[EMAIL PROTECTED] Sent: Friday, July 30, 2004 8:37 PM To: CF-Talk Subject: RE: SQL Help Please? Unfortunately, that will only give me the number of referer's for ONE UserID.I need the number of referer's for EACH of the User's returned in one SQL Statement. Thanks though. Sincerely, Dave Phillips 94percent.com [EMAIL PROTECTED] 615-746-3851 Why do 100% of the work when we'll do 94% of it for you? - http://honor.94percent.com _ From: Cutter (CF-Talk) [mailto:[EMAIL PROTECTED] Sent: Friday, July 30, 2004 8:31 PM To: CF-Talk Subject: Re: SQL Help Please? Maybe I'm misunderstanding what you are trying to do, but try this Select count(Referer) as refcount From myTable Where Referer = #myUserID# Cutter Dave Phillips wrote: Hi guys, I hope this isn't too far off topic, but I'm stuck on an SQL issue and really need some help. I have a table with these two fields: UserID(int) Referer (int) UserID is obviously my user's ID number.Referer refers to the UserID of the person who referred them. I need to run an SQL Query that returns to me the total number of records in which a UserID is in the Referer. For example, Ted is UserID 1 and John is UserID 2 Ted Refers, Jan, Jerry and Jack John Refers Mary and Bill I now have 7 records, all unique UserID's but the referer for Jan, Jerry and Jack is 1 and the referer for Mary and Bill is 2 I want to run an SQL Query that returns the following results: UserIDNumReferred 13 22 I know it's GOT to be possible to do it all in SQl.I know I can do it in CF with no problem by looping over my userid list, but that takes way too long. Any ideas? Thanks! Dave _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Help Please?
Jeff, THANKS!I knew it could be done!I experimented with DISTINCT and COUNT and GROUP BY over and over but couldn't find the right combination. Thanks so much! Sincerely, Dave Phillips 94percent.com [EMAIL PROTECTED] 615-746-3851 Why do 100% of the work when we'll do 94% of it for you? - http://honor.94percent.com _ From: Jeff Chastain [mailto:[EMAIL PROTECTED] Sent: Friday, July 30, 2004 8:39 PM To: CF-Talk Subject: RE: SQL Help Please? This is what you need ... SELECT DISTINCT(referer), COUNT(referer) FROM users GROUP BY referer -- Jeff _ From: Dave Phillips [mailto:[EMAIL PROTECTED] Sent: Friday, July 30, 2004 8:37 PM To: CF-Talk Subject: RE: SQL Help Please? Unfortunately, that will only give me the number of referer's for ONE UserID.I need the number of referer's for EACH of the User's returned in one SQL Statement. Thanks though. Sincerely, Dave Phillips 94percent.com [EMAIL PROTECTED] 615-746-3851 Why do 100% of the work when we'll do 94% of it for you? - http://honor.94percent.com _ From: Cutter (CF-Talk) [mailto:[EMAIL PROTECTED] Sent: Friday, July 30, 2004 8:31 PM To: CF-Talk Subject: Re: SQL Help Please? Maybe I'm misunderstanding what you are trying to do, but try this Select count(Referer) as refcount From myTable Where Referer = #myUserID# Cutter Dave Phillips wrote: Hi guys, I hope this isn't too far off topic, but I'm stuck on an SQL issue and really need some help. I have a table with these two fields: UserID(int) Referer (int) UserID is obviously my user's ID number.Referer refers to the UserID of the person who referred them. I need to run an SQL Query that returns to me the total number of records in which a UserID is in the Referer. For example, Ted is UserID 1 and John is UserID 2 Ted Refers, Jan, Jerry and Jack John Refers Mary and Bill I now have 7 records, all unique UserID's but the referer for Jan, Jerry and Jack is 1 and the referer for Mary and Bill is 2 I want to run an SQL Query that returns the following results: UserIDNumReferred 13 22 I know it's GOT to be possible to do it all in SQl.I know I can do it in CF with no problem by looping over my userid list, but that takes way too long. Any ideas? Thanks! Dave _ _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL help please
If you do that select you will end up with EXACTLY the same data as you started with + a count column that will always be 1. The issue is that you kind of want to group the paycatid field within changes of the paycatid field. Therefore you have to process each record one at a time looking for a change and then returning the proper start and end dates for that range. Therefore you have to use a cursor to process the records one by one. __ Bill Grover Supervisor MIS Phone: 301.424.3300 x3324 EU Services, Inc. FAX:301.424.3696 649 North Horners Lane E-Mail: [EMAIL PROTECTED] Rockville, MD 20850-1299WWW:http://www.euservices.com __ -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 14, 2002 11:06 AM To: CF-Talk Subject: Re: SQL help please try select employeeid, startdate, enddate, count(*), paycatid from X group by employeeid, startdate, enddate, paycatid ksuh @shaw.ca To: CF-Talk [EMAIL PROTECTED] cc: 03/13/02 Subject: Re: SQL help please 04:51 PM Please respond to cf-talk Anyone? Bueller? Bueller? - Original Message - From: [EMAIL PROTECTED] Date: Wednesday, March 13, 2002 2:51 pm Subject: SQL help please Brain not working Must help... Here's my data set: employeeid startdate enddate paycatid --- --- --- --- 936 2002-02-08 2002-02-08 1 936 2002-02-11 2002-02-11 1 936 2002-02-12 2002-02-12 1 936 2002-02-13 2002-02-13 11 936 2002-02-14 2002-02-14 1 936 2002-02-15 2002-02-15 1 936 2002-02-18 2002-02-18 11 936 2002-02-19 2002-02-19 11 936 2002-02-20 2002-02-20 11 936 2002-02-21 2002-02-21 11 936 2002-02-22 2002-02-22 11 936 2002-02-25 2002-02-25 7 936 2002-02-27 2002-02-27 7 What I'm wondering is how do I get something like: employeeid startdate enddate same paycatid in a row paycatid --- --- --- --- -- 936 2002-02-08 2002-02-12 3 1 936 2002-02-13 2002-02-13 1 11 936 2002-02-14 2002-02-15 2 1 936 2002-02-18 2002-02-22 5 11 936 2002-02-25 2002-02-27 2 7 I'm using SQL Server 7. __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL help please
I'm not sure you can do it with a simple select statement. If I understand what you want you want to look at each entry and whenever the paycatid field changes report the first start and last end dates for that id. You will probably need to create and run a stored procedure. Your SP will need to create a cursor to process the records 1 by 1 looking for the paycatid field to change and then saving the start and end dates. Something like this (data types may vary depending on your actual data types). This routine is for a single employee but can be modified to work for multiple employees. CREATE PROCEDURE GetCatInfoForEmp @tnEmpIDsmallint AS SET NOCOUNT ON DECLARE @lnEmpIDsmallint, @lcStartDate varchar(10), @lcEndDatevarchar(10), @lcCatID varchar(2), @lcLastID varchar(2), @lnNumRec smallint, @lcLowStart varchar(10), @lcHighEndvarchar(10 DECLARE DataSet CURSOR FOR SELECT employeeid startdate enddate paycatid FROM sometable WHERE employeeid = @tcEmpID ORDER BY startdate, paycatid CREATE TABLE #PayCatSum (employeeid smallint, startdate varchar(10), enddatevarchar(10), paycatid varchar(2), entrycount smallint) OPEN DataSet FETCH NEXT FROM DataSet INTO @lnEmpID, @lcStartDate, @lcEndDate, @lcCatID SET @lcLastID = @lcCatID SET @lnNumRec = 0 SET @lcLowStart = @lcStartDate SET @lcHighEnd = @lcEndDate WHILE @@FETCH_STATUS = 0 BEGIN IF @lcLastID @lcCatID BEGIN INSERT INTO #PayCatSum VALUES (@lnEmpID, @lcLowStart, @lcHighEnd, @lcLastID, @lnNumRec) SET @lnNumRec = 1 SET @lcLastID = @lcCatID SET @lcLowStart = @lcStartDate SET @lcHighEnd = @lcEndDate END ELSE BEGIN IF @lcStartDate @lcLowStart SET @lcLowStart = @lcStartDate IF @lcEndDate @lcHighEnd SET @lcHighEnd = @lcEndDate SET @lnNumRec = @lnNumRec + 1 END FETCH NEXT FROM DataSet INTO @lnEmpID, @lcStartDate, @lcEndDate, @lcCatID END INSERT INTO #PayCatSum VALUES (@lnEmpID, @lcLowStart, @lcHighEnd, @lcLastID, @lnNumRec) SELECT * FROM #PayCatSum __ Bill Grover Supervisor MIS Phone: 301.424.3300 x3324 EU Services, Inc. FAX:301.424.3696 649 North Horners Lane E-Mail: [EMAIL PROTECTED] Rockville, MD 20850-1299WWW:http://www.euservices.com __ -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 13, 2002 6:10 PM To: CF-Talk Subject: Re: SQL help please Nope, that doesn't work. - Original Message - From: laszlo [EMAIL PROTECTED] Date: Wednesday, March 13, 2002 4:06 pm Subject: Re: SQL help please select ... count(paycatid) as samepaycatid group by paycatid laszlo [EMAIL PROTECTED] wrote: Anyone? Bueller? Bueller? - Original Message - From: [EMAIL PROTECTED] Date: Wednesday, March 13, 2002 2:51 pm Subject: SQL help please Brain not working Must help... Here's my data set: employeeid startdate enddate paycatid --- --- --- --- 936 2002-02-08 2002-02-08 1 936 2002-02-11 2002-02-11 1 936 2002-02-12 2002-02-12 1 936 2002-02-13 2002-02-13 11 936 2002-02-14 2002-02-14 1 936 2002-02-15 2002-02-15 1 936 2002-02-18 2002-02-18 11 936 2002-02-19 2002-02-19 11 936 2002-02-20 2002-02-20 11 936 2002-02-21 2002-02-21 11 936 2002-02-22 2002-02-22 11 936 2002-02-25 2002-02-25 7 936 2002-02-27 2002-02-27 7 What I'm wondering is how do I get something like: employeeid startdate enddate same paycatid in a row paycatid --- --- --- --- -- 936 2002-02-08 2002-02-12 3 1 936 2002-02-13 2002-02-13 1 11 936 2002-02-14 2002-02-15 2 1 936 2002-02-18 2002-02-22 5 11 936 2002-02-25 2002-02-27 2 7 I'm using SQL Server 7. __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL help please
Yeah, I ended up using a cursor to do it. Thanks Bill. -Original Message- From: Bill Grover [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 14, 2002 6:01 AM To: CF-Talk Subject: RE: SQL help please I'm not sure you can do it with a simple select statement. If I understand what you want you want to look at each entry and whenever the paycatid field changes report the first start and last end dates for that id. You will probably need to create and run a stored procedure. Your SP will need to create a cursor to process the records 1 by 1 looking for the paycatid field to change and then saving the start and end dates. Something like this (data types may vary depending on your actual data types). This routine is for a single employee but can be modified to work for multiple employees. CREATE PROCEDURE GetCatInfoForEmp @tnEmpIDsmallint AS SET NOCOUNT ON DECLARE @lnEmpIDsmallint, @lcStartDate varchar(10), @lcEndDatevarchar(10), @lcCatID varchar(2), @lcLastID varchar(2), @lnNumRec smallint, @lcLowStart varchar(10), @lcHighEndvarchar(10 DECLARE DataSet CURSOR FOR SELECT employeeid startdate enddate paycatid FROM sometable WHERE employeeid = @tcEmpID ORDER BY startdate, paycatid CREATE TABLE #PayCatSum (employeeid smallint, startdate varchar(10), enddatevarchar(10), paycatid varchar(2), entrycount smallint) OPEN DataSet FETCH NEXT FROM DataSet INTO @lnEmpID, @lcStartDate, @lcEndDate, @lcCatID SET @lcLastID = @lcCatID SET @lnNumRec = 0 SET @lcLowStart = @lcStartDate SET @lcHighEnd = @lcEndDate WHILE @@FETCH_STATUS = 0 BEGIN IF @lcLastID @lcCatID BEGIN INSERT INTO #PayCatSum VALUES (@lnEmpID, @lcLowStart, @lcHighEnd, @lcLastID, @lnNumRec) SET @lnNumRec = 1 SET @lcLastID = @lcCatID SET @lcLowStart = @lcStartDate SET @lcHighEnd = @lcEndDate END ELSE BEGIN IF @lcStartDate @lcLowStart SET @lcLowStart = @lcStartDate IF @lcEndDate @lcHighEnd SET @lcHighEnd = @lcEndDate SET @lnNumRec = @lnNumRec + 1 END FETCH NEXT FROM DataSet INTO @lnEmpID, @lcStartDate, @lcEndDate, @lcCatID END INSERT INTO #PayCatSum VALUES (@lnEmpID, @lcLowStart, @lcHighEnd, @lcLastID, @lnNumRec) SELECT * FROM #PayCatSum __ Bill Grover Supervisor MIS Phone: 301.424.3300 x3324 EU Services, Inc. FAX:301.424.3696 649 North Horners Lane E-Mail: [EMAIL PROTECTED] Rockville, MD 20850-1299WWW:http://www.euservices.com __ -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 13, 2002 6:10 PM To: CF-Talk Subject: Re: SQL help please Nope, that doesn't work. - Original Message - From: laszlo [EMAIL PROTECTED] Date: Wednesday, March 13, 2002 4:06 pm Subject: Re: SQL help please select ... count(paycatid) as samepaycatid group by paycatid laszlo [EMAIL PROTECTED] wrote: Anyone? Bueller? Bueller? - Original Message - From: [EMAIL PROTECTED] Date: Wednesday, March 13, 2002 2:51 pm Subject: SQL help please Brain not working Must help... Here's my data set: employeeid startdate enddate paycatid --- --- --- --- 936 2002-02-08 2002-02-08 1 936 2002-02-11 2002-02-11 1 936 2002-02-12 2002-02-12 1 936 2002-02-13 2002-02-13 11 936 2002-02-14 2002-02-14 1 936 2002-02-15 2002-02-15 1 936 2002-02-18 2002-02-18 11 936 2002-02-19 2002-02-19 11 936 2002-02-20 2002-02-20 11 936 2002-02-21 2002-02-21 11 936 2002-02-22 2002-02-22 11 936 2002-02-25 2002-02-25 7 936 2002-02-27 2002-02-27 7 What I'm wondering is how do I get something like: employeeid startdate enddate same paycatid in a row paycatid --- --- --- --- -- 936 2002-02-08 2002-02-12 3 1 936 2002-02-13 2002-02-13 1 11 936 2002-02-14 2002-02-15 2 1 936 2002-02-18 2002-02-22 5 11 936 2002-02-25 2002-02-27 2 7 I'm using SQL Server 7. __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http
Re: SQL help please
try select employeeid, startdate, enddate, count(*), paycatid from X group by employeeid, startdate, enddate, paycatid ksuh @shaw.ca To: CF-Talk [EMAIL PROTECTED] cc: 03/13/02 Subject: Re: SQL help please 04:51 PM Please respond to cf-talk Anyone? Bueller? Bueller? - Original Message - From: [EMAIL PROTECTED] Date: Wednesday, March 13, 2002 2:51 pm Subject: SQL help please Brain not working Must help... Here's my data set: employeeid startdate enddate paycatid --- --- --- --- 936 2002-02-08 2002-02-08 1 936 2002-02-11 2002-02-11 1 936 2002-02-12 2002-02-12 1 936 2002-02-13 2002-02-13 11 936 2002-02-14 2002-02-14 1 936 2002-02-15 2002-02-15 1 936 2002-02-18 2002-02-18 11 936 2002-02-19 2002-02-19 11 936 2002-02-20 2002-02-20 11 936 2002-02-21 2002-02-21 11 936 2002-02-22 2002-02-22 11 936 2002-02-25 2002-02-25 7 936 2002-02-27 2002-02-27 7 What I'm wondering is how do I get something like: employeeid startdate enddate same paycatid in a row paycatid --- --- --- --- -- 936 2002-02-08 2002-02-12 3 1 936 2002-02-13 2002-02-13 1 11 936 2002-02-14 2002-02-15 2 1 936 2002-02-18 2002-02-22 5 11 936 2002-02-25 2002-02-27 2 7 I'm using SQL Server 7. __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
SQL help please
Brain not working Must help... Here's my data set: employeeid startdate enddate paycatid --- --- --- --- 936 2002-02-08 2002-02-08 1 936 2002-02-11 2002-02-11 1 936 2002-02-12 2002-02-12 1 936 2002-02-13 2002-02-13 11 936 2002-02-14 2002-02-14 1 936 2002-02-15 2002-02-15 1 936 2002-02-18 2002-02-18 11 936 2002-02-19 2002-02-19 11 936 2002-02-20 2002-02-20 11 936 2002-02-21 2002-02-21 11 936 2002-02-22 2002-02-22 11 936 2002-02-25 2002-02-25 7 936 2002-02-27 2002-02-27 7 What I'm wondering is how do I get something like: employeeid startdate enddate same paycatid in a row paycatid --- --- --- --- -- 936 2002-02-08 2002-02-12 3 1 936 2002-02-13 2002-02-13 1 11 936 2002-02-14 2002-02-15 2 1 936 2002-02-18 2002-02-22 5 11 936 2002-02-25 2002-02-27 2 7 I'm using SQL Server 7. __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL help please
Anyone? Bueller? Bueller? - Original Message - From: [EMAIL PROTECTED] Date: Wednesday, March 13, 2002 2:51 pm Subject: SQL help please Brain not working Must help... Here's my data set: employeeid startdate enddate paycatid --- --- --- --- 936 2002-02-08 2002-02-08 1 936 2002-02-11 2002-02-11 1 936 2002-02-12 2002-02-12 1 936 2002-02-13 2002-02-13 11 936 2002-02-14 2002-02-14 1 936 2002-02-15 2002-02-15 1 936 2002-02-18 2002-02-18 11 936 2002-02-19 2002-02-19 11 936 2002-02-20 2002-02-20 11 936 2002-02-21 2002-02-21 11 936 2002-02-22 2002-02-22 11 936 2002-02-25 2002-02-25 7 936 2002-02-27 2002-02-27 7 What I'm wondering is how do I get something like: employeeid startdate enddate same paycatid in a row paycatid --- --- --- --- -- 936 2002-02-08 2002-02-12 3 1 936 2002-02-13 2002-02-13 1 11 936 2002-02-14 2002-02-15 2 1 936 2002-02-18 2002-02-22 5 11 936 2002-02-25 2002-02-27 2 7 I'm using SQL Server 7. __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL help please
select ... count(paycatid) as samepaycatid group by paycatid laszlo [EMAIL PROTECTED] wrote: Anyone? Bueller? Bueller? - Original Message - From: [EMAIL PROTECTED] Date: Wednesday, March 13, 2002 2:51 pm Subject: SQL help please Brain not working Must help... Here's my data set: employeeid startdate enddate paycatid --- --- --- --- 936 2002-02-08 2002-02-08 1 936 2002-02-11 2002-02-11 1 936 2002-02-12 2002-02-12 1 936 2002-02-13 2002-02-13 11 936 2002-02-14 2002-02-14 1 936 2002-02-15 2002-02-15 1 936 2002-02-18 2002-02-18 11 936 2002-02-19 2002-02-19 11 936 2002-02-20 2002-02-20 11 936 2002-02-21 2002-02-21 11 936 2002-02-22 2002-02-22 11 936 2002-02-25 2002-02-25 7 936 2002-02-27 2002-02-27 7 What I'm wondering is how do I get something like: employeeid startdate enddate same paycatid in a row paycatid --- --- --- --- -- 936 2002-02-08 2002-02-12 3 1 936 2002-02-13 2002-02-13 1 11 936 2002-02-14 2002-02-15 2 1 936 2002-02-18 2002-02-22 5 11 936 2002-02-25 2002-02-27 2 7 I'm using SQL Server 7. __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL help please
Nope, that doesn't work. - Original Message - From: laszlo [EMAIL PROTECTED] Date: Wednesday, March 13, 2002 4:06 pm Subject: Re: SQL help please select ... count(paycatid) as samepaycatid group by paycatid laszlo [EMAIL PROTECTED] wrote: Anyone? Bueller? Bueller? - Original Message - From: [EMAIL PROTECTED] Date: Wednesday, March 13, 2002 2:51 pm Subject: SQL help please Brain not working Must help... Here's my data set: employeeid startdate enddate paycatid --- --- --- --- 936 2002-02-08 2002-02-08 1 936 2002-02-11 2002-02-11 1 936 2002-02-12 2002-02-12 1 936 2002-02-13 2002-02-13 11 936 2002-02-14 2002-02-14 1 936 2002-02-15 2002-02-15 1 936 2002-02-18 2002-02-18 11 936 2002-02-19 2002-02-19 11 936 2002-02-20 2002-02-20 11 936 2002-02-21 2002-02-21 11 936 2002-02-22 2002-02-22 11 936 2002-02-25 2002-02-25 7 936 2002-02-27 2002-02-27 7 What I'm wondering is how do I get something like: employeeid startdate enddate same paycatid in a row paycatid --- --- --- --- -- 936 2002-02-08 2002-02-12 3 1 936 2002-02-13 2002-02-13 1 11 936 2002-02-14 2002-02-15 2 1 936 2002-02-18 2002-02-22 5 11 936 2002-02-25 2002-02-27 2 7 I'm using SQL Server 7. __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL help please
I think if you have to group by all the fields you select. try select ... count(paycatid) as samepaycatid group by paycatid,employeeid, startdate, enddate - Original Message - From: [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, March 13, 2002 4:10 PM Subject: Re: SQL help please Nope, that doesn't work. - Original Message - From: laszlo [EMAIL PROTECTED] Date: Wednesday, March 13, 2002 4:06 pm Subject: Re: SQL help please select ... count(paycatid) as samepaycatid group by paycatid laszlo [EMAIL PROTECTED] wrote: Anyone? Bueller? Bueller? - Original Message - From: [EMAIL PROTECTED] Date: Wednesday, March 13, 2002 2:51 pm Subject: SQL help please Brain not working Must help... Here's my data set: employeeid startdate enddate paycatid --- --- --- --- 936 2002-02-08 2002-02-08 1 936 2002-02-11 2002-02-11 1 936 2002-02-12 2002-02-12 1 936 2002-02-13 2002-02-13 11 936 2002-02-14 2002-02-14 1 936 2002-02-15 2002-02-15 1 936 2002-02-18 2002-02-18 11 936 2002-02-19 2002-02-19 11 936 2002-02-20 2002-02-20 11 936 2002-02-21 2002-02-21 11 936 2002-02-22 2002-02-22 11 936 2002-02-25 2002-02-25 7 936 2002-02-27 2002-02-27 7 What I'm wondering is how do I get something like: employeeid startdate enddate same paycatid in a row paycatid --- --- --- --- -- 936 2002-02-08 2002-02-12 3 1 936 2002-02-13 2002-02-13 1 11 936 2002-02-14 2002-02-15 2 1 936 2002-02-18 2002-02-22 5 11 936 2002-02-25 2002-02-27 2 7 I'm using SQL Server 7. __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
sql help please
QUERY === cfquery name=getSurvey datasource=#application.dsn# SELECT * FROM surveys S, surveySub SB, categories C LEFT OUTER JOIN questions Q ON (C.categoryID = Q.categoryID) LEFT OUTER JOIN questionsN QN ON (Q.questionID = QN.questionID) LEFT OUTER JOIN questionsW QW ON (Q.questionID = QW.questionID) WHERE (S.surveyID = SB.surveyID AND SB.categoryID = C.categoryID) /cfquery === RECORD RETURNED === surveySubID categoryID category questionID question catOrder catweight 9 12 NOO NULL NULL 1 1 7 10 another one 1 building type 1 1 7 10 another one 2 building size 1 1 8 9 test 3 another question 1 1 === CODE === cfoutput query=getSurvey group=categoryID subsurveyid: #getSurvey.surveySubID#br cat: #getSurvey.category#br catid: #getSurvey.categoryID#br input type=text name=order_#getSurvey.categoryID# value=#getSurvey.catOrder# input type=text name=weight_#getSurvey.categoryID# value=#getSurvey.catOrder# input type=checkbox name=remove value=#getSurvey.surveySubID#br Questionsbr cfoutputQuestion: #getSurvey.question#br/cfoutput Add question to this category br hr /cfoutput PROBLEM catid: #getSurvey.categoryID#br This line of code should be pumping out the categoryID returned in the record set. It works fine as long as there is a question associated with the category. However, if the category does not have a question associated with it it returns a null. I thought I should at least get the categoryID back if I did a LEFT JOIN. Any ideas? Won ~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
OT: SQL Help, Please
Sorry for the OT but I'm at a loss on this one.
I've built an application for a Fire/Rescue service to log their calls,
meeting attendance, etc. (geared to small, volunteer agencies). One of the
things that's very important for them is a sum of the hours spent on calls.
Each call record has a uniquely generated call number that is the year
followed by a hyphen and a three digit incrementing number. Each record also
has times for various events from dispatch to return to quarters. The times
are in short time (17:34) format. It's an Access database, it's all they can
afford/support right now.
I need to get a sum of the difference between dispatch time and return to
quarters time for all calls in a given year.
I've tried what seems to me to be the obvious:
SELECT SUM(DateDiff("h",DISPATCH_TIME,RTN_QTRS)) AS SUMHRS
FROM t_Calls
WHERE CALL_NO LIKE '2001-*-';
But that hasn't worked. I've played around with a number of other options
but haven't gotten anything to work. I'd appreciate any help anyone here can
provide.
Thanks,
Scott
Scott Brader
Prairie Software Development LLC
101 East Sadd Street
PO Box 235
North Prairie, WI 53153-0235
Phone: 262.392.9173
Fax: 262.392.9174
Toll Free: 888.821.3427
Mobile: 262.490.1376
http://www.prairiesoftdev.com
Amateurs practice until they get it right,
Experts practice until they can't get it wrong.
~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Help, Please
I've built an application for a Fire/Rescue service to log their calls,
meeting attendance, etc. (geared to small, volunteer agencies). One of the
things that's very important for them is a sum of the hours spent
on calls.
Each call record has a uniquely generated call number that is the year
followed by a hyphen and a three digit incrementing number. Each
record also has times for various events from dispatch to return to
quarters. The times are in short time (17:34) format. It's an Access
database, it's all they can afford/support right now.
I need to get a sum of the difference between dispatch time and return to
quarters time for all calls in a given year.
I've tried what seems to me to be the obvious:
SELECT SUM(DateDiff("h",DISPATCH_TIME,RTN_QTRS)) AS SUMHRS
FROM t_Calls
WHERE CALL_NO LIKE '2001-*-';
But that hasn't worked. I've played around with a number of other options
but haven't gotten anything to work. I'd appreciate any help
anyone here can provide.
OK, since you're using an aggrigate function (SUM), don't use WHERE - use
HAVING
Give that a try - it should work...
Also, your LIKE assumes that CALL_NO ends with a "-", does it? If no, get
rid of the last one
Philip Arnold
Director
Certified ColdFusion Developer
ASP Multimedia Limited
T: +44 (0)20 8680 1133
"Websites for the real world"
**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**
~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Help, Please
Philip, Thanks for the help. Unfortunately, that didn't do the trick. The query returns a -2. I've put in test records to this point, so I know that the return should be 22. Any other ideas? Thanks, Scott Scott Brader Prairie Software Development LLC 101 East Sadd Street PO Box 235 North Prairie, WI 53153-0235 Phone: 262.392.9173 Fax: 262.392.9174 Toll Free: 888.821.3427 Mobile: 262.490.1376 http://www.prairiesoftdev.com Amateurs practice until they get it right, Experts practice until they can't get it wrong. - OK, since you're using an aggrigate function (SUM), don't use WHERE - use HAVING Give that a try - it should work... Also, your LIKE assumes that CALL_NO ends with a "-", does it? If no, get rid of the last one Philip Arnold Director Certified ColdFusion Developer ASP Multimedia Limited T: +44 (0)20 8680 1133 "Websites for the real world" ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. ** ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Help, Please
Philip, Upon further testing, your suggestion does work fine, however, I'm getting the error whenever the times are on two different days; dispatch time was 23:25 and return to quarters time was 02:15. Will I have to change it from short time format to a different date/time format? Thanks, Scott Scott Brader Prairie Software Development LLC 101 East Sadd Street PO Box 235 North Prairie, WI 53153-0235 Phone: 262.392.9173 Fax: 262.392.9174 Toll Free: 888.821.3427 Mobile: 262.490.1376 http://www.prairiesoftdev.com Amateurs practice until they get it right, Experts practice until they can't get it wrong. OK, since you're using an aggrigate function (SUM), don't use WHERE - use HAVING Give that a try - it should work... Also, your LIKE assumes that CALL_NO ends with a "-", does it? If no, get rid of the last one Philip Arnold Director Certified ColdFusion Developer ASP Multimedia Limited T: +44 (0)20 8680 1133 "Websites for the real world" ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. ** ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Help, Please
If they leave at 22:00 and get back at 20:00 the next day, and you are only storing the *time*, not the date and time, then they are indeed getting back 2 hours earlier than they left. Nick -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 07, 2001 1:42 PM To: CF-Talk Subject: RE: SQL Help, Please Philip, Thanks for the help. Unfortunately, that didn't do the trick. The query returns a -2. I've put in test records to this point, so I know that the return should be 22. Any other ideas? Thanks, Scott ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Help, Please
Changing the field type to General Date and storing the date and time solved the problem. Thanks for all the help! Scott Scott Brader Prairie Software Development LLC 101 East Sadd Street PO Box 235 North Prairie, WI 53153-0235 Phone: 262.392.9173 Fax: 262.392.9174 Toll Free: 888.821.3427 Mobile: 262.490.1376 http://www.prairiesoftdev.com Amateurs practice until they get it right, Experts practice until they can't get it wrong. -Original Message- From: DeVoil, Nick [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 07, 2001 7:51 AM To: CF-Talk Subject: RE: SQL Help, Please If they leave at 22:00 and get back at 20:00 the next day, and you are only storing the *time*, not the date and time, then they are indeed getting back 2 hours earlier than they left. Nick -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 07, 2001 1:42 PM To: CF-Talk Subject: RE: SQL Help, Please Philip, Thanks for the help. Unfortunately, that didn't do the trick. The query returns a -2. I've put in test records to this point, so I know that the return should be 22. Any other ideas? Thanks, Scott ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Single Quotes in SQL HELP PLEASE!
ok, I want to enter this character into a SQL table. ' but i get an error. how do I make it enter ' into the database? INSERT INTO MyTABLE(Stuff) VALUES = ''' ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Single Quotes in SQL HELP PLEASE!
One more ' INSERT INTO MyTABLE(Stuff) VALUES = -Original Message- From: Phoeun Pha [mailto:[EMAIL PROTECTED]] Sent: Monday, January 22, 2001 11:59 AM To: CF-Talk Subject: Single Quotes in SQL HELP PLEASE! ok, I want to enter this character into a SQL table. ' but i get an error. how do I make it enter ' into the database? INSERT INTO MyTABLE(Stuff) VALUES = ''' ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Single Quotes in SQL HELP PLEASE!
you can use PreserveSingleQuoutes(#var#) -Original Message- From: Phoeun Pha [mailto:[EMAIL PROTECTED]] Sent: Monday, January 22, 2001 10:59 AM To: CF-Talk Subject: Single Quotes in SQL HELP PLEASE! ok, I want to enter this character into a SQL table. ' but i get an error. how do I make it enter ' into the database? INSERT INTO MyTABLE(Stuff) VALUES = ''' ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Single Quotes in SQL HELP PLEASE!
don't use = in the SQL statement. you should be able to do
INSERT INTO
MyTABLE
(Stuff)
VALUES
(''')
if that doesn't work, use the CF PreserveSingleQuotes() function.
-Original Message-
From: Phoeun Pha [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 22, 2001 8:59 AM
To: CF-Talk
Subject: Single Quotes in SQL HELP PLEASE!
ok, I want to enter this character into a SQL table. '
but i get an error. how do I make it enter ' into the database?
INSERT INTO MyTABLE(Stuff)
VALUES = '''
~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Single Quotes in SQL HELP PLEASE!
If it's in a var CF will auto escape it, if not manually escape it. '' is an escaped single quote for SQL. INSERT INTO MyTABLE(Stuff) VALUES () Would insert a single quote into the db. Same idea as using ## to get CF to display a # inside a CFOUTPUT. -Original Message- From: Phoeun Pha [mailto:[EMAIL PROTECTED]] Sent: January 22, 2001 11:59 To: CF-Talk Subject: Single Quotes in SQL HELP PLEASE! ok, I want to enter this character into a SQL table. ' but i get an error. how do I make it enter ' into the database? INSERT INTO MyTABLE(Stuff) VALUES = ''' ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Single Quotes in SQL HELP PLEASE!
ok, I want to enter this character into a SQL table. '
but i get an error. how do I make it enter ' into the database?
INSERT INTO MyTABLE(Stuff)
VALUES = '''
You might want to look into PreserveSingleQuotes()
Also, your SQL probably won't work;
INSERT INTO myTable (stuff)
VALUES ('#PreserveSingleQuotes("'")#')
SQL naturally allows you to escape single quotes by doubling them, so if you
want to do it without CF
VALUES ()
Philip Arnold
Director
Certified ColdFusion Developer
ASP Multimedia Limited
T: +44 (0)20 8680 1133
"Websites for the real world"
**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**
~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Single Quotes in SQL HELP PLEASE!
you can use PreserveSingleQuoutes(#var#) Actually, the preserve single quotes function should only be used when you are writing whole the SQL expression (rather than just data values). If you do this, you must take special care to escape all single quotes in your single quoted value strings. ok, I want to enter this character into a SQL table. ' but i get an error. how do I make it enter ' into the database? Escaping characters in SQL is much like escaping characters in ColdFusion. You double up the character you need to escape. In this case, you would want to escape the quoted single quote. INSERT INTO MyTABLE(Stuff) VALUES = ''' The following should work: INSERT INTO MyTABLE(Stuff) VALUES = Benjamin S. Rogers Web Developer, c4.net Voice: (508) 240-0051 Fax: (508) 240-0057 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Single Quotes in SQL HELP PLEASE!
Ooops! I didn't read your query thoroughly enough. I just glanced at it and escaped the single quote. My apologies. This is what you'll want to do: INSERT INTO MyTABLE ( Stuff ) VALUES ( ) Sorry about that. I'll have to pay more attention next time. :) Benjamin S. Rogers Web Developer, c4.net Voice: (508) 240-0051 Fax: (508) 240-0057 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Single Quotes in SQL HELP PLEASE!
Or do it dynamically like so: #REReplace(Form.FieldName, "'", "''", "ALL")# John McKown, VP Business Services Delaware.Net, Inc. 30 Old Rudnick Lane, Suite 200 Dover, DE 19901 email: [EMAIL PROTECTED] phone: 302-736-5515 fax: 302-736-5945 icq: 1495432 -Original Message- From: Phoeun Pha [mailto:[EMAIL PROTECTED]] Sent: Monday, January 22, 2001 11:59 AM To: CF-Talk Subject: Single Quotes in SQL HELP PLEASE! ok, I want to enter this character into a SQL table. ' but i get an error. how do I make it enter ' into the database? INSERT INTO MyTABLE(Stuff) VALUES = ''' ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
SQL Help Please (in plain text...sorry...)
Hi,
I have a table with client details ('clients') that is related to another
table with category info ('category').
Now, each client could have more than 1 category in there CategoryID Field.
the user will be clicking on a link which will have one CategoryID as a URL
variable, and the state where they are searching eg:
A
HREF="Display.cfm?state=cfoutput#url.state#/cfoutputCategory=2"categor
y/a
Below is my attempt, but this doesn't seem to work. (dont laugh, it is all i
could think of)
so the problem is that CategoryID could resolve to, for example: '1,5,7,8'
and the url parameter will always be one number, how do i write the SQL for
this...
cfquery datasource="#dsn#" Name="Business"
cachedwithin="#CreateTimeSpan(0,0,45,0)#"
SELECT ClientID,
BusinessName,
ContactName,
Address,
Suburb,
State,
PostCode,
PhoneNumber,
PhoneExt,
FaxNumber,
MobileNumber,
EmailAddress,
FunctionID,
CategoryID,
LogoPath,
IntroText
FROM Clients
WHERE '#Url.category#' IN (CategoryID) AND State = '#Url.State#'
/cfquery
I hope someone can help..
thanks,
Chris Carpenter
WebMaster/IT Manager
ABC Interactive
http://www.abcinteractive.com.au
--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
