March 28, 2016 at 8:12 am
SELECT
dbo.Systems.SiteNumber,
Derived_Policy_Audit_Latest.PolicyID,
dbo.PolicyAudit.ProductCode,
dbo.PolicyAudit.Lob
FROM
dbo.RejectReason RIGHT OUTER JOIN dbo.PolicyAudit ON (dbo.RejectReason.PolicyAuditID=dbo.PolicyAudit.ID)
LEFT OUTER JOIN dbo.Systems ON (dbo.PolicyAudit.SourceSystemID=dbo.Systems.ID)
INNER JOIN(select dbo.PolicyAudit.policyid as policyid,
dbo.PolicyAudit.SourceSystemID as SourceSystemID,
dbo.Systems.SiteNumber as SiteNumber ,
MAX(dbo.PolicyAudit.LastModifiedDate) as Recdate
from dbo.PolicyAudit INNER JOIN dbo.Systems ON (dbo.PolicyAudit.SourceSystemID=dbo.Systems.ID)
group by policyid , SourceSystemID,dbo.Systems.SiteNumber ) Derived_Policy_Audit_Latest
ON (dbo.PolicyAudit.PolicyID=Derived_Policy_Audit_Latest.policyid and dbo.PolicyAudit.LastModifiedDate = Derived_Policy_Audit_Latest.Recdate and dbo.PolicyAudit.SourceSystemID=Derived_Policy_Audit_Latest.SourceSystemID and Derived_Policy_Audit_Latest.SiteNumber= dbo.Systems.SiteNumber)
WHERE
(
dbo.PolicyAudit.Status IN ( 2 )
AND
dbo.PolicyAudit.IsCurrent IN ( 1 )
AND
)
order by 1
Now i need to include if dbo.Systems.SiteNumber=C1 then it should check and restrict for the below conditiosn
1. dbo.PolicyAudit.productcode ='123' and dbo.PolicyAudit.lob IN ('123')
or
2 .dbo.PolicyAudit.productcode ='127' and dbo.PolicyAudit.lob IN ('54')
etc and it goes of with another 5 similar conditions . How can i incorporte in the where clause to restirct this
March 28, 2016 at 8:39 am
Here is your query again, reformatted to help others:
SELECT dbo.Systems.SiteNumber
, Derived_Policy_Audit_Latest.PolicyID
, dbo.PolicyAudit.ProductCode
, dbo.PolicyAudit.Lob
FROM dbo.RejectReason
RIGHT OUTER JOIN dbo.PolicyAudit ON (dbo.RejectReason.PolicyAuditID = dbo.PolicyAudit.ID)
LEFT OUTER JOIN dbo.Systems ON (dbo.PolicyAudit.SourceSystemID = dbo.Systems.ID)
INNER JOIN (SELECT dbo.PolicyAudit.policyid AS policyid
, dbo.PolicyAudit.SourceSystemID AS SourceSystemID
, dbo.Systems.SiteNumber AS SiteNumber
, max(dbo.PolicyAudit.LastModifiedDate) AS Recdate
FROM dbo.PolicyAudit
INNER JOIN dbo.Systems ON (dbo.PolicyAudit.SourceSystemID = dbo.Systems.ID)
GROUP BY policyid
, SourceSystemID
, dbo.Systems.SiteNumber
) Derived_Policy_Audit_Latest ON (dbo.PolicyAudit.PolicyID = Derived_Policy_Audit_Latest.policyid
AND dbo.PolicyAudit.LastModifiedDate = Derived_Policy_Audit_Latest.Recdate
AND dbo.PolicyAudit.SourceSystemID = Derived_Policy_Audit_Latest.SourceSystemID
AND Derived_Policy_Audit_Latest.SiteNumber = dbo.Systems.SiteNumber
)
WHERE (dbo.PolicyAudit.Status IN (2)
AND dbo.PolicyAudit.IsCurrent IN (1)
AND
)
ORDER BY 1;
Can I suggest that you start using table aliases in all this to reduce the clutter? It's hard to see what's going on.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 28, 2016 at 9:27 am
You can always use a CASE expression, but it sounds like you might have a LOT of cases, so a table value constructor might be a better approach. You basically create a derived table constructed with the VALUES expression and then join to that derived table.
SELECT s.SiteNumber
, dpal.PolicyID
, pa.ProductCode
, pa.Lob
FROM dbo.RejectReason rr
RIGHT OUTER JOIN dbo.PolicyAudit pa ON (rr.PolicyAuditID = pa.ID)
LEFT OUTER JOIN dbo.Systems s ON (pa.SourceSystemID = s.ID)
INNER JOIN (SELECT pa.policyid AS policyid
, pa.SourceSystemID AS SourceSystemID
, s.SiteNumber AS SiteNumber
, max(pa.LastModifiedDate) AS Recdate
FROM dbo.PolicyAudit
INNER JOIN dbo.Systems ON (pa.SourceSystemID = s.ID)
GROUP BY policyid
, SourceSystemID
, s.SiteNumber
) dpal ON (pa.PolicyID = dpal.policyid
AND pa.LastModifiedDate = dpal.Recdate
AND pa.SourceSystemID = dpal.SourceSystemID
AND dpal.SiteNumber = s.SiteNumber
)
INNER JOIN (SELECT *
FROM (
VALUES
('C1', '123', '123'),
('C1', '127', '54')
) tvc(SiteNumber, ProductCode, lob)
) sr -- SiteRestrictions
ON s.SiteNumber = sr.SiteNumber
AND pa.ProductCode = sr.ProductCode
AND pa.lob = sr.lob
WHERE (pa.Status IN (2)
AND pa.IsCurrent IN (1)
)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 29, 2016 at 12:00 am
Thanks . I would appreciate if you could help me in using case statements .Looking for your reply how to include case statements .Also i have not come across this approach . Can you explain in detail to understand better to incorporate this .
March 29, 2016 at 1:20 am
Thanks for your reply . When i ran the query and checked
1. Due to inner join that is made ,It check for only c1 . For sitenumber c1 the following product codes and lob mentioned below should be validated and for other site number such as d1,p1,r1 which the data has in the earlier query need to be displayed .
INNER JOIN (SELECT *
FROM (
VALUES
('C1', '123', '123'),
('C1', '127', '54')
) tvc(SiteNumber, ProductCode, lob)
Could you please throw some light on how to validate the product code and lob for the site number c1 and the rest needs to displayed as such .
I tried an left outer join and i am not getting the right results .
March 29, 2016 at 8:10 am
mbavabohrude (3/29/2016)
Thanks for your reply . When i ran the query and checked1. Due to inner join that is made ,It check for only c1 . For sitenumber c1 the following product codes and lob mentioned below should be validated and for other site number such as d1,p1,r1 which the data has in the earlier query need to be displayed .
INNER JOIN (SELECT *
FROM (
VALUES
('C1', '123', '123'),
('C1', '127', '54')
) tvc(SiteNumber, ProductCode, lob)
Could you please throw some light on how to validate the product code and lob for the site number c1 and the rest needs to displayed as such .
I tried an left outer join and i am not getting the right results .
If you want tested code, you need to provide sample data as outlined in Forum Etiquette: How to post data/code on a forum to get the best help[/url]. The code provided was only meant as an illustration of how to set up the table value constructor.
Here is a web site that contains examples of how to set up a CASE expression.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply