SELECT tblCaseTrackerUnique.CompletedBy, 
[FirstName] + ' ' + [LastName] AS Handler, 
tblLkUpTLCs.TeamLeader, 
qryCaseTrackerPrivacyCardByUser.CountOfRecordID AS TotalHandled, 
qryCaseTrackerPrivacyCardPending.CountOfRecordID AS Pending, 
qryCaseTrackerPrivacyCardComplete.CountOfRecordID AS Completed, 
qryCaseTrackerPrivacyCardPendingQR.CountOfRecordID AS PendingQR, 
qryCaseTrackerPrivacyCardNotToBeQRd.CountOfRecordID AS NotToBeQRd, 
qryCaseTrackerPrivacyCardBypass.CountOfRecordID AS Bypass, 
qryCaseTrackerPrivacyCardCorrect.CountOfRecordID AS Correct, 
qryCaseTrackerPrivacyCardIncorrect.CountOfRecordID AS Incorrect

FROM ((((((((tblCaseTrackerUnique WITH (NOLOCK)
LEFT OUTER JOIN (tblNames LEFT OUTER JOIN tblLkUpTLCs ON tblNames.TLCsID = tblLkUpTLCs.TLCsID) ON tblCaseTrackerUnique.CompletedBy = tblNames.IonsID) 

-- qryCaseTrackerUniqueCaseIDByUser
LEFT OUTER JOIN 
	(
	SELECT qryCaseTrackerUniqueCaseIDByUser.CompletedBy, 

	Count(tblCaseTrackerAddInfo.RecordID) AS CountOfRecordID
	FROM tblCaseTrackerAddInfo WITH (NOLOCK) INNER JOIN
		(
		SELECT tblCaseTrackerUnique.*
		FROM tblCaseTrackerUnique WITH (NOLOCK)
		WHERE ((tblCaseTrackerUnique.ProjectID)=@ProjectID)
		) as qryCaseTrackerUniqueCaseIDByUser
	ON tblCaseTrackerAddInfo.ProjectID=qryCaseTrackerUniqueCaseIDByUser.ProjectID 
	AND tblCaseTrackerAddInfo.CaseID=qryCaseTrackerUniqueCaseIDByUser.CaseID
	WHERE (((tblCaseTrackerAddInfo.ProjectID)=30)   AND (tblCaseTrackerAddInfo.NumberG<>0) --added
        AND ((tblCaseTrackerAddInfo.CreatedDate) Between @DateStart And @DateEnd))
	GROUP BY qryCaseTrackerUniqueCaseIDByUser.CompletedBy
	)
AS qryCaseTrackerPrivacyCardByUser ON tblCaseTrackerUnique.CompletedBy = qryCaseTrackerPrivacyCardByUser.CompletedBy) 

LEFT OUTER JOIN 
-- qryCaseTrackerPrivacyCardPending
	(
        SELECT qryCaseTrackerPrivacyCardPending.CompletedBy,
        Count(tblCaseTrackerAddInfo.RecordID) As CountOfRecordID
        FROM tblCaseTrackerAddInfo WITH (NOLOCK) INNER JOIN
            (
            SELECT tblCaseTrackerUnique.*
            FROM tblCaseTrackerUnique WITH (NOLOCK)
	WHERE ((tblCaseTrackerUnique.ProjectID)=@ProjectID)
            ) as qryCaseTrackerPrivacyCardPending
        ON tblCaseTrackerAddInfo.ProjectID=qryCaseTrackerPrivacyCardPending.ProjectID
        AND tblCaseTrackerAddInfo.CaseID=qryCaseTrackerPrivacyCardPending.CaseID
        WHERE (((tblCaseTrackerAddInfo.ProjectID)=30)  AND (tblCaseTrackerAddInfo.NumberG<>0) 
        AND ((tblCaseTrackerAddInfo.CreatedDate) Between @DateStart And @DateEnd))
        AND tblCaseTrackerAddInfo.NumberE=2
        AND tblCaseTrackerAddInfo.NumberJ=4
        GROUP BY qryCaseTrackerPrivacyCardPending.CompletedBy
	)
AS qryCaseTrackerPrivacyCardPending ON tblCaseTrackerUnique.CompletedBy = qryCaseTrackerPrivacyCardPending.CompletedBy) 

LEFT OUTER JOIN 
-- qryCaseTrackerPrivacyCardComplete
	(
        SELECT qryCaseTrackerPrivacyCardComplete.CompletedBy,
        Count(tblCaseTrackerAddInfo.RecordID) As CountOfRecordID
        FROM tblCaseTrackerAddInfo WITH (NOLOCK) INNER JOIN
            (
            SELECT tblCaseTrackerUnique.*
            FROM tblCaseTrackerUnique WITH (NOLOCK)
	WHERE ((tblCaseTrackerUnique.ProjectID)=@ProjectID)
            ) as qryCaseTrackerPrivacyCardComplete
        ON tblCaseTrackerAddInfo.ProjectID=qryCaseTrackerPrivacyCardComplete.ProjectID
        AND tblCaseTrackerAddInfo.CaseID=qryCaseTrackerPrivacyCardComplete.CaseID
        WHERE ((((tblCaseTrackerAddInfo.ProjectID)=30)  AND (tblCaseTrackerAddInfo.NumberG<>0) 
        AND ((tblCaseTrackerAddInfo.CreatedDate) Between @DateStart And @DateEnd))
        AND tblCaseTrackerAddInfo.NumberE=2
        AND tblCaseTrackerAddInfo.NumberJ<>4)
        OR
        ((((tblCaseTrackerAddInfo.ProjectID)=30)  AND (tblCaseTrackerAddInfo.NumberG<>0) 
        AND ((tblCaseTrackerAddInfo.CreatedDate) Between @DateStart And @DateEnd))
        AND tblCaseTrackerAddInfo.NumberE<>2)
        GROUP BY qryCaseTrackerPrivacyCardComplete.CompletedBy
	)
AS qryCaseTrackerPrivacyCardComplete ON tblCaseTrackerUnique.CompletedBy = qryCaseTrackerPrivacyCardComplete.CompletedBy) 

LEFT OUTER JOIN 
--qryCaseTrackerPrivacyCardPendingQR
	(
        SELECT qryCaseTrackerPrivacyCardPendingQR.CompletedBy,
        Count(tblCaseTrackerAddInfo.RecordID) As CountOfRecordID
        FROM tblCaseTrackerAddInfo WITH (NOLOCK) INNER JOIN
            (
            SELECT tblCaseTrackerUnique.*
            FROM tblCaseTrackerUnique WITH (NOLOCK)
	WHERE ((tblCaseTrackerUnique.ProjectID)=@ProjectID)
            ) as qryCaseTrackerPrivacyCardPendingQR
        ON tblCaseTrackerAddInfo.ProjectID=qryCaseTrackerPrivacyCardPendingQR.ProjectID
        AND tblCaseTrackerAddInfo.CaseID=qryCaseTrackerPrivacyCardPendingQR.CaseID
        WHERE (((tblCaseTrackerAddInfo.ProjectID)=30) AND (tblCaseTrackerAddInfo.NumberG<>0) 
        AND ((tblCaseTrackerAddInfo.CreatedDate) Between @DateStart And @DateEnd))
        AND tblCaseTrackerAddInfo.NumberE=1
        AND tblCaseTrackerAddInfo.NumberJ=4
        AND qryCaseTrackerPrivacyCardPendingQR.QRdBy Is Null
        GROUP BY qryCaseTrackerPrivacyCardPendingQR.CompletedBy
	)
AS qryCaseTrackerPrivacyCardPendingQR ON tblCaseTrackerUnique.CompletedBy = qryCaseTrackerPrivacyCardPendingQR.CompletedBy) 

LEFT OUTER JOIN 
--qryCaseTrackerPrivacyCardNotToBeQRd
	(
        SELECT qryCaseTrackerPrivacyCardNotToBeQRd.CompletedBy,
        Count(tblCaseTrackerAddInfo.RecordID) As CountOfRecordID
        FROM tblCaseTrackerAddInfo WITH (NOLOCK) INNER JOIN
            (
            SELECT tblCaseTrackerUnique.*
            FROM tblCaseTrackerUnique WITH (NOLOCK)
	WHERE ((tblCaseTrackerUnique.ProjectID)=@ProjectID)
            ) as qryCaseTrackerPrivacyCardNotToBeQRd
        ON tblCaseTrackerAddInfo.ProjectID=qryCaseTrackerPrivacyCardNotToBeQRd.ProjectID
        AND tblCaseTrackerAddInfo.CaseID=qryCaseTrackerPrivacyCardNotToBeQRd.CaseID
        WHERE (((tblCaseTrackerAddInfo.ProjectID)=30) AND (tblCaseTrackerAddInfo.NumberG<>0) 
        AND ((tblCaseTrackerAddInfo.CreatedDate) Between @DateStart And @DateEnd))
        --AND (tblCaseTrackerAddInfo.NumberE=1 or tblCaseTrackerAddInfo.NumberE=2)
        AND tblCaseTrackerAddInfo.NumberJ=4
        AND qryCaseTrackerPrivacyCardNotToBeQRd.QRdBy Is Not Null
        GROUP BY qryCaseTrackerPrivacyCardNotToBeQRd.CompletedBy
	)
AS qryCaseTrackerPrivacyCardNotToBeQRd ON tblCaseTrackerUnique.CompletedBy = qryCaseTrackerPrivacyCardNotToBeQRd.CompletedBy)

LEFT OUTER JOIN 
--qryCaseTrackerPrivacyCardBypass
	(
        SELECT qryCaseTrackerPrivacyCardBypass.CompletedBy,
        Count(tblCaseTrackerAddInfo.RecordID) As CountOfRecordID
        FROM tblCaseTrackerAddInfo WITH (NOLOCK) INNER JOIN
            (
            SELECT tblCaseTrackerUnique.*

            FROM tblCaseTrackerUnique WITH (NOLOCK)
	WHERE ((tblCaseTrackerUnique.ProjectID)=@ProjectID)
            ) as qryCaseTrackerPrivacyCardBypass
        ON tblCaseTrackerAddInfo.ProjectID=qryCaseTrackerPrivacyCardBypass.ProjectID
        AND tblCaseTrackerAddInfo.CaseID=qryCaseTrackerPrivacyCardBypass.CaseID
        WHERE (((tblCaseTrackerAddInfo.ProjectID)=30) AND (tblCaseTrackerAddInfo.NumberG<>0) 
        AND ((tblCaseTrackerAddInfo.CreatedDate) Between @DateStart And @DateEnd))
        AND tblCaseTrackerAddInfo.NumberJ=3
        GROUP BY qryCaseTrackerPrivacyCardBypass.CompletedBy
	)
AS qryCaseTrackerPrivacyCardBypass ON tblCaseTrackerUnique.CompletedBy = qryCaseTrackerPrivacyCardBypass.CompletedBy) 

LEFT OUTER JOIN 
--qryCaseTrackerPrivacyCardCorrect
	(
        SELECT qryCaseTrackerPrivacyCardCorrect.CompletedBy,
        Count(tblCaseTrackerAddInfo.RecordID) As CountOfRecordID
        FROM tblCaseTrackerAddInfo WITH (NOLOCK) INNER JOIN
            (
            SELECT tblCaseTrackerUnique.*
            FROM tblCaseTrackerUnique WITH (NOLOCK)
	WHERE ((tblCaseTrackerUnique.ProjectID)=@ProjectID)
            ) as qryCaseTrackerPrivacyCardCorrect
        ON tblCaseTrackerAddInfo.ProjectID=qryCaseTrackerPrivacyCardCorrect.ProjectID
        AND tblCaseTrackerAddInfo.CaseID=qryCaseTrackerPrivacyCardCorrect.CaseID
        WHERE (((tblCaseTrackerAddInfo.ProjectID)=30) AND (tblCaseTrackerAddInfo.NumberG<>0)
        AND ((tblCaseTrackerAddInfo.CreatedDate) Between @DateStart And @DateEnd))
        AND tblCaseTrackerAddInfo.NumberJ=1
        GROUP BY qryCaseTrackerPrivacyCardCorrect.CompletedBy
	)
AS qryCaseTrackerPrivacyCardCorrect ON tblCaseTrackerUnique.CompletedBy = qryCaseTrackerPrivacyCardCorrect.CompletedBy) 

