April 21, 2016 at 3:49 am
The requirement for report was to display the Policy as per below logic.
PROCEDURE [dbo].[spExceptionReport]
@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 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
If the Policy’s HasERN status comes out as false =0 then we display it in the report.
Could you please throw some light on this .
April 21, 2016 at 6:51 am
So much looks wrong with so little...
From what I can see:
- In your first procedure, Change to "GROUP BY", and return MAX( PolicyAudit.LastModifiedDate ) - made assumption that LastModifiedDate increases with PolicyAudit.ID
- DROP your second procedure and stop referencing it in your report generation code.
Logic is duplicated, so you'll be fine...just assume all records output need to be displayed on your report.
Might want to clean up your code too...no need for "LEFT JOIN", get rid of pars, change procedure name etc.
e.g.
CREATE PROCEDURE [dbo].[proc_ExceptionReport]
AS
BEGIN
SET NOCOUNT ON;
SELECT
S.SiteNumber, PA.PolicyID, PA.ERNExemptFlag,
CONVERT(VARCHAR(8), MAX(PA.LastModifiedDate, 112)) AS DateSent
FROM
dbo.Systems S
INNER JOIN dbo.PolicyAudit ON PA.SourceSystemID = S.ID
WHERE
S.FileTypeIndicator in ('C','V')
AND PA.Status = 1
AND PA.HasERN = 0
GROUP BY
S.SiteNumber,
PA.PolicyID,
PA.ERNExemptFlag,
END
April 21, 2016 at 6:59 am
Hi ,
Thanks for your response . It should be single query and not procedure .When i ran the query for one record i am getting below error
Msg 174, Level 15, State 1, Line 3
The MAX function requires 1 argument(s).
SELECT
S.SiteNumber, PA.PolicyID, PA.ERNExemptFlag,
CONVERT(VARCHAR(8), MAX(PA.LastModifiedDate, 112)) AS DateSent
FROM
dbo.Systems S
INNER JOIN dbo.PolicyAudit ON PA.SourceSystemID = S.ID
WHERE
S.FileTypeIndicator in ('C','V')
AND PA.Status = 1
AND PA.HasERN = 0
and dbo.PolicyAudit.policyid='000000000000/PHARM0000671'
GROUP BY
S.SiteNumber,
PA.PolicyID,
PA.ERNExemptFlag,
April 21, 2016 at 7:16 am
mbavabohrude (4/21/2016)
Hi ,Thanks for your response . It should be single query and not procedure .When i ran the query for one record i am getting below error
Msg 174, Level 15, State 1, Line 3
The MAX function requires 1 argument(s).
SELECT
S.SiteNumber, PA.PolicyID, PA.ERNExemptFlag,
CONVERT(VARCHAR(8), MAX(PA.LastModifiedDate, 112)) AS DateSent
FROM
dbo.Systems S
INNER JOIN dbo.PolicyAudit ON PA.SourceSystemID = S.ID
WHERE
S.FileTypeIndicator in ('C','V')
AND PA.Status = 1
AND PA.HasERN = 0
and dbo.PolicyAudit.policyid='000000000000/PHARM0000671'
GROUP BY
S.SiteNumber,
PA.PolicyID,
PA.ERNExemptFlag,
The fact that you can't debug this simple mistake is a pretty good indication you don't understand this query.
The problem is right here on this line...
CONVERT(VARCHAR(8), MAX(PA.LastModifiedDate, 112)) AS DateSent
Here is a hint....MAX needs a single parameter and CONVERT has 3
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 21, 2016 at 7:16 am
Change "CONVERT(VARCHAR(8), MAX(PA.LastModifiedDate, 112)) AS DateSent"
to "CONVERT(VARCHAR(8), MAX(PA.LastModifiedDate), 112) AS DateSent"
April 22, 2016 at 1:14 am
For the proc logic i have created a query based on the earlier input but the query run for 2.48 mins .
1.How can i optimize this query .
2.Is there any way can i get the same output ?
3. When i tried with the inner join i could not able to reference it find below the error message
SELECT Distinct Systems.SiteNumber AS 'SiteNumber',
PolicyAudit.PolicyID AS 'PolicyID',
PolicyAudit.ERNExemptFlag AS 'ERNExemptFlag',
CONVERT(VARCHAR(8), PolicyAudit.LastModifiedDate, 112) AS 'DateSent'
FROM PolicyAudit
LEFT JOIN Systems ON Systems.ID = PolicyAudit.SourceSystemID
OUTER APPLY (SELECT Top(1) pa.HasERN,
st.SiteNumber,
pa.PolicyID,
CONVERT(VARCHAR(8), pa.LastModifiedDate, 112) as 'DateSent1' ,
ERNExemptFlag
FROM PolicyAudit pa
LEFT JOIN Systems st ON st.ID = pa.SourceSystemID
WHERE rtrim(ltrim(pa.PolicyID)) =PolicyAudit.PolicyID
And pa.Status=1
Order By pa.ID desc ) Derived_Policy_Audit_Latest
WHERE PolicyAudit.Status =1
AND PolicyAudit.HasERN = 0
AND Systems.FileTypeIndicator in ('C','V')
and PolicyAudit.policyid=Derived_Policy_Audit_Latest.policyid
and CONVERT(VARCHAR(8), PolicyAudit.LastModifiedDate, 112)=Derived_Policy_Audit_Latest.DateSent1
and Systems.SiteNumber=Derived_Policy_Audit_Latest.SiteNumber
and Derived_Policy_Audit_Latest.HasERN=0
--and PolicyAudit.policyid='000000000000/PHARM0000671'
Inner Join with the error
SELECT Distinct Systems.SiteNumber AS 'SiteNumber',
PolicyAudit.PolicyID AS 'PolicyID',
PolicyAudit.ERNExemptFlag AS 'ERNExemptFlag',
CONVERT(VARCHAR(8), PolicyAudit.LastModifiedDate, 112) AS 'DateSent'
FROM PolicyAudit
left outer join Systems ON Systems.ID = PolicyAudit.SourceSystemID
inner join (SELECT Top(1) pa.HasERN,
st.SiteNumber,
pa.PolicyID,
CONVERT(VARCHAR(8), pa.LastModifiedDate, 112) as 'DateSent1' ,
ERNExemptFlag
FROM PolicyAudit pa
LEFT JOIN Systems st ON st.ID = pa.SourceSystemID
---throws error in this partWHERE rtrim(ltrim(pa.PolicyID)) =PolicyAudit.PolicyID
And pa.Status=1
Order By pa.ID desc )
Derived_Policy_Audit_Latest on ( PolicyAudit.policyid=Derived_Policy_Audit_Latest.policyid
and CONVERT(VARCHAR(8), PolicyAudit.LastModifiedDate, 112)=Derived_Policy_Audit_Latest.DateSent1
and Systems.SiteNumber=Derived_Policy_Audit_Latest.SiteNumber)
WHERE PolicyAudit.Status =1
AND PolicyAudit.HasERN = 0
AND Systems.FileTypeIndicator in ('C','V')
and Derived_Policy_Audit_Latest.HasERN=0
Msg 4104, Level 16, State 1, Line 14
The multi-part identifier "PolicyAudit.PolicyID" could not be bound.
How to make reference of the outer table in the inner query in the current situation .could you please let me know and your help is appreciated .
April 22, 2016 at 7:42 am
mbavabohrude (4/22/2016)
For the proc logic i have created a query based on the earlier input but the query run for 2.48 mins .1.How can i optimize this query .
We can't begin to help unless you provide a lot more information. Starting with the table and index definitions. Also, posting the actual execution plan would help considerably. Take a look at this article. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]
2.Is there any way can i get the same output ?
Not sure what you mean by this. Are you asking if once it is optimized if the results can be the same? If not then the optimization went horribly wrong. The absolute most important aspect of any query is correct output. Second is performance.
3. When i tried with the inner join i could not able to reference it find below the error message
SELECT Distinct Systems.SiteNumber AS 'SiteNumber',
PolicyAudit.PolicyID AS 'PolicyID',
PolicyAudit.ERNExemptFlag AS 'ERNExemptFlag',
CONVERT(VARCHAR(8), PolicyAudit.LastModifiedDate, 112) AS 'DateSent'
FROM PolicyAudit
LEFT JOIN Systems ON Systems.ID = PolicyAudit.SourceSystemID
OUTER APPLY (SELECT Top(1) pa.HasERN,
st.SiteNumber,
pa.PolicyID,
CONVERT(VARCHAR(8), pa.LastModifiedDate, 112) as 'DateSent1' ,
ERNExemptFlag
FROM PolicyAudit pa
LEFT JOIN Systems st ON st.ID = pa.SourceSystemID
WHERE rtrim(ltrim(pa.PolicyID)) =PolicyAudit.PolicyID
And pa.Status=1
Order By pa.ID desc ) Derived_Policy_Audit_Latest
WHERE PolicyAudit.Status =1
AND PolicyAudit.HasERN = 0
AND Systems.FileTypeIndicator in ('C','V')
and PolicyAudit.policyid=Derived_Policy_Audit_Latest.policyid
and CONVERT(VARCHAR(8), PolicyAudit.LastModifiedDate, 112)=Derived_Policy_Audit_Latest.DateSent1
and Systems.SiteNumber=Derived_Policy_Audit_Latest.SiteNumber
and Derived_Policy_Audit_Latest.HasERN=0
--and PolicyAudit.policyid='000000000000/PHARM0000671'
Inner Join with the error
SELECT Distinct Systems.SiteNumber AS 'SiteNumber',
PolicyAudit.PolicyID AS 'PolicyID',
PolicyAudit.ERNExemptFlag AS 'ERNExemptFlag',
CONVERT(VARCHAR(8), PolicyAudit.LastModifiedDate, 112) AS 'DateSent'
FROM PolicyAudit
left outer join Systems ON Systems.ID = PolicyAudit.SourceSystemID
inner join (SELECT Top(1) pa.HasERN,
st.SiteNumber,
pa.PolicyID,
CONVERT(VARCHAR(8), pa.LastModifiedDate, 112) as 'DateSent1' ,
ERNExemptFlag
FROM PolicyAudit pa
LEFT JOIN Systems st ON st.ID = pa.SourceSystemID
---throws error in this partWHERE rtrim(ltrim(pa.PolicyID)) =PolicyAudit.PolicyID
And pa.Status=1
Order By pa.ID desc )
Derived_Policy_Audit_Latest on ( PolicyAudit.policyid=Derived_Policy_Audit_Latest.policyid
and CONVERT(VARCHAR(8), PolicyAudit.LastModifiedDate, 112)=Derived_Policy_Audit_Latest.DateSent1
and Systems.SiteNumber=Derived_Policy_Audit_Latest.SiteNumber)
WHERE PolicyAudit.Status =1
AND PolicyAudit.HasERN = 0
AND Systems.FileTypeIndicator in ('C','V')
and Derived_Policy_Audit_Latest.HasERN=0
Msg 4104, Level 16, State 1, Line 14
The multi-part identifier "PolicyAudit.PolicyID" could not be bound.
How to make reference of the outer table in the inner query in the current situation .could you please let me know and your help is appreciated .
This last past I just don't understand at all. I can't figure out what part works, what doesn't or what is going on there. Your life would be a lot simpler if you used aliases in your queries.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2016 at 7:55 am
Thanks for your infor . I will post the execution plan when i get the permission .Along with it i will update the table details .
This query works fine for one record when i tested and this Audit table has 14 lac records . For better understanding kindly see my post with details .
I tried for two option one with outer query and i am going with that . When i use the second qurey after debugging the error i am just getting one record . Hence i left to my knowledge . If any suggestion of getting the qurey tuned by
1. What type of indexes need to be added .
2.Any other query approach
Will be useful
Thanks
April 22, 2016 at 8:43 am
mbavabohrude (4/22/2016)
Thanks for your infor . I will post the execution plan when i get the permission .Along with it i will update the table details .
Make sure you include index definitions.
This query works fine for one record when i tested and this Audit table has 14 lac records . For better understanding kindly see my post with details .
I haven't seen a post yet with details.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2016 at 8:50 am
Along with the requests for information, learn about indexes. We can't just say index col1 or col2. Indexing needs to be balanced across your workload. If there are thousands (lac) of records, then you'll need to consider the various queries and their needs.
In general, you want to index the primary and foreign key fields, along with the fields that are included in the WHERE clause, BUT ...
you want to limit to 5-7 indexes on these tables if they change often.
Also, why remove the procedure? It's easier to fix/tune/update over time than a query embedded in your application.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply