Modify my SQL Server query -- returns too many rows sometimes
I need to update the following query so that it only returns one child record (remittance) per parent (claim).
Table Remit_To_Activate contains exactly one date/timestamp per claim, which is what I wanted.
But when I join the full Remittance table to it, since some claims have multiple remittances with the same date/timestamps, the outermost query returns more than 1 row per claim for those claim IDs.
SELECT * FROM REMITTANCE WHERE BILLED_AMOUNT>0 AND ACTIVE=0 AND REMITTANCE_UUID IN ( SELECT REMITTANCE_UUID FROM Claims_Group2 G2 INNER JOIN Remit_To_Activate t ON ( (t.ClaimID = G2.CLAIM_ID) AND (t.DATE_OF_LATEST_REGULAR_REMIT = G2.CREATE_DATETIME) ) where ACTIVE=0 and BILLED_AMOUNT>0 )
I believe the problem would be resolved if I included REMITTANCE_UUID as a column in Remit_To_Activate. That's the REAL issue. This is how I created the Remit_To_Activate table (trying to get the most recent remittance for a claim):
SELECT MAX(create_datetime) as DATE_OF_LATEST_REMIT, MAX(claim_id) AS ClaimID, INTO Latest_Remit_To_Activate FROM Claims_Group2 WHERE BILLED_AMOUNT>0 GROUP BY Claim_ID ORDER BY Claim_ID
Claims_Group2 contains these fields: REMITTANCE_UUID, CLAIM_ID, BILLED_AMOUNT, CREATE_DATETIME
Here are the 2 rows that are currently giving me the problem--they're both remitts for the SAME CLAIM, with the SAME TIMESTAMP. I only want one of them in the Remits_To_Activate table, so only ONE remittance will be "activated" per Claim: enter image description here
You can change your query like this:
SELECT p.*, latest_remit.DATE_OF_LATEST_REMIT FROM Remittance AS p inner join (SELECT MAX(create_datetime) as DATE_OF_LATEST_REMIT, claim_id, FROM Claims_Group2 WHERE BILLED_AMOUNT>0 GROUP BY Claim_ID ORDER BY Claim_ID) as latest_remit on latest_remit.claim_id = p.claim_id;
This will give you only one row. Untested (so please run and make changes).
4. Query Performance Optimization, Modify my SQL Server query — returns too many rows sometimes Here are the 2 rows that are currently giving me the problem--they're both When working with databases, sometimes you want to find out how many rows will be returned by a query, without actually returning the results of the query. Or sometimes you might just want to find out how many rows are in a given table. In SQL Server, you can use T-SQL ‘s COUNT () function to return the number of rows that would be returned
Without having more information on the structure of your database -- especially the structure of
REMITTANCE, and the relationship between them, it's not really possible to advise you on how to introduce a remittance UUID into
Since you are using SQL Server, however, it is possible to use a window function to introduce a synthetic means to choose among remittances having the same timestamp. For example, it looks like you could approach the problem something like this:
select * from ( select r.*, row_number() over (partition by cg2.claim_id order by cg2.create_datetime desc) as rn from remittance r join claims_group2 cg2 on r.remittance_uuid = cg2.remittance_uuid where r.active = 0 and r.billed_amount > 0 and cg2.active = 0 and cg2.billed_amount > 0 ) t where t.rn = 1
Note that that that does not depend on your
DATE_OF_LATEST_REMIT table at all, it having been subsumed into the inline view. Note also that this will introduce one extra column into your results, though you could avoid that by enumerating the columns of table
remittance in the outer
It also seems odd to be filtering on two sets of
billed_amount columns, but that appears to follow from what you were doing in your original queries. In that vein, I urge you to check the results carefully, as lifting the filter conditions on
cg2 columns up to the level of the join to
remittance yields a result that may return rows that the original query did not (but never more than one per
Top 5 Reasons for Wrong Results in SQL Server, We then dig much deeper into query optimization and server internals. how MySQL executes a particular query, and you'll learn how to change the query execution plan. Beware of placing too much emphasis on query execution time. This query will return 10 rows, and EXPLAIN shows that MySQL uses Note that even though SQL Server auto-parameterized the query (that’s the @1 part at the top), SQL Server chose a different execution plan. This time, the actual plan shows that SQL Server expected 3mm rows to come back – so here, it makes more sense to do a clustered index scan rather than first make a list of the users that match, then do 3mm key lookups to get the SELECT * part.
A co-worker offered me this elegant demonstration of a solution. I'd never used "over" or "partition" before. Works great! Thank you John and Gaurasvsa for your input.
if OBJECT_ID('tempdb..#t') is not null drop table #t select *, ROW_NUMBER() over (partition by CLAIM_ID order by CLAIM_ID) as ROW_NUM into #t from ( select '2018-08-15 13:07:50.933' as CREATE_DATE, 1 as CLAIM_ID, NEWID() as REMIT_UUID union select '2018-08-15 13:07:50.933', 1, NEWID() union select '2017-12-31 10:00:00.000', 2, NEWID() ) x select * from #t order by CLAIM_ID, ROW_NUM select CREATE_DATE, MAX(CLAIM_ID), MAX(REMIT_UUID) from #t where ROW_NUM = 1 group by CREATE_DATE
More efficient SQL with query planning and optimization (article , Many people have come across situations where their queries are returning SQL queries are often constructed within application code or The following can never return the wrong results (e.g. will always return all LastName FROM dbo.x WHERE LastName = @ln_varchar; -- 0 rows View all my tips. Holger Schmeling is a SQL Server Consultant based in Munich, Germany. He has been working with SQL Server since 1996 (Version 6.5) and has used it in many projects as a developer, administrator, database architect and designer. Occasionally he also gives classes on SQL Server development, administration and tuning.
Ten Common SQL Programming Mistakes, Do a "full table scan": look at every single row in the table, return the matching rows. If we were executing that query many times (more than 23 times) or if we Sometimes, you might discover a badly performing query after it takes so long that it into different SQL query planners that I found interesting: SQL Server Query The SQL Server plan cache is the receptacle in which the plan from the output of the query optimizer will be placed. In SQL Server 2005 and new versions, the Dynamic Management View named sys.dm_exec_cached_plans exists to allow the DBA to interrogate the plan cache and pull relevant statistics from it to illuminate the patterns in which plans
Why MySQL Could Be Slow With Large Tables?, The examples are presented using SQL Server's Transact-SQL dialect, but You may have been expecting this query to return two rows (for 'black' and This mistake will often surface if requirements change, and a Some people seem to like them so much that they throw one into almost every query, SQL Server performs sort, intersect, union, and difference operations using in-memory sorting and hash join technology. Using this type of query plan, SQL Server supports vertical table partitioning, sometimes called columnar storage. SQL Server employs four types of join operations: Nested Loops joins. Adaptive joins (Starting with SQL Server
Performance Tuning SQL Queries, Peter Zaitsev explains how to optimize your MySQL table design MySQL has installations with many billions of rows and delivers great +------------+ So if you're dealing with large data sets and complex queries here are few tips. are often faster than range scans and other types of index lookups. What is a SQL Server Cursor. A SQL Server cursor is a set of T-SQL logic to loop over a predetermined number of rows one at a time. The purpose for the cursor may be to update one row at a time or perform an administrative process such as SQL Server database backups in a sequential manner.