LEFT OUTER JOIN 
--qryCaseTrackerPrivacyCardIncorrect
	(
        SELECT qryCaseTrackerPrivacyCardIncorrect.CompletedBy,
        Count(tblCaseTrackerAddInfo.RecordID) As CountOfRecordID
        FROM tblCaseTrackerAddInfo WITH (NOLOCK) INNER JOIN
            (
            SELECT tblCaseTrackerUnique.*
            FROM tblCaseTrackerUnique WITH (NOLOCK)
	WHERE ((tblCaseTrackerUnique.ProjectID)=@ProjectID)
            ) as qryCaseTrackerPrivacyCardIncorrect
        ON tblCaseTrackerAddInfo.ProjectID=qryCaseTrackerPrivacyCardIncorrect.ProjectID
        AND tblCaseTrackerAddInfo.CaseID=qryCaseTrackerPrivacyCardIncorrect.CaseID
        WHERE (((tblCaseTrackerAddInfo.ProjectID)=30) AND (tblCaseTrackerAddInfo.NumberG<>0) 
        AND ((tblCaseTrackerAddInfo.CreatedDate) Between @DateStart And @DateEnd))
        AND tblCaseTrackerAddInfo.NumberJ=2
        GROUP BY qryCaseTrackerPrivacyCardIncorrect.CompletedBy
	)
