Re: (ot) SQL Question - flattening data
Just read the original post properly - please ignore me. Dominic 2009/11/15 Dominic Watson > Depending on what you are doing with this data, seems to me that this > should be done in the front end and not the db. So get your data in the > original format: > > USER, CODE > > rick,AL > rick,FR > rick,TR > rick,HS > joe,AL > joe,FU > Bob,FM > > And then use cfoutput with query and group (a very rough output here): > > > >User>Codes > > > > > > #user > > #code# > > > > > > > > > Dominic > > 2009/11/13 Rick Root > > >> From the documentation, pivot tables seem to require aggregate >> functions... The generic description would seem to work but the >> examples make it difficult to see how. >> >> But... I figured out a solution! Using SQL Server's row_number() over >> (partition by XXX order by XXX) I can make a subquery that returns >> data like this >> >> entityid,rownum,rectyp >> >> And then run this query (tb901 is my "primary" table) >> >> select >>tb901.entityid, >>R1.rectypcd as rectype1, >>r2.rectypcd as rectype2, >>r3.rectypcd as rectype3, >>r4.rectypcd as rectype4, >>r5.rectypcd as rectype5 >> from >>tb901 >>left join >>( >>select row_number() over(partition by entityid >> order by rectypcd) >> as rownum, entityid, rectypcd >>from tb906 >>) R1 on tb901.entityid=R1.entityid and R1.rownum=1 >>left join >>( >>select row_number() over(partition by entityid >> order by rectypcd) >> as rownum, entityid, rectypcd >>from tb906 >>) R2 on tb901.entityid=R2.entityid and R2.rownum=2 >>left join >>( >>select row_number() over(partition by entityid >> order by rectypcd) >> as rownum, entityid, rectypcd >>from tb906 >>) R3 on tb901.entityid=R3.entityid and R3.rownum=3 >>left join >>( >>select row_number() over(partition by entityid >> order by rectypcd) >> as rownum, entityid, rectypcd >>from tb906 >>) R4 on tb901.entityid=R4.entityid and R4.rownum=4 >>left join >>( >>select row_number() over(partition by entityid >> order by rectypcd) >> as rownum, entityid, rectypcd >>from tb906 >>) R5 on tb901.entityid=R5.entityid and R5.rownum=5 >> >> ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328397 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: (ot) SQL Question - flattening data
Depending on what you are doing with this data, seems to me that this should be done in the front end and not the db. So get your data in the original format: USER, CODE rick,AL rick,FR rick,TR rick,HS joe,AL joe,FU Bob,FM And then use cfoutput with query and group (a very rough output here): User>Codes #user #code# Dominic 2009/11/13 Rick Root > > From the documentation, pivot tables seem to require aggregate > functions... The generic description would seem to work but the > examples make it difficult to see how. > > But... I figured out a solution! Using SQL Server's row_number() over > (partition by XXX order by XXX) I can make a subquery that returns > data like this > > entityid,rownum,rectyp > > And then run this query (tb901 is my "primary" table) > > select >tb901.entityid, >R1.rectypcd as rectype1, >r2.rectypcd as rectype2, >r3.rectypcd as rectype3, >r4.rectypcd as rectype4, >r5.rectypcd as rectype5 > from >tb901 >left join >( >select row_number() over(partition by entityid order > by rectypcd) > as rownum, entityid, rectypcd >from tb906 >) R1 on tb901.entityid=R1.entityid and R1.rownum=1 >left join >( >select row_number() over(partition by entityid order > by rectypcd) > as rownum, entityid, rectypcd >from tb906 >) R2 on tb901.entityid=R2.entityid and R2.rownum=2 >left join >( >select row_number() over(partition by entityid order > by rectypcd) > as rownum, entityid, rectypcd >from tb906 >) R3 on tb901.entityid=R3.entityid and R3.rownum=3 >left join >( >select row_number() over(partition by entityid order > by rectypcd) > as rownum, entityid, rectypcd >from tb906 >) R4 on tb901.entityid=R4.entityid and R4.rownum=4 >left join >( >select row_number() over(partition by entityid order > by rectypcd) > as rownum, entityid, rectypcd >from tb906 >) R5 on tb901.entityid=R5.entityid and R5.rownum=5 > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328396 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: (ot) SQL Question - flattening data
You wrote a pivot query without using pivot. BTW, the aggregate for the pivot query can be Count(). -Original Message- From: Rick Root [mailto:rick.r...@gmail.com] Sent: Friday, November 13, 2009 4:19 PM To: cf-talk Subject: Re: (ot) SQL Question - flattening data >From the documentation, pivot tables seem to require aggregate functions... The generic description would seem to work but the examples make it difficult to see how. But... I figured out a solution! Using SQL Server's row_number() over (partition by XXX order by XXX) I can make a subquery that returns data like this entityid,rownum,rectyp And then run this query (tb901 is my "primary" table) select tb901.entityid, R1.rectypcd as rectype1, r2.rectypcd as rectype2, r3.rectypcd as rectype3, r4.rectypcd as rectype4, r5.rectypcd as rectype5 from tb901 left join ( select row_number() over(partition by entityid order by rectypcd) as rownum, entityid, rectypcd from tb906 ) R1 on tb901.entityid=R1.entityid and R1.rownum=1 left join ( select row_number() over(partition by entityid order by rectypcd) as rownum, entityid, rectypcd from tb906 ) R2 on tb901.entityid=R2.entityid and R2.rownum=2 left join ( select row_number() over(partition by entityid order by rectypcd) as rownum, entityid, rectypcd from tb906 ) R3 on tb901.entityid=R3.entityid and R3.rownum=3 left join ( select row_number() over(partition by entityid order by rectypcd) as rownum, entityid, rectypcd from tb906 ) R4 on tb901.entityid=R4.entityid and R4.rownum=4 left join ( select row_number() over(partition by entityid order by rectypcd) as rownum, entityid, rectypcd from tb906 ) R5 on tb901.entityid=R5.entityid and R5.rownum=5 ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328395 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: (ot) SQL Question - flattening data
>From the documentation, pivot tables seem to require aggregate functions... The generic description would seem to work but the examples make it difficult to see how. But... I figured out a solution! Using SQL Server's row_number() over (partition by XXX order by XXX) I can make a subquery that returns data like this entityid,rownum,rectyp And then run this query (tb901 is my "primary" table) select tb901.entityid, R1.rectypcd as rectype1, r2.rectypcd as rectype2, r3.rectypcd as rectype3, r4.rectypcd as rectype4, r5.rectypcd as rectype5 from tb901 left join ( select row_number() over(partition by entityid order by rectypcd) as rownum, entityid, rectypcd from tb906 ) R1 on tb901.entityid=R1.entityid and R1.rownum=1 left join ( select row_number() over(partition by entityid order by rectypcd) as rownum, entityid, rectypcd from tb906 ) R2 on tb901.entityid=R2.entityid and R2.rownum=2 left join ( select row_number() over(partition by entityid order by rectypcd) as rownum, entityid, rectypcd from tb906 ) R3 on tb901.entityid=R3.entityid and R3.rownum=3 left join ( select row_number() over(partition by entityid order by rectypcd) as rownum, entityid, rectypcd from tb906 ) R4 on tb901.entityid=R4.entityid and R4.rownum=4 left join ( select row_number() over(partition by entityid order by rectypcd) as rownum, entityid, rectypcd from tb906 ) R5 on tb901.entityid=R5.entityid and R5.rownum=5 ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328377 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: (ot) SQL Question - flattening data
Actually, if MSSQL 2005+ is being used, PIVOT might come in handy here. I had a procedure that used cursors. It ran for 2 minutes. I converted it to use PIVOT instead and I get the same results in 2 seconds! Steve -Original Message- From: Dave Phelan [mailto:dphe...@lifepoint.com] Sent: Friday, November 13, 2009 1:11 PM To: cf-talk Subject: RE: (ot) SQL Question - flattening data Is there a particular reason to return them in this format? I would think that the straight query output would be simpler to work with. However, you can accomplish this either by using cursors to loop over the query output and build what you are looking for or by building a crosstab query of the data. I haven't built a crosstab query in quite a while and don't remember all the specifics, but the output would be similar to: Entity AL FR TR HS FU FM RickX X X X Joe X X Bob X Crosstab queries can be a little hairy to build. IMHO, go with the cursors. -Original Message- From: Rick Root [mailto:rick.r...@gmail.com] Sent: Friday, November 13, 2009 10:41 AM To: cf-talk Subject: (ot) SQL Question - flattening data I'm trying to flatten out some data using only SQL we currently have a mainframe job that produces a datafeed for me uses cobol to do the work of looping through all the entities and putting up to 5 record types in 5 "record type" fields in the output file. I'm trying to figure out a way to do it with SQL alone so I can just use a transact-sql job to produced my flattened reporting table. So for example, let's say I've got a table like this: create table entityRecordTypes ( entityid char(10), recordType char(2), primary key (entityid, recordType) ); How do I get from here ... rick,AL rick,FR rick,TR rick,HS joe,AL joe,FU Bob,FM to a view or table that has this structure entityid,rectype1,rectype2,rectype3,rectype4,rectype5 rick,AL,FR,TR,HS,NULL joe,AL,FU,NULL,NULL,NULL bob,FM,NULL,NULL,NULL,NULL using SQL. if an entity had more than 5 record types, only the first 5 would be put into the output table/view. Rick ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328357 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: (ot) SQL Question - flattening data
Is there a particular reason to return them in this format? I would think that the straight query output would be simpler to work with. However, you can accomplish this either by using cursors to loop over the query output and build what you are looking for or by building a crosstab query of the data. I haven't built a crosstab query in quite a while and don't remember all the specifics, but the output would be similar to: Entity AL FR TR HS FU FM RickX X X X Joe X X Bob X Crosstab queries can be a little hairy to build. IMHO, go with the cursors. -Original Message- From: Rick Root [mailto:rick.r...@gmail.com] Sent: Friday, November 13, 2009 10:41 AM To: cf-talk Subject: (ot) SQL Question - flattening data I'm trying to flatten out some data using only SQL we currently have a mainframe job that produces a datafeed for me uses cobol to do the work of looping through all the entities and putting up to 5 record types in 5 "record type" fields in the output file. I'm trying to figure out a way to do it with SQL alone so I can just use a transact-sql job to produced my flattened reporting table. So for example, let's say I've got a table like this: create table entityRecordTypes ( entityid char(10), recordType char(2), primary key (entityid, recordType) ); How do I get from here ... rick,AL rick,FR rick,TR rick,HS joe,AL joe,FU Bob,FM to a view or table that has this structure entityid,rectype1,rectype2,rectype3,rectype4,rectype5 rick,AL,FR,TR,HS,NULL joe,AL,FU,NULL,NULL,NULL bob,FM,NULL,NULL,NULL,NULL using SQL. if an entity had more than 5 record types, only the first 5 would be put into the output table/view. Rick ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328354 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
(ot) SQL Question - flattening data
I'm trying to flatten out some data using only SQL we currently have a mainframe job that produces a datafeed for me uses cobol to do the work of looping through all the entities and putting up to 5 record types in 5 "record type" fields in the output file. I'm trying to figure out a way to do it with SQL alone so I can just use a transact-sql job to produced my flattened reporting table. So for example, let's say I've got a table like this: create table entityRecordTypes ( entityid char(10), recordType char(2), primary key (entityid, recordType) ); How do I get from here ... rick,AL rick,FR rick,TR rick,HS joe,AL joe,FU Bob,FM to a view or table that has this structure entityid,rectype1,rectype2,rectype3,rectype4,rectype5 rick,AL,FR,TR,HS,NULL joe,AL,FU,NULL,NULL,NULL bob,FM,NULL,NULL,NULL,NULL using SQL. if an entity had more than 5 record types, only the first 5 would be put into the output table/view. Rick ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328346 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Semi-OT: SQL question...Select first item for each person for each day
Thanks to everyone, I got it working. For the sake of posterity, here is what I came up with: SELECT a.* FROMappointment a INNER JOIN (SELECT patient_id, MIN(appointment_date) AS appointment_date FROM appointment GROUP BY patient_id, convert(varchar(10), appointment_date, 101)) b ON a.patient_id = b.patient_id AND a.appointment_date = b.appointment_date ORDER BY a.patient_id, a.appointment_date Still have some testing to make sure that everything is included properly but this seems to hit the spot. Cheers, Judah On Tue, Feb 24, 2009 at 2:51 PM, Judah McAuley wrote: > I like this approach. That middle query inserting into the temp table > might be what I need for the view. > > Thanks, > Judah > > On Tue, Feb 24, 2009 at 12:48 PM, wrote: >> >> These sort of queries are always kind of annoying. My approach is to to >> use an intermediate temp table, group by patient and day, and find the >> min() appointment date. Then join that temp table back to your >> appointment table to pull out the rest of the information for that first >> appointment based on the min date you found. Keep in mind, this >> approach ASSUMES that one patient will never have two appointments at >> the same time on the same day. >> >> >> This runs on SQL Server 2000. It is a little crude, but should get the >> point across: >> >> declare @appointment table >> (id int identity primary key, >> patient_id int, >> doctor varchar(50), >> appointment_date datetime ) >> >> declare @first_appointment_per_day table >> (patient_id int, >> appointment_date datetime) >> >> >> insert into @appointment (patient_id, doctor, appointment_date) >> select 1, 'McDreamy', '2009-02-24 8:00' >> union all select 1, 'McSteamy', '2009-02-24 8:30' >> union all select 1, 'McRib', '2009-02-25 9:00' >> union all select 1, 'McSteamy', '2009-02-25 11:00' >> union all select 2, 'McDreamy', '2009-02-24 9:00' >> union all select 2, 'McSteamy', '2009-02-24 9:30' >> union all select 2, 'McRib', '2009-02-25 7:00' >> union all select 2, 'McSteamy', '2009-02-25 7:45' >> >> insert into @first_appointment_per_day >> (patient_id, appointment_date) >> (select patient_id, min(appointment_date) >> from @appointment >> group by patient_id, convert(varchar(10), appointment_date, 101)) >> >> select app.patient_id, app.appointment_date, app.doctor >> from @first_appointment_per_day tmp1 >> inner join @appointment app on tmp1.patient_id = app.patient_id >> and tmp1.appointment_date = app.appointment_date >> >> ~Brad >> >> Original Message >> Subject: Semi-OT: SQL question...Select first item for each person for >> each day >> From: Judah McAuley >> Date: Tue, February 24, 2009 1:53 pm >> To: cf-talk >> >> What I need to do right now is generate a view that lists only the >> first appointment >> for each patient each day. >> >> >> ~| 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:319776 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Semi-OT: SQL question...Select first item for each person for each day
Isn't this way easier and executes way faster? select * from appointment left join ( select patient_id, min(appointment_date) as firstAppt, datepart(yy,appointment_date), datepart(dd,appointment_date), datepart(mm,appointment_date) from appointment group by patient_id, datepart(yy,appointment_date), datepart(dd,appointment_date), datepart(mm,appointment_date) ) a on a.firstAppt = appointment.appointment_date and appointment.patient_id = a.patient_id order by appointment_date asc On Tue, Feb 24, 2009 at 3:51 PM, Judah McAuley wrote: > > I like this approach. That middle query inserting into the temp table > might be what I need for the view. > > Thanks, > Judah > > On Tue, Feb 24, 2009 at 12:48 PM, wrote: > > > > These sort of queries are always kind of annoying. My approach is to to > > use an intermediate temp table, group by patient and day, and find the > > min() appointment date. Then join that temp table back to your > > appointment table to pull out the rest of the information for that first > > appointment based on the min date you found. Keep in mind, this > > approach ASSUMES that one patient will never have two appointments at > > the same time on the same day. > > > > > > This runs on SQL Server 2000. It is a little crude, but should get the > > point across: > > > > declare @appointment table > > (id int identity primary key, > > patient_id int, > > doctor varchar(50), > > appointment_date datetime ) > > > > declare @first_appointment_per_day table > > (patient_id int, > > appointment_date datetime) > > > > > > insert into @appointment (patient_id, doctor, appointment_date) > > select 1, 'McDreamy', '2009-02-24 8:00' > > union all select 1, 'McSteamy', '2009-02-24 8:30' > > union all select 1, 'McRib','2009-02-25 9:00' > > union all select 1, 'McSteamy', '2009-02-25 11:00' > > union all select 2, 'McDreamy', '2009-02-24 9:00' > > union all select 2, 'McSteamy', '2009-02-24 9:30' > > union all select 2, 'McRib','2009-02-25 7:00' > > union all select 2, 'McSteamy', '2009-02-25 7:45' > > > > insert into @first_appointment_per_day > > (patient_id, appointment_date) > > (select patient_id, min(appointment_date) > > from @appointment > > group by patient_id, convert(varchar(10), appointment_date, 101)) > > > > select app.patient_id, app.appointment_date, app.doctor > > from @first_appointment_per_day tmp1 > > inner join @appointment app on tmp1.patient_id = app.patient_id > >and tmp1.appointment_date = app.appointment_date > > > > ~Brad > > > > Original Message > > Subject: Semi-OT: SQL question...Select first item for each person for > > each day > > From: Judah McAuley > > Date: Tue, February 24, 2009 1:53 pm > > To: cf-talk > > > > What I need to do right now is generate a view that lists only the > > first appointment > > for each patient each day. > > > > > > > > ~| 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:319775 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Semi-OT: SQL question...Select first item for each person for each day
I like this approach. That middle query inserting into the temp table might be what I need for the view. Thanks, Judah On Tue, Feb 24, 2009 at 12:48 PM, wrote: > > These sort of queries are always kind of annoying. My approach is to to > use an intermediate temp table, group by patient and day, and find the > min() appointment date. Then join that temp table back to your > appointment table to pull out the rest of the information for that first > appointment based on the min date you found. Keep in mind, this > approach ASSUMES that one patient will never have two appointments at > the same time on the same day. > > > This runs on SQL Server 2000. It is a little crude, but should get the > point across: > > declare @appointment table > (id int identity primary key, > patient_id int, > doctor varchar(50), > appointment_date datetime ) > > declare @first_appointment_per_day table > (patient_id int, > appointment_date datetime) > > > insert into @appointment (patient_id, doctor, appointment_date) > select 1, 'McDreamy', '2009-02-24 8:00' > union all select 1, 'McSteamy', '2009-02-24 8:30' > union all select 1, 'McRib', '2009-02-25 9:00' > union all select 1, 'McSteamy', '2009-02-25 11:00' > union all select 2, 'McDreamy', '2009-02-24 9:00' > union all select 2, 'McSteamy', '2009-02-24 9:30' > union all select 2, 'McRib', '2009-02-25 7:00' > union all select 2, 'McSteamy', '2009-02-25 7:45' > > insert into @first_appointment_per_day > (patient_id, appointment_date) > (select patient_id, min(appointment_date) > from @appointment > group by patient_id, convert(varchar(10), appointment_date, 101)) > > select app.patient_id, app.appointment_date, app.doctor > from @first_appointment_per_day tmp1 > inner join @appointment app on tmp1.patient_id = app.patient_id > and tmp1.appointment_date = app.appointment_date > > ~Brad > > Original Message > Subject: Semi-OT: SQL question...Select first item for each person for > each day > From: Judah McAuley > Date: Tue, February 24, 2009 1:53 pm > To: cf-talk > > What I need to do right now is generate a view that lists only the > first appointment > for each patient each day. > > > ~| 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:319774 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Semi-OT: SQL question...Select first item for each person for each day
This is the sort of approach I was trying to think of, couldn't remember derived tables. This query only grabs the earliest appointment for each patient though not the earliest for every day. But it is a good starting point, I'll try to massage a group by date in there and see what I can come up with. Thanks, Judah On Tue, Feb 24, 2009 at 12:52 PM, Ben Conner wrote: > > Hi Judah, > > I suppose there's more than one way to do this, but this should work... > > SELECT a.* > FROM appointment a INNER JOIN > (SELECT patient_id, MIN(appointment_date) > AS appointment_date > FROM appointment > GROUP BY patient_id) b ON a.patient_id = > b.patient_id AND a.appointment_date = b.appointment_date > > --Ben > > Judah McAuley wrote: >> I'm ill and having difficulty wrapping my head around an issue I know >> I've done before, so I'm hoping that someone can help me out. >> >> I have a table that lists patient's appointments. >> >> Appointment >> >> id >> patient_id >> doctor >> appointment_date (datetime) >> >> A patient can have 0...n appointments on any given day. What I need to >> do right now is generate a view that lists only the first appointment >> for each patient each day. I'm pretty sure I need to be grouping by a >> day of year function on the appointment_date but my brain is kind of >> losing it on the rest. DB server is Sql Server 2000. >> >> Any thoughts? >> >> Thanks in advance, >> Judah >> >> > > ~| 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:319772 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Semi-OT: SQL question...Select first item for each person for each day
Maybe Select top 1 * from appointment where appointment between (date/time and date/time) order by appointment_date Ben Conner wrote: > Hi Judah, > > I suppose there's more than one way to do this, but this should work... > > SELECT a.* > FROM appointment a INNER JOIN > (SELECT patient_id, MIN(appointment_date) > AS appointment_date > FROM appointment > GROUP BY patient_id) b ON a.patient_id = > b.patient_id AND a.appointment_date = b.appointment_date > > --Ben > > Judah McAuley wrote: > >> I'm ill and having difficulty wrapping my head around an issue I know >> I've done before, so I'm hoping that someone can help me out. >> >> I have a table that lists patient's appointments. >> >> Appointment >> >> id >> patient_id >> doctor >> appointment_date (datetime) >> >> A patient can have 0...n appointments on any given day. What I need to >> do right now is generate a view that lists only the first appointment >> for each patient each day. I'm pretty sure I need to be grouping by a >> day of year function on the appointment_date but my brain is kind of >> losing it on the rest. DB server is Sql Server 2000. >> >> Any thoughts? >> >> Thanks in advance, >> Judah >> >> >> > > ~| 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:319768 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Semi-OT: SQL question...Select first item for each person for each day
Hi Judah, I suppose there's more than one way to do this, but this should work... SELECT a.* FROM appointment a INNER JOIN (SELECT patient_id, MIN(appointment_date) AS appointment_date FROM appointment GROUP BY patient_id) b ON a.patient_id = b.patient_id AND a.appointment_date = b.appointment_date --Ben Judah McAuley wrote: > I'm ill and having difficulty wrapping my head around an issue I know > I've done before, so I'm hoping that someone can help me out. > > I have a table that lists patient's appointments. > > Appointment > > id > patient_id > doctor > appointment_date (datetime) > > A patient can have 0...n appointments on any given day. What I need to > do right now is generate a view that lists only the first appointment > for each patient each day. I'm pretty sure I need to be grouping by a > day of year function on the appointment_date but my brain is kind of > losing it on the rest. DB server is Sql Server 2000. > > Any thoughts? > > Thanks in advance, > Judah > > ~| 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:319764 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Semi-OT: SQL question...Select first item for each person for each day
These sort of queries are always kind of annoying. My approach is to to use an intermediate temp table, group by patient and day, and find the min() appointment date. Then join that temp table back to your appointment table to pull out the rest of the information for that first appointment based on the min date you found. Keep in mind, this approach ASSUMES that one patient will never have two appointments at the same time on the same day. This runs on SQL Server 2000. It is a little crude, but should get the point across: declare @appointment table (id int identity primary key, patient_id int, doctor varchar(50), appointment_date datetime ) declare @first_appointment_per_day table (patient_id int, appointment_date datetime) insert into @appointment (patient_id, doctor, appointment_date) select 1, 'McDreamy', '2009-02-24 8:00' union all select 1, 'McSteamy', '2009-02-24 8:30' union all select 1, 'McRib','2009-02-25 9:00' union all select 1, 'McSteamy', '2009-02-25 11:00' union all select 2, 'McDreamy', '2009-02-24 9:00' union all select 2, 'McSteamy', '2009-02-24 9:30' union all select 2, 'McRib','2009-02-25 7:00' union all select 2, 'McSteamy', '2009-02-25 7:45' insert into @first_appointment_per_day (patient_id, appointment_date) (select patient_id, min(appointment_date) from @appointment group by patient_id, convert(varchar(10), appointment_date, 101)) select app.patient_id, app.appointment_date, app.doctor from @first_appointment_per_day tmp1 inner join @appointment app on tmp1.patient_id = app.patient_id and tmp1.appointment_date = app.appointment_date ~Brad Original Message Subject: Semi-OT: SQL question...Select first item for each person for each day From: Judah McAuley Date: Tue, February 24, 2009 1:53 pm To: cf-talk What I need to do right now is generate a view that lists only the first appointment for each patient each day. ~| 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:319762 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Semi-OT: SQL question...Select first item for each person for each day
I'm ill and having difficulty wrapping my head around an issue I know I've done before, so I'm hoping that someone can help me out. I have a table that lists patient's appointments. Appointment id patient_id doctor appointment_date (datetime) A patient can have 0...n appointments on any given day. What I need to do right now is generate a view that lists only the first appointment for each patient each day. I'm pretty sure I need to be grouping by a day of year function on the appointment_date but my brain is kind of losing it on the rest. DB server is Sql Server 2000. Any thoughts? Thanks in advance, Judah ~| 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:319755 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: (ot) SQL question...
Mark/Dave... thanks so much. That worked perfectly! -Original Message- From: Mark Kruger [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 19, 2008 10:19 AM To: CF-Talk Subject: RE: (ot) SQL question... Che, Well you could it inline... something like Select sum(t.total) as total, t.source FROM ( select count(*) as total, source from listings group by source union all select count(*) as total, source from speclistings group by source union all select count(*) as total, source from psportlistings group by source ) t Group by t.source Order by total ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311254 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: (ot) SQL question...
Che, Well you could it inline... something like Select sum(t.total) as total, t.source FROM ( select count(*) as total, source from listings group by source union all select count(*) as total, source from speclistings group by source union all select count(*) as total, source from psportlistings group by source ) t Group by t.source Order by total ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311251 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: (ot) SQL question...
Che, Try this: SELECT count(total) as sourcetotal, source FROM ( select count(*) as total, source from listings group by source union all select count(*) as total, source from speclistings group by source union all select count(*) as total, source from psportlistings group by source ) GROUP BY source ORDER BY source DESC Sincerely, Dave Phillips http://www.dave-phillips.com -Original Message- From: Che Vilnonis [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 19, 2008 9:11 AM To: CF-Talk Subject: (ot) SQL question... Hello all. I'm looking to output the total number of entries, grouped by source from 3 tables. I'd like to modify the sql below so that each source shows up only once, yet tablulates the totals from all of the tables. Any ideas. Thanks, Che. --- select count(*) as total, source from listings group by source union all select count(*) as total, source from speclistings group by source union all select count(*) as total, source from psportlistings group by source order by total desc ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311248 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
(ot) SQL question...
Hello all. I'm looking to output the total number of entries, grouped by source from 3 tables. I'd like to modify the sql below so that each source shows up only once, yet tablulates the totals from all of the tables. Any ideas. Thanks, Che. --- select count(*) as total, source from listings group by source union all select count(*) as total, source from speclistings group by source union all select count(*) as total, source from psportlistings group by source order by total desc ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311246 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: OT: SQL Question -- Order by a column's value?
I actually prefer to do this in the ORDER BY clause (keeping the ordering logic in the ORDER BY instead of in the SELECT) but the end result is the same. If you won't or can't add a sort column to the table, a CASE statement is about the only other way to do this in the query itself. On Jan 25, 2008 3:00 PM, Ian Skinner <[EMAIL PROTECTED]> wrote: > Che Vilnonis wrote: > > I was trying to do that w/o adding another column. Can it be done? > Yes, see Crow's, Charlie's or my post on using CASE to create an inline > sort column with SQL. > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297531 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: OT: SQL Question -- Order by a column's value?
Che Vilnonis wrote: > I was trying to do that w/o adding another column. Can it be done? Yes, see Crow's, Charlie's or my post on using CASE to create an inline sort column with SQL. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297467 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: OT: SQL Question -- Order by a column's value?
Although you can do it as Charlie demonstrated, using CASE, Dominic's solution is probably the best. Put the data in a table where it belongs. Then, that same data can be reused for other purposes. M!ke -Original Message- From: Dominic Watson [mailto:[EMAIL PROTECTED] Sent: Friday, January 25, 2008 12:55 PM To: CF-Talk Subject: Re: OT: SQL Question -- Order by a column's value? Do you mean put them in a predifind order based on the college, other than alphabetical? If so, and if you have a lookup table for your colleges, you will have to add a numerical column called 'Ordinal' (or something else) with which you can set their order. Then simply order by that in your SQL statement. There is no way to do it with pure SQL alone. Dominic ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297461 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: OT: SQL Question -- Order by a column's value?
There is no way to do it with pure SQL alone. Dominic Well actually you can do in pure SQL. SQL has code that can be used to create dynamic columns and values on the fly in your record set and then one can order on this set. It sort of depends on whether the desire order is permanent or flexible. I.E. sometimes one wants 'Harvard', 'Princeton' and 'Dartmouth' and another time one wants 'Princeton', 'Dartmouth' and 'Harvard'. SELECT CASE college WHEN 'Harvard' THEN 1 WHEN 'Princeton' THEN 2 WHEN 'Dartmouth' THEN 3 END AS sortCol FROM aTable ORDER BY sortCol The exact syntax can very from database to database management system. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297460 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: OT: SQL Question -- Order by a column's value?
Yes, you can do this using case statements in your order by: example: select * from viewoffers where [EMAIL PROTECTED] order by case status when 'active' then 1 when 'rejected' then 2 else 99 end Of course, this is really a kludge. The DB should be deisgned a little better, but sometimes a kludge is what you need to get the job done. Not everything can be elegant. On Jan 25, 2008 12:41 PM, Che Vilnonis <[EMAIL PROTECTED]> wrote: > Suppose I have a small set of data with a column named "Colleges". Is > there > a way to write an ORDER BY statement to say something like... > ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'??? > > Just wondering... Che > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297459 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: OT: SQL Question -- Order by a column's value?
Che Vilnonis wrote: > Suppose I have a small set of data with a column named "Colleges". Is there > a way to write an ORDER BY statement to say something like... > ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'??? > > Just wondering... Che If I understand your question correctly, you want to order a record set by first 'Harvard' record(s) then 'Princeton' record(s) and finally 'Dartmouth' record(s). If so yes, but you do most of the work in the SELECT clause. Using logic functions in the SELECT clause such as CASE you can create dynamically create a sortable column with the appropriate values such as Harvard = 1, Princeton = 2 and Dartmouth = 3. Then you just ORDER BY on this dynamic column and you the the result set you desire. HTH Because I don't have an example at my finger tips or remember the exact syntax off the top of my head to cobble one together this exact second. Ian ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297452 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: OT: SQL Question -- Order by a column's value?
something like... SELECT Colleges, CASE when Colleges = 'Harvard' THEN 1 when Colleges = 'Princeton' THEN 2 when Colleges = 'Dartmouth' THEN 3 END AS collegeOrder FROM myTable ORDER BY collegeOrder (not tested) :) On Jan 25, 2008 10:41 AM, Che Vilnonis <[EMAIL PROTECTED]> wrote: > Suppose I have a small set of data with a column named "Colleges". Is there > a way to write an ORDER BY statement to say something like... > ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'??? > > Just wondering... Che > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297453 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: OT: SQL Question -- Order by a column's value?
I was trying to do that w/o adding another column. Can it be done? -Original Message- From: Todd [mailto:[EMAIL PROTECTED] Sent: Friday, January 25, 2008 1:50 PM To: CF-Talk Subject: Re: OT: SQL Question -- Order by a column's value? Nope, add a sort_order column and sort your colleges appropriately. On Jan 25, 2008 1:41 PM, Che Vilnonis <[EMAIL PROTECTED]> wrote: > Suppose I have a small set of data with a column named "Colleges". Is > there a way to write an ORDER BY statement to say something like... > ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'??? > > Just wondering... Che ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297456 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: OT: SQL Question -- Order by a column's value?
Sorry, add sort_order column and then do an ORDER BY sort_order and set all the colleges in the appropriate sorting that you want it to be. On Jan 25, 2008 1:49 PM, Todd <[EMAIL PROTECTED]> wrote: > > Nope, add a sort_order column and sort your colleges appropriately. > > > On Jan 25, 2008 1:41 PM, Che Vilnonis <[EMAIL PROTECTED]> wrote: > > > Suppose I have a small set of data with a column named "Colleges". Is > > there > > a way to write an ORDER BY statement to say something like... > > ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges > > 'Dartmouth'??? > > > > Just wondering... Che > > http://www.web-rat.com/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297451 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: OT: SQL Question -- Order by a column's value?
Do you mean put them in a predifind order based on the college, other than alphabetical? If so, and if you have a lookup table for your colleges, you will have to add a numerical column called 'Ordinal' (or something else) with which you can set their order. Then simply order by that in your SQL statement. There is no way to do it with pure SQL alone. Dominic On 25/01/2008, Che Vilnonis <[EMAIL PROTECTED]> wrote: > > Suppose I have a small set of data with a column named "Colleges". Is > there > a way to write an ORDER BY statement to say something like... > ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'??? > > Just wondering... Che > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297454 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: OT: SQL Question -- Order by a column's value?
Nope, add a sort_order column and sort your colleges appropriately. On Jan 25, 2008 1:41 PM, Che Vilnonis <[EMAIL PROTECTED]> wrote: > Suppose I have a small set of data with a column named "Colleges". Is > there > a way to write an ORDER BY statement to say something like... > ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'??? > > Just wondering... Che ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297450 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
OT: SQL Question -- Order by a column's value?
Suppose I have a small set of data with a column named "Colleges". Is there a way to write an ORDER BY statement to say something like... ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'??? Just wondering... Che ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297449 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: OT: SQL Question
Thanks for your replies. I didn't get a chance to play with anything this afternoon due to meetings. I'll try to implement something tommorrow and fill you all in on the results. The recordsets aren't that big. About 9k records in one table and 3k in the other. ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade & see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274383 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: OT: SQL Question
Jerry Barnes wrote: > The following query is slow. I'd like to speed it up a bit. Any > suggestions would be appreciated. > > > SELECT >F.pid, >F.acrostic, >F.recid, >F.recordthread, >F.aed_onset, >F.d_form > FROM >vfrm_sae F > LEFT OUTER JOIN >v_sae_jna_mr M > ON >F.recordthread = M.i_recordThread > WHERE >(M.i_recid IS NULL) How does the execution plan look? ISTM you want a mergejoin with vfrm_sae as the driving table and v_sae_jna_mr as the inner table. If your execution plan shows differently, make sure you have clustered indexes on recordthread and i_recordThread and try again. Jochem ~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274342 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: OT: SQL Question
Has the i_recid field in the v_sae_jna_mr table been indexed? If your table is very large, this could slow your performance. Dean -- __ Dean Lawrence, CIO/Partner Internet Data Technology 888.GET.IDT1 ext. 701 * fax: 888.438.4381 http://www.idatatech.com/ Corporate Internet Development and Marketing Specialists ~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274332 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: OT: SQL Question
whats not being asked is how big is the dataset, what type of hardware, how do you know its running slow? compared to what? I mean if you have a million records on a Pentium 2 with 128m of ram, your going to have less then average response time. also are the CF server and DB server on the same LAN? I think the sql statement you have is just about as straight forward as you are going to get. how long does a straight SELECT * FROM F INNER JOIN M taking?? -Original Message- From: Kris Jones [mailto:[EMAIL PROTECTED] Sent: Monday, April 02, 2007 11:43 To: CF-Talk Subject: Re: OT: SQL Question How about something like this: SELECT F.pid, F.acrostic, F.recid, F.recordthread, F.aed_onset, F.d_form FROM vfrm_sae F WHERE NOT EXISTS (SELECT 1 FROM v_sae_jna_mr WHERE recid=F.recordthread) Not sure it'll be much faster, but it's worth a try. Cheers, Kris > The following query is slow. I'd like to speed it up a bit. Any > suggestions would be appreciated. > > SELECT >F.pid, >F.acrostic, >F.recid, >F.recordthread, >F.aed_onset, >F.d_form > FROM >vfrm_sae F > LEFT OUTER JOIN >v_sae_jna_mr M > ON >F.recordthread = M.i_recordThread > WHERE >(M.i_recid IS NULL) ~| 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:274329 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: OT: SQL Question
How about something like this: SELECT F.pid, F.acrostic, F.recid, F.recordthread, F.aed_onset, F.d_form FROM vfrm_sae F WHERE NOT EXISTS (SELECT 1 FROM v_sae_jna_mr WHERE recid=F.recordthread) Not sure it'll be much faster, but it's worth a try. Cheers, Kris > The following query is slow. I'd like to speed it up a bit. Any > suggestions would be appreciated. > > SELECT >F.pid, >F.acrostic, >F.recid, >F.recordthread, >F.aed_onset, >F.d_form > FROM >vfrm_sae F > LEFT OUTER JOIN >v_sae_jna_mr M > ON >F.recordthread = M.i_recordThread > WHERE >(M.i_recid IS NULL) ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274327 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
OT: SQL Question
The following query is slow. I'd like to speed it up a bit. Any suggestions would be appreciated. SELECT F.pid, F.acrostic, F.recid, F.recordthread, F.aed_onset, F.d_form FROM vfrm_sae F LEFT OUTER JOIN v_sae_jna_mr M ON F.recordthread = M.i_recordThread WHERE (M.i_recid IS NULL) Basically, the query is looking for items in one table that have not been marked in another table. I have done this before but don't ever remember it being this slow. I can probably figure this out on my own at some point, but the mental block I have now may take several days to work out. Thanks. J ~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274325 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
OT: sql question
I have a stored procedure that is taking 15-20 seconds to run. However, if I take the query inside the stored procedure and run it... it only takes 2-3 seconds. I've read something about parameter sniffing but not sure if it applies to my stored procedure. Here is my sp: CREATE PROC dbo.get_vendorActivityPeriod ( @periodMonth integer , @periodYear integer , @propertyID varchar(6) = NULL ) as Begin select count(*) countVoucherHeader , month(dateadd(day, A.date_posted-693596, '1 Jan 1900')) periodMonth , year(dateadd(day, A.date_posted-693596, '1 Jan 1900')) periodYear , A.vendor_code , (select top 1 address_name from apmaster where vendor_code = A.vendor_code order by address_type, pay_to_code) address_name , sum(amt_gross) as amountGross , case when @propertyID IS NULL then '' else @propertyID end propertyID , case when @propertyID IS NULL then 'All Communities' else (select distinct display_name from property.dbo.complex where display_num = @propertyID) end propertyDescription from apvohdr A where month(dateadd(day, A.date_posted-693596, '1 Jan 1900')) = @periodMonth and year(dateadd(day, A.date_posted-693596, '1 Jan 1900')) = @periodYear and A.posting_code = IsNull(@propertyID, A.posting_code) group by month(dateadd(day, A.date_posted-693596, '1 Jan 1900')) , year(dateadd(day, A.date_posted-693596, '1 Jan 1900')) , vendor_code order by year(dateadd(day, A.date_posted-693596, '1 Jan 1900')) , month(dateadd(day, A.date_posted-693596, '1 Jan 1900')) , address_name end thanks! ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268343 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: OT: SQL question
Chad Gray wrote: > > I am basically doing a query on a table of catalogs and elements > inside of the catalog. Each element has a history of status changes > (new, in progress, done etc). So each element always has a corresponding value in the history table. > SELECT c.CatalogID, c.CatalogName, e.*, es.ElementStatus, es. > ElementStatusDate, es.UserName > FROM Catalogs as c > LEFT JOIN Elements as e ON c.CatalogID = e.CatalogID > LEFT JOIN ( > SELECT MAX(ElementStatusHistoryID) as MaxESHID, ElementID > FROM ElementStatusHistory > GROUP BY ElementID > ) as maxESH ON (e.ElementID = maxESH.ElementID) > LEFT JOIN ( > SELECT ElementStatusHistoryID, ElementStatus, ElementStatusDate, > UserName > FROM ElementStatusHistory > ) as es ON (es.ElementStatusHistoryID = maxESH.MaxESHID) > WHERE c.CatalogID = 10 Try: SELECT c.CatalogID, c.CatalogName, e.*, es.ElementStatus, es. ElementStatusDate, es.UserName FROM Catalogs C LEFT JOIN ( SELECT * FROM ( SELECT MAX(ElementStatusHistoryID) as MaxESHID, ElementID FROM ElementStatusHistory GROUP BY ElementID ) tmp INNER JOIN ElementStatusHistory ON es.ElementStatusHistoryID = tmp.MaxESHID INNER JOIN Elements ON e.ElementID = tmp.ElementID ) E ON C.CatalogID = E.CatalogID Jochem ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:262232 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
OT: SQL question
I am not a MS SQL guru and this bit of SQL is about as advanced as I get. I am basically doing a query on a table of catalogs and elements inside of the catalog. Each element has a history of status changes (new, in progress, done etc). I want to get the most current status for the element from the Statushistory table so I use MAX() on the UID of the history table (see the second left join). Then I take that max UID and use it to look up the information on the Status (see the third left join). Is there a better way of writing this? If there are a lot of elements (100 or more) then the query can take a couple of seconds to perform. SELECT c.CatalogID, c.CatalogName, e.*, es.ElementStatus, es.ElementStatusDate, es.UserName FROM Catalogs as c LEFT JOIN Elements as e ON c.CatalogID = e.CatalogID LEFT JOIN ( SELECT MAX(ElementStatusHistoryID) as MaxESHID, ElementID FROM ElementStatusHistory GROUP BY ElementID ) as maxESH ON (e.ElementID = maxESH.ElementID) LEFT JOIN ( SELECT ElementStatusHistoryID, ElementStatus, ElementStatusDate, UserName FROM ElementStatusHistory ) as es ON (es.ElementStatusHistoryID = maxESH.MaxESHID) WHERE c.CatalogID = 10 ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:262221 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
OT: SQL Question
using a "between": where getdate() between tbl.fromdate and tbl.todate i'm getting a problem using the getdate, says it expects a column whereas i want to use the getdate any ideas anyone please? thanks, Jenny -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006 ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234189 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
OT: SQL Question. Compare one list to another
I've got a table with a column that holds a comma seperated list of keywords. I'm just not seeing the solution right now. I've got a keyword search where the user can put in a comma seperated list of keywords. I need the query to search the DB to see if any of the keywords match the keywords in the keywords column. I'm sure it's right in front of me -- John Wilker Writer/Web Consultant www.johnwilker.com / www.red-omega.com "The measure of success is not whether you have a tough problem to deal with, but whether it's the same problem you had last year." ~John Foster Dulles, Former US Sec. of State. ~| 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:226129 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: OT SQL question-SOLVED
danke! -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 08, 2005 2:12 PM To: CF-Talk Subject: Re: OT SQL question Eric Creese wrote: > I want to verify email addresses that are entered into one of my apps. > Unfortunately I already inherited close to 100k email address. So I want to > do the following in SQL via a stored procedure so I can write the bad > addresses out to an error table. Need to check if there is an @ sign, if the > TDL is valid from the list I have from ICANN. When I check through each like > statement but that is not going to work. Any other ideas? This can not be > done through a CF page but eventually the result will b posted to one. First create a table with all your TLDs and then: INSERT INTO bademail (personid,email) SELECT DISTINCT personid, email FROM People LEFT JOIN tlds ON (people.email LIKE '[EMAIL PROTECTED]' || tlds.tld) WHERE MbrExpireDate > '2/1/2005' AND tlds.tld IS NULL This presumes standard SQL, i.e. || is the concatenation operator and _ means exactly one character. Jochem ~| 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:193728 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: OT SQL question
Eric Creese wrote: > I want to verify email addresses that are entered into one of my apps. > Unfortunately I already inherited close to 100k email address. So I want to > do the following in SQL via a stored procedure so I can write the bad > addresses out to an error table. Need to check if there is an @ sign, if the > TDL is valid from the list I have from ICANN. When I check through each like > statement but that is not going to work. Any other ideas? This can not be > done through a CF page but eventually the result will b posted to one. First create a table with all your TLDs and then: INSERT INTO bademail (personid,email) SELECT DISTINCT personid, email FROM People LEFT JOIN tlds ON (people.email LIKE '[EMAIL PROTECTED]' || tlds.tld) WHERE MbrExpireDate > '2/1/2005' AND tlds.tld IS NULL This presumes standard SQL, i.e. || is the concatenation operator and _ means exactly one character. Jochem ~| 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:193713 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: OT SQL question
Thanks but I also need to try to test the TDLs like .com, .net, .uk... -Original Message- From: Qasim Rasheed [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 08, 2005 1:19 PM To: CF-Talk Subject: Re: OT SQL question I think you can write a UDF to validate email addresses. Here is link http://vyaskn.tripod.com/handling_email_addresses_in_sql_server.htm On Tue, 8 Feb 2005 12:55:42 -0600, Eric Creese <[EMAIL PROTECTED]> wrote: > I want to verify email addresses that are entered into one of my apps. > Unfortunately I already inherited close to 100k email address. So I want to > do the following in SQL via a stored procedure so I can write the bad > addresses out to an error table. Need to check if there is an @ sign, if the > TDL is valid from the list I have from ICANN. When I check through each like > statement but that is not going to work. Any other ideas? This can not be > done through a CF page but eventually the result will b posted to one. > > SELECT DISTINCT personid,email > FROM People > WHERE MbrExpireDate > '2/1/2005' > AND (email not like '[EMAIL PROTECTED]' > OR email NOT LIKE '%.AC' > > OR email NOT LIKE '%.ZW') > > ~| 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:193708 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: OT SQL question
I think you can write a UDF to validate email addresses. Here is link http://vyaskn.tripod.com/handling_email_addresses_in_sql_server.htm On Tue, 8 Feb 2005 12:55:42 -0600, Eric Creese <[EMAIL PROTECTED]> wrote: > I want to verify email addresses that are entered into one of my apps. > Unfortunately I already inherited close to 100k email address. So I want to > do the following in SQL via a stored procedure so I can write the bad > addresses out to an error table. Need to check if there is an @ sign, if the > TDL is valid from the list I have from ICANN. When I check through each like > statement but that is not going to work. Any other ideas? This can not be > done through a CF page but eventually the result will b posted to one. > > SELECT DISTINCT personid,email > FROM People > WHERE MbrExpireDate > '2/1/2005' > AND (email not like '[EMAIL PROTECTED]' > OR email NOT LIKE '%.AC' > > OR email NOT LIKE '%.ZW') > > ~| 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:193700 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
OT SQL question
I want to verify email addresses that are entered into one of my apps. Unfortunately I already inherited close to 100k email address. So I want to do the following in SQL via a stored procedure so I can write the bad addresses out to an error table. Need to check if there is an @ sign, if the TDL is valid from the list I have from ICANN. When I check through each like statement but that is not going to work. Any other ideas? This can not be done through a CF page but eventually the result will b posted to one. SELECT DISTINCT personid,email FROM People WHERE MbrExpireDate > '2/1/2005' AND (email not like '[EMAIL PROTECTED]' OR email NOT LIKE '%.AC' OR email NOT LIKE '%.ZW') ~| 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:193697 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: OT: Sql question
Well.. oneway to do it is to create a new relationship table.. i.e tblRelatedTrails > relationID > trailID > relatedTrailID and when doing a select for review you can do a sub select on the relatedTrails table.. and use IN() anotherway is to use the geo info of the trails.. and select.. all reviews falling in a radius. John Munyan wrote: > Yes, the crux of the question is how to handle the subset question. In the > below example a review left for SnowLake would be availble for the Snowlake Hike only. If I user left a review for Gem lake, then the review should be available for SnowLake or Gemlake since you pass by Snow lake on your way to Gem lake. Similiarly, Wright Mountain, would show reviews left for Wright Mountain, but also for Gem Lake and Snow lake since you pass by them en route to Wright Mountain. > > Thus how do you handle nesting of these elements where one is a subset of > another... > > Thanks, > > John > -- Umer Farooq Octadyne Systems +1 (519) 489-1119 voice +1 (519) 635-2795 mobile +1 (530) 326-3586 fax WEB SOLUTIONS FOR NON-PROFIT ORGANIZATION: http://www.Non-ProfitSites.biz WARNING: --- The information contained in this document and attachments is confidential and intended only for the person(s) named above. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution, or any other use of the information is strictly prohibited. If you have received this document by mistake, please notify the sender immediately and destroy this document and attachments without making any copy of any kind. ~| 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:193150 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: OT: Sql question
Yes, the crux of the question is how to handle the subset question. In the below example a review left for SnowLake would be availble for the Snowlake Hike only. If I user left a review for Gem lake, then the review should be available for SnowLake or Gemlake since you pass by Snow lake on your way to Gem lake. Similiarly, Wright Mountain, would show reviews left for Wright Mountain, but also for Gem Lake and Snow lake since you pass by them en route to Wright Mountain. Thus how do you handle nesting of these elements where one is a subset of another... Thanks, John From: Umer Farooq [mailto:[EMAIL PROTECTED] Sent: Fri 2/4/2005 12:54 PM To: CF-Talk Subject: Re: OT: Sql question John Munyan wrote: > I have a question about how a relationship would be best modeled in SQL. > Currently I have a hiking website, which hosts trail reviews. People can add > their own comments which are associated with the hike. > > For instance maybe I hiked snow lake on 12/1/05 and also 6/1/05. However, > the user review gets associated with the instance of the hike either on > 12/1/05. or 6/1/05. I want to combine these reviews so that they are > associated with both. I think a group ID could be assigned so this could be > accomplished. Hmm.. wouldn't just querying to figure out if there is another review from the same user.. for a same trail.. take care of that.. am I missing something here.. > However... > > The real problem however is how to model something like this where one hike > is a subset or superset of another. Take for instance this example. > > TrailHead---3miles--Snowlake2milesGemlake---1mile---WrightMountain. > > I would like the Snow lake reviews to be shown when either GemLake is viewed > or Wright Mountain. However, I wouldn't want reviews for Gem Lake when the > user is only intending to go to Snowlake. Thus the problem is how one would > handle this superset/subset association of reviews. > > If anyone has any thoughts about how this would be handled I would be > grateful for the advice. Simplest thing that comes to my mind is to create a another table.. which holds... review relationship between the trails.. snowLake : gemlake: snowLake,trailHead wrightMountain : snowLake trailHead : gemLake -- Umer Farooq Octadyne Systems +1 (519) 489-1119 voice +1 (519) 635-2795 mobile +1 (530) 326-3586 fax WEB SOLUTIONS FOR NON-PROFIT ORGANIZATION: http://www.Non-ProfitSites.biz WARNING: --- The information contained in this document and attachments is confidential and intended only for the person(s) named above. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution, or any other use of the information is strictly prohibited. If you have received this document by mistake, please notify the sender immediately and destroy this document and attachments without making any copy of any kind. ~| 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:193143 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: OT: Sql question
John Munyan wrote: > I have a question about how a relationship would be best modeled in SQL. > Currently I have a hiking website, which hosts trail reviews. People can add > their own comments which are associated with the hike. > > For instance maybe I hiked snow lake on 12/1/05 and also 6/1/05. However, > the user review gets associated with the instance of the hike either on > 12/1/05. or 6/1/05. I want to combine these reviews so that they are > associated with both. I think a group ID could be assigned so this could be > accomplished. Hmm.. wouldn't just querying to figure out if there is another review from the same user.. for a same trail.. take care of that.. am I missing something here.. > However... > > The real problem however is how to model something like this where one hike > is a subset or superset of another. Take for instance this example. > > TrailHead---3miles--Snowlake2milesGemlake---1mile---WrightMountain. > > I would like the Snow lake reviews to be shown when either GemLake is viewed > or Wright Mountain. However, I wouldn't want reviews for Gem Lake when the > user is only intending to go to Snowlake. Thus the problem is how one would > handle this superset/subset association of reviews. > > If anyone has any thoughts about how this would be handled I would be > grateful for the advice. Simplest thing that comes to my mind is to create a another table.. which holds... review relationship between the trails.. snowLake : gemlake: snowLake,trailHead wrightMountain : snowLake trailHead : gemLake -- Umer Farooq Octadyne Systems +1 (519) 489-1119 voice +1 (519) 635-2795 mobile +1 (530) 326-3586 fax WEB SOLUTIONS FOR NON-PROFIT ORGANIZATION: http://www.Non-ProfitSites.biz WARNING: --- The information contained in this document and attachments is confidential and intended only for the person(s) named above. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution, or any other use of the information is strictly prohibited. If you have received this document by mistake, please notify the sender immediately and destroy this document and attachments without making any copy of any kind. ~| 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:193139 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
OT: Sql question
I have a question about how a relationship would be best modeled in SQL. Currently I have a hiking website, which hosts trail reviews. People can add their own comments which are associated with the hike. For instance maybe I hiked snow lake on 12/1/05 and also 6/1/05. However, the user review gets associated with the instance of the hike either on 12/1/05. or 6/1/05. I want to combine these reviews so that they are associated with both. I think a group ID could be assigned so this could be accomplished. However... The real problem however is how to model something like this where one hike is a subset or superset of another. Take for instance this example. TrailHead---3miles--Snowlake2milesGemlake---1mile---WrightMountain. I would like the Snow lake reviews to be shown when either GemLake is viewed or Wright Mountain. However, I wouldn't want reviews for Gem Lake when the user is only intending to go to Snowlake. Thus the problem is how one would handle this superset/subset association of reviews. If anyone has any thoughts about how this would be handled I would be grateful for the advice. Thanks, John ~| 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:193130 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: ot: sql question
yeah, i figured that, i made sep. dsn's and its all good now :) thanks. tony On Wed, 22 Sep 2004 15:21:53 -0400, Qasim Rasheed <[EMAIL PROTECTED]> wrote: > As far as I know you cannot. > > > > > - Original Message - > From: Tony Weeg <[EMAIL PROTECTED]> > Date: Wed, 22 Sep 2004 09:59:03 -0400 > Subject: ot: sql question > To: CF-Talk <[EMAIL PROTECTED]> > > is it true that we cannot use go in sql statements using cfquery? > > -- > tony > > Tony Weeg > > macromedia certified cold fusion developer > email: tonyweeg [at] gmail [dot] com > blog: http://www.revolutionwebdesign.com/blog/ > cool tool: http://www.antiwrap.com [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: ot: sql question
As far as I know you cannot. - Original Message - From: Tony Weeg <[EMAIL PROTECTED]> Date: Wed, 22 Sep 2004 09:59:03 -0400 Subject: ot: sql question To: CF-Talk <[EMAIL PROTECTED]> is it true that we cannot use go in sql statements using cfquery? -- tony Tony Weeg macromedia certified cold fusion developer email: tonyweeg [at] gmail [dot] com blog: http://www.revolutionwebdesign.com/blog/ cool tool: http://www.antiwrap.com [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
ot: sql question
is it true that we cannot use go in sql statements using cfquery? -- tony Tony Weeg macromedia certified cold fusion developer email: tonyweeg [at] gmail [dot] com blog: http://www.revolutionwebdesign.com/blog/ cool tool: http://www.antiwrap.com [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
OT: SQL question. Someone help!!!
I am having a hard time finding the right syntax in SQL. Backend Database is SQL Server 7 What I am trying to acomplish. I have some duplicate records that I need to get rid of, the problem is that all these records already have a unique key, but the rest of the fields are the same. I was hoping to be able to join two fieldnames together and look for the duplicates that way.. For example... this is where I was headed... Select from billing.first_name+ billing.last_name AS combined HAVING COUNT( * ) > 1 It wouldn't work Any help would greatly be appreciated. Jeff [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
ot: sql question
hi there. say I have a database, mssql. and I know 1 column that should be unique, and actually I have duplicates in there. is there a query that I can write that will select them all, find the duplicates and remove only 1 of the duplicate records? thanks. ...tony tony weeg senior web applications architect navtrak, inc. www.navtrak.net [EMAIL PROTECTED] 410.548.2337 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: ot: sql question
thank you jochem, that makes sense, I was close, but forgot about the abs function, which I presume is for absolute number anyway, now I get this Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'ReportsInDatabase'. Server: Msg 207, Level 16, State 1, Line 1 Invalid column name 'DistinctTimes'. Server: Msg 207, Level 16, State 1, Line 1 Invalid column name 'ReportsInDatabase'. ...tony tony weeg senior web applications architect navtrak, inc. www.navtrak.net [EMAIL PROTECTED] 410.548.2337 -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 11:10 AM To: CF-Talk Subject: Re: ot: sql question Tony Weeg wrote: > how would I use HAVING to select out where the difference between > reportsInDatabase and DistinctTimes is above 15% > select r.IpAddressNumber, v.VehicleIp, > Count(r.ReportId) as ReportsInDatabase, Count(DISTINCT Time) as > DistinctTimes, v.VehicleName, c.companyName from reportsView r INNER > JOIN vehicletable v on v.IpAddressNumber = r.IpAddressNum INNER JOIN > companyTable c on v.companyIdNumber = c.companyIdNumber where > r.currentDate between '09-29-2003 00:00:00.000' and '09-29-2003 > 23:59:59.999' > Group By v.IpAddressNumber, r.IpAddressNum, v.VehicleName, > v.VehicleIp, c.companyName Order by ReportsInDatabase DESC SELECT r.IpAddressNumber, v.VehicleIp, Count(r.ReportId) as ReportsInDatabase, Count(DISTINCT Time) as DistinctTimes, v.VehicleName, c.companyName FROM reportsView r INNER JOIN vehicletable v ON v.IpAddressNumber = r.IpAddressNum INNER JOIN companyTable c ON v.companyIdNumber = c.companyIdNumber WHERE r.currentDate BETWEEN '09-29-2003 00:00:00.000' AND '09-29-2003 23:59:59.999' GROUP BY v.IpAddressNumber, r.IpAddressNum, v.VehicleName, v.VehicleIp, c.companyName HAVING Abs((ReportsInDatabase-DistinctTimes)/ReportsInDatabase) > 0.15 ORDER BY ReportsInDatabase DESC Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: ot: sql question
Tony Weeg wrote: > how would I use HAVING to select out where the difference between > reportsInDatabase and DistinctTimes is above 15% > select r.IpAddressNumber, v.VehicleIp, > Count(r.ReportId) as ReportsInDatabase, > Count(DISTINCT Time) as DistinctTimes, v.VehicleName, c.companyName > from reportsView r > INNER JOIN > vehicletable v > on v.IpAddressNumber = r.IpAddressNum > INNER JOIN > companyTable c > on v.companyIdNumber = c.companyIdNumber > where r.currentDate between '09-29-2003 00:00:00.000' and '09-29-2003 > 23:59:59.999' > Group By v.IpAddressNumber, r.IpAddressNum, v.VehicleName, > v.VehicleIp, c.companyName > Order by ReportsInDatabase DESC SELECT r.IpAddressNumber, v.VehicleIp, Count(r.ReportId) as ReportsInDatabase, Count(DISTINCT Time) as DistinctTimes, v.VehicleName, c.companyName FROM reportsView r INNER JOIN vehicletable v ON v.IpAddressNumber = r.IpAddressNum INNER JOIN companyTable c ON v.companyIdNumber = c.companyIdNumber WHERE r.currentDate BETWEEN '09-29-2003 00:00:00.000' AND '09-29-2003 23:59:59.999' GROUP BY v.IpAddressNumber, r.IpAddressNum, v.VehicleName, v.VehicleIp, c.companyName HAVING Abs((ReportsInDatabase-DistinctTimes)/ReportsInDatabase) > 0.15 ORDER BY ReportsInDatabase DESC Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
ot: sql question
how would I use HAVING to select out where the difference between reportsInDatabase and DistinctTimes is above 15% im stuck and its just not clicking this morningthanks if you can figure this out select r.IpAddressNumber, v.VehicleIp, Count(r.ReportId) as ReportsInDatabase, Count(DISTINCT Time) as DistinctTimes, v.VehicleName, c.companyName from reportsView r INNER JOIN vehicletable v on v.IpAddressNumber = r.IpAddressNum INNER JOIN companyTable c on v.companyIdNumber = c.companyIdNumber where r.currentDate between '09-29-2003 00:00:00.000' and '09-29-2003 23:59:59.999' Group By v.IpAddressNumber, r.IpAddressNum, v.VehicleName, v.VehicleIp, c.companyName Order by ReportsInDatabase DESC im stuck ...tony tony weeg senior web applications architect navtrak, inc. www.navtrak.net [EMAIL PROTECTED] 410.548.2337 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
OT: SQL Question
I know this is a little off list but I'm stumped, so if some guru out there could please give me a hand...I've got two tables, tblDates and tblEnt. tblEnt uses the intDateID field of tblDates. I am trying to pull the next 7 days where there is entertainment scheduled. tblEnt has a row for each day, but each day does not necessarily have entertainment. How can I go about this? It's easy when everyday has entertainment, but when there are one or two "dark" days it get's confusing. Any help is greatly appreciated.Cutter [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: OT: SQL Question
> I have a ColdFusion app that is dynamically managing a SQL Server 2K > database. What I am running into is the need to change a column > data type - specifically an ntext data type. Anybody got any > suggestions? > > It appears that I cannot used ALTER TABLE/COLUMN with an ntext data > type, so what other options are there? Is there a clean way to > create a new nvarchar column for example, then copy and truncate all > of the info in the ntext column, then delete the ntext column? You are correct that you cannot employ ALTER TABLE/ALTER COLUMN when the column datatype is text. One solution might be to create a new table, insert data from the old table, drop the old table and then rename the new table. I think this may also be how this is accomplished when you change the datatype of a text column from within EM although I'm not really sure about this. Stephen > Thanks for any pointers. > -- Jeff > > > ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 > Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 > Unsubscribe: > http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=2137.2057 > .4 > > Your ad could be here. Monies from ads go to support these lists and > provide more resources for the community. > http://www.fusionauthority.com/ads.cfm ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
OT: SQL Question
I have a ColdFusion app that is dynamically managing a SQL Server 2K database. What I am running into is the need to change a column data type - specifically an ntext data type. Anybody got any suggestions? It appears that I cannot used ALTER TABLE/COLUMN with an ntext data type, so what other options are there? Is there a clean way to create a new nvarchar column for example, then copy and truncate all of the info in the ntext column, then delete the ntext column? Thanks for any pointers. -- Jeff ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
OT sql question (xp_cmdshell)
Hello All, I'm trying to use xp_cmdshell to run a dts job. This is what I have: exec master..xp_cmdshell "DTSRun /S ServerName /U Username /P Password /N [Load pr_Labor Table]" but getting this error: Error string: The specified DTS Package ('Name = '[Load pr_Labor Table]'; ID.VersionID = {[not specified]}.{[not specified]}') does not exist. Any ideas? Also.. how would I add a global parameter to that call? Thanks! Tim ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: OT SQL Question
> Can someone assist me with a quick SQL statement. > > I have a table with 3 columns: A, B, C (A would be the primary key) > I need to swap the values in column B with the values in column C > and vice versa. This should work with MS SQL assuming columns b and c are the same data type: UPDATE t1 SET b=c, c=b Stephen ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: OT SQL Question
Are you talking about a one time query to change the values stored in the DB? UPDATE tableName SET tableName.B = tableName.C, tableName.C = tableName.B WHERE tableName.A = tableName.A Adam. > -Original Message- > From: Tangorre, Michael [mailto:[EMAIL PROTECTED] > Sent: Monday, March 03, 2003 8:36 AM > To: CF-Talk > Subject: OT SQL Question > > > Can someone assist me with a quick SQL statement. > > I have a table with 3 columns: A, B, C (A would be the primary key) > I need to swap the values in column B with the values in > column C and vice versa. > > TIA, > > Mike > > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: OT SQL Question
yeap actually simplier... INSERT t1 SELECT a as a , b as c ,c as b FROM t1 TEST b4 you do it WG -Original Message- From: Tangorre, Michael [mailto:[EMAIL PROTECTED] Sent: 03 March 2003 14:44 To: CF-Talk Subject: RE: OT SQL Question SQL 7 does this still apply? -Original Message- From: webguy [mailto:[EMAIL PROTECTED] Sent: Monday, March 03, 2003 9:42 AM To: CF-Talk Subject: RE: OT SQL Question SQL server ? use a format like this.. INSERT author_sales EXECUTE (' SELECT ''EXEC STRING'', authors.au_id, authors.au_lname, SUM(titles.price * sales.qty) FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id INNER JOIN titles ON titleauthor.title_id = titles.title_id INNER JOIN sales ON titles.title_id = sales.title_id WHERE authors.au_id like ''8%'' GROUP BY authors.au_id, authors.au_lname ') wg -Original Message- From: Tangorre, Michael [mailto:[EMAIL PROTECTED] Sent: 03 March 2003 14:36 To: CF-Talk Subject: OT SQL Question Can someone assist me with a quick SQL statement. I have a table with 3 columns: A, B, C (A would be the primary key) I need to swap the values in column B with the values in column C and vice versa. TIA, Mike ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: OT SQL Question
SQL 7 does this still apply? -Original Message- From: webguy [mailto:[EMAIL PROTECTED] Sent: Monday, March 03, 2003 9:42 AM To: CF-Talk Subject: RE: OT SQL Question SQL server ? use a format like this.. INSERT author_sales EXECUTE (' SELECT ''EXEC STRING'', authors.au_id, authors.au_lname, SUM(titles.price * sales.qty) FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id INNER JOIN titles ON titleauthor.title_id = titles.title_id INNER JOIN sales ON titles.title_id = sales.title_id WHERE authors.au_id like ''8%'' GROUP BY authors.au_id, authors.au_lname ') wg -Original Message- From: Tangorre, Michael [mailto:[EMAIL PROTECTED] Sent: 03 March 2003 14:36 To: CF-Talk Subject: OT SQL Question Can someone assist me with a quick SQL statement. I have a table with 3 columns: A, B, C (A would be the primary key) I need to swap the values in column B with the values in column C and vice versa. TIA, Mike ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: OT SQL Question
SQL server ? use a format like this.. INSERT author_sales EXECUTE (' SELECT ''EXEC STRING'', authors.au_id, authors.au_lname, SUM(titles.price * sales.qty) FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id INNER JOIN titles ON titleauthor.title_id = titles.title_id INNER JOIN sales ON titles.title_id = sales.title_id WHERE authors.au_id like ''8%'' GROUP BY authors.au_id, authors.au_lname ') wg -Original Message- From: Tangorre, Michael [mailto:[EMAIL PROTECTED] Sent: 03 March 2003 14:36 To: CF-Talk Subject: OT SQL Question Can someone assist me with a quick SQL statement. I have a table with 3 columns: A, B, C (A would be the primary key) I need to swap the values in column B with the values in column C and vice versa. TIA, Mike ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
OT SQL Question
Can someone assist me with a quick SQL statement. I have a table with 3 columns: A, B, C (A would be the primary key) I need to swap the values in column B with the values in column C and vice versa. TIA, Mike ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: OT: SQL Question
Thanks Everyone! I appreciate the help! Thanks, Tony -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 09, 2002 7:26 PM To: CF-Talk Subject: Re: OT: SQL Question Tony Carcieri wrote: > Hi all, > > Here's what I want to do: > UPDATE tablename > SET column = 0 > WHERE ID = ??? > > I want to specify a range of numbers (like 100-200) and increment it by 2. > So, 100, 102,104etc would only be updated and the rest wouldn't. WHERE ID BETWEEN 100 AND 200 AND MOD(ID, 2) = 0 Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
Re: OT: SQL Question
Tony Carcieri wrote: > Hi all, > > Here's what I want to do: > UPDATE tablename > SET column = 0 > WHERE ID = ??? > > I want to specify a range of numbers (like 100-200) and increment it by 2. > So, 100, 102,104etc would only be updated and the rest wouldn't. WHERE ID BETWEEN 100 AND 200 AND MOD(ID, 2) = 0 Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
OT: SQL Question
Hi all, Here's what I want to do: UPDATE tablename SET column = 0 WHERE ID = ??? I want to specify a range of numbers (like 100-200) and increment it by 2. So, 100, 102,104etc would only be updated and the rest wouldn't. Any ideas? Thanks, T ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: OT: SQL question
Off the top of my head, in SQL Server you can do it like this : SELECT PartNumber, 'FirstBitOfPartNumber' = LEFT( PartNumber, FIND( '.', PartNumber ) ) FROM blahblahblah If you wanted to just get matching rows, you can use that as a join condition, e.g. SELECT PartNumber FROM Table1 INNER JOIN Table2 ON Table2.PartNumber = LEFT( Table1.PartNumber, FIND( '.', Table1.PartNumber ) ) WHERE blah blah blah Hope that helps Alistair Alistair Davidson Application Developer www.smartgroups.com Freeserve.com PLC -Original Message- From: Alex [mailto:[EMAIL PROTECTED]] Sent: 29 July 2002 21:27 To: CF-Talk Subject: Re: OT: SQL question Great. You did not provide your database or setup. I can only assume you are working on a mainframe hitting DB2. On Mon, 29 Jul 2002, Phillip B wrote: > I need to do this and don't know where to start. > > Compare part of a part number in one table to another table. The part numbers look like this. > > 12345.123 > 12345.234 > 12345.654 > 02nt911dc.123 > 02nt911dc.054 > 02thq5 > 02thq7 > > The part before the dot is all I want to use to compare to the other table which will only contain the first part. It would be something like this. > > 12345.123 = 12345 > 12345.234 = 12345 > 12345.654 = 12345 > 02nt911dc.123 = 02nt911dc > 02nt911dc.054 = 02nt911dc > 02thq5 = 02thq5 > 02thq7 = 02thq7 > > How would I do that using just sql statements? > > Maybe when I have the server import I could have it create an extra column that can contain a trimmed version of the part number. A master number of sorts. Just a thought. > > > Thanks > > Phillip Broussard > Tracker Marine Group > 417-873-5957 > > > __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm 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: OT: SQL question
Great. You did not provide your database or setup. I can only assume you are working on a mainframe hitting DB2. On Mon, 29 Jul 2002, Phillip B wrote: > I need to do this and don't know where to start. > > Compare part of a part number in one table to another table. The part numbers look >like this. > > 12345.123 > 12345.234 > 12345.654 > 02nt911dc.123 > 02nt911dc.054 > 02thq5 > 02thq7 > > The part before the dot is all I want to use to compare to the other table which >will only contain the first part. It would be something like this. > > 12345.123 = 12345 > 12345.234 = 12345 > 12345.654 = 12345 > 02nt911dc.123 = 02nt911dc > 02nt911dc.054 = 02nt911dc > 02thq5 = 02thq5 > 02thq7 = 02thq7 > > How would I do that using just sql statements? > > Maybe when I have the server import I could have it create an extra column that can >contain a trimmed version of the part number. A master number of sorts. Just a >thought. > > > Thanks > > Phillip Broussard > Tracker Marine Group > 417-873-5957 > > > __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
OT: SQL question
I need to do this and don't know where to start. Compare part of a part number in one table to another table. The part numbers look like this. 12345.123 12345.234 12345.654 02nt911dc.123 02nt911dc.054 02thq5 02thq7 The part before the dot is all I want to use to compare to the other table which will only contain the first part. It would be something like this. 12345.123 = 12345 12345.234 = 12345 12345.654 = 12345 02nt911dc.123 = 02nt911dc 02nt911dc.054 = 02nt911dc 02thq5 = 02thq5 02thq7 = 02thq7 How would I do that using just sql statements? Maybe when I have the server import I could have it create an extra column that can contain a trimmed version of the part number. A master number of sorts. Just a thought. Thanks Phillip Broussard Tracker Marine Group 417-873-5957 __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
OT: SQL Question
Howdy all. I have a client that wants me to build him a reporting section based on the order month. I know how to do a SQL to get like the total of orders for a particular customer. SELECT Cust_ID, SUM(Order_Total) AS Total FROM Orders GROUP BY Cust_ID But, is there a way to group on the month? Something like: SELECT Cust_ID, Order_Date, SUM(Order_Total) AS Total FROM Orders GROUP BY Cust_ID, DatePart(Month) Thanks. -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ 954.721.3452 __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm 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: OT: SQL Question (Access) the solution
Well DOH!! Thanks to all of you who pointed out to me the blindingly obvious - all I had to do was join the tables. Jeez, I knew that .. I had just momentarily forgotten is all. Bob's answer was one of many who pointed out what I should obviously have known and recalled. The only extra piece that was needed was to allow for the fact that there were several answers for some questions, so I had to add Distinct to the SQL like so: SELECT DISTINCT HNTQuestion.QuestionID, HNTQuestion.Category, HNTQuestion.QuestionShort, HNTCategories.HNTCategoryID, HNTCategories.HNTCategoryName FROM HNTQuestion, HNTCategories, tblAnswers WHERE HNTQuestion.Category = HNTCategories.HNTCategoryID AND HNTQuestion.QuestionID = tblAnswers.ANSQuestionID ORDER BY HNTQuestion.Category, HNTQuestion.QuestionShort Thanks a lot for all your patience and help folks. Cheers, Mike Kear Windsor, NSW, Australia AFP WebWorks -Original Message- From: Bob Silverberg [[EMAIL PROTECTED]] Just add the tblAnswers table to your join, like so: SELECT HNTQuestion.QuestionID, HNTQuestion.Category, HNTQuestion.QuestionShort, HNTCategories.HNTCategoryID, HNTCategories.HNTCategoryName FROM HNTQuestion, HNTCategories, tblAnswers WHERE HNTQuestion.Category = HNTCategories.HNTCategoryID AND HNTQuestion.QuestionID = tblAnswers.ANSQuestionID ORDER BY HNTQuestion.Category, HNTQuestion.QuestionShort Bob -Original Message- From: Michael Kear [mailto:[EMAIL PROTECTED]] Sent: March 28, 2001 5:33 PM To: CF-Talk Subject: OT: SQL Question (Access) I'm sorry if this is off-topic, but I'm hoping for some help from people who know more about SQL than I do ... I have a hints'n'tips section on one of my sites, and it has 3 tables - tblCategories, tblQuestions and tblAnswers. I want to have an index page that lists the questions in their categories, so you click on the question and a window opens with all the answers to that question. Here's the bit I am having trouble with .. A few questions don't have any answers yet - for example some haven't been loaded up yet. How do I get SQL to retrieve only the questions that have answers in the Answers table, leaving out any questions that have no answers? Here's the SQL I'm using now, that returns all the questions (if it's relevant here, the database is Access2000): SELECT HNTQuestion.QuestionID, HNTQuestion.Category, HNTQuestion.QuestionShort, HNTCategories.HNTCategoryID, HNTCategories.HNTCategoryName FROM HNTQuestion, HNTCategories WHERE HNTQuestion.Category = HNTCategories.HNTCategoryID ORDER BY HNTQuestion.Category, HNTQuestion.QuestionShort An answer is linked to its associated question with a field ANSQuestionID in the table tblAnswers which has the same value as the field HNTQuestion.QuestionID. Can anyone help me please? Cheers, Mike Kear Windsor, NSW, Australia AFP Webworks. ~~ 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 Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
OT: SQL Question (Access)
I'm sorry if this is off-topic, but I'm hoping for some help from people who know more about SQL than I do ... I have a hints'n'tips section on one of my sites, and it has 3 tables - tblCategories, tblQuestions and tblAnswers. I want to have an index page that lists the questions in their categories, so you click on the question and a window opens with all the answers to that question. Here's the bit I am having trouble with .. A few questions don't have any answers yet - for example some haven't been loaded up yet. How do I get SQL to retrieve only the questions that have answers in the Answers table, leaving out any questions that have no answers? Here's the SQL I'm using now, that returns all the questions (if it's relevant here, the database is Access2000): SELECT HNTQuestion.QuestionID, HNTQuestion.Category, HNTQuestion.QuestionShort, HNTCategories.HNTCategoryID, HNTCategories.HNTCategoryName FROM HNTQuestion, HNTCategories WHERE HNTQuestion.Category = HNTCategories.HNTCategoryID ORDER BY HNTQuestion.Category, HNTQuestion.QuestionShort An answer is linked to its associated question with a field ANSQuestionID in the table tblAnswers which has the same value as the field HNTQuestion.QuestionID. Can anyone help me please? Cheers, Mike Kear Windsor, NSW, Australia AFP Webworks. ~~ 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: OT: SQL Question
On 1/16/01, Gieseman, Athelene penned: > > INSERT X_Invoices (Vendor, Inv_GL, Inv_City, Inv_Dept, Inv_Date, >Inv_No, Inv_Desc, ApprovedDate, Notes, Inv_Amount) > values ('#Vendor#', '#Inv_GL#', '#Inv_City#', '#Inv_Dept#', >'#Inv_Date#', '#Inv_No#', '#Inv_Desc#', '#ApprovedDate#', '#Notes#', >#Inv_Amount#) > > >I'm sure I'm not seeing something obvious. But it looks to me like there >are 10 items for each of the values and table columns. Can anyone see what >I'm not? One thing to look for is columns that don't have single quotes around them. If you input an #Inv_Amount# with a comma in it, like 1,000.00, it will think that is 2 fields, because of the comma. Try using: #rereplace(Inv_Amount, "[^0-9\.]+", "", "ALL")# Or even just: #replace(Inv_Amount, ",", "", "ALL")# -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ 954.721.3452 ~~ 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: OT: SQL Question
That was it! Thank you! -Original Message- From: Bud [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 16, 2001 4:18 PM To: CF-Talk Subject: Re: OT: SQL Question On 1/16/01, Gieseman, Athelene penned: > > INSERT X_Invoices (Vendor, Inv_GL, Inv_City, Inv_Dept, Inv_Date, >Inv_No, Inv_Desc, ApprovedDate, Notes, Inv_Amount) > values ('#Vendor#', '#Inv_GL#', '#Inv_City#', '#Inv_Dept#', >'#Inv_Date#', '#Inv_No#', '#Inv_Desc#', '#ApprovedDate#', '#Notes#', >#Inv_Amount#) > > >I'm sure I'm not seeing something obvious. But it looks to me like there >are 10 items for each of the values and table columns. Can anyone see what >I'm not? One thing to look for is columns that don't have single quotes around them. If you input an #Inv_Amount# with a comma in it, like 1,000.00, it will think that is 2 fields, because of the comma. Try using: #rereplace(Inv_Amount, "[^0-9\.]+", "", "ALL")# Or even just: #replace(Inv_Amount, ",", "", "ALL")# -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ 954.721.3452 ~~ 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: OT: SQL Question
On 1/16/01, Gieseman, Athelene penned: > > INSERT X_Invoices (Vendor, Inv_GL, Inv_City, Inv_Dept, Inv_Date, >Inv_No, Inv_Desc, ApprovedDate, Notes, Inv_Amount) > values ('#Vendor#', '#Inv_GL#', '#Inv_City#', '#Inv_Dept#', >'#Inv_Date#', '#Inv_No#', '#Inv_Desc#', '#ApprovedDate#', '#Notes#', >#Inv_Amount#) > > >I'm sure I'm not seeing something obvious. But it looks to me like there >are 10 items for each of the values and table columns. Can anyone see what >I'm not? One thing to look for is columns that don't have single quotes around them. If you input an #Inv_Amount# with a comma in it, like 1,000.00, it will think that is 2 fields, because of the comma. Try using: #rereplace(Inv_Amount, "[^0-9\.]+", "", "ALL")# Or even just: #replace(Inv_Amount, ",", "", "ALL")# -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ 954.721.3452 ~~ 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 Question
I am getting the following SQL error: Microsoft][ODBC SQL Server Driver][SQL Server]There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement. This doesn't happen all the time. I can't seem to find what's unique about the records that do insert from those that don't. The insert statement is as follows: INSERT X_Invoices (Vendor, Inv_GL, Inv_City, Inv_Dept, Inv_Date, Inv_No, Inv_Desc, ApprovedDate, Notes, Inv_Amount) values ('#Vendor#', '#Inv_GL#', '#Inv_City#', '#Inv_Dept#', '#Inv_Date#', '#Inv_No#', '#Inv_Desc#', '#ApprovedDate#', '#Notes#', #Inv_Amount#) I'm sure I'm not seeing something obvious. But it looks to me like there are 10 items for each of the values and table columns. Can anyone see what I'm not? Athelene Gieseman ~~ 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 question
Whew . . . I'm still in one piece after that wind last night . . . What's the best way to query a column in a table that has a comma delimited list? For example, in the query below: If B_xRef is a comma delimited list, what's the most effecient way to find all the Table_b rows that contain the value of A_xRef (which is a single value) SELECT A.A_xRef, B.B_xRef FROM Table_a A, Table_b B WHERE A.A_xRef IN B.B_xRef (or something to this effect?) Thanks, Dave = "What we need is a list of specific unknown problems we will encounter" David Hannum Web Analyst/Programmer Ohio University [EMAIL PROTECTED] (740) 597-2524 ~~ 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 Question
It's been a very long day (a very long year in fact :), and I'm severely stumped on something I guess is dead easy. Using CF 4.5.1 and SQL Server, I have a list of names like so (fields firstname and lastname in a db): John Smith Jean Paul Gaultier Jane Doe I construct links like this: who.cfm/JohnSmith who.cfm/JeanPaulGaultier who.cfm/JaneDoe The last bit is caught in a variable "who", and then I'd like to do a query like this SELECT * FROM tblNames WHERE replace(firstname+lastname," ","")='#who#' SQL server chokes on this. Replace() is a valid TSQL function, I guess I need something like DE() in CF... Any ideas? Michel -- Michel Vuijlsteke Managing Director Netpoint NV ~~ 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 question
Hi List, I am Learning SQL by trial and error. So far so good -- Until today. I have a feedback form that captures comments. Currently I am using the TEXT datatype in SQL. Today, a user entered a comment that was about 1500 characters long and the dB truncated the msg. What datatype would be best for this situation? Thank You, Cori Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: ot: sql question
This hurts to look at. Is that the entire table? Or are there additional fields that make those rows unique? If it's the entire table, why do you have identical rows, and why do you want to retain them? If there's more to the table, extend your 'where' clause to identify the unique row that you want to delete. I suppose you could delete all of these rows and then reinsert one, with a CFTRANSACTION around the delete and insert. Also, if the id's are numeric, omit the quotes. -David On Wed, 27 Sep 2000 13:58:57 -0500 Gavin Myers <[EMAIL PROTECTED]> writes: > here's what i'm doing > > delete from class_registration > where users_id='123123' > and class_id='4242424' > > here's what the table looks like > > name users_idclass_id > gavin 123123 4242424 > gavin 123123 4242424 > gavin 123123 4242424 > gavin 123123 4242424 > > what i want to do is delete only 1 of those rows > > suggestions? > > thanks, > Gavin YOU'RE PAYING TOO MUCH FOR THE INTERNET! Juno now offers FREE Internet Access! Try it today - there's no risk! For your FREE software, visit: http://dl.www.juno.com/get/tagj. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ 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: ot: sql question
Oh dear. Non-unique rows. Hmmm... Unless its possible to do this with cursors (I know nothing about them), I'd say you'd have to delete, then re-insert the data, or better yet add another column to make it unique... ;) David Cummins Gavin Myers wrote: > > here's what i'm doing > > delete from class_registration > where users_id='123123' > and class_id='4242424' > > here's what the table looks like > > nameusers_idclass_id > gavin 123123 4242424 > gavin 123123 4242424 > gavin 123123 4242424 > gavin 123123 4242424 > > what i want to do is delete only 1 of those rows > > suggestions? > > thanks, > Gavin > -- > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > 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.mail-archive.com/cf-talk@houseoffusion.com/ 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.
ot: sql question
here's what i'm doing delete from class_registration where users_id='123123' and class_id='4242424' here's what the table looks like nameusers_idclass_id gavin 123123 4242424 gavin 123123 4242424 gavin 123123 4242424 gavin 123123 4242424 what i want to do is delete only 1 of those rows suggestions? thanks, Gavin -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ 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: OT - SQL question
Which of Fred's OrderNums do you want? If you don't care, then use SELECT DISTINCT Name FROM table If you want the Minimum, try SELECT Name, MIN (OrderNum) FROM table GROUP BY Name > -Original Message- > From: PC [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, May 03, 2000 10:45 PM > To: [EMAIL PROTECTED] > Subject: OT - SQL question > > > Hey ... > > How do you do a query where all rows returned except in the case of > duplicates, based on a name column, in which only the first > is returned. > > So the query run against the following rows: > > NameOrderNum > Fred21 > Beth36 > Fred76 > > Would return : > NameOrderNum > Fred 21 > Beth 36 > > Much thanks!! > > > -- > > Archives: http://www.eGroups.com/list/cf-talk > To Unsubscribe visit > http://www.houseoffusion.com/index.cfm?sidebar=lists&body=list s/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.
RE: OT - SQL question
SELECT DISTINCT Name, OrderNum FROM tablename ___ Pete Freitag CFDEV.COM Cold Fusion Developer Resources http://www.cfdev.com/ -Original Message- From: PC [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 03, 2000 4:45 PM To: [EMAIL PROTECTED] Subject: OT - SQL question Hey ... How do you do a query where all rows returned except in the case of duplicates, based on a name column, in which only the first is returned. So the query run against the following rows: NameOrderNum Fred21 Beth36 Fred76 Would return : NameOrderNum Fred 21 Beth 36 Much thanks!! -- 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.
OT - SQL question
Hey ... How do you do a query where all rows returned except in the case of duplicates, based on a name column, in which only the first is returned. So the query run against the following rows: NameOrderNum Fred21 Beth36 Fred76 Would return : NameOrderNum Fred 21 Beth 36 Much thanks!! -- 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: slightly OT - SQL question
Thanks for the excellent information. I am off to do battle with M$ SQL... Nick [EMAIL PROTECTED] - Original Message - From: "Darryl Davidson" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, March 24, 2000 11:13 AM Subject: Re: slightly OT - SQL question > If you're talking about moving a database, a coworker ran into this > recently. Here's what he found out, verbatim: (in other words, your mileage > may definitely vary, no I haven't tried it, etc.) > > ---snip > - From EM (Enterprise Manager), perform a backup of the database to a file > (NWT.BAK). When specifying a file name put the '.BAK' on the end of it. (The > resultant file can be compressed for permanent storage). > > - Copy the backup file (NWT.BAK) to some location on the destination server. > The typical place for backup files is in the "BACKUP" sub-directory of the > SQL Server data directory. > > Here is where the problem comes in. If the destination server does not have > the same DATA directory location and structure as the source server, you > will receive an error when trying to perform a restore from within EM. The > error indicates that we should use the 'MOVE' option. If you receive this > error, the only way to restore the database is to use the more versatile > commands found in TRANSACT SQL. > > - Start a SQL Server Query Analyzer window attached to the destination > server as dbo equivalent and issue the following commands. > USE MASTER (press F5 to execute the command) > > RESTORE FILELISTONLY > FROM DISK = 'F:\NWT.BAK' (F5) > > (Condensed Results) > > LogicalName > - --- > > Northwind D:\MSSQL7DATA\DATA\northwnd.mdf > Northwind_log D:\MSSQL7DATA\DATA\northwnd.ldf > > Examine the list of files in the backup, pay particular attention to the > 'Logical Name' and Physical Name fields. Then, issue the restore command: > > RESTORE DATABASE NWT >FROM DISK = 'F:\NWT.BAK' >WITH RECOVERY, > MOVE 'Northwind' to 'NWT.MDF', >MOVE 'Northwind_log' to 'NWT.LDF'(don't forget the F5) > > There are many more options and this assumes that we wanted a copy of > Northwind with different logical and file names, and that there were > conflicts with existing files. > > I found this information in the online 'SQL Server Books Online' help file > in the "Transact-SQL | Administering SQL Server | Backing Up and Restoring > Databases" section. > > Hope that you find this useful. > > Nick Call wrote: > > Help SQL Server 7.0 gurus > > > > I need to attach a SQL database to my SQL server. It was created on another > > machine, and you know how MS SQL hates foreign DB's. It was suggested that > > I try "attach_db", but all I get is the tables, not the data. I am NOT a > > SQL guru. Please help. I will be working all weekend until I figure this > > out. > > > Darryl Davidson [EMAIL PROTECTED] > SCIENTECH, Inc. > Idaho Falls, Idaho, USA 83402 > > -- > 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.
RE: slightly OT - SQL question
>>Here is where the problem comes in. If the destination server does not have the same DATA directory location and structure as the source server, you will receive an error when trying to perform a restore from within EM. The error indicates that we should use the 'MOVE' option. If you receive this error, the only way to restore the database is to use the more versatile commands found in TRANSACT SQL.<< This is not true. I restore databases from machine to machine frequently, and have run into situations where the data directory is different many times. In the options tab in the restor dialog, you can re-direct the backup location. Michael J. Sheldon Internet Applications Developer Phone: 480.699.1084 http://www.desertraven.com/ PGP Key Available on Request -- 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: slightly OT - SQL question
If you're talking about moving a database, a coworker ran into this recently. Here's what he found out, verbatim: (in other words, your mileage may definitely vary, no I haven't tried it, etc.) ---snip - From EM (Enterprise Manager), perform a backup of the database to a file (NWT.BAK). When specifying a file name put the '.BAK' on the end of it. (The resultant file can be compressed for permanent storage). - Copy the backup file (NWT.BAK) to some location on the destination server. The typical place for backup files is in the "BACKUP" sub-directory of the SQL Server data directory. Here is where the problem comes in. If the destination server does not have the same DATA directory location and structure as the source server, you will receive an error when trying to perform a restore from within EM. The error indicates that we should use the 'MOVE' option. If you receive this error, the only way to restore the database is to use the more versatile commands found in TRANSACT SQL. - Start a SQL Server Query Analyzer window attached to the destination server as dbo equivalent and issue the following commands. USE MASTER (press F5 to execute the command) RESTORE FILELISTONLY FROM DISK = 'F:\NWT.BAK' (F5) (Condensed Results) LogicalName - --- Northwind D:\MSSQL7DATA\DATA\northwnd.mdf Northwind_log D:\MSSQL7DATA\DATA\northwnd.ldf Examine the list of files in the backup, pay particular attention to the 'Logical Name' and Physical Name fields. Then, issue the restore command: RESTORE DATABASE NWT FROM DISK = 'F:\NWT.BAK' WITH RECOVERY, MOVE 'Northwind' to 'NWT.MDF', MOVE 'Northwind_log' to 'NWT.LDF'(don't forget the F5) There are many more options and this assumes that we wanted a copy of Northwind with different logical and file names, and that there were conflicts with existing files. I found this information in the online 'SQL Server Books Online' help file in the "Transact-SQL | Administering SQL Server | Backing Up and Restoring Databases" section. Hope that you find this useful. Nick Call wrote: > Help SQL Server 7.0 gurus > > I need to attach a SQL database to my SQL server. It was created on another > machine, and you know how MS SQL hates foreign DB's. It was suggested that > I try "attach_db", but all I get is the tables, not the data. I am NOT a > SQL guru. Please help. I will be working all weekend until I figure this > out. Darryl Davidson [EMAIL PROTECTED] SCIENTECH, Inc. Idaho Falls, Idaho, USA 83402 -- 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.
slightly OT - SQL question
Help SQL Server 7.0 gurus I need to attach a SQL database to my SQL server. It was created on another machine, and you know how MS SQL hates foreign DB's. It was suggested that I try "attach_db", but all I get is the tables, not the data. I am NOT a SQL guru. Please help. I will be working all weekend until I figure this out. Thanks in advance. Nick -- 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.