Combining Unique Records Into One
Mar 5, 2008
Could some one please help me with the following query.
I have multiple tables link together base on wo_No and prt_Mark fields and I get the following results.
wo_No | prt_Mark | dwg_Seq |
324037 | d400 | 1S2 |
324037 | d400 | A1 |
324037 | d400 | 1 |
219001 | 56 | 2B |
219001 | 56 | 2C |
219001 | 56 | 2C |
What I would like is to combine the unique dwg_Seq where they have the same wo_No and prt_Mark.
324037 | d400 | 1S2,A1,1 |
219001 | 56 | 2B,2C |
I would also be happy with the following results get the Max unique dwg_Seq and count how many more unique dwg_Seq there are.
324037 | d400 | A1 + 2 |
219001 | 56 | 2B + 1 |
Any help would be greatly appreciated.
View 2 Replies
ADVERTISEMENT
Aug 27, 2007
Hi,
Can anybody please tell how can I combing all records in field into one field.
For example
If my table is like
Final
_______
aaa
bbb
ccc
ddd
and i want result as
final1
_____
aaabbbcccddd
I do not want to use cursors for this. Please let me know if somebody knows the answer
Thanks
View 2 Replies
View Related
Dec 18, 2006
I have a database table tblobjects like this:
object_name, reference_id
a 1
a 2
a 3
a 4
b 2
b 3
b 1
b 4
c 2
c 4
c 5
c 6
d 2
d 4
d 5
d 6
I now would like to have a SQL query which gives me the number of
unique object and reference combinations, like this:
a
b
c
d shouldn't be displayed, because it's equal to c. The problem is also that a sequence of object references is
also important. So, for instance, object a shouldn't be equal to object b. The solution should also work is MS SQL and Mysql.
Any ideas how can I do this?
Thanks!
View 1 Replies
View Related
Jul 17, 2007
Using SQL 2000, how can you combine multiple records into 1?The source data is varchar(255), the destination will be text. I need helpwith the select statement.example tables:CREATE TABLE [NoteHeader] ([NoteID] [int],[CustomerID] [int] ,[Desc1] [varchar] (255),[Date] [datetime] ,)GOCREATE TABLE [NoteDetail] ([NoteId] [int],[SeqNum] [int] NOT NULL ,[Note1] [varchar] (255),[Note2] [varchar] (255),[Note3] [varchar] (255),[Note4] [varchar] (255),[Note5] [varchar] (255))GOSample script joining tables:SELECT *FROM NoteHeader INNER JOINNoteDetail ON NoteHeader.NoteID = NoteDetail.NoteIdSample results:NoteID CustomerID Desc1 DateNote1 Note2.....Note51111 987 Note Header Description 2007-07-15Notes detail record 1 field 1 Notes detail record 1 field2 .....1111 987 Note Header Description 2007-07-15Notes detail record 2 field 1 Notes detail record 2 field 2Desired results:NoteID CustomerID Desc1 DateCombinedNotes1111 987 Note Header Description 2007-07-15Notes detail record 1 field 1 +Notes detail record 1 field2 +Notes detail record 2 field 1 +Notes detail record 2 field 2 +through unlimited number of records up to 5fields eachThe NoteID field is the unique number. 1 record per NoteID in NoteHeader,NoteDetail can have unlimited number of same NoteID (usually not more than10)
View 2 Replies
View Related
Mar 2, 2008
Could some one please help me with the following query.
I have multiple tables link together base on wo_No and prt_Mark fields and I get the following results.
[wo_No] [prt_Mark] [dwg_Seq]
[324037] [d400] [1S2]
[324037] [d400] [A1]
[324037] [d400] [1]
What I would like is to combine the dwg_Seq into one record where they have the same wo_No and prt_Mark.
[324037] [d400] [1S2, A1, 1]
Any help would be greatly appreciated.
View 2 Replies
View Related
Dec 19, 2014
I have data that looks like this:
Cus_no Inv_No Trans_type Amt_Inv Amt_Paid
100 12345 Other 0 -21.76
100 12345 Discount 0 -6.39
100 12345 Discount 6.39 6.39
100 12345 Discount 21.76 21.76
100 12345 Sales Inv 218.99 218.99
What I would like to do is a select statement that returns:
Cus_no Inv_no Amt_Inv Amt_Paid Disc_amt Other
100 12345 247.14 218.99 -6.39 -21.76
I've tried something like this but since I'm referencing trans_type I get a message that trans_type must be in Group by. doing that give me multiple records.
select cus_no, Inv_no, Sum(Amount_Invoiced_DC) AS InvAmt,
case trans_type when 'Sales Inv' then sum(Amount_Paid_DC) else 0 end as AmtPaid,
case when trans_type = 'Discount' and sum(Amount_Paid_DC)<0 then sum(Amount_Paid_DC) else 0 end as DiscountAmt
FROM BI50_BankTransactions_AR_InvcDt_H
where cus_no is not null
group by cus_no, Inv_no
View 2 Replies
View Related
Nov 21, 2007
Hi,
I am trying to combine information from two or more records into one and I am completely stuck on a solution for my problem so I hope there is someone out there who can help me.
My table looks like this:
ID - DayNr - Transportation - TransOrder - Route
25 - 1 - Car - 1 - Text A
25 - 1 - Train - 1 - Text B
25 - 1 - Train - 2 - Text C
25 - 7 - Train - 1 - Text D
25 - 7 - Train - 2 - Text E
I want to combine all Route - information belonging to the same combination of ID & DayNr & Transportation into one new record. The result should look like:
Column 1 - Column 2
25/1/Car - Text A
25/1/Train - TextB;TextC
25/7/Train - TextD;TextE
I have tried Coalesce-statements and Cursor-solutions but until now everything I tried didn't work. Ideas anyone?
Thanks.
RMG
P.S. ID is not my primary key and doesn't have to be unique
View 14 Replies
View Related
Sep 22, 2005
Following is a stored procedure that currently runs on the system (compacted version). I need to combine this data with data from another Table .. tblAdjustments. The schema for this table is fairly close to tblShipmentDet.
tblShipmentHdr --> tblShipmentDet (Key = ShipmentID)
tblAdjustments --> standalone
Result: combine tblShipmentHdr + attached tblShipmentDet records with
tblAdjustments records.
Would the best approach be to use a UNION SELECT?
@XToDate datetime = '7/31/2005' ,@XBegDate datetime = '7/1/2005'
AS
SELECT
SHPH.ProductID,
SHPH.ReceivedDate,
SHPH.ShipmentNo,
SHPD.Vendor,
SHPD.Quantity,
QRecvdDate = CASE WHEN SHPH.ReceivedDate < convert(varchar(40),@XBegDate,121)
THEN NULL ELSE SHPH.ReceivedDate
END,
QShipQty = CASE WHEN SHPD.TransCd = 'F'
THEN NULL
WHEN SHPH.ReceivedDate < convert(varchar(40),@XBegDate,121)
THEN NULL
ELSE SHPH.ShippingQty
END,
PROD.ProductName,
QOpenAccrual = CASE WHEN MEND.OpeningAccrual is Null
THEN 0 ELSE MEND.OpeningAccrual
END
FROM dbo.tblShipmentHdr SHPH
LEFT OUTER JOIN dbo.tblProducts as PROD ON Left(SHPH.ProductID,7) = Left(PROD.ProductID,7)
LEFT OUTER JOIN dbo.tblShipmentDet as SHPD ON SHPH.ShipmentID = SHPD.ShipmentID
LEFT OUTER JOIN dbo.tblMonthend as MEND ON SHPH.ProductID = MEND.ProductID And MEND.MEPeriod = convert(varchar(40),@XBegDate,121)
WHERE ((SHPH.ReceivedDate >= '7/1/2005' AND SHPH.ReceivedDate <= '7/31/2005') OR (SHPD.DatePaid >= '7/1/2005' AND SHPD.DatePaid <= '7/31/2005'))
View 1 Replies
View Related
Dec 20, 2007
Hi All,
I'm trying to develop a query that joins one record from a table with multiple matching records from another table all in one record,
Table1 has the primary key
id
--
1
2
3
4
Table2 has the follwing records
id year subject
-----------------
1 2000 English
1 2002 French
2 2004 English
2 2005 English
2 2006 English
3 2007 French
I want the result to be like this
id 2000 2001 2002 2003 2004 2005 2006 2007
-----------------------------------------------------------
1 English null French null null null null null
2 null null null null English English English null
3 null null null null null null null English
Appretiate your assistance
View 4 Replies
View Related
Dec 20, 2006
I'm working on a data migration that requires combining rows/values from one table to update rows in another table, and I can't figure out if I need to do a nested FOREACH or something else. Here's the example.
I have a table called Health that has a unique child record, key is childID.
I have another table called Concerns that has multiple records for each child. The Concerns table structure has several Boolean fields that need to capture and retain a true value, no matter what the value is in the next record, i.e. once a field is true, it's always true. Then those values need to update the child record in the Health table.
So if the Concerns table has the following records for a child:
ChildID, DentalConcern, VisionConcern, HearingConcern.
1, True, False, False
1, False, True, False
1, False, False, False
The final values I need to update the Health table are:
1, True, True, False.
And of course, my recordset of Concerns has records for many children.
O.K., that's the background. I have Foreach Loop container set up to enumerate through the ADO recordset of the Concerns table. I have recordset variables set up for childID and each of the boolean Concerns fields. My thought was then to do a nested Foreach Loop container on the childID variable, with a Script Task to read in the recordset variables, then collect the True/False values in my readwrite variables I set up to "collect" the values of each record.
I think then I can compare the incoming recordset childID with the readwrite childID variable to see if it's changed, and if it has then I want to do the SQL update to the Health table. I'm stuck trying to figure out where to put my Execute SQL task to update the child record when I'm finished with one child. in the the Script Task. If it's in the nested Foreach, won't it execute the SQL for every record? Same question on the outer Foreach that's looping through the entire ADO recordset.
So should I put the Update sql statement in the Script Task instead of a separate Execute SQL Task?
Or is there a totally different way I need to look at looping through the entire recordset but doing processing on a subset based on the childID value?
Hope that makes sense, and thanks in advance for any help/suggestions.
Chera
View 3 Replies
View Related
Nov 21, 2007
Hi,
I am trying to combine information from two or more records into one and I am completely stuck on a solution for my problem so I hope there is someone out there who can help me.
My table looks like this:
ID - DayNr - Transportation - TransOrder - Route
25 - 1 - Car - 1 - Text A
25 - 1 - Train - 1 - Text B
25 - 1 - Train - 2 - Text C
25 - 7 - Train - 1 - Text D
25 - 7 - Train - 2 - Text E
I want to combine all Route - information belonging to the same combination of ID & DayNr & Transportation into one new record. The result should look like:
Column 1 - Column 2
25/1/Car - Text A
25/1/Train - TextB;TextC
25/7/Train - TextD;TextE
I have tried Coalesce-statements and Cursor-solutions but until now everything I tried didn't work. The big issue here is that I have to base my concatenation on 3 columns. Ideas anyone?
Thanks.
RMG
P.S. ID is not my primary key and doesn't have to be unique
View 1 Replies
View Related
Sep 4, 2007
I have a table of Sales Order Releases. Following is what the table looks like:
SMIPartNo
QtyType
PO
POLine
QtyDue
UOM
DateDue
DateDueType
fsono
fcustno
finumber
frelease
fspq
JI-117933A1
Firm
N40136001
234
200
EA
7/2/2007 0:00
SH Ship Date
E00001
20
001
1
1800
JI-117933A1
Firm
N40136001
234
400
EA
7/9/2007 0:00
SH Ship Date
E00001
20
001
2
1800
JI-117933A1
Firm
N40136001
234
400
EA
7/30/2007 0:00
SH Ship Date
E00001
20
001
3
1800
JI-117933A1
Firm
N40136001
234
400
EA
8/6/2007 0:00
SH Ship Date
E00001
20
001
4
1800
JI-117933A1
Raw Material
N40136001
234
400
EA
8/13/2007 0:00
SH Ship Date
E00001
20
001
5
1800
JI-117933A1
Raw Material
N40136001
234
200
EA
8/20/2007 0:00
SH Ship Date
E00001
20
001
6
1800
JI-117933A1
Raw Material
N40136001
234
400
EA
8/24/2007 0:00
SH Ship Date
E00001
20
001
7
1800
JI-117933A1
Raw Material
N40136001
234
200
EA
9/3/2007 0:00
SH Ship Date
E00001
20
001
8
1800
JI-117933A1
Raw Material
N40136001
234
400
EA
9/10/2007 0:00
SH Ship Date
E00001
20
001
9
1800
JI-117933A1
Raw Material
N40136001
234
400
EA
9/17/2007 0:00
SH Ship Date
E00001
20
001
10
1800
See 2nd post for rest of question
View 1 Replies
View Related
Jul 20, 2005
I have a table;CREATE TABLE theLiterals (theKey varchar (255) NOT NULL ,theValue varchar (255) NULL)INSERT INTO theLiterals VALUES('defaultServer','\MyServer')INSERT INTO theLiterals VALUES('defaultShare','MyShare')INSERT INTO theLiterals VALUES('defaultFolder','MyFolder')INSERT INTO theLiterals VALUES('defaultFile','MyFile.dat')I then try;SELECTdefaultServer = CASE WHEN theKey = 'defaultServer' THEN theValue END,defaultShare = CASE WHEN theKey = 'defaultShare' THEN theValue END,defaultFolder = CASE WHEN theKey = 'defaultFolder' THEN theValue END,defaultFile = CASE WHEN theKey = 'defaultFile' THEN theValue ENDFROM theLiteralsand I get;defaultServer defaultShare defaultFolder defaultFile\MyServer NULL NULL NULLNULL MyShare NULL NULLNULL NULL MyFolder NULLNULL NULL NULL MyFile.datbut I want it COALESCEd like this;defaultServer defaultShare defaultFolder defaultFile\MyServer MyShare MyFolder MyFile.dat....but my syntax is incorrect. Is there an efficient way of doing this.I want to have a script/UDF where I can say...GetLiteralsFor('defaultServer','defaultShare','def aultFolder','defaultFile')and then my one-row recordset will be...RS(0) will = '\MyServer'RS(1) will = 'MyShare'RS(2) will = 'MyFolder'RS(3) will = 'MyFile.dat'Thanks for any help!
View 5 Replies
View Related
Sep 4, 2014
CREATE TABLE DHS(CUSTOMERNBR VARCHAR(20), CONTRACT VARCHAR(20), SUBCONTRACT VARCHAR(20) , STARTDATE DATETIME, ENDDATE DATETIME, EFLAG VARCHAR(20), HFLAG VARCHAR(20))
The data which will be going into this table is from two table which have a 1 to many relationship:
Here is the 1 side and data:
CREATE TABLE CUSTOMERS(
CUSTOMERNBR VARCHAR(20),
CONTRACT VARCHAR(20),
SUBCONTRACT VARCHAR(20),
STARTDATE DATETIME,
ENDDATE DATETIME DEFAULT '12/31/2099')
[Code] ....
Here is the Many side and data:
CREATE TABLE FLAGS(CUSTOMERNBR VARCHAR(20), FLAGCODE VARCHAR(20), STARTDATE DATETIME, ENDDATE DATETIME DEFAULT '12/31/2099')
INSERT INTO FLAGS(CUSTOMERNBR, FLAGCODE, STARTDATE, ENDDATE) VALUES('10001000101', 'H', '02/01/2014', '03/31/2014')
[Code] ....
The CUSTOMERS table holds the record date span into which the FLAGS table records have to "fit". In no case will the date spans from the FLAGS table fall outside the STARTDATE - ENDDATE span in the CUSTOMERS table for any CUSTOMERNBR. Here is an example of the final expected output in the DHS table from combining records in the CUSTOMERS and FLAGS tables:
CUSTOMERNBRCONTRACTSUBCONTRACTSTARTDATEENDDATEEFLAGHFLAG
10001000101A910400801/01/201401/31/2014
10001000101A910400802/01/201403/21/2014H
10001000101A910400804/01/201404/30/2014
10001000101A910400805/01/201405/31/2014H
10001000101A910400806/01/201412/31/2099E
10001000102A555500101/01/201403/31/2014E
10001000102A555500104/01/201404/30/2014EH
10001000102A555500105/01/201406/30/2014E
10001000102A555500107/01/201412/31/2099
10001000103A666600401/01/201410/01/2014
View 1 Replies
View Related
Jun 25, 2015
I have a question about combining multiple records with same ID but different values between records on different fields into a single record with all values combined for all fields. Example: Multiple records with the same record ID:
RECORD_ID BO_ID Code Code_Date SubMsgCD1 SubMsgCD2 LNMsgCD1
1380900 XZ01 12 1/6/2015 P302
1380900 XZ01 12 1/6/2015 L405
1380900 XZ01 12 1/6/2015 P302 1004
INTO a single record:
RECORD_ID BO_ID Code Code_Date SubMsgCD1 SubMsgCD2 LNMsgCD1
1380900 XZ01 12 1/6/2015 P302 L405 1004
View 2 Replies
View Related
May 6, 2004
Is there a way to get a Stored Procedure to return only unique records from a table? I am using a Stored PRocedure to query a table and it returns all the records in the table and there are many duplicates in the information. I am using SQL Server 2K.
View 12 Replies
View Related
May 12, 2008
I have the following sql:
SELECT COUNT(patient.patientID) AS total_patients
FROM patient
LEFT JOIN patient_record ON patient_record.patientID = patient.patientID
WHERE sub_categoryID = 4 OR patient_record.allocated = 4
from the database this gives me a COUNT of 22, it should only be 10. I am doing a join and it gives the total records in the two tables where I only want the total in the left table(patient).
How can I GROUP BY patient.patientID in a COUNT query
Thanx, Robson
View 2 Replies
View Related
Sep 17, 2007
Hello Everyone and thanks for your help in advance. I have a SQL Server Table wtih approximately 100,000 records. I need to determine if there are duplicate records in this table. My probelm is that there is a unique ID column that was added for each row, so I'm not exactly sure how to filter the rows. Any help on this would be greatly appreciated. Thanks.
View 4 Replies
View Related
Apr 15, 2008
I'm trying to read from a table and return only the unique records from a table.
The table has 3 columns,
ID - Autoinc
Amount - decimal 6,2
Name - varchar(10)
If there are records like
ID Amount Name
1 0.03 Name1
2 0.07 Name9
3 0.05 Name3
4 0.03 Name8
5 0.07 Name4
6 0.06 Name7
I am wanting to retreive only records 3 & 6 (values 0.05
0.06) as they are the only ones where amount is unique i.e. only 1 entry in the table.
I put the following sql statement together which achieves this but it it only returns the amount value
select Count(amount),amount from bids group by amount having count(amount)<2 order by amount
So I then expanded it to read
select Count(amount),amount,name from bids group by amount,name having count(amount)<2 order by amount
not sure where i'm going wrong but the results returned were
0.03
0.03
0.05
0.06
0.07
0.07
Appreciate any help here.
View 2 Replies
View Related
Apr 24, 2007
Hello,
I have a table with sixty columns in it, five of which define uniqueness for the records. Currently there are 190,775 records in the table. One of the records is a duplicate. I need to insert only the unique records from this table (all columns) into another table. I cannot use a unique nonclistered index with IGNORE_DUP_KEY in the destination table because of a problem I am having with the 'duplicate key was ignored' message. The destination table has a primary key with a clustered index on the same five columns.
How can I put together a SELECT statement that will give me all of the columns in the source table based on uniqueness of the five key columns?
Does my request make sense? Please let me know if you have questions.
Thank you for your help!
CSDunn
View 3 Replies
View Related
Dec 3, 2007
Let's say I have a data entry from a pool of employees:
table is as follow:
EmpNo Branch Date Amount
1 A101 11/30/2007 $0.90
1 A101 11/30/2007 $1.20
2 A101 11/30/2007 $0.90
3 A101 11/30/2007 $0.80
How can I select the whole table and only take in 1 unique latest entry if there are multiple entries for the same day, same branch under same employee number?
Thanks! :D
View 7 Replies
View Related
Mar 21, 2014
I am trying to create a select query similar to the following but the problem I am having is that I want to only select one record where there may be several with the same dw_order_no. I have tried various ways using SQL developer but without success
SELECT VE_EZP_ORDER_TRANS.EZP_BILL_STATUS AS EZP_BILL_STATUS1,
VE_EZP_AGED_CUSTOMER_DEBT.SURNAME,
VE_EZP_AGED_CUSTOMER_DEBT.DEBT_AGE_CATEGORY,
VE_EZP_AGED_CUSTOMER_DEBT.DEBT_AGE,
VE_ORDERLINE.DW_ORDER_NO,
[code]...
View 3 Replies
View Related
Oct 7, 2005
Steve writes "I need a query to show all the medicare patients only. I want only one kind of insurance for each patient. I am getting records where each patient has more than one insurance like medicare and something else.
Each patient has one,two, three or more insurances like
medicare,medical,blue cross,tricare etc.
The result should look like this:
Sam pick Medicare
John white Medicare
Ann Richmond Medicare
My query retrieves patients with more than one insurance.
select patient_first_name f_name,patient_last_name l_name,patient_zip zip,
count(distinct payer_id) payer_id
from claims
where payer_id ='abcdefhj'
group by patient_first_name, patient_last_name,patient_zip
having count(distinct payer_id)= 1
order by patient_zip asc;
please help me. Greatly appreciate your response."
View 1 Replies
View Related
Nov 23, 2007
I have a stored procedure that appends data from a temp table to a destination table. The procedure is called from an aspx web page. The destination table has an index on certain fields so as to not allow duplicates.
The issue I'm having is if the imported data contains some records that are unique and some that would be duplicate, the procedure stops and no records are appended. How can I have this procedure complete it's run, passing over the duplicates and appending the unique records? Since the data is in a temp table (which gets deleted after each append) should I run some sort of 'find duplicates' query, and delete the duplicates from the temp table first, then append to the destination table?
Thanks in advance.SMc
View 2 Replies
View Related
Apr 28, 2008
Hi All,
I need a bit of help with a join. I have 2 tables :
TradeSummary
has fields : SymbolID, CurrentPrice, TotalValue
Trades
has fields : SymbolID, TradeID, ExecutionTime, TradeValue
TradeSummary has one entry for each SymbolID, while Trades contains one or more entries per SymbolID
and what I want to retreive is :
For every item in TradeSummary get CurrentPrice, TotalValue from TradeSummary
and also get TradeValue from Trades for the record for max(ExecutionTime)
tables are joined on TradeSummary.SymbolID = Trades.SymbolID
Every attempt of mine so far returns multiple rows for each SymbolID - I want only one row per SymbolID
thanks in advance
View 11 Replies
View Related
Mar 17, 2014
Using SSE 2012 64-bit...How can I check all fields for duplicate records?I tried OVER PARTITION..But that is returning an error message
Code:
USE db
SELECT ROW_NUMBER() OVER (PARTITION BY all fields)
ORDER BY ID --Not unique) AS RowNumber
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator
View 4 Replies
View Related
Jun 7, 2008
I am new to SQL, still learning.
If I want to retreive unique records for e.g. VendorName,City and State however no 2 vendors must be of the same city or state.
How can I do this?
I have used Select and distinct but that still brings unique vendornames in the same state and city. I only need one vendor per city and State.
Help!
View 4 Replies
View Related
Mar 5, 2006
I am trying to pull only those records with a maximum upload date for a file upload log.
This table keeps a list of files uploaded by supplierID and will have multiple records from the same supplier but with all different upload dates. Overall there are over 1,000 records for 48 uniqur suppliers.
The field names are:
SupplierID, UploadDate, FeedFileName, RecordCount, FeedFileDate, RecordCount, and FeedLoaded.
So for each distinct SupplierID I'd like to grab the line item based on the max feedfiledate for each one - in other words I am trying to get the latest uploaded file information, how many records were in the file, and when it was uploaded... I've tried multiple group by and max clauses but there is something I am missing...
Thanks Much in advance
View 4 Replies
View Related
Apr 12, 2007
Hello,
I'm hoping someone can point me in the right path with a design issue I'm running into. I'm somewhat new with database design and SQL server, so bear with me.
I'm creating a sort of project management system, and I've got a main PROJECT table, and then a STUDENT_PROJECT_PARTICIPANT table, with foreign keys to STUDENT and PROJECT tables. Each has an 'institute' field. What I want to do is to be able to report on the number of students involved in each project, the number of unique students involved in project with a particular institute, and then the number of unique students involved in all projects. I'd also like to be able to report the number of unique students involved in a custom-picked list of projects (this seems the least feasible). The trick is that I need to report this statics within a specific date range. My original idea was to just save statistics of the numbers of students involved in a project every time it is saved, but from there I'm not sure how I can find out the unique count for all projects, since I don't have the date that each student was saved. That's my basic problem. Let me know if you need any clarification.
If anyone has done something similar or has any advice for me on this, please let me know!
Thanks!
Daryl
View 6 Replies
View Related
Feb 21, 2006
> This is a common problem with some solution[color=blue]>>[/color]/************************************************** *********************************** Problem:* Determine the Duplicated Records in a table using single SELECT.** We shall be using Northwind database, add some duplicate records.** Here we want to know if 2 columns (CompanyName,* PHone) are duplicated in a table.*** ShipperID CompanyName Phone* ----------- ------------------------- ------------------* 1 Speedy Express (503) 555-9831* 2 United Package (503) 555-3199* 3 Federal Shipping (503) 555-9931* 4 Federal Shipping (503) 555-9931* 5 Speedy Express (503) 555-9831* 6 Federal Shipping (503) 555-9931***************************************************** **/================================================== SOLUTION 1: Gives me the IDs that are duplicated.================================================== SELECTShipperID, CompanyName, PhoneFROMSHIPPERSWHEREEXISTS (SELECTNULLFROMSHIPPERS bWHEREb.CompanyName = SHIPPERS.CompanyNameAND b.Phone = SHIPPERS.PhoneGROUP BYb.CompanyName, b.PhoneHAVINGSHIPPERS.ShipperID < MAX( b.ShipperID ))/* ********************* Output results********************/ShipperID CompanyName Phone----------- ----------------------------------------------------------------1 Speedy Express (503) 555-98313 Federal Shipping (503) 555-99314 Federal Shipping (503) 555-9931(3 row(s) affected)================================================== ===========SOLUTION 2: Gives me the data which are duplicate butnot the IDs================================================== ===========SELECTCompanyName, PhoneFROMSHIPPERSGROUP BYCompanyName, PhoneHAVINGCOUNT(*) > 1/* ********************* Output results********************/CompanyName Phone---------------------------------------- ------------------------Speedy Express (503) 555-9831Federal Shipping (503) 555-9931(2 row(s) affected)
View 1 Replies
View Related
Jul 11, 2006
Hi all,We have an app that uses SQL 2000. I am trying to track when a code field(selcode) is changed on an order which then causes a status field (status)to change. I tried a trigger but the app may use 2 different updatestatements to change these fields depending on what the user does. When thetrigger fires (on update to selcode), the status field has already beenchanged. So my trigger to record the changes from inserted and deleted donot get the true 'before' value of the status field.The app does use a log table that tracks these changes. The problem I amhaving is that 2 records are created, one for the change to selcode andanother for the change to status.I am looking for help with a script to combine the existence of these 2 logrecords into 1 unique record or occurance that I can track.example:ordlog: table that logs order changesordernr: order numbervarname: name of field being changedold_value: contents of field before changenew_value: contents of field after changesyscreated: date/time of log entrySELECT ordernr, varname, old_value, new_value, syscreatedFROM ordlogwhere varname = 'selcode' and ordernr = '10580'SELECT ordernr, varname, old_value, new_value, syscreatedFROM ordlogwhere varname = 'status' and ordernr = '10580' and old_value = 'A' andnew_value = 'O'So I need a way to combine these 2 log entries into a unique occurance. Theordernr and syscreated could be used to link records. syscreated alwaysappears to be the same for the 2 log entries down to the second. Selcodecan change from NULL to a number of different values or back to NULL.Statusis either 'A' for approved or 'O' for open. An order can have many logentries during its life. The selcode may be changed several times for thesame order.Ideally, I would like a result that links 2 log entries and shows the statuschanged from 'A' to 'O' when selcode changed.Thanks for your time.
View 1 Replies
View Related
May 22, 2008
hilet me explain my need..following are the tables im using..tbl_company (company table - parent) id company 1 test 2 test123 tbl_dept (department table - master) id dept 1 dept1 2 dept2tbl_compdept (company departments table - child) cmpid deptid 1 1 2 1 2 2 wats my need is.. while the company is listing..by query using joins, result was like this..company depttest dept1test123 dept1test123 dept2i need company test123 should be listd only once..when i use group by or distinct means, all r listed..is there any way to filter out therepeating company list by just listing the company list only once..
View 5 Replies
View Related
Sep 27, 2005
Hello, everyone:
I have two tables with some duplicated records like,
ZZZTest:
C_IDC1C2C3
10AAA
20BBB
30AAA
40BBB
ZZZTestTable:
D_IDC11C22C33
1AAA
2AAA
3BBB
4BBB
5AAA
6AAA
7BBB
I wand to get the unique records by SELECT / JOIN statement. Now I used a query,
SELECT * FROM ZZZTest t
INNER JOIN ZZZTestTable tt
ON t.Col1=tt.Col11 AND t.Col2=tt.Col22 AND t.Col3 = tt.Col33
and got the records like,
C_IDC1C2C3D_IDC11C22C33
10AAA1AAA
30AAA1AAA
10AAA2AAA
30AAA2AAA
20BBB3BBB
40BBB3BBB
20BBB4BBB
40BBB4BBB
10AAA5AAA
30AAA5AAA
10AAA6AAA
30AAA6AAA
20BBB7BBB
40BBB7BBB
What I am expecting is,
C_IDC1C2C3D_IDC11C22C33
10AAA1AAA
30AAA2AAA
20BBB3BBB
40BBB4BBB
Any suggestion will be great appreciated.
Thanks
ZYT
View 3 Replies
View Related