AS qryCaseTrackerPrivacyCardIncorrect ON tblCaseTrackerUnique.CompletedBy = qryCaseTrackerPrivacyCardIncorrect.CompletedBy

WHERE (((qryCaseTrackerPrivacyCardByUser.CompletedBy) =@ID))

GROUP BY tblCaseTrackerUnique.CompletedBy, 
[FirstName] + ' ' +[LastName], tblLkUpTLCs.TeamLeader, 
qryCaseTrackerPrivacyCardByUser.CountOfRecordID, 
qryCaseTrackerPrivacyCardPending.CountOfRecordID, 
qryCaseTrackerPrivacyCardComplete.CountOfRecordID, 
qryCaseTrackerPrivacyCardPendingQR.CountOfRecordID, 
qryCaseTrackerPrivacyCardNotToBeQRd.CountOfRecordID, 
qryCaseTrackerPrivacyCardBypass.CountOfRecordID, 
qryCaseTrackerPrivacyCardCorrect.CountOfRecordID, 
qryCaseTrackerPrivacyCardIncorrect.CountOfRecordID

quote:

---

splats it into a GraphViz file and gets dot to draw the damned thing instead.

---

quote:

---

I'm not sure I fully understand what that old coder did, but maybe you should make a stored proc that has the "function calls" in it and the function calls call a stored proc that takes care of whatever the function call should do.

---

PARAMETERS dbname Text;
INSERT INTO QueryTQRefs ( [Database], Query, type, object )
SELECT mQuery.Database, mQuery.Query, TableQueryUnion.type, TableQueryUnion.object
FROM TableQueryUnion INNER JOIN mQuery ON TableQueryUnion.database = mQuery.Database
WHERE (((mQuery.Database)=[dbname]) AND ((mQuery.SQL) Like ("*[!!.][[]" & [Object] & "]*")))
   OR (((mQuery.Database)=[dbname]) AND ((TableQueryUnion.object) Not Like "*[- /+µ&()#]*")
         AND ((mQuery.SQL) Like ("*[, (]" & [Object] & "[,; )]*")));

SELECT O.Name AS Query, Q.Name1 AS Tab
FROM MSysObjects AS O
INNER JOIN MSysQueries AS Q ON O.Id = Q.ObjectId
WHERE Q.Attribute=5

SELECT O.Name AS Query, Q.Name1 AS Tab
FROM MSysObjects AS O
INNER JOIN MSysQueries AS Q ON O.Id = Q.ObjectId
WHERE Q.Attribute=1 AND Q.Flag IN (2,3)

quote:

---

Thanks Mike, but there are 5 sprocs, each with 100 "functions". That would mean I'd have to recode 500 sprocs...no thanks...I've been give this "gift" as maint mode only...and that's where I'll leave it...

Just wondering when(if) perfromance will bottom out, in which case I'll have to do something...



Brett

8-)

---

Aw, gee, Brett, you get ALL the fun projects!

Not sure what would cause "performance to bottom out" other than perhaps some call that might trigger recursive calls to the sproc (such as calling another function inside the same sproc that calls another function, etc., etc.) Hopefully they don't do that. Other than that, I think it's mostly "just" a management and maintenance nightmare. But don't worry, I think hunting season for former employees and consultants opens in just a few weeks.

------------------------------------------------------
The more you know, the more you know you don't know.

quote:

---

Mark,

10 gauge or 12 gauge?

I'm seriously worried about this mess...

It'll be like:

"here you go...transition...BOOOOOOOOOOOOOOOOOOOOOOOOOOOM"

---

Use whatever you like, Brett. Personally, I kinda like poison-tipped darts from a blow gun, but my absolute favorite is the Gunting. It generates more pain and agony. And I don't want to scare you off here, but have you noticed how many of the folks who have posted more than 1000 times here have gone through a "transition" in their place of work in the last year?

------------------------------------------------------
The more you know, the more you know you don't know.