CODEWITHSUNDEEP
sqlsql-servert-sql
Callum Evans
Callum Evans

Reputation: 361

Returning column with count of 0

I have a query that looks up a list of documents depending on their department and their status.

DECLARE @StatusIds NVARCHAR(MAX) = '1,2,3,4,5';
DECLARE @DepartmentId NVARCHAR(2) = 'IT';

SELECT ILDPST.name,
       COUNT(*) AS TodayCount
FROM dbo.TableA ILDP
LEFT JOIN dbo.TableB ILDPS ON ILDPS.IntranetLoanDealPreStateId = ILDP.IntranetLoanDealPreStateId
LEFT JOIN dbo.TableC ILDPST ON ILDPST.IntranetLoanDealPreStateTypeId = ILDPS.CurrentStateTypeId
WHERE (ILDP.CreatedByDepartmentId = @DepartmentId OR @DepartmentId IS NULL)
    AND ILDPS.CurrentStateTypeId IN (
        SELECT value
        FROM dbo.StringAsIntTable(@StatusIds)
    )
GROUP BY ILDPST.name;

This returns the results:

enter image description here

However, I'd also like to be able to return statuses where the TodayCount is equal to 0 (i.e. any status with an id included in @StatusIds should be returned, regardless of TodayCount).

I've tried messing with some unions / joins / ctes but I couldn't quite get it to work. I'm not much of an SQL person so not sure what else to provide that could be useful.

Thanks!

Upvotes: 1

Views: 112

Answers (4)

t-clausen.dk
t-clausen.dk

Reputation: 44326

Try this instead:

DECLARE @StatusIds NVARCHAR(MAX) = '1,2,3,4,5';
DECLARE @DepartmentId NVARCHAR(2) = 'IT';

SELECT ILDPST.name,
       COUNT(ILDP.IntranetLoanDealPreStateId) AS TodayCount
FROM 
  dbo.TableC ILDPST 
  LEFT JOIN
    dbo.TableB ILDPS ON ILDPST.IntranetLoanDealPreStateTypeId = ILDPS.CurrentStateTypeId
  LEFT JOIN 
    dbo.TableA ILDP ON ILDPS.IntranetLoanDealPreStateId = ILDP.IntranetLoanDealPreStateId
  AND (ILDP.CreatedByDepartmentId = @DepartmentId OR @DepartmentId IS NULL)
WHERE 
    ILDPST.IntranetLoanDealPreStateTypeId
    IN (
         SELECT value
         FROM dbo.StringAsIntTable(@StatusIds)
       )
GROUP BY ILDPST.name;

Upvotes: 2

shadow
shadow

Reputation: 1903

It is not tested but give it a try:

;With Cte ( Value ) As
( Select Distinct Value From dbo.StringAsIntTable( @StatusIds ) )
Select
    ILDPST.name,
    COUNT(*) AS TodayCount
From
    dbo.TableC              As ILDPST
    Inner Join Cte                      On ( ILDPST.IntranetLoanDealPreStateTypeId = Cte.Value )                                            
    Left Join dbo.TableB    As ILDPS    On ( ILDPST.IntranetLoanDealPreStateTypeId = ILDPS.CurrentStateTypeId )
    Left Join dbo.TableA    As ILDP     On ( ILDPS.IntranetLoanDealPreStateId = ILDP.IntranetLoanDealPreStateId )
                                            And ( ( ILDP.CreatedByDepartmentId = @DepartmentId ) Or ( @DepartmentId Is Null ) ) 
Group By
    ILDPST.name

Upvotes: 0

Y.B.
Y.B.

Reputation: 3586

If you want to have all the records from TableC you need to left join all other tables to it, not left join it to the other tables. Also it's best to INNER JOIN the filtering table you create from @StatusIds rather then apply it through INclause. Try this:

DECLARE @StatusIds NVARCHAR(MAX) = '1,2,3,4,5';
DECLARE @DepartmentId NVARCHAR(2) = 'IT';

SELECT ILDPST.Name, COUNT(ILDP.IntranetLoanDealPreStateId) AS TodayCount
FROM (SELECT DISTINCT value FROM dbo.StringAsIntTable(@StatusIds)) StatusIds
INNER JOIN dbo.TableC ILDPST
  ON ILDPST.IntranetLoanDealPreStateTypeId = StatusIds.value
LEFT JOIN dbo.TableB ILDPS
  ON ILDPS.CurrentStateTypeId = ILDPST.IntranetLoanDealPreStateTypeId
LEFT JOIN dbo.TableA ILDP
  ON ILDP.IntranetLoanDealPreStateId = ILDPS.IntranetLoanDealPreStateId
 AND (ILDP.CreatedByDepartmentId = @DepartmentId OR @DepartmentId IS NULL)
GROUP BY ILDPST.Name;

Upvotes: 3

KMB
KMB

Reputation: 473

You could use the following function to create a table value for your status id's.

CREATE FUNCTION [dbo].[SplitString] 
(
    @myString varchar(max),
    @deliminator varchar(2)
)
RETURNS 
@ReturnTable TABLE 
(
    [Part] [varchar](max) NULL
)
AS
BEGIN
        Declare @iSpaces int
        Declare @part varchar(max)

        --initialize spaces
        Select @iSpaces = charindex(@deliminator,@myString,0)
        While @iSpaces > 0

        Begin
            Select @part = substring(@myString,0,charindex(@deliminator,@myString,0))

            Insert Into @ReturnTable(Part)
            Select @part

    Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(' ',@myString,0))


            Select @iSpaces = charindex(@deliminator,@myString,0)
        end

        If len(@myString) > 0
            Insert Into @ReturnTable
            Select @myString

    RETURN 
END

This can now be used as a table that you can LEFT JOIN to.

DECLARE @StatusIds NVARCHAR(MAX) = '1,2,3,4,5';

SELECT * FROM dbo.SplitString(@StatusIds, ',')

Upvotes: 0

Related Questions