CODEWITHSUNDEEP
t-sql
glaeran
glaeran

Reputation: 426

NULL with Union

Updated.

Current query looks like:

SELECT MAX([Ztel]) as [Ztel] , MAX([Beztel]) as [Beztel], MAX([Otwarte]) as [Otwarte], MAX([Otwartez]) as [Otwartez], [Windykator]
FROM   (
   SELECT COUNT(C.CaseDetailsId) AS [Ztel], NULL AS [Beztel], NULL AS [Otwarte], NULL as [Otwartez], U.FirstName 
          + ' ' + U.LastName      AS [Windykator]
   FROM   CaseDetails             AS C
          JOIN DebtorDetails      AS D
               ON  C.CaseDetailsId = D.CaseDetailsId
          JOIN Users              AS U
               ON  C.UserId = U.UserId
   WHERE  D.DebtorDetailsId IN (SELECT DebtorDetailsId
                                FROM   DebtorPhone
                                WHERE  (IsValid = 'True')
                                       OR (IsDefault = 'True'))
          AND C.CaseStatusId <> 2
          AND C.CaseStatusId <> 6
   GROUP BY
          U.FirstName, U.LastName
   UNION ALL
   SELECT NULL, COUNT(CaseDetailsId), NULL, NULL, U.FirstName + ' ' + U.LastName  AS [Windykator]
   FROM   CaseDetails  AS C
          JOIN Users   AS U
               ON  C.UserId = U.UserId
   WHERE  C.CaseStatusId <> 2
          AND C.CaseStatusId <> 6
   GROUP BY
          U.FirstName, U.LastName
   UNION ALL
   SELECT NULL, NULL, COUNT(CaseDetailsId), NULL, U.FirstName + ' ' + U.LastName  AS [Windykator]
   FROM   CaseDetails  AS C
          JOIN Users   AS U
               ON  C.UserId = U.UserId
   WHERE  C.CaseStatusId = 1
   GROUP BY
          U.FirstName, U.LastName
   UNION ALL
   SELECT NULL, NULL, NULL, COUNT(C.CaseDetailsId), U.FirstName 
          + ' ' + U.LastName      AS [Windykator]
   FROM   CaseDetails             AS C
          JOIN DebtorDetails      AS D
               ON  C.CaseDetailsId = D.CaseDetailsId
          JOIN Users              AS U
               ON  C.UserId = U.UserId
   WHERE  D.DebtorDetailsId IN (SELECT DebtorDetailsId
                                FROM   DebtorPhone
                                WHERE  (IsValid = 'True')
                                       OR (IsDefault = 'True'))
          AND C.CaseStatusId = 1
  GROUP BY
          U.FirstName, U.LastName
   ) AS x
 GROUP BY [Windykator]

Works fine however they want me to add 3 additional columns into dataset. Taken from CaseDetailsView columns: ClientNameShort, SetId, EndServiceDate. Ideas? Or should I make another subquery?

Expected results: http://oi43.tinypic.com/2vdi5vk.jpg

Tables to be used:

CaseDetailsView: SetId, ClientNameShort, UserId, EndServiceDate, CaseDetailsId, CaseStatusId - overall list of cases
DebtorDetails: CaseDetailsId, DebtorDetailsId - details of debtor
DebtorPhone: DebtorDetailsId - list of debtors' phone numbers.

Column3: number of overall cases where CaseStatusId <> 2 and <> 6
Column4: number of overall cases where CaseStatusId <> 2 and <> 6 and exists in DebtorPhone (so the number of cases where we got phone number to debtor)
Column5: number of Open cases (where CaseStatusId = 1) overall
Column6: number of Open Cases (where CaseStatusId = 1) and exists in DebtorPhone (so we have phone number to debtor)

Upvotes: 1

Views: 490

Answers (1)

EricZ
EricZ

Reputation: 6205

If you just want show everything in one row, you can try this

SELECT MAX([Ztel]), MAX([Beztel]), MAX([Windykator])
FROM   (
         .....
   ) AS x

UPDATE:

Base on your comment, I think what you are looking for is this

SELECT MAX([Ztel]), MAX([Beztel]), [Windykator]
FROM   (
   SELECT COUNT(C.CaseDetailsId) AS [Ztel], NULL AS [Beztel], U.FirstName 
          + ' ' + U.LastName      AS [Windykator]
   FROM   CaseDetails             AS C
          JOIN DebtorDetails      AS D
               ON  C.CaseDetailsId = D.CaseDetailsId
          JOIN Users              AS U
               ON  C.UserId = U.UserId
   WHERE  D.DebtorDetailsId IN (SELECT DebtorDetailsId
                                FROM   DebtorPhone
                                WHERE  (IsValid = 'True')
                                       OR (IsDefault = 'True'))
          AND C.CaseStatusId <> 2
          AND C.CaseStatusId <> 6
   GROUP BY
          U.FirstName, U.LastName
   UNION ALL
   SELECT NULL, COUNT(CaseDetailsId), U.FirstName + ' ' + U.LastName  AS [Windykator]
   FROM   CaseDetails  AS C
          JOIN Users   AS U
               ON  C.UserId = U.UserId
   WHERE  C.CaseStatusId <> 2
          AND C.CaseStatusId <> 6
   GROUP BY
          U.FirstName, U.LastName
   ) AS x
 GROUP BY [Windykator]

Upvotes: 2

Related Questions