Grouping By Partial String

Jun 12, 2007

Hello All,



I am looking for an expression for a group in a matric. I am trying to figure out how to group by the a certain amount of letters in a string. For example if I have the followong fields I am grouping...



Bob001

Bob

Robert005

Doug053

Doug100

Douglas

Barney001

Frank



I want to group it up as...



Bob

Doug

Barney

Frank



And then be able to summarize the results in the matrix.



Thanks in advance for any help

-Clint

View 13 Replies


ADVERTISEMENT

Pull In Partial String

Dec 13, 2007

Could someone please help me? I am trying to pull in a partial string (the last six characters of the field, to be exact).

This is an example of my code:

select *
into #temp_2
from #temp_1 a, Server2.DBa.dbo.table2 r
where r.field1r = a.field1a and
r.field3r = a.field3a (field3a is where I need just the last 6 characters)

To be more specific:
r.field3r looks like 000884
a.field3a looks like 17445000884
So- I just want to pull in the 000884 off of a.field3a

View 2 Replies View Related

How Do You Test For Partial String In SQL2000?

Apr 23, 2007

Is it possible using Sql2000 to compare the first four characters of a parameter?
WHERE (tblLinkInfo.CG_ID = @BCG_ID) AND ( 'first 4 characters' of Location_Zip.Zip = 'first 4 characters' of @ZipCodeIn)

View 2 Replies View Related

Query To Update Partial String

Mar 21, 2000

Sorry, I realize that this is probably a pretty simple question for you, but I am in a quick time bind and would really appreciate it if someone could help me with this update that I'm trying to run.

Let's say you have a table (call it testtable) that contains the column "text".

