Re: SQL Help, please ...

2007-06-27 Thread Jochem van Dieten 
[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

RE: SQL Help, please ...

2007-06-27 Thread Brad Wood 
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

SQL Help, please ...

2007-06-26 Thread tjones 
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

2005-06-03 Thread Jeff Fongemie 
>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

RE: SQL Help Please

2005-06-03 Thread Dave Watts 
> 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

2005-06-03 Thread Matthew Small 
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

SQL Help Please

2005-06-03 Thread Jeff Fongemie 
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?

2004-07-30 Thread Dave Phillips 
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:
> 
> UserID   NumReferred
> 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?

2004-07-30 Thread Jeff Chastain 
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:
> 
> UserID   NumReferred
> 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?

2004-07-30 Thread Dave Phillips 
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:
> 
> UserID   NumReferred
> 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?

2004-07-30 Thread Cutter (CF-Talk) 
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:
> 
> UserID   NumReferred
> 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]

SQL Help Please?

2004-07-30 Thread Dave Phillips 
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:

UserID   NumReferred
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

2002-03-15 Thread Bill Grover 

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

2002-03-14 Thread ccook22 

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

RE: SQL help please

2002-03-14 Thread Kwang Suh 

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
> > > > --- --- --- -

RE: SQL help please

2002-03-14 Thread Bill Grover 

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

Re: SQL help please

2002-03-13 Thread Nathan Chen 

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

Re: SQL help please

2002-03-13 Thread ksuh 

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

2002-03-13 Thread laszlo 

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

2002-03-13 Thread ksuh 

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

SQL help please

2002-03-13 Thread ksuh 

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

sql help please

2001-09-18 Thread Won Lee 

QUERY
===

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)



===
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
===

subsurveyid: #getSurvey.surveySubID#
cat: #getSurvey.category#
catid: #getSurvey.categoryID#



Questions
Question: #getSurvey.question#
Add question to this category




PROBLEM


catid: #getSurvey.categoryID#

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

RE: SQL Help, Please

2001-03-07 Thread Scott Brader 

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

RE: SQL Help, Please

2001-03-07 Thread DeVoil, Nick 

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

2001-03-07 Thread Scott Brader 

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



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

2001-03-07 Thread Scott Brader 

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



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

2001-03-07 Thread Philip Arnold - ASP 

> 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

OT: SQL Help, Please

2001-03-07 Thread Scott Brader 

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



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: Single Quotes in SQL HELP PLEASE!

2001-01-22 Thread John McKown 

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

RE: Single Quotes in SQL HELP PLEASE!

2001-01-22 Thread Benjamin S. Rogers 

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!

2001-01-22 Thread Benjamin S. Rogers 

> 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!

2001-01-22 Thread Raymond B. 

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 .


-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!

2001-01-22 Thread Philip Arnold - ASP 

> 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!

2001-01-22 Thread Dylan Bromby 

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!

2001-01-22 Thread Clint Tredway 

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!

2001-01-22 Thread bflynn 

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

Single Quotes in SQL HELP PLEASE!

2001-01-22 Thread Phoeun Pha 

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: SQL Help Please (in plain text...sorry...)

2000-03-27 Thread Pete Freitag 

You can't use the IN operator they way you are using it. It works like
this...

SELECT * FROM tablename
WHERE category IN (1,2,3)

The above query selects any field with categories of either 1, 2 or 3. It's
equivalent to saying

SELECT * FROM tablename
WHERE
category = 1
OR category = 2
OR category = 3

What you had probably generated a syntax error.

The way you have things set up, it makes things tricky, you couldn't even
use the IN operator.  I would recommend using a relational model.  Make
another table called CatClient with the following prototype...

int CatClientID, int CategoryID, int ClientID

You would have an easier time solving your problems.  Your query might look
like this...

SELECT * FROM Clients, CatClient
WHERE CatClient.CategoryID = #url.category#
AND CatClient.ClientID = Clients.ClientID

You could also use a Join here.

Some more resources...
http://msdn.microsoft.com/sqlserver
http://technet.oracle.com/
http://www.cfdev.com/resources/discussion/messages.cfm?id=7
http://www.sqlmag.com

___
Pete Freitag
CFDEV.COM
Cold Fusion Developer Resources
http://www.cfdev.com/


-Original Message-
From: Chris Carpenter [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 28, 2000 2:15 AM
To: coldfusion
Subject: 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:

categor
y

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...


 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#'



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=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.

--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit 
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a 
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.

SQL Help Please (in plain text...sorry...)

2000-03-27 Thread Chris Carpenter 

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:

categor
y

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...


 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#'



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=lists&body=lists/cf_talk or send a 
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.

Re: Sql Help Please....

2000-03-27 Thread Jeff 

chris,

turn off the html mail, post in plain text and we'll try.

thanks,

.jeff

--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit 
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a 
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.

Sql Help Please....

2000-03-27 Thread Chris Carpenter 

This is a multi-part message in MIME format.

--=_NextPart_000_0033_01BF98D5.78A071A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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.=20
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:

#url.state#&Category=3D2=
">category

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...


 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 =3D '#Url.State#'


I hope someone can help..
thanks,
Chris Carpenter
WebMaster/IT Manager
ABC Interactive
http://www.abcinteractive.com.au

--=_NextPart_000_0033_01BF98D5.78A071A0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable








Hi,
 
I have a table with client details =
('clients') that=20
is related to another table with category info =
('category').
Now, each client could have more than 1 =
category in=20
there CategoryID Field. 
the user will be clicking on a link =
which will have=20
one CategoryID as a URL variable, and the state where they are searching =

eg:
 
#url.state#&=
amp;Category=3D2">category
 
Below is my attempt, but this doesn't =
seem to work.=20
(dont laugh, it is all i could think of)
 
so the problem is that CategoryID could =
resolve to,=20
for example: '1,5,7,8' and the url parameter will always be one number, =
how do i=20
write the SQL for this...
 
 SELECT=20
 ClientID,
   =20
           =20
BusinessName,
       =20
           =20
ContactName,
       =20
            =
Address,
       =20
            =
Suburb,
       =20
            =
State,
       =20
            =
PostCode,
       =20
           =20
PhoneNumber,
       =20
            =
PhoneExt,
       =20
            =
FaxNumber,
       =20
           =20
MobileNumber,
       =20
           =20
EmailAddress,
       =20
           =20
FunctionID,
       =20
           =20
CategoryID,
       =20
            =
LogoPath,
       =20
            =
IntroText FROM=20
Clients WHERE '#Url.category#' IN (CategoryID) AND State =3D=20
'#Url.State#'

 
I hope someone can =
help..
thanks,
Chris CarpenterWebMaster/IT =
ManagerABC=20
Interactivehttp://www.abcinteractive.com.au">http://www.abcinteractive.com.a=
u

--=_NextPart_000_0033_01BF98D5.78A071A0--

--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit 
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a 
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.