expert sql challenge
Hi we have a relational database and one task is taking far too long but we cannot see any way to improve it, although i do feel there is a way to massively improve it... so would like some expert help on this we have a normal table which has a One-to-Many table coming off of it... table 1 is client details (one to one table), and table 2 is client telephone numbers (one to many table and has the clientid as a foreign key) we need to process a query that contains all the client details that have the telephone numbers put into one cell and separated by commas for example, this is the output query that we need client details | telephone numbers mr client 1| 123456789,234567891,21342 mr client 2| 583736245,828262u82 we have no idea if there is a way to ask SQL to combine the one to many telephone numbers into one cell and seperate them by commas for now we are getting all clients. then in a seperate query we are getting all telephone numbers. we then add a column to the clients query. then we run an outer loop to loop through all clients, and an inner loop that runs through all the telephone numbers, and appending the telephone number to the client if the client ids in both queries match. this seems a very long way around it but are not sure if there is a better way we would appreciate any suggestions to improve this thanks ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321581 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: expert sql challenge
What DB are you using? If it's MySQL, you can use this: select client.name, group_concat(clientphone.number) as phone numbers from client left outer join clientphone on client.id = clientphone.clientId group by client.name If it's MS SQL Server you can use a subquery with FOR XML PATH and STUFF to approximate the same functionality. Don't know about other platforms. At the very least, pull a single recordset with the same JOIN as above, but no GROUP BY, and then you can do the rollup in a single CFOUTPUT loop. That'll save you a lot of trips to the DB, and therefor a lot of wasted time. cheers, barneyb On Tue, Apr 14, 2009 at 12:38 PM, Richard White rich...@j7is.co.uk wrote: Hi we have a relational database and one task is taking far too long but we cannot see any way to improve it, although i do feel there is a way to massively improve it... so would like some expert help on this we have a normal table which has a One-to-Many table coming off of it... table 1 is client details (one to one table), and table 2 is client telephone numbers (one to many table and has the clientid as a foreign key) we need to process a query that contains all the client details that have the telephone numbers put into one cell and separated by commas for example, this is the output query that we need client details | telephone numbers mr client 1 | 123456789,234567891,21342 mr client 2 | 583736245,828262u82 we have no idea if there is a way to ask SQL to combine the one to many telephone numbers into one cell and seperate them by commas for now we are getting all clients. then in a seperate query we are getting all telephone numbers. we then add a column to the clients query. then we run an outer loop to loop through all clients, and an inner loop that runs through all the telephone numbers, and appending the telephone number to the client if the client ids in both queries match. this seems a very long way around it but are not sure if there is a better way we would appreciate any suggestions to improve this thanks ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321582 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: expert sql challenge
The easiest way is use a JOIN to get a query that will have a phone number on each row, then use the 'group' attribute of cfoutput to display them correctly. On Tue, Apr 14, 2009 at 3:38 PM, Richard White rich...@j7is.co.uk wrote: Hi we have a relational database and one task is taking far too long but we cannot see any way to improve it, although i do feel there is a way to massively improve it... so would like some expert help on this we have a normal table which has a One-to-Many table coming off of it... table 1 is client details (one to one table), and table 2 is client telephone numbers (one to many table and has the clientid as a foreign key) we need to process a query that contains all the client details that have the telephone numbers put into one cell and separated by commas for example, this is the output query that we need client details | telephone numbers mr client 1| 123456789,234567891,21342 mr client 2| 583736245,828262u82 we have no idea if there is a way to ask SQL to combine the one to many telephone numbers into one cell and seperate them by commas for now we are getting all clients. then in a seperate query we are getting all telephone numbers. we then add a column to the clients query. then we run an outer loop to loop through all clients, and an inner loop that runs through all the telephone numbers, and appending the telephone number to the client if the client ids in both queries match. this seems a very long way around it but are not sure if there is a better way we would appreciate any suggestions to improve this thanks ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321583 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: expert sql challenge
Have you looked into StoredProcs? Push the load off the web server onto the SQL Server (or Oracle whichever) On Tue, Apr 14, 2009 at 3:38 PM, Richard White rich...@j7is.co.uk wrote: Hi we have a relational database and one task is taking far too long but we cannot see any way to improve it, although i do feel there is a way to massively improve it... so would like some expert help on this we have a normal table which has a One-to-Many table coming off of it... table 1 is client details (one to one table), and table 2 is client telephone numbers (one to many table and has the clientid as a foreign key) we need to process a query that contains all the client details that have the telephone numbers put into one cell and separated by commas for example, this is the output query that we need client details | telephone numbers mr client 1| 123456789,234567891,21342 mr client 2| 583736245,828262u82 we have no idea if there is a way to ask SQL to combine the one to many telephone numbers into one cell and seperate them by commas for now we are getting all clients. then in a seperate query we are getting all telephone numbers. we then add a column to the clients query. then we run an outer loop to loop through all clients, and an inner loop that runs through all the telephone numbers, and appending the telephone number to the client if the client ids in both queries match. this seems a very long way around it but are not sure if there is a better way we would appreciate any suggestions to improve this thanks ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321584 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: expert sql challenge
There isn't really an efficient way to get a comma separated list in one column with your DB query. However, there is a far more efficient method that uses a single query and groups the output: cfquery name=qryClients datasource=myDsn SELECT c.clientId, c.firstname, c.lastname, t.number FROM client c LEFT JOIN clientPhoneNumber t ON t.clientId = c.clientId /cfquery ... cfoutput query=qryClients group=clientId #firstname# #lastname# numbers: cfoutput#number#/cfoutput /cfoutput ... Its a shame you can't do group on the cfloop tag but its a wonderful thing that saves you in these kinds of cases. Dominic 2009/4/14 Richard White rich...@j7is.co.uk: Hi we have a relational database and one task is taking far too long but we cannot see any way to improve it, although i do feel there is a way to massively improve it... so would like some expert help on this we have a normal table which has a One-to-Many table coming off of it... table 1 is client details (one to one table), and table 2 is client telephone numbers (one to many table and has the clientid as a foreign key) we need to process a query that contains all the client details that have the telephone numbers put into one cell and separated by commas for example, this is the output query that we need client details | telephone numbers mr client 1 | 123456789,234567891,21342 mr client 2 | 583736245,828262u82 we have no idea if there is a way to ask SQL to combine the one to many telephone numbers into one cell and seperate them by commas for now we are getting all clients. then in a seperate query we are getting all telephone numbers. we then add a column to the clients query. then we run an outer loop to loop through all clients, and an inner loop that runs through all the telephone numbers, and appending the telephone number to the client if the client ids in both queries match. this seems a very long way around it but are not sure if there is a better way we would appreciate any suggestions to improve this thanks ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321585 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: expert sql challenge
thanks for all your replies. barney, yes we are using mysql and didnt even know about the qroup_concat. it works like a dream and where this task was taking 23 seconds to complete it is now taking a matter of milliseconds :) fantastic and thanks once again for all your replies :) What DB are you using? If it's MySQL, you can use this: select client.name, group_concat(clientphone.number) as phone numbers from client left outer join clientphone on client.id = clientphone.clientId group by client.name If it's MS SQL Server you can use a subquery with FOR XML PATH and STUFF to approximate the same functionality. Don't know about other platforms. At the very least, pull a single recordset with the same JOIN as above, but no GROUP BY, and then you can do the rollup in a single CFOUTPUT loop. That'll save you a lot of trips to the DB, and therefor a lot of wasted time. cheers, barneyb ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321586 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: expert sql challenge
Nice. So group_concat works sort of like ColdFusion's valuelist method. Very nice indeed! -Original Message- From: Richard White [mailto:rich...@j7is.co.uk] Sent: Tuesday, April 14, 2009 2:59 PM To: cf-talk Subject: Re: expert sql challenge thanks for all your replies. barney, yes we are using mysql and didnt even know about the qroup_concat. it works like a dream and where this task was taking 23 seconds to complete it is now taking a matter of milliseconds :) fantastic and thanks once again for all your replies :) What DB are you using? If it's MySQL, you can use this: select client.name, group_concat(clientphone.number) as phone numbers from client left outer join clientphone on client.id = clientphone.clientId group by client.name If it's MS SQL Server you can use a subquery with FOR XML PATH and STUFF to approximate the same functionality. Don't know about other platforms. At the very least, pull a single recordset with the same JOIN as above, but no GROUP BY, and then you can do the rollup in a single CFOUTPUT loop. That'll save you a lot of trips to the DB, and therefor a lot of wasted time. cheers, barneyb ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321587 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: expert sql challenge
Is there a MSSQL version of that puppy? On Tue, Apr 14, 2009 at 3:19 PM, Andy Matthews li...@commadelimited.com wrote: Nice. So group_concat works sort of like ColdFusion's valuelist method. Very nice indeed! -Original Message- From: Richard White [mailto:rich...@j7is.co.uk] Sent: Tuesday, April 14, 2009 2:59 PM To: cf-talk Subject: Re: expert sql challenge thanks for all your replies. barney, yes we are using mysql and didnt even know about the qroup_concat. it works like a dream and where this task was taking 23 seconds to complete it is now taking a matter of milliseconds :) fantastic and thanks once again for all your replies :) What DB are you using? If it's MySQL, you can use this: select client.name, group_concat(clientphone.number) as phone numbers from client left outer join clientphone on client.id = clientphone.clientId group by client.name If it's MS SQL Server you can use a subquery with FOR XML PATH and STUFF to approximate the same functionality. Don't know about other platforms. At the very least, pull a single recordset with the same JOIN as above, but no GROUP BY, and then you can do the rollup in a single CFOUTPUT loop. That'll save you a lot of trips to the DB, and therefor a lot of wasted time. cheers, barneyb ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321589 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: expert sql challenge
Here is an Oracle UDF (for lack of a better description) that I just found. create or replace function join ( p_cursor sys_refcursor, p_del varchar2 := ',' ) return varchar2 is l_value varchar2(32767); l_result varchar2(32767); begin loop fetch p_cursor into l_value; exit when p_cursor%notfound; if l_result is not null then l_result := l_result || p_del; end if; l_result := l_result || l_value; end loop; return l_result; end join; Syntax: join(cursor(select name form users)). If you eant to change the default delim (which is a comma) you would do this: join(cursor(select name form users), '|') On Tue, Apr 14, 2009 at 3:31 PM, C. Hatton Humphrey chumph...@gmail.comwrote: Is there a MSSQL version of that puppy? On Tue, Apr 14, 2009 at 3:19 PM, Andy Matthews li...@commadelimited.com wrote: Nice. So group_concat works sort of like ColdFusion's valuelist method. Very nice indeed! -Original Message- From: Richard White [mailto:rich...@j7is.co.uk] Sent: Tuesday, April 14, 2009 2:59 PM To: cf-talk Subject: Re: expert sql challenge thanks for all your replies. barney, yes we are using mysql and didnt even know about the qroup_concat. it works like a dream and where this task was taking 23 seconds to complete it is now taking a matter of milliseconds :) fantastic and thanks once again for all your replies :) What DB are you using? If it's MySQL, you can use this: select client.name, group_concat(clientphone.number) as phone numbers from client left outer join clientphone on client.id = clientphone.clientId group by client.name If it's MS SQL Server you can use a subquery with FOR XML PATH and STUFF to approximate the same functionality. Don't know about other platforms. At the very least, pull a single recordset with the same JOIN as above, but no GROUP BY, and then you can do the rollup in a single CFOUTPUT loop. That'll save you a lot of trips to the DB, and therefor a lot of wasted time. cheers, barneyb ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321592 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: expert sql challenge
Here you go. Note that they're not really semantically equivalent,
they just happen to return the same results. The approach for
obtaining the result is completely different, and the MS SQL one,
while less direct for the actual comma separate list creation, is far
more expressive for what you're actually trying to accomplish (i.e.
uses a correlated subquery instead of a grouped join).
MySQL:
select name, group_concat(phone) as phones
from client
inner join phone on client.id = phone.clientId
group by name
MS SQL Server:
select name,
stuff((
select distinct ',' + phone
from phone
where clientId = client.id
for xml path ('')
), 1, 1, '') as phones
from client
cheers,
barneyb
On Tue, Apr 14, 2009 at 12:31 PM, C. Hatton Humphrey
chumph...@gmail.com wrote:
Is there a MSSQL version of that puppy?
On Tue, Apr 14, 2009 at 3:19 PM, Andy Matthews li...@commadelimited.com
wrote:
Nice. So group_concat works sort of like ColdFusion's valuelist method. Very
nice indeed!
-Original Message-
From: Richard White [mailto:rich...@j7is.co.uk]
Sent: Tuesday, April 14, 2009 2:59 PM
To: cf-talk
Subject: Re: expert sql challenge
thanks for all your replies.
barney, yes we are using mysql and didnt even know about the qroup_concat.
it works like a dream and where this task was taking 23 seconds to complete
it is now taking a matter of milliseconds :)
fantastic and thanks once again for all your replies :)
What DB are you using? If it's MySQL, you can use this:
select client.name, group_concat(clientphone.number) as phone numbers
from client
left outer join clientphone on client.id = clientphone.clientId group
by client.name
If it's MS SQL Server you can use a subquery with FOR XML PATH and
STUFF to approximate the same functionality. Don't know about other
platforms.
At the very least, pull a single recordset with the same JOIN as above,
but no GROUP BY, and then you can do the rollup in a single CFOUTPUT
loop. That'll save you a lot of trips to the DB, and therefor a lot of
wasted time.
cheers,
barneyb
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321593
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: expert sql challenge
Barney, that TSQL is brilliant. I've never used the Stuff function and
had only used FOR XML PATH when generating xml. I had to go look at
the books online and sure enough they have an example turning results
into a value list in the same way that you are although they do the
select as data(). I'm not familiar with the data() function and why
they would use it but your query doesn't Would you mind elaborating?
And for those interested in what I'm talking about his Barney's use of
FOR XML PATH, the msdn reference is here:
http://msdn.microsoft.com/en-us/library/ms189885(SQL.90).aspx
Thanks,
Judah
On Tue, Apr 14, 2009 at 1:20 PM, Barney Boisvert bboisv...@gmail.com wrote:
Here you go. Note that they're not really semantically equivalent,
they just happen to return the same results. The approach for
obtaining the result is completely different, and the MS SQL one,
while less direct for the actual comma separate list creation, is far
more expressive for what you're actually trying to accomplish (i.e.
uses a correlated subquery instead of a grouped join).
MySQL:
select name, group_concat(phone) as phones
from client
inner join phone on client.id = phone.clientId
group by name
MS SQL Server:
select name,
stuff((
select distinct ',' + phone
from phone
where clientId = client.id
for xml path ('')
), 1, 1, '') as phones
from client
cheers,
barneyb
On Tue, Apr 14, 2009 at 12:31 PM, C. Hatton Humphrey
chumph...@gmail.com wrote:
Is there a MSSQL version of that puppy?
On Tue, Apr 14, 2009 at 3:19 PM, Andy Matthews li...@commadelimited.com
wrote:
Nice. So group_concat works sort of like ColdFusion's valuelist method. Very
nice indeed!
-Original Message-
From: Richard White [mailto:rich...@j7is.co.uk]
Sent: Tuesday, April 14, 2009 2:59 PM
To: cf-talk
Subject: Re: expert sql challenge
thanks for all your replies.
barney, yes we are using mysql and didnt even know about the qroup_concat.
it works like a dream and where this task was taking 23 seconds to complete
it is now taking a matter of milliseconds :)
fantastic and thanks once again for all your replies :)
What DB are you using? If it's MySQL, you can use this:
select client.name, group_concat(clientphone.number) as phone numbers
from client
left outer join clientphone on client.id = clientphone.clientId group
by client.name
If it's MS SQL Server you can use a subquery with FOR XML PATH and
STUFF to approximate the same functionality. Don't know about other
platforms.
At the very least, pull a single recordset with the same JOIN as above,
but no GROUP BY, and then you can do the rollup in a single CFOUTPUT
loop. That'll save you a lot of trips to the DB, and therefor a lot of
wasted time.
cheers,
barneyb
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321596
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: expert sql challenge
I don't know about the data function. I try to avoid SQL Server when
I can. ;) We use it for some projects with MySQL's query language
wasn't rich enough to express certain types of queries, but in general
I use MySQL when possible.
Did they have an example of a query equivalent to mine that uses the
data function? Because it seems like that'd probably be more
efficient than going through an XML process.
cheers,
barneyb
On Tue, Apr 14, 2009 at 3:13 PM, Judah McAuley ju...@wiredotter.com wrote:
Barney, that TSQL is brilliant. I've never used the Stuff function and
had only used FOR XML PATH when generating xml. I had to go look at
the books online and sure enough they have an example turning results
into a value list in the same way that you are although they do the
select as data(). I'm not familiar with the data() function and why
they would use it but your query doesn't Would you mind elaborating?
And for those interested in what I'm talking about his Barney's use of
FOR XML PATH, the msdn reference is here:
http://msdn.microsoft.com/en-us/library/ms189885(SQL.90).aspx
Thanks,
Judah
On Tue, Apr 14, 2009 at 1:20 PM, Barney Boisvert bboisv...@gmail.com wrote:
Here you go. Note that they're not really semantically equivalent,
they just happen to return the same results. The approach for
obtaining the result is completely different, and the MS SQL one,
while less direct for the actual comma separate list creation, is far
more expressive for what you're actually trying to accomplish (i.e.
uses a correlated subquery instead of a grouped join).
MySQL:
select name, group_concat(phone) as phones
from client
inner join phone on client.id = phone.clientId
group by name
MS SQL Server:
select name,
stuff((
select distinct ',' + phone
from phone
where clientId = client.id
for xml path ('')
), 1, 1, '') as phones
from client
cheers,
barneyb
On Tue, Apr 14, 2009 at 12:31 PM, C. Hatton Humphrey
chumph...@gmail.com wrote:
Is there a MSSQL version of that puppy?
On Tue, Apr 14, 2009 at 3:19 PM, Andy Matthews li...@commadelimited.com
wrote:
Nice. So group_concat works sort of like ColdFusion's valuelist method.
Very
nice indeed!
-Original Message-
From: Richard White [mailto:rich...@j7is.co.uk]
Sent: Tuesday, April 14, 2009 2:59 PM
To: cf-talk
Subject: Re: expert sql challenge
thanks for all your replies.
barney, yes we are using mysql and didnt even know about the qroup_concat.
it works like a dream and where this task was taking 23 seconds to complete
it is now taking a matter of milliseconds :)
fantastic and thanks once again for all your replies :)
What DB are you using? If it's MySQL, you can use this:
select client.name, group_concat(clientphone.number) as phone numbers
from client
left outer join clientphone on client.id = clientphone.clientId group
by client.name
If it's MS SQL Server you can use a subquery with FOR XML PATH and
STUFF to approximate the same functionality. Don't know about other
platforms.
At the very least, pull a single recordset with the same JOIN as above,
but no GROUP BY, and then you can do the rollup in a single CFOUTPUT
loop. That'll save you a lot of trips to the DB, and therefor a lot of
wasted time.
cheers,
barneyb
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321599
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: expert sql challenge
On Tue, Apr 14, 2009 at 3:47 PM, Barney Boisvert bboisv...@gmail.com wrote:
Did they have an example of a query equivalent to mine that uses the
data function? Because it seems like that'd probably be more
efficient than going through an XML process.
MSDN actually used almost exactly what you wrote. If you go to the MSDN page:
http://msdn.microsoft.com/en-us/library/ms189885(SQL.90).aspx
And look down for C. Generating a value list using PATH mode it
shows their example.
The only real difference is that they were using AS data() which I
am not familiar with. It might be because the result returned from the
subquery in their example is being put into a tsql variable and then
used in another xml path query.
Here is the subquery in their example:
(SELECT ProductID as data()
FROM Production.Product
WHERE Production.Product.ProductModelID =
Production.ProductModel.ProductModelID
FOR XML PATH ('')) as @ProductIDs
Cheers
Judah
On Tue, Apr 14, 2009 at 3:13 PM, Judah McAuley ju...@wiredotter.com wrote:
Barney, that TSQL is brilliant. I've never used the Stuff function and
had only used FOR XML PATH when generating xml. I had to go look at
the books online and sure enough they have an example turning results
into a value list in the same way that you are although they do the
select as data(). I'm not familiar with the data() function and why
they would use it but your query doesn't Would you mind elaborating?
And for those interested in what I'm talking about his Barney's use of
FOR XML PATH, the msdn reference is here:
http://msdn.microsoft.com/en-us/library/ms189885(SQL.90).aspx
Thanks,
Judah
On Tue, Apr 14, 2009 at 1:20 PM, Barney Boisvert bboisv...@gmail.com wrote:
Here you go. Note that they're not really semantically equivalent,
they just happen to return the same results. The approach for
obtaining the result is completely different, and the MS SQL one,
while less direct for the actual comma separate list creation, is far
more expressive for what you're actually trying to accomplish (i.e.
uses a correlated subquery instead of a grouped join).
MySQL:
select name, group_concat(phone) as phones
from client
inner join phone on client.id = phone.clientId
group by name
MS SQL Server:
select name,
stuff((
select distinct ',' + phone
from phone
where clientId = client.id
for xml path ('')
), 1, 1, '') as phones
from client
cheers,
barneyb
On Tue, Apr 14, 2009 at 12:31 PM, C. Hatton Humphrey
chumph...@gmail.com wrote:
Is there a MSSQL version of that puppy?
On Tue, Apr 14, 2009 at 3:19 PM, Andy Matthews li...@commadelimited.com
wrote:
Nice. So group_concat works sort of like ColdFusion's valuelist method.
Very
nice indeed!
-Original Message-
From: Richard White [mailto:rich...@j7is.co.uk]
Sent: Tuesday, April 14, 2009 2:59 PM
To: cf-talk
Subject: Re: expert sql challenge
thanks for all your replies.
barney, yes we are using mysql and didnt even know about the qroup_concat.
it works like a dream and where this task was taking 23 seconds to
complete
it is now taking a matter of milliseconds :)
fantastic and thanks once again for all your replies :)
What DB are you using? If it's MySQL, you can use this:
select client.name, group_concat(clientphone.number) as phone numbers
from client
left outer join clientphone on client.id = clientphone.clientId group
by client.name
If it's MS SQL Server you can use a subquery with FOR XML PATH and
STUFF to approximate the same functionality. Don't know about other
platforms.
At the very least, pull a single recordset with the same JOIN as above,
but no GROUP BY, and then you can do the rollup in a single CFOUTPUT
loop. That'll save you a lot of trips to the DB, and therefor a lot of
wasted time.
cheers,
barneyb
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321600
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
SQL Challenge
Is there a way in MSSQL and Oracle or either that will allow you to do a select on a varchar field and be able to see if that record can be converted to a number? We have a ton of databases that used varchar to handle numbers and are getting issues now that some records have gotten data other than a number in them. I want to convert them to an integer field but can't do that until we fix the problem records. Bob ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231826 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 Challenge
Robert Everland III wrote: Is there a way in MSSQL and Oracle or either that will allow you to do a select on a varchar field and be able to see if that record can be converted to a number? We have a ton of databases that used varchar to handle numbers and are getting issues now that some records have gotten data other than a number in them. I want to convert them to an integer field but can't do that until we fix the problem records. You mean like the Transact-SQL function isNumeric()? SELECT CompanyName, PostalCode, isnumeric(PostalCode) AS 'PostCodeIsNumeric' FROM suppliers http://www.brettb.com/SQL_Help_IsNumeric_Function.asp (first result in the following google search: http://www.google.com/search?hl=enq=isnumeric+transact-sql ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231828 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 Challenge
In sql server if you want to find the records that aren't numeric you can use: Select * From your_table Where isNumeric(fieldToCheck) = 0 -Original Message- From: Robert Everland III [mailto:[EMAIL PROTECTED] Sent: Thursday, February 09, 2006 2:52 PM To: CF-Talk Subject: SQL Challenge Is there a way in MSSQL and Oracle or either that will allow you to do a select on a varchar field and be able to see if that record can be converted to a number? We have a ton of databases that used varchar to handle numbers and are getting issues now that some records have gotten data other than a number in them. I want to convert them to an integer field but can't do that until we fix the problem records. Bob ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231830 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 Challenge
SQL 2k uses ISNUMERIC(column) to determine if it's numeric or not. SELECT ISNUMERIC(myCol) AS NumericTest FROM Table I'm not sure about Oracle, though. If Oracle does not have a function, you could probably use a function that parses a numeric value and then compares it to the original value. If they are the same, then you could assume it is numeric. SELECT * FROM Table WHERE PARSEINT(myCol) = myCol (I just made up the PARSEINT() function. But you get the gist of it.) M!ke -Original Message- From: Robert Everland III [mailto:[EMAIL PROTECTED] Sent: Thursday, February 09, 2006 1:52 PM To: CF-Talk Subject: SQL Challenge Is there a way in MSSQL and Oracle or either that will allow you to do a select on a varchar field and be able to see if that record can be converted to a number? We have a ton of databases that used varchar to handle numbers and are getting issues now that some records have gotten data other than a number in them. I want to convert them to an integer field but can't do that until we fix the problem records. ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231829 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 Challenge
In MS-SQL, there is a isNumeric function...it may get you most of the
way there, but also I will tell you that sometimes it returns true for
things that won't convert...case in point...throw this in query
analyzer
select isnumeric('1,1.1') 'returns true
select cast('1,1.1' as numeric) 'throws error
On 2/9/06, Robert Everland III [EMAIL PROTECTED] wrote:
Is there a way in MSSQL and Oracle or either that will allow you to do a
select on a varchar field and be able to see if that record can be converted
to a number? We have a ton of databases that used varchar to handle numbers
and are getting issues now that some records have gotten data other than a
number in them. I want to convert them to an integer field but can't do that
until we fix the problem records.
Bob
~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231832
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 Challenge
In Oracle I this function:
CREATE OR REPLACE function is_number
( p_string in varchar2 )
return number
deterministic
as
l_num number;
begin
l_num := p_string;
return 1;
exception
when others then
return null;
end;
/
On 2/9/06, Jim Wright [EMAIL PROTECTED] wrote:
In MS-SQL, there is a isNumeric function...it may get you most of the
way there, but also I will tell you that sometimes it returns true for
things that won't convert...case in point...throw this in query
analyzer
select isnumeric('1,1.1') 'returns true
select cast('1,1.1' as numeric) 'throws error
On 2/9/06, Robert Everland III [EMAIL PROTECTED] wrote:
Is there a way in MSSQL and Oracle or either that will allow you to do a
select on a varchar field and be able to see if that record can be converted
to a number? We have a ton of databases that used varchar to handle numbers
and are getting issues now that some records have gotten data other than a
number in them. I want to convert them to an integer field but can't do that
until we fix the problem records.
Bob
~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231834
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 Challenge
-Original Message-
From: Robert Everland III [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 09, 2006 2:52 PM
To: CF-Talk
Subject: SQL Challenge
Is there a way in MSSQL and Oracle or either that will allow you to do a
select on a varchar field and be able to see if that record can be
converted to a number? We have a ton of databases that used varchar to
handle numbers and are getting issues now that some records have gotten
data other than a number in them. I want to convert them to an integer
field but can't do that until we fix the problem records.
Sadly, Oracle doesn't provide a native function like isNumeric() to do
this, to my knowledge. However, you don't specify which version of
Oracle, so I'll give you two solution, neither of which I can claim
credit for, but have lived in my code snippet archive for a while:
Pre-Oracle 10g:
Create a function called isNumber:
CREATE OR REPLACE FUNCTION isNumber(p_val IN VARCHAR2) RETURN NUMBER IS
l_val NUMBER;
BEGIN
l_val := TO_NUMBER(p_val);
RETURN 1;
EXCEPTION WHEN VALUE_ERROR THEN
RETURN 0;
END;
Then, in your query, you'd just do something like:
SELECT
isNumber('2006') AS isNumeric
FROM
dual;
That should return 1.
SELECT
isNumber('Dave') AS isNumeric
FROM
dual;
should return 0.
If you're using Oracle 10g, they've added great RegEx support (it was
there before, but with some packages I honestly never used, so I don't
know the syntax):
SELECT
id
FROM
myTable
WHERE
REGEXP_LIKE (myColumn, '^[0-9]+$');
That should return the *non*-numeric values from that column.
These are untested, but should work.
Regards,
Dave.
~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231836
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
SQL Challenge
I've got a challenge for any and all SQL guru's.I need to return a record set consisting of all records with a date in the future of today as well as one and only one record previous to the current date, if one exists. Would this best be done with a union of two selects, or is there a trick that would allow this to be done in one select? If it matters, this is against an Oracle 8 database and the date field is stored as an integer in the format of mmdd. Thanks -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
SQL Challenge
Ok here is the challenge. Surprise is at the end. I am trying to put together a report on our member's membership periods. This report needs to identify the entire member's membership history. Memberships are based on an annual subscription. However, there are sometime breaks in one's membership due to cancellation, expired, whatever. So I will give 2 examples of some memberships. Example Member A joined 7/1/2001 and their current expiration is 7/31/2004. They have been good member for 3 years and renewed every year without incident. In my history table I have the following for Member A's EXPIRATION DATES OLDVAL NEWVAL 07/31/2003 07/31/2004 07/31/2002 07/31/2003 07/31/2001 07/31/2002 Example Member B joined 7/1/2001 and their current expiration is 8/31/2004. This member forgot to renew one year and rejoined in August. In my history table I have the following for Member B's EXPIRATION DATES OLDVAL NEWVAL 07/31/2003 08/31/2004 07/31/2002 07/31/2003 07/31/2001 07/31/2002 So on my report I would like to see the following to determine how long their memberships have been contiguous and gauge how long between rejoins for lapses in membership. This data could be kept in a temporary table. MEMBER A 07/1/2001 07/31/2004 MEMBER B 08/1/2001 08/31/2004 MEMBER B 07/1/2001 07/31/2003 If someone can provide me a solution I will send them a surprise via mail, we got some cool stuff around my place of business. Eric [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL Challenge
Eric, Your example for Member B doesn't make sense to me. According to the dates you gave, there was no lapse, he just got a few extra months. Can you clarify? Steve -Original Message- From: Eric Creese [mailto:[EMAIL PROTECTED] Sent: Thursday, November 06, 2003 3:41 PM To: CF-Talk Subject: SQL Challenge Ok here is the challenge. Surprise is at the end. I am trying to put together a report on our member's membership periods. This report needs to identify the entire member's membership history. Memberships are based on an annual subscription. However, there are sometime breaks in one's membership due to cancellation, expired, whatever. So I will give 2 examples of some memberships. Example Member A joined 7/1/2001 and their current expiration is 7/31/2004. They have been good member for 3 years and renewed every year without incident. In my history table I have the following for Member A's EXPIRATION DATES OLDVAL NEWVAL 07/31/2003 07/31/2004 07/31/2002 07/31/2003 07/31/2001 07/31/2002 Example Member B joined 7/1/2001 and their current expiration is 8/31/2004. This member forgot to renew one year and rejoined in August. In my history table I have the following for Member B's EXPIRATION DATES OLDVAL NEWVAL 07/31/2003 08/31/2004 07/31/2002 07/31/2003 07/31/2001 07/31/2002 So on my report I would like to see the following to determine how long their memberships have been contiguous and gauge how long between rejoins for lapses in membership. This data could be kept in a temporary table. MEMBER A 07/1/2001 07/31/2004 MEMBER B 08/1/2001 08/31/2004 MEMBER B 07/1/2001 07/31/2003 If someone can provide me a solution I will send them a surprise via mail, we got some cool stuff around my place of business. Eric _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL Challenge
Made a mistake for the report output MEMBER A07/1/200107/31/2004 MEMBER B08/1/200308/31/2004 MEMBER B07/1/200107/31/2003 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL Challenge
does this have to be solely in sql server code? or can it be cf/sql? ...tony tony weeg senior web applications architect navtrak, inc. www.navtrak.net [EMAIL PROTECTED] 410.548.2337 -Original Message- From: Eric Creese [mailto:[EMAIL PROTECTED] Sent: Thursday, November 06, 2003 4:12 PM To: CF-Talk Subject: RE: SQL Challenge Made a mistake for the report output MEMBER A07/1/200107/31/2004 MEMBER B08/1/200308/31/2004 MEMBER B07/1/200107/31/2003 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL Challenge
if you join in any part of a month, the last day of that month becomes your expiration date. So for Member B, he joined in July originally. Paid his first and second year, then for his third year he did not pay son the membership expired 7/31/2003. He rejoined 8/17/2003 thus giving him a new expiration date of 8/31/2004. What I am trying to spit out in the report is the uninterrupted membership time for a given membership I had a typo in the original post, should have been: MEMBER A 07/1/200107/31/2004 MEMBER B 08/17/2003 08/31/2004 MEMBER B 07/1/200107/31/2003 -Original Message- From: DURETTE, STEVEN J (AIT) [mailto:[EMAIL PROTECTED] Sent: Thursday, November 06, 2003 3:11 PM To: CF-Talk Subject: RE: SQL Challenge Eric, Your example for Member B doesn't make sense to me. According to the dates you gave, there was no lapse, he just got a few extra months. Can you clarify? Steve -Original Message- From: Eric Creese [mailto:[EMAIL PROTECTED] Sent: Thursday, November 06, 2003 3:41 PM To: CF-Talk Subject: SQL Challenge Ok here is the challenge. Surprise is at the end. I am trying to put together a report on our member's membership periods. This report needs to identify the entire member's membership history. Memberships are based on an annual subscription. However, there are sometime breaks in one's membership due to cancellation, expired, whatever. So I will give 2 examples of some memberships. Example Member A joined 7/1/2001 and their current expiration is 7/31/2004. They have been good member for 3 years and renewed every year without incident. In my history table I have the following for Member A's EXPIRATION DATES OLDVAL NEWVAL 07/31/2003 07/31/2004 07/31/2002 07/31/2003 07/31/2001 07/31/2002 Example Member B joined 7/1/2001 and their current expiration is 8/31/2004. This member forgot to renew one year and rejoined in August. In my history table I have the following for Member B's EXPIRATION DATES OLDVAL NEWVAL 07/31/2003 08/31/2004 07/31/2002 07/31/2003 07/31/2001 07/31/2002 So on my report I would like to see the following to determine how long their memberships have been contiguous and gauge how long between rejoins for lapses in membership. This data could be kept in a temporary table. MEMBER A 07/1/2001 07/31/2004 MEMBER B 08/1/2001 08/31/2004 MEMBER B 07/1/2001 07/31/2003 If someone can provide me a solution I will send them a surprise via mail, we got some cool stuff around my place of business. Eric _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL Challenge
Has to be in SQL. Will need to put it in a Stored Proc at some point. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL Challenge
What kind of database are we dealing with since date functions vary from DB to DB? -Original Message- From: Eric Creese [mailto:[EMAIL PROTECTED] Sent: Thursday, November 06, 2003 2:41 PM To: CF-Talk Subject: SQL Challenge Ok here is the challenge. Surprise is at the end. I am trying to put together a report on our member's membership periods. This report needs to identify the entire member's membership history. Memberships are based on an annual subscription. However, there are sometime breaks in one's membership due to cancellation, expired, whatever. So I will give 2 examples of some memberships. Example Member A joined 7/1/2001 and their current expiration is 7/31/2004. They have been good member for 3 years and renewed every year without incident. In my history table I have the following for Member A's EXPIRATION DATES OLDVAL NEWVAL 07/31/2003 07/31/2004 07/31/2002 07/31/2003 07/31/2001 07/31/2002 Example Member B joined 7/1/2001 and their current expiration is 8/31/2004. This member forgot to renew one year and rejoined in August. In my history table I have the following for Member B's EXPIRATION DATES OLDVAL NEWVAL 07/31/2003 08/31/2004 07/31/2002 07/31/2003 07/31/2001 07/31/2002 So on my report I would like to see the following to determine how long their memberships have been contiguous and gauge how long between rejoins for lapses in membership. This data could be kept in a temporary table. MEMBER A 07/1/2001 07/31/2004 MEMBER B 08/1/2001 08/31/2004 MEMBER B 07/1/2001 07/31/2003 If someone can provide me a solution I will send them a surprise via mail, we got some cool stuff around my place of business. Eric _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL Challenge
Send the prize first pelase :-) -Original Message- From: Eric Creese [mailto:[EMAIL PROTECTED] Sent: Thursday, November 06, 2003 3:41 PM To: CF-Talk Subject: SQL Challenge Ok here is the challenge. Surprise is at the end. I am trying to put together a report on our member's membership periods. This report needs to identify the entire member's membership history. Memberships are based on an annual subscription. However, there are sometime breaks in one's membership due to cancellation, expired, whatever. So I will give 2 examples of some memberships. Example Member A joined 7/1/2001 and their current expiration is 7/31/2004. They have been good member for 3 years and renewed every year without incident. In my history table I have the following for Member A's EXPIRATION DATES OLDVAL NEWVAL 07/31/2003 07/31/2004 07/31/2002 07/31/2003 07/31/2001 07/31/2002 Example Member B joined 7/1/2001 and their current expiration is 8/31/2004. This member forgot to renew one year and rejoined in August. In my history table I have the following for Member B's EXPIRATION DATES OLDVAL NEWVAL 07/31/2003 08/31/2004 07/31/2002 07/31/2003 07/31/2001 07/31/2002 So on my report I would like to see the following to determine how long their memberships have been contiguous and gauge how long between rejoins for lapses in membership. This data could be kept in a temporary table. MEMBER A 07/1/2001 07/31/2004 MEMBER B 08/1/2001 08/31/2004 MEMBER B 07/1/2001 07/31/2003 If someone can provide me a solution I will send them a surprise via mail, we got some cool stuff around my place of business. Eric _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL Challenge
Eric,
Here is the code that does the job. (MSSQLl2K)
It's not very pretty but it works.
/* Create table with demo info */
declare @memberinfo table(
memberId char(1),
startDate datetime,
endDate datetime
)
insert into @memberinfo(memberId, startDate, endDate)
values('A','07/31/2003','07/31/2004')
insert into @memberinfo(memberId, startDate, endDate)
values('A','07/31/2002','07/31/2003')
insert into @memberinfo(memberId, startDate, endDate)
values('A','07/31/2001','07/31/2002')
insert into @memberinfo(memberId, startDate, endDate)
values('B','08/01/2003','08/31/2004')
insert into @memberinfo(memberId, startDate, endDate)
values('B','08/01/2003','08/31/2004')
insert into @memberinfo(memberId, startDate, endDate)
values('B','07/31/2001','07/31/2002')
declare @holding table(
recid int identity(1,1),
memberid char(1),
startDate datetime,
endDate datetime
)
/* start processing */
declare @finalTable table(
recid int identity(1,1),
memberid char(1),
startDate datetime,
endDate datetime
)
insert into @holding(memberId, startDate, endDate)
select memberId, startDate, endDate
from @memberinfo
order by memberId, startDate
declare @mbrId char(1)
declare @strtDt datetime
declare @endDt datetime
DECLARE mycursor CURSOR FOR
SELECT memberId, startDate, endDate
FROM @holding
order by memberId, startDate
OPEN mycursor
FETCH NEXT FROM mycursor INTO @mbrId, @strtDt, @endDt
WHILE @@FETCH_STATUS = 0
BEGIN
if(@mbrId not in (select distinct memberId
from @finalTable))
begin
insert into @finalTable(memberId, startDate, endDate)
values(@mbrId, @strtDt, @endDt)
end
else
begin
if(@strtDt = (select max(endDate)
from @finalTable
where memberId = @mbrId))
begin
update @finalTable
set endDate = @endDt
where memberId = @mbrId
and endDate = (select max(endDate)
from @finalTable
where memberId = @mbrId)
end
else
begin
insert into @finalTable(memberId, startDate, endDate)
values(@mbrId, @strtDt, @endDt)
end
end
fetch next from mycursor into @mbrId, @strtDt, @endDt
END
CLOSE mycursor
DEALLOCATE mycursor
select * from @finalTable
The output was:
1 A 2001-07-31 00:00:00.000 2004-07-31 00:00:00.000
2 B 2001-07-31 00:00:00.000 2002-07-31 00:00:00.000
3 B 2003-08-01 00:00:00.000 2004-08-31 00:00:00.000
So what do I get? :)
-Original Message-
From: Eric Creese [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 06, 2003 4:26 PM
To: CF-Talk
Subject: RE: SQL Challenge
Has to be in SQL. Will need to put it in a Stored Proc at some point.
_
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
