Transact SQL :: Avoid Duplicates From Join
Jun 8, 2015
I am doing some audit and i have below query, how can i get rid of duplicates from the below query any T SQL to get rid of duplicates...
I am using SP_Who2 and sql server Audit for auditing all data happening on sql server databases and dumping them to tables Audit_DBAudit abd Audit_sp_who2 and from then i am trying to get data which is not repeating/duplicate ...
SELECT
A.ProgramName
,a.HostName,[Server_principal_name],[Server_instance_name],[Database_name],[Object_name],F.Statement
FROM Audit_DBAudit as F
Join [Audit_sp_who2] AS a
on LTRIM(RTRIM(F.server_principal_name))=LTRIM(RTRIM(A.Login))
View 11 Replies
ADVERTISEMENT
Nov 2, 2015
INSERT
INTO [Table2Distinct]
([CLAIM_NUMBER]
,[ACCIDENT_DATE]
[code]....
I used the above query and it still inserts all the duplicate records. What is wrong with my statement?
View 5 Replies
View Related
Dec 16, 2007
Hi all,
I have 10 tables with unique values such as mobile no: and message in each table.But now the problem is that this same mobile no: may be there in other tables.How can i eliminate the records from other tables.Can anyone tel me a suggestion.
Thank U.
View 13 Replies
View Related
Jul 8, 2000
Hi,
I'd really appreciate advice on now to avoid displaying duplicates in a resultset like the one below, only displaying the first field once and all the rows that correspond to that field all listed listed below. Distinct doesn't work. Thanks
Customer Name Address
============= =======
Big Bob's Books 123 Stone Lane
456 Seaside Drive
789 Waterhouse Square
1234 Mystery Drive
5678 Simple St
Tiny Tim's Tools
Tiny Tim's Tools
View 1 Replies
View Related
Aug 20, 2013
I am facing issues with a LEFT JOIN in my query. It takes 45 secs to process on the production server due to huge number of records.building a query to avoid the LEFT JOIN. I am Trying to use UNION ALL and it works much faster except that I am stuck in the last bit.
scripts (sample):
CREATE TABLE [dbo].[tbl_PersonDetails](
[PersonID] [int] NOT NULL,
[LeaveTimeId] [int] NOT NULL
) ON [PRIMARY]
[code]...
Need Rows from tbl_PersonDetails macthing (all 3 below) following criteria :
1. tbl_PersonDetails.PersonID is present in tbl_PersonLeaveDetails
2.tbl_PersonDetails.TimeID does not fall between any of the aligned (matching personid) FromTimeID and ToTimeID in tbl_PersonLeaveDetails.
3. not using LEFT join
View 9 Replies
View Related
Oct 22, 2004
Can some kind person out there please help me, I've been stuck on this for daaaa-y-s.
I have a database that allows users to search for pdf's of technical drawings.
Basically I have one huge table with multiple columns, which the user can only search on any combination of one of these two columns
"drawing_series" eg 0100, 0046, 1000
"drawing_number" eg 0076000, 0000123, 0000004
There is also a Revision column(which the user can't see) that goes up by 1 each time a drawing has been modified and resubmitted to the database.
"revision" eg 01, 02, 03, ....... 99
So a search on 0046 series might pull back drawings
0046-0010000-01
0046-0010000-02
0046-0010000-03
0046-0076000-01
0046-0076888-01
0046-0076888-02
The problem is that I only want drawings with the highest revisions returned eg
0046-0010000-03
0046-0076000-01
0046-0076888-02
The code below worked like a charm in the test stages pulling back a few hundred records but now that i've uploaded 10's of thousands of records to the DB the whole lot dies if the search result pulls back more than a few thousand records.
SELECT * FROM dbo.Drawing_Database
where dbo.Drawing_Database.revision=(select max(revision) from dbo.Drawing_Database self where self.drawing_series + self.drawing_number = dbo.Drawing_Database.drawing_series + dbo.Drawing_Database.drawing_number) Drawing_Series like '0046' order by Drawing_Series, Drawing_Number
There must be a simpler way of doing this as i can pull out duplicate series + numbers using " HAVING Count(*)>1" but dont know where to go from there.
Help!
TheMaster
View 2 Replies
View Related
Jul 23, 2005
Hi All,I am banging my head against a brick wall over this problem, so anyhelp in the correct direction would be muchly appreciated!I have 2 SQL (MS SQL) server tables, realated to -a Property,Sales of that property.A property is uniquely identifed by its Roll, valuation Number andSuffix (not my choosing).Each property can only appear in the property table once, and can onlyhave 1 assessment - but can have multiple sales (ie - over theannalysis period the same property can sell more than once).There is approximatly 19000 properties relating to about 8000 sales.When creating a query to list property and most recent sale (if thereis any) I end up with somthing like this -SELECT [roll], [valuation], [suffix], [sale date]FROM [property]LEFT JOIN [sales]ON[property].[roll] = [sales].[roll] AND[property].[valuation] = [sales].[valuation] AND[property].[suffix] = [sales].[suffix](table names simplifed).I get rows where there is all the property data there, but sale date(etc.) is null (as I would expect from a left join), but the problem is- when there is more than 1 sale for a property it pulls out anothercopy of the property data.In short, because of that I come out with more records than properties.ie -roll valuation suffix sale date12 456789 A 1/1/200312 788988 B NULL14 123456 A 1/1/200314 123456 A 1/1/2004(Note - the last two are the same property).I didn't know that the left join can affect both joined tables!Is there any way around this? Any suggestions/hints in the rightdirection would be very much appreciated!THANKS!
View 3 Replies
View Related
Jul 20, 2005
When I run the attached query, I get duplicates when there is one tomany relationship between tableA and tableB. The query, tested schemaand the result is attached. Sorry for the long post.Here is tested Schema and Data inserts.----------------------create table TestTblA(ShipDate datetime,CPEID varchar(30),phonenum char(14))gocreate table TestTblB(CPEID varchar(30),itemID varchar(30),active char(1))gocreate table TestTblC(itemID varchar(30),descr varchar(50))goinsert into TestTblA values (getdate(),'TWMUA','(408)-555-1211')insert into TestTblA values (getdate(),'TWMUA','(408)-555-1212')insert into TestTblA values (getdate(),'TWMUB','(408)-555-1211')insert into TestTblA values (getdate(),'TWMUB','(408)-555-1212')insert into TestTblA values (getdate(),'TWMUB','(408)-555-1213')insert into TestTblA values (getdate(),'TWMUC','(408)-555-1211')insert into TestTblA values (getdate(),'TWMUC','(408)-555-1212')insert into TestTblA values (getdate(),'TWMUC','(408)-555-1213')insert into TestTblA values (getdate(),'WWEXI','(408)-555-1211')insert into TestTblA values (getdate(),'WWEXI','(408)-555-1212')insert into TestTblA values (getdate(),'WWEXI','(408)-555-1211')insert into TestTblB values ('TWMUA','1000-000043-000','Y')insert into TestTblB values ('TWMUB','1000-100002-001','Y')insert into TestTblB values ('TWMUC','1000-200005-000','Y')insert into TestTblB values ('WWEXI','1000-401001-000','Y')insert into TestTblB values ('WWEXI','1000-401002-000','Y')insert into TestTblC values ('1000-000043-000','descrUA')insert into TestTblC values ('1000-100002-001','descrUB')insert into TestTblC values ('1000-200005-000','descrUC')insert into TestTblC values ('1000-401001-000','descrWW')insert into TestTblC values ('1000-401002-000','descrWW')----------------Query follows------------SELECT A.ShipDate,A.CPEId,ItemId = CASEWHEN A.CPEId = 'TWMUA' THEN 'New - Single User'WHEN A.CPEID = 'TWMUB' THEN 'New - Multi User'WHEN A.CPEID = 'TWMUC' THEN 'New - Triple User'When B.ITEMID is NULL THEN 'Unknown'When B.ITEMID = ' ' THEN 'Unknown'else B.ItemIdend,MODEL_NO = CaseWhen B.ITEMID = '1000-000043-000' Then rtrim(C.DESCR)When B.ITEMID = '1000-100002-001' Then rtrim(C.DESCR)When B.ITEMID = '1000-200005-000' Then rtrim(C.DESCR)WHEN A.CPEId = 'TWMUA' THEN '1100'WHEN A.CPEID = 'TWMUB' THEN '1100'WHEN A.CPEID = 'TWMUC' THEN '1000SW'When C.DESCR is NULL THEN 'Unknown'else 'Unknown'end ,COUNT(A.phonenum)FROM TestTblA A LEFT OUTER JOIN TestTblB B ON A.CPEID=B.CPEID andb.active = 'Y'LEFT OUTER JOIN TestTblC C ON B.ItemId=C.ITEMIDGROUP BY A.ShipDate,A.CPEId,B.ItemId,C.DESCRORDER BY A.ShipDate,A.CPEId,B.ItemId,C.DESCR---- end of queryThe result (modified the output format to fit a single line)ShipDate CPEId ItemId MODEL_NO Count2003-07-18 TWMUA New - Single User descrUA 22003-07-18 TWMUB New - Multi User descrUB 32003-07-18 TWMUC New - Triple User descrUC 32003-07-18 WWEXI 1000-401001-000 NULL 32003-07-18 WWEXI 1000-401002-000 NULL 3** The problem **I need WWEXI or any similar entry to only show once, it shows twice.Thanks for your help.
View 3 Replies
View Related
Jun 3, 2008
Hi all,
Below are my tables:
Rowid
Name
1
John
2
Peter
3
Jack Table
Rowid
Rowid1
1
1
1
2
1
3
2
1
2
2
3
2
3
3 Table1
Rowid1
Country
1
USA
2
UK
3
JAPAN Table2
I tried to get the Country for all the people in the first table.
My SQL statement is: SELECT Table.Name, Table2.Country FROM Table Left Join Table1 ON Table.Rowid = Table1.Rowid Left Join Table2 ON Table1.Rowid1 = Table2.Rowid1
My final result is shown on Table2. But is it possible if I can generate the results without the duplicate Names (as shown below)?
Name
Country
John
USA
UK
JAPAN
Peter
USA
UK
Jack
UK
JAPAN
Any advice will much appreciated.
View 3 Replies
View Related
Oct 27, 2015
This query below is giving product join for me, is there a way to avoid this?
SELECT DISTINCT a.RevID, indexdate, transadate
FROM temp1 AS a
INNER JOIN temp2 AS d ON transdate BETWEEN indexdate-60 AND indexdate+60
)
View 5 Replies
View Related
Feb 6, 2014
Got a data set like this:
rowID PersonID Start Date End Date
===== ======== ========== ==========
001 6575556 19/06/2013 09/07/2013
001 6575556 20/06/2013 12/07/2013
001 6575556 21/06/2013 12/07/2013
002 9478522 15/05/2013 18/05/2013
003 7753423 22/08/2013 01/09/2013
Person can have more than one start/end date therefore I get multiple of the same row ID and Person ID when looking at their dates.
I want to display the most recent end date and associated data if there is more than one start/end date for the same person. I decided to do a self join with max Date aggregate using this against a main select from the Table1:
SELECT PersonID,
MAX([End Date]) AS MaxEndDate
FROM Table1
GROUP BY
PersonID
And join it this way:
select RowID,
PersonID,
[End Date]
FROM Table1 INNER JOIN (
SELECT PersonID,
MAX([End Date]) AS MaxEndDate
[Code] ....
When I run the sub-query on its own it gives me the single PersonID and Max Date but on self-joining with Table1 I still get the duplicates values.
View 2 Replies
View Related
Sep 2, 2004
I really must be missing something here...
Trying to cross-update 2 tables.
Picture a checkbook reconcilliation without common check numbers. The checkbook has uniqueids and the bank has transaction ids but they are different. So the match is on date/payee and amount. So I wrote 2 checks to the same person, on the same day, for the same amount but forgot to enter one in the register.
when i run the update statement:
update b set b.bankid=c.myid
from checks c
join bank b on c.cdate=b.cdate
and c.payee=b.payee
and c.cost=b.cost
Both bank statement records would be updated to my one check record [can't happen]
Also: this will be running on a hundred thousand records per month with potential for duplication/ommission on either side.
What's a poor newbie missing??
I'm doing something similar on a lesser volume by running sequential statements through an ASP script but performance is poor. I know SQL can do this, just not how to approach it.
Thanks for any guidance
Dale
View 4 Replies
View Related
Nov 21, 2015
The benifit of UPDLOCK is that it avoids deadlock in case both sessions run the below query at the same time.The table has clustered index on ID column
----session 1 --------
begin transaction
select * from a1
update a1
set id = 22
where id = 2
----session 2 --------
begin transaction
select * from a1
update a1
set id = 22
where id = 2
Now to avoid deadlock in the above scenario we should use (UPDLOCK) hint in the select statement.Now my question is that deadlock will be avoided in this case when both the sessions use UPDLOCK hint. If only one session uses UPDLOCk and other does not then there will be deadlock .For example session 1 uses UPDLOCK hint this will hold the U lock on the row, but the session 2 does not use this hint and apply shared lock on the same row. Now there will be deadlock when session 1 tries to update the record and is blocked by shared locks of session 2. same will be the case with session 2 and both will wait for each other and hence dead lock.so what steps can be taken to avoid deadlocks in this case. I do not want to use Snapshot isolation.
View 3 Replies
View Related
Aug 12, 2014
join three tables and wont be duplicate records.
I have tried and attached the computed results and also expecting results.
IF OBJECT_ID('tempdb..#tmpExam1')IS NOT NULL DROP TABLE #tmpExam1
IF OBJECT_ID('tempdb..#tmpExam2')IS NOT NULL DROP TABLE #tmpExam2
IF OBJECT_ID('tempdb..#tmpExam3')IS NOT NULL DROP TABLE #tmpExam3
[Code]....
View 4 Replies
View Related
Mar 9, 2015
I have this statement:
SELECT top 100 P.LastName ,
P.FirstName,
P.MiddleName,
P.PractitionerTypeID,
P.SuffixID,
A.Linenumber1,
A.LineNumber2,
Z.City,
[code]....
Seems like no matter which join type I choose i still get duplicates.
View 5 Replies
View Related
Jan 1, 2007
I have a table named "AUTHOR" with columns (name, citizenship, birth_year). I want to select all available pairs of authors (e.g. author1 author2) WITHOUT the reversed pairs (e.g. author2 author1). I am allowed only to use select (maybe a select is in an other select command).
Can anyone help me please??
View 4 Replies
View Related
Aug 3, 2006
I am writing a script to create a audit trigger on any table. I am getting duplicate rows inserted into my audit table, only for the primary key columns. Anybody see why?
Right now I am debugging an Insert, so I think you can ignore the "U" update part of the Where clause.
....starts with other code to determine columns and primary key fields for selected table....
--get number of columns
select
@rowId = min(RowId),
@MaxRowId = max(RowId)
from #tblFieldNames
-- Loop through fields and build Sql string
while @RowId <= @MaxRowId
BEGIN
SELECT @fieldname = colName FROM #tblFieldNames WHERE RowId = @RowId
SELECT @sql = 'insert tblAuditAdmin (TableAltered, [Action], FieldName, OldValue, NewValue, UpdateDate, UpdateNumber, UserName)'
SELECT @sql = @sql + ' select distinct''' + @TableName + ''''
SELECT @sql = @sql + ',''' + @TriggerType + ''''
SELECT @sql = @sql + ',''' + @fieldname + ''''
SELECT @sql = @sql + ',convert(varchar(1000),d.' + @fieldname + ')'
SELECT @sql = @sql + ',convert(varchar(1000),i.' + @fieldname + ')'
SELECT @sql = @sql + ',''' + @UpdateDate + ''''
SELECT @sql = @sql + ', 1'
SELECT @sql = @sql + ',''' + @UserName + ''''
SELECT @sql = @sql + ' FROM #ins i FULL OUTER JOIN #del d'
SELECT @sql = @sql + @pkJoinClause
SELECT @sql = @sql + ' WHERE (''' + @TriggerType + ''' = ''I'')'
SELECT @sql = @sql + ' OR (''' + @TriggerType + ''' = ''D'')'
SELECT @sql = @sql + ' OR (''' + @TriggerType + ''' = ''U'' AND '
SELECT @sql = @sql + '((i.' + @fieldname + ' <> d.' + @fieldname + ')'
SELECT @sql = @sql + ' OR (''' + @fieldname + ''' in (Select colName from #primaryKeyFields))'
SELECT @sql = @sql + ' OR (i.' + @fieldname + ' IS NULL AND d.' + @fieldname + ' is NOT null)'
SELECT @sql = @sql + ' OR (i.' + @fieldname + ' IS NOT NULL AND d.' + @fieldname + ' is null)))'
EXEC (@sql)
set @RowId = @RowId + 1
END
View 5 Replies
View Related
Oct 27, 2015
I have a table that has Serial numbers and MSDSID numbers and many other fields in the table.
What I need to figure out is if the table contains a distinct Serial number with different MSDSIDs.
So If I have in the table
Serial MSDSID Date Size...
001 20 1/1/2015 5
002 21 1/1/2015 3
001 22 2/1/2015 1
003 21 3/1/2015 1
004 23 1/15/2015 5
003 22 1/20/2015 6
004 23 2/21/2015 5
002 21 4/25/2015 4
I would like the results to show:
Serial Count
001 2 the count is 2 because Serial 001 has an MSDSID of 20 and 22
002 1 the count is 1 because Serial 002 only has MSDSID 21
003 2 the count is 2 because Serial 003 has an MSDSID of 21 and 22
004 1 the count is 1 because Serial 002 only has MSDSID 23
It would be even better if the results just showed where the count is greater than 1.
View 5 Replies
View Related
May 6, 2015
I'm trying to pull records from a source/staging table where there is a duplicate row in it.I don't need that as the requirement is to garbage in /garbage out.when I do that from mart and use joins btw fact and dimensions, Im not getting this duplicate record as Im using distinct/group by. If I removed it, then it returns more than 3000 rows which is not correct. Is there a way I can keep these duplicates without removing group by...Im using correct joins and filters.
View 5 Replies
View Related
Sep 9, 2004
Hello All,
We all were new at one point.... any help is appreciated.
Objective:
Combining two 49,000 row tables and remove records where there is only 1 column difference. (keeping the specified column value removing the one with a blank.)
Reason:
I have 2 people going through a list, coding a specific column with a single letter value. They both have different progress on each sheet. Hence I am trying to UNION them and have a result of their combined efforts without duplicates.
My progress/where I'm stuck:
Here is my first query/union:
SELECT * FROM [Eds table]
UNION SELECT * FROM [Vickis table];
As shown above, I have unioned these 2 tables and my results removed th obvious whole record duplicates, but since 1 column is different on these, a union without criteria considers them unique.....
an example of duplicates that I must remove are as follows:
142301 - Product 5000 - 150# - S (Keep)
142031 - Product 5000 - 150# - "" <--- Blank (Remove)
I am trying to run another query on my first query results so I don't mess my first query up. Here it is:
SELECT DISTINCT [Prod #], [Prod Name], [Prod Description], [Product Type]
FROM [Combined Tables]
WHERE [Product Type]<>" ";
Please Help! Thank you in advance.
--------------------
5 minutes away from pulling my last one!
BaldNAskewed
View 7 Replies
View Related
Oct 8, 2015
I was writing a query using both left outer join and inner join. And the query was ....
SELECT
S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
INNER JOIN Production.Categories AS C
[code]....
However ,the result that i got was correct.But when i did the same query using the left outer join in both the cases
i.e..
SELECT
S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
LEFT OUTER JOIN Production.Categories AS C
ON C.categoryid = P.categoryid)
ON
S.supplierid = P.supplierid
WHERE
S.country = N'Japan';
The result i got was same,i.e
supplier country productid productname unitprice categorynameSupplier QOVFD Japan 9 Product AOZBW 97.00 Meat/PoultrySupplier QOVFD Japan 10 Product YHXGE 31.00 SeafoodSupplier QOVFD Japan 74 Product BKAZJ 10.00 ProduceSupplier QWUSF Japan 13 Product POXFU 6.00 SeafoodSupplier QWUSF Japan 14 Product PWCJB 23.25 ProduceSupplier QWUSF Japan 15 Product KSZOI 15.50 CondimentsSupplier XYZ Japan NULL NULL NULL NULLSupplier XYZ Japan NULL NULL NULL NULL
and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.
View 5 Replies
View Related
Oct 26, 2015
I have a table that exists of Serial, MSDSID and other fields.
What I need to check is to see if there are Serial numbers that multiple MSDSID numbers.
So if I have
Serial MSDSID
001 23
002 24
003 25
004 23
005 26
006 24
The results would be
Serial MSDSID Count
001 23 2
004 23 2
002 24 2
006 24 2
View 12 Replies
View Related
Sep 1, 2015
I have table with columns as ID, DupeID1, DupeID2. ID column is unique. DupeID1 and DupeID2 -- the combination should only be there once. I don't want reverse combination of duplicates, i.e. DupeID2, DupeID1 in the table. How can I delete the reverse duplicates from this table?
View 10 Replies
View Related
Jun 2, 2015
I have an existing stored table with duplicate rows that I want to delete.Using a cte gives me
WITH CTE AS
(
SELECT rn = ROW_NUMBER()
OVER(
PARTITION BY employeeid, dateofincident, typeid, description
ORDER BY Id ASC), *
FROM dbo.TableName
)
DELETE FROM cte
WHERE rn > 1
This is what I want to do basically. But this is only deleting in my CTE, is there anyway I can update my existing table "TableName" with this, without using temp tables?
View 4 Replies
View Related
Jul 27, 2015
How would you differentiate duplicates in a table without deleting.
I have a table name ANGEL that contains duplicate value and i want to append a letter V to the duplicates instead of deleting them.
View 5 Replies
View Related
Nov 18, 2015
I need to make a selection on join datasets with 2 conditions and populate the results in another dataset(Report).It is working with the fist condition "AccountingTypeCharacteristicCodeId = 3"...
INSERT INTO SurveyInterface.tblLoadISFNotification (OperatingEntityNumber, SDDS, SurveyCodeId, QuestionnaireTypeCodeId, ReferencePeriod, DataReplacementIndicator, PrecontactFlag, SampledUnitPriority)
SELECT ISF.OperatingEntityNumber
,[SDDS]
,[SurveyCodeId]
,[QuestionnaireTypeCodeId]
,[ReferencePeriod]
,[DataReplacementIndicator]
[code]....
Know I also want to add in that new dataset(report) all the duplicates of concatenated variables
ISF.OperatingEntityNumber/ISF.QuestionnaireTypeCodeId
GROUP BY ISF.OperatingEntityNumber, ISF.QuestionnaireTypeCodeId
View 4 Replies
View Related
Oct 22, 2015
I have a job which executes hourly.
Essentially:
Begin
Truncate table A
Insert into A
(Col1,
Col2,
Col3...
)
Select Value1,
Value2,
Value3...
From Table B
End
The insert operation query takes approximately 3.5 minutes to execute. What's occurring is the Table is immediately truncated, and there are no rows in the table for those 3.5 minutes.
How can I avoid having this gap - where there are no rows in the table for that period of time during the job execution ? The table could be locked, but that doesn't seem like the best solution.
View 8 Replies
View Related
Nov 20, 2015
I have this 40,000,000 rows table... I am trying to clean this 'Contacts' table since I know there are a lot of duplicates.
At first, I wanted to get a count of how many there are.
I need to compare records where these fields are matched:
MATCHED: (email, firstname) but not MATCH: (lastname, phone, mobile).
MATCHED: (email, firstname, mobile)
But not MATCH: (lastname, phone)
MATCHED: (email, firstname, lastname)
But not MATCH: (phone, mobile)
View 9 Replies
View Related
Oct 22, 2015
Got a query taking too much time because of lack of cross columns MAX/MIN functions. Consider a similar example where a View is required to reflect distribution of Water among different towns each having four different levels of distribution reservoir tanks of different sizes:In this case the basic table has columns like:
PurchaseDate
TownName
QuantityPurchased
Tank1_Size
Tank2_Size
Tank3_Size
Tank4_Size
Now suppose I need a query to distribute QuantityPurchased in the Four additional Columns computed on the basis depending on the sizes declared in the last four fields,in the same order of preference.For example: I have to use IIFs to check: whether the quantity purchased is less than Tank_A if yes then Qty Purchased otherwise Tank_A_Size itself for Tank_A_Filled
then again IIF but this time to check:
Whether the quantity purchased less Tank_A_Filled (Which again needs to be calculated as above) is less than Tank_B if yes then Tank_A_Filled (Which again needs to be calculated as above) otherwise Tank_B_Size itself for Tank_B_Filled
View 2 Replies
View Related
Nov 19, 2015
There are 3 tables Property , PropertyExternalReference , PropertyAssesmentValuation which are common for 60 business rule
SELECT
PE.PropertyExternalReferenceValue [BAReferenceNumber]
, PA.DescriptionCode
[PSDCode]
, PV.ValuationEffectiveDate
[EffectiveDate]
, PV.PropertyListAlterationDate
[ListAlterationDate]
[code]....
Can we push the data for the above query in a physical table and create index to make the query fast rather than using the same set tables multiple times
View 11 Replies
View Related
Oct 22, 2014
I have a table with 22 million Business records. I can see that there are duplicates when I group by BusinessName and Address and Phone. I'd like to place only the duplicates into a table, with a ranking, oldest business key gets a ranking of 1.
As a bonus I'd like each group to have a distinct group name (although not necessary, just want to know how to do this)
Later after I run more verifications to make sure these are not referenced elsewhere I'll delete everything with a matchRank > 1 out of the main Business table.
DROP TABLE [dbo].[TestBusiness];
GO
CREATE TABLE [dbo].[TestBusiness](
[Business_pk] INT IDENTITY(1,1) NOT NULL,
[BusinessName] VARCHAR (200) NOT NULL,
[Address] VARCHAR(MAX) NOT NULL,
[code]....
View 9 Replies
View Related
Oct 1, 2015
I have 2 tables:
create table #InsuredLoc (
PolGK int,
InsuredLocGK varchar(20),
InsuredLocEffDt datetime,
InsuredLocType varchar(20),
InsuredLocStatus varchar(20),
InsuredLocAddress varchar(50),
PolEdrGk varchar(20),
[Code] ...
I will need to join the #InsuredLoc table to the #PolicyEndorsement table using PolGk and PolEdrGk and get the min(BkgDt) and min(PolEdrRowUpdateDt) for the distinct list of InsuredLocType, InsuredLocStatus, and InsuredLocAddress fields from the #InsuredLoc table above.
I will also need the min(InsuredLocEffDt) and the max(InsuredLocUpdateDt) from the #InsuredLoc table for those records. So after the first run, i should get the following:
I tried to use CTE's with ranking, but some records are dropping off and I'm not sure why.
View 6 Replies
View Related
Jan 26, 2015
Is there a query or a way to convert duplicates value in a column to non duplicates.
View 14 Replies
View Related
