April 26, 2016 at 3:19 am
Hi ,
When i run this query i am getting only one record .The one with the comented policy id DLKB6663 alone comes here . I have almost 1o lakhs of records . How to make all the records to be displayed by satisfying the below conditons . As outer apply is causing a performace issue i tried this but not able to find the root cause why it fetches one record only . Could you please help how to get all the records to be displayed . Your help is highly appreciated .
SELECT DISTINCT
dbo.Systems.SiteNumber,
dbo.PolicyAudit.ID,
dbo.PolicyAudit.PolicyID,
CONVERT(VARCHAR(8), dbo.PolicyAudit.LastModifiedDate, 112),
dbo.PolicyAudit.CoverStartDate,
dbo.PolicyAudit.EmployerName,
dbo.PolicyAudit.Branch,
dbo.PolicyAudit.Workgroup,
dbo.PolicyAudit.ProductCode,
dbo.PolicyAudit.Lob,
DATEDIFF(day,dbo.PolicyAudit.CoverStartDate,GETDATE()),
dbo.PolicyAudit.ERNExemptFlag
FROM
dbo.Systems RIGHT OUTER JOIN dbo.PolicyAudit ON (dbo.PolicyAudit.SourceSystemID=dbo.Systems.ID)
INNER JOIN (
SELECT Top 1 pa.HasERN as HasERN ,
pa.id as id,
pa.PolicyID as PolicyID ,
pa.LastModifiedDate as DateSent ,
ERNExemptFlag as ERNExemptFlag
FROM PolicyAudit pa
where pa.Status=1
Order By pa.ID desc
) Derived_Table3 ON (Derived_Table3.id=dbo.PolicyAudit.ID and Derived_Table3.PolicyID=dbo.PolicyAudit.PolicyID and Derived_Table3.DateSent=dbo.PolicyAudit.LastModifiedDate)
WHERE
(
dbo.PolicyAudit.Status = 1
AND
dbo.PolicyAudit.HasERN = 0
AND
CASE WHEN dbo.Systems.FileTypeIndicator= 'C' THEN 'Current' WHEN dbo.Systems.FileTypeIndicator= 'H' THEN 'Historic' WHEN dbo.Systems.FileTypeIndicator= 'V' THEN 'Voidance' ELSE '' END IN ( 'Current','Voidance' )
AND
Derived_Table3.HasERN IN ( 0 )
--AND
--dbo.PolicyAudit.PolicyID='DLKB6663'
)
April 26, 2016 at 3:59 am
Try this alternative to your query. The significant change is removing the right join. Most folks avoid them - a mix of right joins and left joins in the same query is a real head-scratcher. Settle for left joins and adjust your query to accommodate the change.
SELECT --DISTINCT
s.SiteNumber,
p.ID,
p.PolicyID,
CONVERT(VARCHAR(8), p.LastModifiedDate, 112),
p.CoverStartDate,
p.EmployerName,
p.Branch,
p.Workgroup,
p.ProductCode,
p.Lob,
DATEDIFF(day,p.CoverStartDate,GETDATE()),
p.ERNExemptFlag
FROM dbo.PolicyAudit p
LEFT JOIN dbo.Systems s
ON p.SourceSystemID = s.ID
INNER JOIN (
SELECT Top 1
pa.HasERN as HasERN ,
pa.id as id,
pa.PolicyID as PolicyID ,
pa.LastModifiedDate as DateSent ,
ERNExemptFlag as ERNExemptFlag
FROM PolicyAudit pa
where pa.Status = 1
Order By pa.ID desc
) d
ON d.id = p.ID
and d.PolicyID = p.PolicyID
and d.DateSent = p.LastModifiedDate
WHERE p.Status = 1
AND p.HasERN = 0
AND s.FileTypeIndicator IN ('C','V') -- 'Current', 'Voidance'
AND d.HasERN = 0
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 26, 2016 at 4:36 am
Thanks for your info . When i just run the query i am still getting one record alone . Why i am not getting all the policy records in the table . I really dont know why it happens ? Is any key work i need to input to fetch all the poicy records.
April 26, 2016 at 4:43 am
mbavabohrude (4/26/2016)
Thanks for your info . When i just run the query i am still getting one record alone . Why i am not getting all the policy records in the table . I really dont know why it happens ? Is any key work i need to input to fetch all the poicy records.
There are several filters in the WHERE clause of your query. One of them is this
AND s.FileTypeIndicator IN ('C','V') -- 'Current', 'Voidance'
move it from the WHERE clause into the join for the System table, like this:
LEFT JOIN dbo.Systems s
ON p.SourceSystemID = s.ID
AND s.FileTypeIndicator IN ('C','V') -- 'Current', 'Voidance'
and try again
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 26, 2016 at 4:45 am
To add with it is taking the inner query output (policy id) and returing the one row only . I really dont understand why the other policyid is coming into picture .Could you please make me to understand better .
April 26, 2016 at 4:47 am
I assume that the ID columns in PolicyAudit and Systems are unique? If so, you're only going to get one row because you're joining on them to the single-row result set of a subquery. Incidentally, the third line of your WHERE clause turns your LEFT JOIN into an INNER JOIN.
John
April 26, 2016 at 4:53 am
Comment out the derived table, then selectively remove and restore the filters from the WHERE clause. Observe what happens. How many rows are in table dbo.PolicyAudit with p.Status = 1 and p.HasERN = 0?
SELECT --DISTINCT
s.SiteNumber,
p.ID,
p.PolicyID,
CONVERT(VARCHAR(8), p.LastModifiedDate, 112),
p.CoverStartDate,
p.EmployerName,
p.Branch,
p.Workgroup,
p.ProductCode,
p.Lob,
DATEDIFF(day,p.CoverStartDate,GETDATE()),
p.ERNExemptFlag
FROM dbo.PolicyAudit p
LEFT JOIN dbo.Systems s
ON p.SourceSystemID = s.ID
AND s.FileTypeIndicator IN ('C','V') -- 'Current', 'Voidance'
/*
INNER JOIN (
SELECT Top 1
--pa.HasERN as HasERN ,
pa.id as id,
pa.PolicyID as PolicyID ,
pa.LastModifiedDate as DateSent--,
-- ERNExemptFlag as ERNExemptFlag
FROM PolicyAudit pa
where pa.Status = 1
AND d.HasERN = 0
Order By pa.ID desc
) d
ON d.id = p.ID
and d.PolicyID = p.PolicyID
and d.DateSent = p.LastModifiedDate
*/
WHERE p.Status = 1
AND p.HasERN = 0
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 26, 2016 at 5:14 am
I just commented and ran it fetches around 1074733 in 9 mins .When i run the inner query it fetches one row
when i run the inner join seperately
INNER JOIN (
SELECT Top 1
pa.HasERN as HasERN ,
pa.id as id,
pa.PolicyID as PolicyID ,
pa.LastModifiedDate as DateSent ,
ERNExemptFlag as ERNExemptFlag
FROM PolicyAudit pa
where pa.Status = 1
Order By pa.ID desc
) d
it fetches one record and this record details alone is coming and i am totally confused .
April 26, 2016 at 5:32 am
mbavabohrude (4/26/2016)
I just commented and ran it fetches around 1074733 in 9 mins .When i run the inner query it fetches one rowwhen i run the inner join seperately
INNER JOIN (
SELECT Top 1
pa.HasERN as HasERN ,
pa.id as id,
pa.PolicyID as PolicyID ,
pa.LastModifiedDate as DateSent ,
ERNExemptFlag as ERNExemptFlag
FROM PolicyAudit pa
where pa.Status = 1
Order By pa.ID desc
) d
it fetches one record and this record details alone is coming and i am totally confused .
It looks like you're trying to get the "first policy row", but do you want that by ID or by DateSent? It's not much of a change to get this to work.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 26, 2016 at 5:40 am
By id i am testing the latest record and testing the condition . It is actually a two procedure that needs to be combine to get the single query output and find below the details
PROCEDURE [dbo].[spERNExceptionReport]
@Month int, -- Should be a value from 1 to 12 (Not being used now)
@Year int -- Should be 4 digit year number (Not being used now)
AS
BEGIN
SET NOCOUNT ON;
SELECT Distinct Systems.SiteNumber AS 'SiteNumber',
PolicyAudit.PolicyID AS 'PolicyID',
PolicyAudit.ERNExemptFlag AS 'ERNExemptFlag',
CONVERT(VARCHAR(8), PolicyAudit.LastModifiedDate, 112) AS 'DateSent'
FROM Systems
LEFT JOIN PolicyAudit ON Systems.ID = PolicyAudit.SourceSystemID
WHERE --MONTH(PolicyAudit.LastModifiedDate) = @Month AND
--YEAR(PolicyAudit.LastModifiedDate) = @Year AND
PolicyAudit.Status = 1 AND
PolicyAudit.HasERN = 0 AND
Systems.FileTypeIndicator in ('C','V')
END
We iterate through all the records which we receive from above query. Then for each policy, we fetch “HasERN” field as per following procedure logic
PROCEDURE [dbo].[spHelperProcedureERNExceptionReport]
@PolicyID varchar (30)
AS
BEGIN
SELECT Top(1) [HasERN],
PolicyAudit.PolicyID AS 'PolicyID',
CONVERT(VARCHAR(8), PolicyAudit.LastModifiedDate, 112) AS 'DateSent',
Systems.SiteNumber AS 'SiteNumber',
PolicyAudit.ERNExemptFlag AS 'ERNExemptFlag'
FROM PolicyAudit
Left Join [Systems]
on PolicyAudit.SourceSystemID = [Systems].ID
WHERE rtrim(ltrim(PolicyID)) = @PolicyID
And Systems.FileTypeIndicator in ('C','V')
And [Status] = '1'
Order By PolicyAudit.ID desc
END
Hope i am clear in explaining the details and kindly let me know for any clarifications
April 26, 2016 at 6:11 am
Have you tried this?
SELECT Distinct Systems.SiteNumber AS 'SiteNumber',
PolicyAudit.PolicyID AS 'PolicyID',
PolicyAudit.ERNExemptFlag AS 'ERNExemptFlag',
CONVERT(VARCHAR(8), PolicyAudit.LastModifiedDate, 112) AS 'DateSent'
FROM Systems
LEFT JOIN PolicyAudit ON Systems.ID = PolicyAudit.SourceSystemID
CROSS APPLY (
SELECT Top(1) [HasERN],
PolicyAudit.PolicyID AS 'PolicyID',
CONVERT(VARCHAR(8), PolicyAudit.LastModifiedDate, 112) AS 'DateSent',
Systems.SiteNumber AS 'SiteNumber',
PolicyAudit.ERNExemptFlag AS 'ERNExemptFlag'
FROM PolicyAudit
Left Join [Systems]
on PolicyAudit.SourceSystemID = [Systems].ID
WHERE rtrim(ltrim(PolicyID)) = PolicyAudit.PolicyID
And Systems.FileTypeIndicator in ('C','V')
And [Status] = '1'
Order By PolicyAudit.ID desc
) x
WHERE --MONTH(PolicyAudit.LastModifiedDate) = @Month AND
--YEAR(PolicyAudit.LastModifiedDate) = @Year AND
PolicyAudit.Status = 1 AND
PolicyAudit.HasERN = 0 AND
Systems.FileTypeIndicator in ('C','V')
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 26, 2016 at 6:43 am
If CROSS APPLY option is too slow (as I understood from the previous post) you could try this one:
SELECT DISTINCT
S.SiteNumber,
PA.ID,
PA.PolicyID,
CONVERT(VARCHAR(8), PA.LastModifiedDate, 112),
PA.CoverStartDate,
PA.EmployerName,
PA.Branch,
PA.Workgroup,
PA.ProductCode,
PA.Lob,
DATEDIFF(day,PA.CoverStartDate,GETDATE()),
PA.ERNExemptFlag
FROM dbo.Systems S
RIGHT OUTER JOIN dbo.PolicyAudit PA ON PA.SourceSystemID=S.ID
INNER JOIN (
SELECT ROW_NUMBER() OVER (PARTITION BY ipa.ID, ipa.PolicyID, ipa.LastModifiedDate ORDER BY ipa.ID DESC) AS rn,
ipa.HasERN as HasERN ,
ipa.id as id,
ipa.PolicyID as PolicyID ,
ipa.LastModifiedDate as DateSent ,
ERNExemptFlag as ERNExemptFlag
FROM PolicyAudit ipa
where ipa.Status=1 ) DT3 ON DT3.id=PA.ID
AND DT3.PolicyID=PA.PolicyID
AND DT3.DateSent=PA.LastModifiedDate
AND DT3.rn = 1
WHERE PA.Status = 1
AND PA.HasERN = 0
AND S.FileTypeIndicator IN( 'C', 'V')
AND DT3.HasERN IN ( 0 )
--AND PA.PolicyID='DLKB6663'
;
Any reason for using DISTINCT?
If the problems continue, post DDL for tables and indexes. Also post actual execution plan.
April 26, 2016 at 9:29 am
INNER JOIN (
SELECT Top 1
that will return one , single row, not one row per policy number, right?
so the other table is limited to match one row as well.
maybe you need to use something like row_number, and join on the "latest" record instead?
Lowell
April 26, 2016 at 9:53 am
I bet I'm not the only one around here who sits on the next potential iteration of the OP's query waiting for responses before posting. Here's one of those:
SELECT --DISTINCT
s.SiteNumber AS [SiteNumber],
p.PolicyID AS [PolicyID],
p.ERNExemptFlag AS [ERNExemptFlag],
CONVERT(VARCHAR(8), p.LastModifiedDate, 112) AS [DateSent]
FROM Systems s
INNER JOIN PolicyAudit p
ON s.ID = p.SourceSystemID
CROSS APPLY (
SELECT TOP(1)
pa.[HasERN]
FROM PolicyAudit pa
INNER JOIN [Systems] si
ON pa.SourceSystemID = si.ID
WHERE pa.PolicyID = p.PolicyID -- outer reference
AND si.FileTypeIndicator IN ('C','V')
AND pa.[Status] = '1'
ORDER BY pa.ID DESC
) x
WHERE --MONTH(PolicyAudit.LastModifiedDate) = @Month AND
--YEAR(PolicyAudit.LastModifiedDate) = @Year AND
p.[Status] = 1
AND p.HasERN = 0
AND s.FileTypeIndicator IN ('C','V')
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 27, 2016 at 3:14 am
Is it not possible to rank over the table once, and get everything you ever need from that table, instead of PolicyAudit self join (select top 1 PolicyAudit )?
Top sets a row goal so could force the optimiser into doing silly things.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply