CODEWITHSUNDEEP
sqlsql-serverjoincross-join
RustyHamster
RustyHamster

Reputation: 359

Grouping a Cross JOIN SQL

I am trying to create a cross join as below. My problem is that i want the cross join to be grouped by the Advisor Currently my Query return this

 Advisor    Recieved    Advisor Issued
    104        5          104    10
    266        3          104    10
    104        5          266    8
    266        3          266    8

What I would like it to return is this

Advisor Recieved Issued
  104      5      10
  266      3      8

Here is my code

SELECT * FROM
(
SELECT p.Advisor,
Count(p.id) AS Recieved
 FROM BPS.dbo.tbl_Profile_Mortgage AS pm
JOIN dbo.tbl_Profile AS p
    ON p.ID = FK_ProfileId
WHERE 
p.CaseTypeID = '2' AND
p.Advisor IN ('266','104')  AND
  (MONTH(pm.DateAppRcvd) = MONTH(GETDATE())
      AND YEAR(pm.DateAppRcvd) = YEAR(GETDATE())) AND pm.DateCancelled IS NULL
GROUP BY p.Advisor
) AS t1

CROSS JOIN 

(
SELECT p.Advisor AS Advisor ,
Count(p.id) AS Issued

FROM BPS.dbo.tbl_Profile_Mortgage AS pm
JOIN dbo.tbl_Profile AS p
    ON p.ID = FK_ProfileId
WHERE 
p.CaseTypeID = '2' AND
p.Advisor IN ('266','104') AND
  (MONTH(pm.DateAppIssued) = MONTH(GETDATE())
      AND YEAR(pm.DateAppIssued) = YEAR(GETDATE())) AND pm.DateCancelled IS NULL
GROUP BY p.Advisor
) AS t2

Any help would be much appreciated thanks

Upvotes: 1

Views: 721

Answers (1)

MatBailie
MatBailie

Reputation: 86706

Change your CROSS JOIN into a FULL OUTER JOIN and join your two subqueries on Advisor.

EDIT - example code now that I'm no longer on my mobile.

SELECT
    COALESCE(t1.Advisor , t2.Advisor)   AS Advisor,
    COALESCE(t1.Received, 0         )   AS Received,
    COALESCE(t2.Issued  , 0         )   AS Issued
FROM
(
    SELECT p.Advisor,
    Count(p.id) AS Recieved
     FROM BPS.dbo.tbl_Profile_Mortgage AS pm
    JOIN dbo.tbl_Profile AS p
        ON p.ID = FK_ProfileId
    WHERE 
    p.CaseTypeID = '2' AND
    p.Advisor IN ('266','104')  AND
      (MONTH(pm.DateAppRcvd) = MONTH(GETDATE())
          AND YEAR(pm.DateAppRcvd) = YEAR(GETDATE())) AND pm.DateCancelled IS NULL
    GROUP BY p.Advisor
)
    AS t1
FULL OUTER JOIN
(
    SELECT p.Advisor AS Advisor ,
    Count(p.id) AS Issued
    FROM BPS.dbo.tbl_Profile_Mortgage AS pm
    JOIN dbo.tbl_Profile AS p
        ON p.ID = FK_ProfileId
    WHERE 
    p.CaseTypeID = '2' AND
    p.Advisor IN ('266','104') AND
      (MONTH(pm.DateAppIssued) = MONTH(GETDATE())
          AND YEAR(pm.DateAppIssued) = YEAR(GETDATE())) AND pm.DateCancelled IS NULL
    GROUP BY p.Advisor
)
    AS t2
        ON t1.Advisor = t2.Advisor

EDIT: Another example in a single query

SELECT
    p.Advisor,
    SUM(CASE WHEN pm.DateAppRcvd   >= DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) THEN 1 ELSE 0 END)   AS Recieved,
    SUM(CASE WHEN pm.DateAppIssued >= DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) THEN 1 ELSE 0 END)   AS Issued
FROM
    BPS.dbo.tbl_Profile_Mortgage AS pm
INNER JOIN
    dbo.tbl_Profile AS p
        ON p.ID = FK_ProfileId
WHERE 
        p.CaseTypeID = '2'
    AND p.Advisor IN ('266','104')
    AND pm.DateCancelled IS NULL
    AND (
            pm.DateAppRcvd   >= DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
        OR  pm.DateAppIssued >= DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
        )
GROUP BY
    p.Advisor

EDIT: A tidier query

SELECT
    p.Advisor,
    SUM(CASE WHEN pm.DateAppRcvd   >= this_month.start THEN 1 ELSE 0 END)   AS Recieved,
    SUM(CASE WHEN pm.DateAppIssued >= this_month.start THEN 1 ELSE 0 END)   AS Issued
FROM
    BPS.dbo.tbl_Profile_Mortgage AS pm
INNER JOIN
    dbo.tbl_Profile AS p
        ON p.ID = FK_ProfileId
INNER JOIN
(
    SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS start
)
    AS this_month
        ON  pm.DateAppRcvd   >= this_month.start
        OR  pm.DateAppIssued >= this_month.start
WHERE 
        p.CaseTypeID = '2'
    AND p.Advisor IN ('266','104')
    AND pm.DateCancelled IS NULL
GROUP BY
    p.Advisor

Upvotes: 2

Related Questions