Now in column text you want to replace all instantiations of the phrase "in the US" with "to the United States", how would you structure your update query to perform this change (Please note that this phrase could be embedded in the middle of a larger sentence like 'Send a package in the US'. You'd want to change this instance to 'Send a package to the United States')

Thanks in advance, I really appreciate it guys.

View 3 Replies View Related

Removing Partial String From Column

Oct 11, 2005

how do i update a table which has like two strings in 1 column like

blog, joe ?

i want to strip the joe into a new field and the blog into another field

update Agency
set firstname= substring(firstname,charindex(' ',firstname)+1 ,len(firstname))

i managed to strip the first name which is the string at the back but not the last name which is the string at the front

View 5 Replies View Related

Repalce Partial String In Table

Apr 21, 2004

Hi! I need to replace a string in a SQL table with a new path.

Essentially, I want to find and replace the share path while retaining the rest of the path past that point.

I tried using Update (on a test table of course) but it sompley find and replaces with no regards to the rest of the path.

Any help is greatly appreciated!

JJ

View 8 Replies View Related

Auto Search Partial Text String?

Oct 7, 2014

Daily, I have to map thoursands of items into its correct category. What I have to do is Filter item by the ClSID and SCLISD, then look into the Label for where the item should be. For example from the attachment,

CLSID = 1 and SCLSID = 23 - if in the label has the keyword "notebook", its NEW_CAT should be 14 and its NEW_CATNAME is LAPTOPS;

CLSID = 1 and SCLSID = 23 - if in the Label contains Tablet PC, its NEW_CAT name should be 2474 and its NEW_CATNAME is PC LAPTOPS.

CLSID = 3 and SCLSID = 54 - if in the Label contains Watch, its NEW_CAT name should be A6052 and its New_CATNAME is SMART WATCH.

Basically, the work flow is looking into the CLSID, then SCLSID,then, Label and then assign a correct NEW_CAT and NEW_CATNAME of the item.

I have written a code but I can't get it look into specific CLSID and SCLSID.

For example, i have when upper(Label) like upper ('%Tablet%PC%') then '2474 PC Laptops'

I couldn't attach either the file or image.

View 1 Replies View Related

Exclude Data From Partial String In Predicate

May 8, 2015

I have to write one query to exclude data from partial string in the predicate.

Select * from table where col NOT in ('%abc%,%xyz%).

So the data where col value doesn't contain the ABC and xyz string value only should be selected. I am also getting xyz data in the result.

View 4 Replies View Related

How To Return Partial String Using CharIndex And Left In Same Select

Jun 22, 2015

Select
left(
[Description],(charindex(';',[Description],1)-1))
from xxxx

Example of Description contains

Ankle Supports; Color=Black; Size=S

So I want the left side up to and NOT including the semi colon.

View 14 Replies View Related

Conditionally Grouping Different String Values Together

Jan 29, 2008

 I have a sql server table column that can contain the following possible values. 1. 766/IT 2. 777/HR3.  890/EG4.  012/AS5.  Trainee6.  Contractor 7.  Others I want to write an SQL query grouped by this column, Trainee should be one group, Contractor should be another different group, Others should be another different group and then  every thing else ( 766/IT ,777/HR ,890/EG, 012/AS) should be grouped together as one group. Think of it in terms of a pie chart with those groups. 

View 4 Replies View Related

Grouping Sorting String And Numerical Fields

Feb 6, 2007

I've got a report built and I'm trying to figure out how sorting and grouping works. I can group the report by Patient, Albumin and it groups as I would expect.

Patient Date Albumin
Adams, John 01/28/2007 4.1
Adams, John 12/30/2007 3.9
Adams, John 01/15/2007 3.2
Barker, Mark 01/18/2007 4.3
Barker, Mark 01/22/2007 4.1
Barker, Mark 01/05/2007 3.9

However, when I try to group by Albumin, Patient, it just sorts by Albumin.
Patient Date Albumin
Barker, Mark 01/18/2007 4.3
Adams, John 01/28/2007 4.1
Barker, Mark 01/22/2007 4.1
Adams, John 12/30/2007 3.9
Barker, Mark 01/05/2007 3.9
Adams, John 01/15/2007 3.2

What I'm looking for is this:
Patient Date Albumin
Barker, Mark 01/18/2007 4.3
Barker, Mark 01/22/2007 4.1
Barker, Mark 01/05/2007 3.9
Adams, John 01/28/2007 4.1
Adams, John 12/30/2007 3.9
Adams, John 01/15/2007 3.2

Is this something that can be done with grouping and sorting?

Thanks,
Chad

View 1 Replies View Related

Query Or Grouping Problem (some Kind Of Parallel Grouping?)

Nov 26, 2007

I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.

I'm trying to get information like this in to a report:

WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Detail #etc
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Parts #etc

I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?

There are 4 tables:

Work Order Header
Work Order Line
Work Order Line Details
Work Order Line Requisitions

The Header has a unique PK.
The Line uses the Header and a Line # as foreign keys that together are unique.
The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:

WO WOL WOLR WOLD
226952 10000 10000 10000
226952 10000 10000 20000
226952 10000 10000 30000
226952 10000 10000 40000
226952 10000 20000 10000
226952 10000 20000 20000
226952 10000 20000 30000
226952 10000 20000 40000
399999 10000 NULL 10000
375654 10000 10000 NULL
etc


Hierarchy:
WO > WOL > WOLD
WO > WOL > WOLR

It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:

SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No,
[Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No
FROM [Work Order Header] LEFT OUTER JOIN
[Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN
[Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN
[Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]

View 1 Replies View Related

Getting Partial Recordset.

Apr 19, 1999

I'm an SQL novice, but I know this must be a common problem.

I'm trying to select a recordset (using ASP), but I know I only want part of the recordset, and am not sure how to limit it ahead of time.

For example, the query will return about 500 rows, but I know I only want to use a small section of these records.
I want to give the user the ability to navigate through small sections of these 500 rows without having to get all rows all the time.
I know ahead of time which rows to get, but have no idea how to limit the recordset before I get it (there is no fields in the database to help).

This is what I'm doing now. "select * from xyz where id=xxx order by date desc;" I know I only want the first 10, or 10-20, or 400-410.
The way I'm doing it now, I'm getting the whole recordset each time, doing a "rs.move x" where x is where I want to start.
This is really a waste of network traffic and memory since my SQL server is on a different machine as the web server running ASP.

How do I do this?

Please email me if you could at pmt@vantagenet.com

View 1 Replies View Related

Partial Search

Sep 26, 2004

Hello - I was wondering if anyone knew how to do this -

I have a database with a field for Id, LName, GName, DOB. In the LName field, some of the names have * placed after the names. Is there a way I can search for the entries in LName with the * in the record?

Thank you!

Liz

View 3 Replies View Related

Partial Trust

Sep 30, 2007

Hi Guys,

I have started a project using Linq for sql and SSCE. Everything goes well until I realize that SSCE can only run in full trust. Linq for sql is planned to support partial trust scenario. Any plans for such a support in Sql Compact Edition?

Dany

View 1 Replies View Related

How Can I Get My Code To Look For Partial Payments

Apr 12, 2008

The following code accepts a couple of parameters and creates a temp table to hold unpaid schedule rows (like invoices). Then is takes the payment amount passed and starts paying the oldest ones first, until it runs out of money or pays a partial. When it pays it inserts a row in the applieds table with the schedule_ID, Receipt_ID (payment id), applied amount, and applied date.
What I need help with is: Say there is a partial payment from a previous payment, I need to pay that one, but only the unpaid part. Then continue to pay other schedule rows...
I appreciate any help,
@PaymentAmount money,@PledgeID Int,@Receipt_ID IntAS-- Get unpaid reminder rows for the passed in pledge id and put them into the temp tableDeclare @temp_oldest Table(PledgeSchedule_ID int,ReminderDueDate datetime,AmountDue money)INSERT INTO @temp_oldest SELECT PledgeSchedule_ID, PledgeDueDate, AmountDueFROM tblPledgeReminderScheduleWHERE (dbo.tblPledgeReminderSchedule.Pledge_ID=@PledgeID) AND (dbo.tblPledgeReminderSchedule.ReceivedDate IS NULL) -- AND (dbo.tblPledgeReminderSchedule.PledgeDueDate < GETDATE())WHILE((SELECT Count(*) FROM @temp_oldest)>0)BEGIN-- If the payment is greater or equal to the amount due for the current row, do thisIF(@PaymentAmount >= (SELECT Top 1 AmountDue FROM @temp_oldest))BEGIN-- Update the reminder row with todays dateUPDATE tblPledgeReminderSchedule SET ReceivedDate = GETDATE()WHERE PledgeSchedule_ID = (SELECT Top 1 PledgeSchedule_ID FROM @temp_oldest)-- Insert a row to track applied payment and reminder row associatedINSERT INTO tblPledgeReminderSchedule_Applieds (PledgeSchedule_ID,Receipt_ID,Applied_Amount,Applied_Date)(SELECT Top 1 PledgeSchedule_ID,@Receipt_ID,AmountDue,GETDATE() FROM @temp_oldestWHERE PledgeSchedule_ID = (SELECT Top 1 PledgeSchedule_ID FROM @temp_oldest))-- Subtract the amountdue from the paymentamount and reset itSET @PaymentAmount = (@PaymentAmount - (SELECT Top 1 AmountDue FROM @temp_oldest))ENDELSEIF(@PaymentAmount < (SELECT Top 1 AmountDue FROM @temp_oldest))BEGIN -- Insert a row to track applied PARTIAL payment and reminder row associatedINSERT INTO tblPledgeReminderSchedule_Applieds (PledgeSchedule_ID,Receipt_ID,Applied_Amount,Applied_Date)(SELECT Top 1 PledgeSchedule_ID,@Receipt_ID,@PaymentAmount,GETDATE() FROM @temp_oldestWHERE PledgeSchedule_ID = (SELECT Top 1 PledgeSchedule_ID FROM @temp_oldest))BREAKENDELSEIF @PaymentAmount = 0-- Delete all rows from temp tableDELETE FROM @temp_oldestELSE-- Delete only the current row from the temo tableprint @PaymentAmountDELETE FROM @temp_oldest WHERE PledgeSchedule_ID = (SELECT Top 1 PledgeSchedule_ID FROM @temp_oldest)END 

View 1 Replies View Related

Partial Field Search

May 6, 2005

I was just wondering if anyone could tell me how to do a search for a partial data match. Say one data field is 123, 234, 345, 456 and another is 111, 222, 333, 444 and another is 555, 666, 777, 888 and I want to search for the unique number 234 but not the whole number 123, 234, 345, 456 ... is there any way to do that or does every search have to be exactly like the data in the field?
Thanks for any help.
Dennis

View 4 Replies View Related

Partial Replication Problem

Jun 15, 2000

In our database we have the concept of 'Companies' each company has an entry in a tblCompanyControls using a field lCompanyNumber to uniquely identify it.

Company specific data is then grouped within additional tables using this
lCompanyNumber.
Thus all the departments for a particular company (eg 4) exist in a table
tblDepartments with lCompanyNumber =4. The same applies for pensions, pay
elements, employees and so on.

Each employee has various attributes stored in several tables, thus the
main data is stored in tblEmployees with an lCompanyNumber to illustrate
the company they belong to. And a system generated lUniqueID that is the 1
to many relationship to tables such as tblEmployeePayElements,
tblEmployeePensionSchemes.

These in turn have a primary key that establishes a 1 to many with the
period data for pay elements etc.

What we want to do is replicate only the data for a specific company, for
tblEmployees, tblDepartments this looks straightforward as i just set a
filter like 'tblEmployees.lCompanyNumber = 4'. My problem is how do I
replicate just the tblEmployeePayElements for those employees that are in
the specific company as the table does not contain the lCompanyNumber. Can
you replicate a view? I an quite ignorant of replication and would really
appreciate some pointers. Note DRI is not in use.

View 1 Replies View Related

Partial Updates By Cursor

Sep 18, 1998

I am are running SQLSERVER SP4 on WINNT SP3.

I am serious problem of partial update my query is
something like
bEGIN tRAN
declare cursor...

SElect * from tableA where flag = null

open cursor
fetch first...

while @@FETCH_STATUS = 0
BEGIN
UPDATE TABLE tableB ..
.
.
.

fetch next
END
CLOSE ...
DEALLOCATE..
COMMIT TRAN

The problem is the cursor select retrieves
says about 10000 rows, goes thru the
loop for 1000 rows and just terminates without
giving an error message,or rolling back
in case of errors, but comes out as successfully
completed.


I am at loss as what could be the problems..
any suggestions welcome..

Thanks in advance,
Balajee.

View 2 Replies View Related

Delete Partial From Column

Jan 30, 2006

Guys, i have a table that one of the columns (Email To) is
a concatenated list of email addresses separated by semi colons ";".

i.e.:

rrb7@yahoo.com;richard.butcher@sthou.com;administr ator@sthou.com

etc like that.
each row varies with one exception. administrator@sthou.com is in each one.

is there a simple way thru sql or T-SQL to delete that "administrator@sthou.com" part? or should i call each row individually into say, a VB.net form using a split with the deliminator ";"
and then looping thru and updating each row?

thanks again for any easy answer
rik

View 7 Replies View Related

How To Join In As A Partial Column

Oct 2, 2013

We have here 3 tables which are linked by Order number. there is one more table we need to use to get the Shipping zone code. This column however is 10 pos. ( the order number on that table)whilst the others are all 8. We want to join on MHORDR in the table MFH1MHL0, then we are done.

SELECT
ALL T01.OHORDD, T03.IHINV#, T01.OHORDT, T01.OHJOB3, T01.OHORD#,
T02.IDPRLC, T02.IDNTU$*(IDSHP#) AS EXTSHP, T02.IDPRT#
FROM ASTDTA.OEORHDOH T01 LEFT OUTER JOIN
ASTDTA.OEIND1 T02
ON T01.OHORD# = T02.IDORD# LEFT OUTER JOIN
ASTDTA.OEINHDIH T03
ON T01.OHORD# = T03.IHORD#
WHERE T01.OHOSTC = 'CL'
AND T01.OHORDD >= 20120101
ORDER BY T01.OHORD# ASC

View 5 Replies View Related

Desperate For Even Partial Recovery

Feb 12, 2008

I have been asked to see what I can recover from a development server whose database became suspect during a power failure.

One developer from another group who's time is limited tried to repair the database using checkdb, but it is still suspect.

There are no recent backups of anything whatsoever. Needless to say, we are lacking in DBA skills here. At this point, we don't care whether we get the data back, but we are desperate to recover the table definitions, user-defined functions and stored procedures -- if not all of them than most of them; if not most of them than some.

What are our options here?

Are their any good third-party tools to help us with this problem?

View 20 Replies View Related

Joining On Partial Matches

Jul 20, 2005

Hi all,I have 2 files containing Id numbers and surnames (these filesessentially contain the same data) I want to select distinct() andjoin on id number to return a recordset containing every individuallisted in both the files HOWEVER, in some cases an incomplete IDnumber has been collected into one of the 2 files -is there a way tojoin on partial matches not just identical records in the same way asyou can select where LIKE '%blah, blah%'??Is hash joining an option i should investigate?TIAMark

View 4 Replies View Related

Partial Searches Over A Lot Of Fields

Jul 20, 2005

Hi All,I have the following scenario. I have a table called Invoice. Thishas around 30 columns of which i have to do a retrieval based onfilter conditions on 10 columns. These filters need to be partialsearches i.e. for e.g the Customer name could be 'Arun', 'Parthiv','Aaron', now i should be able to search the customer as 'ar' and itshould return 'Arun' and 'Parthiv'. My concern is there are 10 columnson which this like '%x%' search has to be done and there willpractically be hudreds of thousands of rows. can anybody suggest me toimprove the performance of such a query. Currently what i am thinkinof isselect Id, Memo, .. FROM Invoice where CustomerName like '%' + @Name +'%' and etc.P.S. am using ASP.Net as the front end.

View 1 Replies View Related

Partial Row At End Of File - Best Way To Handle?

Jul 5, 2007

Hi,



I have a file where there is a partial row at the end. It doesn't cause an error, but I get a "partial row" warning during execution.



What do most people do with these partial rows? Do they just ignore them as long as they don't cause errors? Or is it better to handle the partial row with a conditional split, for example?



Just wondering what other people's thoughts on this are. I tend to be of the "get rid of it" camp, but maybe that's overkill? Just looking for opinions, best practices.



Thanks

View 11 Replies View Related

Export A Partial Report

Aug 31, 2007



hi


I have 10 pages in my report and i want to export only 3 page .
any body can help me on this

Thanks
Pratik Mehta

View 3 Replies View Related

Partial Backup &&amp; Restore

May 8, 2008

Hi - I am using partial backup & restore on a Data Warehouse database currently in development.

When I recently tested the restore procedure I got the following error when trying to an online restore of one of the ReadOnly filegroups:


Msg 3125, Level 16, State 1, Line 1

The database is using the simple recovery model. The data in the backup it is not consistent with the current state of the database. Restoring more data is required before recovery is possible. Either restore a full file backup taken since the data was marked read-only, or restore the most recent base backup for the target data followed by a differential file backup.


I believe I received this message as the Filegroup I was attempting to restore had been set ReadWrite since it was backed up.

So - I am looking for a query to test that all my filegroup backups are consistent with the live database.

I think I can achieve this by checking the read_write_lsn & read_only_lsn values for the filegroup to restore are the same as the values in sys.master_files for the live database.


I am reading the lsn values for the backup from msdb.dbo.backupfile

Can anyone confirm this is the correct approach? or is there a better way to do this??

Many Thanks

View 2 Replies View Related

Partial Or No Cache Modes

Mar 20, 2008

I have a question, I keep getting the warning where the lookup found duplicate reference key values when caching reference data. It also says to switch over to partial or no cache mode. I switched to partial and and it took way longer to run my package. I have around 200,000 records right now but this table will continue to grow. I guess my question is which mode should I be using? I have another table that will have millions of records in it.

thanks,

View 6 Replies View Related

Order By Partial Field

Nov 13, 2006

How can I "Order By" the second + third characters of a 7 char field ?

Sample data looks like:



LCA - L

LCB - L

LCF - M

LCE - M

LCE - A

LCA - A

LCB - A
If I order by the whole field I get:



LCA - A

LCA - L

LCB - A

LCB - L

LCE - A

LCE - M

LCF - L

LCF - M
What I want is:



LCA - L

LCB - L

LCF - L

LCA - A

LCB - A

LCE - A

LCE - M

LCF - M
I'm still at the stage in this project were I can 'split' the field (if I have to) into first 3 and last 1. But the sort order of the last 1 is not alphabetic (I want 'L', 'A', "M").
Can I substitute a custom SortOrder some way ? (I've done that with mainframe Cobol).
All suggestions appreciated.
Thanks
Roger

View 1 Replies View Related

Replicating Partial Database

Dec 14, 2006

Replicating partial database


Hi there, actually I have a set of tables from my database model on SQL SERVER 2005 that I want to replicate (using merge replication) to a different server with SQL SERVER 2005 EXPRESS, Everything seems to be ok until I try so replicate just a portion of my model. I€™ve got some parent tables to replicate but I€™m not going to replicate some of their children, when I try to move them (just the parents and SOME children) to the other server using subscription, I have to erase some foreign keys (the children€™s foreign keys I wasn€™t supposed to migrate) in the target server to get my database migrated.

What Can I do to solve this problem?

View 1 Replies View Related

Partial Field Formatting

Feb 8, 2008

Is it possible to format just part of a field? I've got a report that return a collection of users based on some search criteria. Bascially, you enter a string and the report returns all users with a user name or display name simialr to your search string. I wanted to highlight the part of the name that matches the search string.

For instance, if you search for 'nny'

You might see a result of 'Johnny User'

Thanks.

J

View 4 Replies View Related

Mirroring Partial Failover

Nov 8, 2006

I'm testing mirroring in a high availability mode and getting some odd results. If I kill the SQL Service (or reboot the machine) fail-over works quickly and correctly. If I unplug the Network cable though I will get some of the databases failed over and some of the databases will either be Mirroring / In recovery or Principal / In recovery and never go live. Right clicking on them and going to properties lists there error that the database is unable to communicate with the Partner or Witness. The databases that this will occur on seem to move around (i.e. it isn't always the same databases, but will often be many of the same databases). If I reboot the databases that will not fail over tend to change fairly dramatically. I've seen instances where only 3 databases would fail over (I'm testing 17 databases) and other instances where all but 3 databases would fail over. I've had exactly one test where all databases failed over correctly. Again if I reboot a principal server, fail over works every time.



Has anyone else experienced only partial failovers for network issues or have a work around?

I have temporarily created a script that will force the databases to be the principal, but it's not great for high availability (probably will work for disaster recovery though).



Thanks



Larry

View 1 Replies View Related

Is There A Way To Do A Partial Distinct? (very Complexe Problem)

Nov 5, 2007

I'll try to explain the situation as simply as I can.
 A master table exists that has several columns which can either contain trade IDs, or a joint ID of two trade IDs.  These trade IDs exist for contracts.  Contracts can contain many trades (e.g. Contract ABC is written for 40 trades).
 There exists a need for a list of documentation for these trades/contracts that must be taken from the master table, based off the trade ID, and if the documentation type matches a certain criteria.  Because the users cannot understand a simple list of document IDs, added information about each document must be taken from trade/contract tables about each piece of documentation available.  The problem is, two different types of documents can exist for the same contract, but we do NOT need documents for each trade in the contract.
Currently, the stored procedure that someone else wrote is incredibly inefficient and regularly times the server out. 

It begins by creating two identical tables (called "first" and "second" from here on) set up to hold all relevant info about these documents. 
It then selects into one table document IDs for all trades that can be found in all of the relevant columns in the master table, including trades found in "joint IDs." 
It then creates a cursor and selects each row, searching several different things based off the trade ID and updates the first table with these new values.  This is the HUGE hangup because there are full table scans on a handful of tables about 4 times for every row in the table.
Then, after it updates all the information in the first table, it goes about weeding out the duplicate entries by doing the following:
Creates another cursor and does a distinct select on 4 columns which creates a unique ID to pull back exactly the right rows per contract per document from the first table. 
It then stores the information for all of the unique documents by inserting into the second table doing a "select top 1 * from firstTable where column1+column2+column3+column4 = 4DistinctColumnsFromFirstTable." 
 This, as you can imagine creates another huge sink by inducing table scans from a table resident only in memory... AGAIN for every row.
It finishes by selecting all rows from the second table.
Now... what I've done so far, is I've managed to create a complex system of joins to grab all relevant information in one select (steps 1-4).  I have not stored this information anywhere, nor have I used any cursors to do so (obviously as it is one select), but I imagine that I'll have to store all of this data and do a select on the stored data.
 
My question that would solve all of this is, can I do a partial distinct select?  As in, can I select 7 columns where 4 of them are distinct without having to go through inserting into temp tables, using cursors, using top 1... etc.  If not, is there some more efficient way of selecting out an entire batch of info based on only a few columns?
 
If you made it through that mess and decide to help me out, I sincerely appreciate your time....

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved