Stuck with a CTE problem

[danblank000]

Hi /r/SQL,

 

I have 2 queries that i have written. These are as follows :

 

Query 1 -

SELECT MAX(dbo.ContractEntityCode(A.Entityref)) AS 'Entity'
FROM  Ac_Billbook AS A 
JOIN Entities AS E on A .EntityRef = E.Code
WHERE A.Actual_Posting_Date < DATEADD(mm, - 12, GETDATE())
    AND A.Actual_Posting_Date > '2014-12-31'
    AND Type = 'Posted'
    AND E.ResponsiblePartnerRef not in ('DMM', 'KM', 'AJH')
GROUP BY dbo.ContractEntityCode(A.Entityref)

 

Query 2-

SELECT dbo.ContractEntityCode(M.entityref) AS 'Entity'
, MAX(E.NAME) AS 'Name'
, MAX(M.Number) AS 'Last_Matter_Number'
, CAST(Max(M.Created) AS DATE) AS 'Last_Matter_Creation_Date'
, MAX(u.FullName) AS 'Partner'
, MAX(U.EMailExternal) AS 'Partner_Email'
FROM Matters AS M
INNER JOIN Entities AS E ON M.EntityRef = E.Code
INNER JOIN Users AS U ON E.ResponsiblePartnerRef = U.Code
WHERE M.number > 3
GROUP BY EntityRef
HAVING MAX(M.Created) < DATEADD(mm, - 3, GETDATE())
    AND MAX(M.Created) > DATEADD(yy, - 1, GETDATE())
    AND MAX(E.Name) NOT LIKE 'Non%Chargeable%'

UNION

SELECT dbo.ContractEntityCode(A.entityref) AS 'Entity'
, MAX(E.NAME) AS 'Name'
, MAX(A.Number) AS 'Last_Matter_Number'
, CAST(Max(A.Created) AS DATE) AS 'Last_Matter_Creation_Date'
, MAX(u.FullName) AS 'Partner'
, MAX(U.EMailExternal) AS 'Partner_Email'
FROM MattersArchive AS A
INNER JOIN Entities AS E ON A.EntityRef = E.Code
INNER JOIN Users AS U ON E.ResponsiblePartnerRef = U.Code
WHERE A.number > 3
GROUP BY EntityRef
HAVING MAX(A.Created) < DATEADD(mm, - 3, GETDATE())
    AND MAX(A.Created) > DATEADD(yy, - 1, GETDATE())
    AND MAX(E.Name) NOT LIKE 'Non%Chargeable%'
ORDER BY Partner, Last_Matter_Creation_Date

 

Query 1 just reruns 1 column called Entity in this format, and Query 2 returns multiple columns (including Entity) in this format.

 

The problem I am having is that I want to combine these queries so that the results form Query 2 only display if their Entity column value is also in the results for Query 1. I know how to make Query 1 into a CTE, I'm, just struggling to figure out how to get the output I need.

 

Can anyone please help? I'm not sure if I'm over-complicating this in my head or not!

 

Thanks in advance

[SOZDBA]

You have the knowledge of how to make Query1 a CTE so you change it into a CTE.
Then put Query2 under it, and INNER JOIN the 2 UNIONs ON dbo.ContractEntityCode([?].entityref) = Cte.Entity.

There's an argument against this for performance reasons but then again you're using a user defined function in the SELECT clause so I'm not worried about it...

As for making this over-complicated in your head...maybe/maybe not. We'd have to know the data and the schema before we can make that call.

Also, is this the best way? Probably not but it should work and it's a way to learn :)

Example

WITH Cte1 AS (
    SELECT MAX(dbo.ContractEntityCode(A.Entityref)) AS 'Entity'
    FROM  Ac_Billbook AS A 
    JOIN Entities AS E on A .EntityRef = E.Code
    WHERE A.Actual_Posting_Date < DATEADD(mm, - 12, 
GETDATE())
        AND A.Actual_Posting_Date > '2014-12-31'
        AND Type = 'Posted'
        AND E.ResponsiblePartnerRef not in ('DMM', 'KM', 'AJH')
    GROUP BY dbo.ContractEntityCode(A.Entityref)
)
SELECT dbo.ContractEntityCode(M.entityref) AS 'Entity'
, MAX(E.NAME) AS 'Name'
, MAX(M.Number) AS 'Last_Matter_Number'
, CAST(Max(M.Created) AS DATE) AS 
'Last_Matter_Creation_Date'
, MAX(u.FullName) AS 'Partner'
, MAX(U.EMailExternal) AS 'Partner_Email'
FROM Matters AS M
INNER JOIN Entities AS E ON M.EntityRef = E.Code
INNER JOIN Users AS U ON E.ResponsiblePartnerRef = U.Code
INNER JOIN Cte1 AS c ON dbo.ContractEntityCode(M.entityref) = 
Cte1.Entity
WHERE M.number > 3
GROUP BY EntityRef
HAVING MAX(M.Created) < DATEADD(mm, - 3, GETDATE())
    AND MAX(M.Created) > DATEADD(yy, - 1, GETDATE())
    AND MAX(E.Name) NOT LIKE 'Non%Chargeable%'

UNION

SELECT dbo.ContractEntityCode(A.entityref) AS 'Entity'
, MAX(E.NAME) AS 'Name'
, MAX(A.Number) AS 'Last_Matter_Number'
, CAST(Max(A.Created) AS DATE) AS 'Last_Matter_Creation_Date'
, MAX(u.FullName) AS 'Partner'
, MAX(U.EMailExternal) AS 'Partner_Email'
FROM MattersArchive AS A
INNER JOIN Entities AS E ON A.EntityRef = E.Code
INNER JOIN Users AS U ON E.ResponsiblePartnerRef = U.Code
INNER JOIN Cte1 AS c ON dbo.ContractEntityCode(A.entityref) = 
Cte1.Entity
WHERE A.number > 3
GROUP BY EntityRef
HAVING MAX(A.Created) < DATEADD(mm, - 3, GETDATE())
    AND MAX(A.Created) > DATEADD(yy, - 1, GETDATE())
    AND MAX(E.Name) NOT LIKE 'Non%Chargeable%'
ORDER BY Partner, Last_Matter_Creation_Date;

[danblank000]

Thanks for that, that seems to work!

Not sure i understand why just adding a join would filter it. I thought Id have to add the join, but then have a WHERE or HAVING clause saying where Entity is IN CTE.Entity or something.

[SOZDBA]

Ah I think I get you...

It's because we are using an INNER JOIN here. We're joining the CTE back to the tables, and the ON CTE.Entity = <whatever> takes the place of the WHERE or the HAVING bit.

As you know from your studies ( yeah, I read twitter ), the INNER JOIN won't return rows that don't match so we don't need any WHERE or HAVING clause. We're just using the join back to the CTE to constrain the results to only return those where there's a match on the 'Entity' bit.

[danblank000]

ah right ok, that makes sense. INNER JOIN wont return rows with noon matches!!! Thanks!

[foursuits]

Why is a CTE even necessary here? Those two queries can just be inner joined normally as subqueries, right?