Tuesday, June 11, 2013

[T-SQL] Finding duplicate rows with multiple fields for comparison

[T-SQL] Finding duplicate rows with multiple fields for comparison

Finding duplicate rows with multiple fields for comparison

Posted: 11 Jun 2013 01:16 AM PDT

I've taken a look at the scripts and forum posts on the site regarding finding duplicate rows, but I haven't been able to adapt them to my situation. I have data being pulled from Oracle and placed in a table in my sql server db. The table consists of a primary key identity field, a field containing the oracle ID, and 4 fields containing data. I need to find all duplicates, with a duplicate being defined as a match across all 4 data fields. I've tried queries along the following lines, but they don't work. [code="sql"]SELECT MAX(id), MAX(oracle_id), field1, field2, field3, field4FROM oracleRecordsGROUP BY field1, field2, field3, field4 HAVING COUNT(*) > 1[/code]If I understand how this is [i]supposed [/i]to work, only duplicate rows should be returned. Is this the case? I've gotten it to work when comparing only one field, but when I try to compare more than one I break it. Any suggestions?

Hi guys this urgent reg int to varchar conversion

Posted: 10 Jun 2013 12:46 PM PDT

I am a fresher dba i got a task where i need to convert int value to varchar please help me with this in the highlighted value else 0 instead i want 'not enabled' and 'not scheduled' SELECT distinct @@SERVERNAME AS ServerName, CASE WHEN J.Name IS NOT NULL THEN 1 -- job exists ELSE 0 -- Job does not exist END AS IsExisting, CASE J.enabled WHEN 1 Then 1 -- job is enabled ELSE 0 -- instead of 0 i want to return 'not enabled' job does not exist error condition END AS IsEnabled, CASE WHEN ss.enabled IS NOT NULL THEN 1 -- schedule is enabled ELSE 0--instead of 0 i want to return 'not scheduled' END AS IsScheduleEnabled , J.NAME, CASE WHEN MAX(H.RUN_DATE) IS NULL THEN 0 ELSE MAX(h.run_date) END AS LASTRUNDATE ,getdate() as Date FROM MSDB..sysjobs J LEFT JOIN MSDB..sysjobhistory H ON J.job_id = H.job_id LEFT JOIN MSDB..sysjobschedules sjs ON sjs.job_id = J.job_id LEFT JOIN MSDB..sysschedules ss ON ss.schedule_id = sjs.schedule_id LEFT JOIN msdb..sysjobsteps steps on steps.job_id = j.job_id AND steps.step_id = 1 -- we are just checking for job existance here so it won't matter if there are multiple steps where j.name like 'sys%' and (j.enabled=0 or ss.enabled is null or j.name is null or h.run_date is null) GROUP BY J.NAME,J.enabled, SS.enabled,h.run_date

not able to understand that store procedure running recursively or not

Posted: 10 Jun 2013 07:56 PM PDT

create procedure testRecur()BEGIN if call testRecur(); end if; select 'hi'; //This return does not execute anymore even I cant debug that procedure running //recursively or not END

subquery returning more than one value

Posted: 10 Jun 2013 06:24 AM PDT

I have two tables, a and b. I want to select all from table a (except where proc_num is null) and one field from table b when it matches a row in table a. I keep getting the data in the table b field for all the rows in table a. select *.a, b.result from tablea as a left outer join tableb as b on a.id = b.parent_id The result I get are:proc_id dtime proc_num result70360 20130404 3706 positive 63 20080313 0960 positive2956 20080313 null positive59913 20120327 3705 positiveI want to get 3 rows for this. I don't want the row for 2956 to show, I only want the result to be positive for the row with proc_id of 63, the rest should be null. I probably need a subquery but I can't figure out how to make this work. Thanks very much for any ideas.

Pivoting help

Posted: 10 Jun 2013 12:07 PM PDT

Hi,I trying to get the dataset Ptid Test Result Date1 BP Neg 1/1/20131 CG Pos 1/2/2013I want result as Ptiid BP Date CG Date1 Neg 1/1/2013 Pos 1/2/2013

using Correlated subQuery in Join not working

Posted: 10 Jun 2013 07:11 AM PDT

I have a large query that is returning multiple rows that I tracked down to my jornal table.It has 2 (or more) credit entries for some entries and I only want one (doesn't matter which one). So I want to use a "TOP 1" but can't use it directly in my JOIN. I can use a CROSS APPLY but not sure if that is the best way. My query is something like:[code]SELECT *FROM Client scJOIN dbo.JOURNAL jrnlON jrnl.ClientId = sc.ClientIdAND jrnl.DC = 'c'[/code]This will give me back 2 records.I tried to do a correlate subquery, like so:[code]SELECT *FROM dbo.Client scJOIN ( SELECT TOP 1 * FROM dbo.JOURNAL jrnl WHERE jrnl.ClientId= sc.ClientId AND jrnl.DC= 'c') jrnlON jrnl.ClientId= sc.ClientIdWHERE sc.ClientId= 942222[/code]The problem is that the "sc.ClientId" inside the join gets an error:The multi-part Identifier "sc.ClientId" could not be bound.Why is that?I can get this to work using a CROSS APPLY (not sure why) but wanted to use a normal join to solve the issue. This works:[code]SELECT *FROM dbo.Client scCROSS APPLY( SELECT TOP 1 * FROM dbo.JOURNAL jrnl WHERE jrnl.ClientId= sc.ClientId AND jrnl.DC= 'c') jrnlWHERE sc.ClientId= 942222[/code]Why doesn't the normal Join work?Thanks,Tom

More efficient way to iterate through table rows to perform Update

Posted: 10 Jun 2013 05:51 AM PDT

I have a temporary table in SQL Server 2008. I want to perform UPDATE row-by-row.As of now I am using a less efficient way by adding an Id column and then updating the row number. Later, I am running a loop and picking row with Id matching the loop counter.Is there any more efficient way to iterate the table row by row and performing Update operation?I have MERGE in mind, but the problem is that I want to check several conditions before update is performed. With MERGE, I will be bound to use CASE WHEN.There are two tables, #Temp1 and SalesRecord. I want to read from #Temp1 and update SalesRecordBoth tables have few similar columns.[b]#Temp1 (CustId int, AllocVal decimal, RowId)SalesRecord (CustId int , Points decimal)[/b][code]declare @Total as int select @Total = count(*) from #Temp1 declare @counter as int set @counter = 1 declare @CurrentVal as decimal declare @CurrentCustomer as int declare @SumPoints as decimalwhile (@counter <= @Total) begin -- Get row from #Temp1 select @CurrentCustomer = CustId, @CurrentVal = allocVal from #Temp1 where RowId = @counter select @SumPoints = Sum(Points) from SalesRecord where CustId = @CurrentCustomerif ((@CurrentVal + @SumPoints) <= 1) begin update SalesRecord set Points = @CurrentVal end else begin delete from SalesRecord where CustId = @CurrentCustomer end set @counter = @counter + 1 end[/code]

bulk insert not firing triggers ?

Posted: 10 Jun 2013 02:24 AM PDT

hii have table with triggers on the table while bulk inserting it is not firing what is the problem ?with best regardsPradeep
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog