February 23, 2010 at 1:29 pm
Hi,
My client has a piece if SQL he is having trouble with. Here is the SQL:
He wants to return the results of these joins returned as well as all rows from the BatSvc table that match the predicates. He cannot get the correct results.
Can anybody help?
TIA,
Steve
------------------------------------------------------
SELECT
EntID.NamStr AS Name,
Enc.EpsID,
AdmPriAut.AutNum AS Description,
InsPln.Des AS [Ins. Plan],
CONVERT(varchar(8), AdmPriAut.BegDat, 1)AS [Auth Start],
CONVERT(varchar(8), AdmPriAut.EndDat, 1) AS [Auth End],
AdmPriAut.Unt AS [Auth. Original],
AdmPriAut.UntBal AS [Auth Bal.],
AutSvcDtl.Dsc AS [Disc.],
AutSvcDtl.Unt AS [Disc. Original],
AutSvcDtl.UntBal AS [Disc. Bal.],
CONVERT(VARCHAR(20), COUNT(BatSvc.TraSys)) + ' ' + CONVERT(VARCHAR(20), Svc.SvcID) AS [In Batches],
AdmPriAut.AutSys
FROM
Svc RIGHT OUTER JOIN
AutSvcDtl RIGHT OUTER JOIN
Enc INNER JOIN
AdmPriAut ON Enc.AdmSys = AdmPriAut.AdmSys LEFT OUTER JOIN
InsPln RIGHT OUTER JOIN
FrpPol ON InsPln.PlnSys = FrpPol.PlnSys RIGHT OUTER JOIN
FrpBilSeq ON FrpPol.FrpSys = FrpBilSeq.FrpSys AND FrpPol.FrpInsSeq = FrpBilSeq.FrpInsSeq ON AdmPriAut.FrpInsSeq = FrpBilSeq.FrpInsSeq AND AdmPriAut.AdmSys = FrpBilSeq.AdmSys ON AutSvcDtl.AutSys = AdmPriAut.AutSys LEFT OUTER JOIN
EntID ON Enc.FrpSys = EntID.EntSys LEFT OUTER JOIN
BatSvc ON Enc.AdmSys = BatSvc.AdmSys AND AdmPriAut.AdmSys = BatSvc.AdmSys ON Svc.SvcSys = BatSvc.SvcSys
WHERE(Svc.BilSts = 'Y')
AND(AdmPriAut.AutSys IN
(SELECT DISTINCT AdmPriAut_2.AutSys
FROM AutSvcDtl AS AutSvcDtl_2 RIGHT OUTER JOIN
Enc AS Enc_2 INNER JOIN
AdmPriAut AS AdmPriAut_2 ON Enc_2.AdmSys = AdmPriAut_2.AdmSys LEFT OUTER JOIN
InsPln AS InsPln_2 RIGHT OUTER JOIN
FrpPol AS FrpPol_2 ON InsPln_2.PlnSys = FrpPol_2.PlnSys RIGHT OUTER JOIN
FrpBilSeq AS FrpBilSeq_2 ON FrpPol_2.FrpSys = FrpBilSeq_2.FrpSys AND FrpPol_2.FrpInsSeq = FrpBilSeq_2.FrpInsSeq ON
AdmPriAut_2.FrpInsSeq = FrpBilSeq_2.FrpInsSeq AND AdmPriAut_2.AdmSys = FrpBilSeq_2.AdmSys ON
AutSvcDtl_2.AutSys = AdmPriAut_2.AutSys LEFT OUTER JOIN
EntID AS EntID_2 ON Enc_2.FrpSys = EntID_2.EntSys
WHERE (FrpBilSeq_2.BilSeq = 1)
AND (FrpBilSeq_2.CvgBeg <= GETDATE())
AND (FrpBilSeq_2.CvgEnd > GETDATE() OR FrpBilSeq_2.CvgEnd IS NULL)
AND (AdmPriAut_2.UntBal < 20)
AND (CONVERT(varchar(8), AdmPriAut_2.EndDat, 1) > GETDATE() ORCONVERT(varchar(8), AdmPriAut_2.EndDat, 1) IS NULL)
AND (Enc_2.EndDat >= GETDATE() OREnc_2.EndDat IS NULL)
AND (Enc_2.BegDat <= GETDATE()) OR(FrpBilSeq_2.BilSeq = 1)
AND (FrpBilSeq_2.CvgBeg <= GETDATE())
AND (FrpBilSeq_2.CvgEnd > GETDATE() ORFrpBilSeq_2.CvgEnd IS NULL)
AND (CONVERT(varchar(8), AdmPriAut_2.EndDat, 1) > GETDATE() ORCONVERT(varchar(8), AdmPriAut_2.EndDat, 1) IS NULL)
AND (Enc_2.EndDat >= GETDATE() OR Enc_2.EndDat IS NULL)
AND (Enc_2.BegDat <= GETDATE())
AND (AutSvcDtl_2.UntBal < 20)))
OR (Svc.BilSts = 'Y')
AND (AdmPriAut.AutSys IN
(SELECT DISTINCT AdmPriAut_1.AutSys
FROM AutSvcDtl AS AutSvcDtl_1 RIGHT OUTER JOIN
Enc AS Enc_1 INNER JOIN
AdmPriAut AS AdmPriAut_1 ON Enc_1.AdmSys = AdmPriAut_1.AdmSys LEFT OUTER JOIN
InsPln AS InsPln_1 RIGHT OUTER JOIN
FrpPol AS FrpPol_1 ON InsPln_1.PlnSys = FrpPol_1.PlnSys RIGHT OUTER JOIN
FrpBilSeq AS FrpBilSeq_1 ON FrpPol_1.FrpSys = FrpBilSeq_1.FrpSys AND FrpPol_1.FrpInsSeq = FrpBilSeq_1.FrpInsSeq ON
AdmPriAut_1.FrpInsSeq = FrpBilSeq_1.FrpInsSeq AND AdmPriAut_1.AdmSys = FrpBilSeq_1.AdmSys ON
AutSvcDtl_1.AutSys = AdmPriAut_1.AutSys LEFT OUTER JOIN
EntID AS EntID_1 ON Enc_1.FrpSys = EntID_1.EntSys
WHERE (FrpBilSeq_1.BilSeq = 1)
AND (FrpBilSeq_1.CvgBeg <= GETDATE())
AND (FrpBilSeq_1.CvgEnd > GETDATE() OR FrpBilSeq_1.CvgEnd IS NULL)
AND (AdmPriAut_1.UntBal < 20)
AND (CONVERT(varchar(8), AdmPriAut_1.EndDat, 1) > GETDATE() OR CONVERT(varchar(8), AdmPriAut_1.EndDat, 1) IS NULL)
AND (Enc_1.EndDat >= GETDATE() OR Enc_1.EndDat IS NULL)
AND (Enc_1.BegDat <= GETDATE()) OR (FrpBilSeq_1.BilSeq = 1)
AND (FrpBilSeq_1.CvgBeg <= GETDATE())
AND (FrpBilSeq_1.CvgEnd > GETDATE() OR FrpBilSeq_1.CvgEnd IS NULL)
AND (CONVERT(varchar(8), AdmPriAut_1.EndDat, 1) > GETDATE() ORCONVERT(varchar(8), AdmPriAut_1.EndDat, 1) IS NULL)
AND (Enc_1.EndDat >= GETDATE() OR Enc_1.EndDat IS NULL)
AND (Enc_1.BegDat <= GETDATE())
AND (AutSvcDtl_1.UntBal < 20)))
GROUP BY
EntID.NamStr,
Enc.EpsID,
AdmPriAut.AutNum,
InsPln.Des,
CONVERT(varchar(8), AdmPriAut.BegDat, 1),
CONVERT(varchar(8), AdmPriAut.EndDat, 1),
AdmPriAut.Unt,
AdmPriAut.UntBal,
AutSvcDtl.Dsc,
AutSvcDtl.Unt,
AutSvcDtl.UntBal,
AdmPriAut.EndDat,
AdmPriAut.EndDat,
Svc.SvcID,
AdmPriAut.AutSys
HAVING (AdmPriAut.UntBal < 20)
AND (CONVERT(varchar(8), AdmPriAut.EndDat, 1) > GETDATE() OR CONVERT(varchar(8), AdmPriAut.EndDat, 1) IS NULL)
AND (COUNT(BatSvc.TraSys) IS NULL OR COUNT(BatSvc.TraSys) = 0 OR COUNT(BatSvc.TraSys) <> 0)
OR (CONVERT(varchar(8), AdmPriAut.EndDat, 1) > GETDATE() OR CONVERT(varchar(8), AdmPriAut.EndDat, 1) IS NULL)
AND (COUNT(BatSvc.TraSys) IS NULL OR COUNT(BatSvc.TraSys) = 0 OR COUNT(BatSvc.TraSys) <> 0)
AND (AutSvcDtl.UntBal < 20)
ORDER BY
Name,
AdmPriAut.EndDat
--------------------------------------------------------------
February 23, 2010 at 1:53 pm
I would need table definitions, insert statements with sample data, and expected results, before I'd be able to venture a solution on this.
For one thing, I'm not clear on your requirements. You seem to be saying you need all the rows from BatSvc (based on certain predicates), but then it does a left join to that table. A left join is for when you want all the results for the right-hand table, and only the results that match from the left table. So that's not clear to me.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 23, 2010 at 3:17 pm
Thanks G! I will try to get thos things for you and post them back here tomorrow.
February 23, 2010 at 3:36 pm
Steve Barlow-144765 (2/23/2010)
Hi,My client has a piece if SQL he is having trouble with. Here is the SQL:
He wants to return the results of these joins returned as well as all rows from the BatSvc table that match the predicates. He cannot get the correct results.
Can anybody help?
TIA,
Steve
Steve, it often helps to format the code - the more code there is, the more important the job. I made a start on it (see below) and it's beginning to look to me like the query was written by trial and error using a query builder tool, with no real understanding of the database. If this is the case, and you should verify this, then I'd recommend that this query is rewritten by design. It will produce the correct results, with a fraction of the code, in a fraction of the time. I'm sorry I can't be more helpful.
SELECT
EntID.NamStr AS Name,
Enc.EpsID,
AdmPriAut.AutNum AS Description,
InsPln.Des AS [Ins. Plan],
CONVERT(varchar(8), AdmPriAut.BegDat, 1)AS [Auth Start],
CONVERT(varchar(8), AdmPriAut.EndDat, 1) AS [Auth End],
AdmPriAut.Unt AS [Auth. Original],
AdmPriAut.UntBal AS [Auth Bal.],
AutSvcDtl.Dsc AS [Disc.],
AutSvcDtl.Unt AS [Disc. Original],
AutSvcDtl.UntBal AS [Disc. Bal.],
CONVERT(VARCHAR(20), COUNT(BatSvc.TraSys)) + ' ' + CONVERT(VARCHAR(20), Svc.SvcID) AS [In Batches],
AdmPriAut.AutSys
FROM Svc
RIGHT OUTER JOIN AutSvcDtl
RIGHT OUTER JOIN Enc
INNER JOIN AdmPriAut
ON Enc.AdmSys = AdmPriAut.AdmSys
LEFT OUTER JOIN InsPln
RIGHT OUTER JOIN FrpPol
ON InsPln.PlnSys = FrpPol.PlnSys
RIGHT OUTER JOIN FrpBilSeq
ON FrpPol.FrpSys = FrpBilSeq.FrpSys AND FrpPol.FrpInsSeq = FrpBilSeq.FrpInsSeq
ON AdmPriAut.FrpInsSeq = FrpBilSeq.FrpInsSeq AND AdmPriAut.AdmSys = FrpBilSeq.AdmSys
ON AutSvcDtl.AutSys = AdmPriAut.AutSys
LEFT OUTER JOIN EntID
ON Enc.FrpSys = EntID.EntSys
LEFT OUTER JOIN BatSvc
ON Enc.AdmSys = BatSvc.AdmSys AND AdmPriAut.AdmSys = BatSvc.AdmSys
ON Svc.SvcSys = BatSvc.SvcSys
WHERE (Svc.BilSts = 'Y')
AND (AdmPriAut.AutSys IN
(SELECT DISTINCT AdmPriAut_2.AutSys
FROM AutSvcDtl AS AutSvcDtl_2
RIGHT OUTER JOIN Enc AS Enc_2
INNER JOIN AdmPriAut AS AdmPriAut_2
ON Enc_2.AdmSys = AdmPriAut_2.AdmSys
LEFT OUTER JOIN InsPln AS InsPln_2
RIGHT OUTER JOIN FrpPol AS FrpPol_2
ON InsPln_2.PlnSys = FrpPol_2.PlnSys
RIGHT OUTER JOIN FrpBilSeq AS FrpBilSeq_2
ON FrpPol_2.FrpSys = FrpBilSeq_2.FrpSys AND FrpPol_2.FrpInsSeq = FrpBilSeq_2.FrpInsSeq
ON AdmPriAut_2.FrpInsSeq = FrpBilSeq_2.FrpInsSeq AND AdmPriAut_2.AdmSys = FrpBilSeq_2.AdmSys
ON AutSvcDtl_2.AutSys = AdmPriAut_2.AutSys
LEFT OUTER JOIN EntID AS EntID_2
ON Enc_2.FrpSys = EntID_2.EntSys
WHERE (FrpBilSeq_2.BilSeq = 1)
AND (FrpBilSeq_2.CvgBeg <= GETDATE())
AND (FrpBilSeq_2.CvgEnd > GETDATE() OR FrpBilSeq_2.CvgEnd IS NULL)
AND (AdmPriAut_2.UntBal < 20)
AND (CONVERT(varchar(8), AdmPriAut_2.EndDat, 1) > GETDATE() OR CONVERT(varchar(8), AdmPriAut_2.EndDat, 1) IS NULL)
AND (Enc_2.EndDat >= GETDATE() OR Enc_2.EndDat IS NULL)
AND (Enc_2.BegDat <= GETDATE()) OR (FrpBilSeq_2.BilSeq = 1)
AND (FrpBilSeq_2.CvgBeg <= GETDATE())
AND (FrpBilSeq_2.CvgEnd > GETDATE() OR FrpBilSeq_2.CvgEnd IS NULL)
AND (CONVERT(varchar(8), AdmPriAut_2.EndDat, 1) > GETDATE() OR CONVERT(varchar(8), AdmPriAut_2.EndDat, 1) IS NULL)
AND (Enc_2.EndDat >= GETDATE() OR Enc_2.EndDat IS NULL)
AND (Enc_2.BegDat <= GETDATE())
AND (AutSvcDtl_2.UntBal < 20)))
OR (Svc.BilSts = 'Y')
AND (AdmPriAut.AutSys IN
(SELECT DISTINCT AdmPriAut_1.AutSys
FROM AutSvcDtl AS AutSvcDtl_1 RIGHT OUTER JOIN
Enc AS Enc_1 INNER JOIN
AdmPriAut AS AdmPriAut_1 ON Enc_1.AdmSys = AdmPriAut_1.AdmSys LEFT OUTER JOIN
InsPln AS InsPln_1 RIGHT OUTER JOIN
FrpPol AS FrpPol_1 ON InsPln_1.PlnSys = FrpPol_1.PlnSys RIGHT OUTER JOIN
FrpBilSeq AS FrpBilSeq_1 ON FrpPol_1.FrpSys = FrpBilSeq_1.FrpSys AND FrpPol_1.FrpInsSeq = FrpBilSeq_1.FrpInsSeq ON
AdmPriAut_1.FrpInsSeq = FrpBilSeq_1.FrpInsSeq AND AdmPriAut_1.AdmSys = FrpBilSeq_1.AdmSys ON
AutSvcDtl_1.AutSys = AdmPriAut_1.AutSys LEFT OUTER JOIN
EntID AS EntID_1 ON Enc_1.FrpSys = EntID_1.EntSys
WHERE (FrpBilSeq_1.BilSeq = 1)
AND (FrpBilSeq_1.CvgBeg <= GETDATE())
AND (FrpBilSeq_1.CvgEnd > GETDATE() OR FrpBilSeq_1.CvgEnd IS NULL)
AND (AdmPriAut_1.UntBal < 20)
AND (CONVERT(varchar(8), AdmPriAut_1.EndDat, 1) > GETDATE() OR CONVERT(varchar(8), AdmPriAut_1.EndDat, 1) IS NULL)
AND (Enc_1.EndDat >= GETDATE() OR Enc_1.EndDat IS NULL)
AND (Enc_1.BegDat <= GETDATE()) OR (FrpBilSeq_1.BilSeq = 1)
AND (FrpBilSeq_1.CvgBeg <= GETDATE())
AND (FrpBilSeq_1.CvgEnd > GETDATE() OR FrpBilSeq_1.CvgEnd IS NULL)
AND (CONVERT(varchar(8), AdmPriAut_1.EndDat, 1) > GETDATE() OR CONVERT(varchar(8), AdmPriAut_1.EndDat, 1) IS NULL)
AND (Enc_1.EndDat >= GETDATE() OR Enc_1.EndDat IS NULL)
AND (Enc_1.BegDat <= GETDATE())
AND (AutSvcDtl_1.UntBal < 20)))
GROUP BY
EntID.NamStr,
Enc.EpsID,
AdmPriAut.AutNum,
InsPln.Des,
CONVERT(varchar(8), AdmPriAut.BegDat, 1),
CONVERT(varchar(8), AdmPriAut.EndDat, 1),
AdmPriAut.Unt,
AdmPriAut.UntBal,
AutSvcDtl.Dsc,
AutSvcDtl.Unt,
AutSvcDtl.UntBal,
AdmPriAut.EndDat,
AdmPriAut.EndDat,
Svc.SvcID,
AdmPriAut.AutSys
HAVING (AdmPriAut.UntBal < 20)
AND (CONVERT(varchar(8), AdmPriAut.EndDat, 1) > GETDATE() OR CONVERT(varchar(8), AdmPriAut.EndDat, 1) IS NULL)
AND (COUNT(BatSvc.TraSys) IS NULL OR COUNT(BatSvc.TraSys) = 0 OR COUNT(BatSvc.TraSys) <> 0)
OR (CONVERT(varchar(8), AdmPriAut.EndDat, 1) > GETDATE() OR CONVERT(varchar(8), AdmPriAut.EndDat, 1) IS NULL)
AND (COUNT(BatSvc.TraSys) IS NULL OR COUNT(BatSvc.TraSys) = 0 OR COUNT(BatSvc.TraSys) <> 0)
AND (AutSvcDtl.UntBal < 20)
ORDER BY
Name,
AdmPriAut.EndDat
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply