Adding Conditional Clause(Where) to Dense Rank Function

Question

I want to create an Rank function to count the number of times a person a visited to property BY DATE but with condition of not including a visit category. 'Calls'

DENSE_RANK() over(partition by activitytable.[Property] 
ORDER BY activitytable.[Date] as Job rank

Doing this ranks the whole of the communication table which I dont want.

ActivityID	Property	DATE	CommunicationType	Rank
1046	Red Property	30/10/2019	Field	2
10467	Red Property	29/10/2019	Field	1
10591	Red Property	28/10/2019	Calls
10971	Blue Property	27/10/2019	Field	2
10971	Blue Property	26/10/2019	Field	1
10971	Blue Property	26/10/2019	calls
10965	Green Property	24/10/2019	calls
10765	Green Property	23/10/2019	calls
10765	Green Property	19/10/2019	field	3
10765	Green Property	15/10/2019	field	2
10765	Green Property	12/10/2019	field	1

Ideally I want the table to appear like above to ignoring the calls elements of the communication type column and count only fields category. How could I do this?

Answer 1

You need to partition by Property and CommunicationType:

Table:

CREATE TABLE #Data (
    ActivityID int,
    Property varchar(100),
    [DATE] date,
    CommunicationType varchar(10)
)
INSERT INTO #Data
    (ActivityID, Property, [DATE], CommunicationType)
VALUES
    (1046,  'Red Property',    '20191030', 'field'),
    (10467, 'Red Property',    '20191029', 'field'),
    (10591, 'Red Property',    '20191028', 'calls'),
    (10971, 'Blue Property',   '20191027', 'field'),
    (10971, 'Blue Property',   '20191026', 'field'),
    (10971, 'Blue Property',   '20191026', 'calls'),
    (10965, 'Green Property',  '20191024', 'calls'),
    (10765, 'Green Property',  '20191023', 'calls'),
    (10765, 'Green Property',  '20191019', 'field'),
    (10765, 'Green Property',  '20191015', 'field'),
    (10765, 'Green Property',  '20191012', 'field')

Statement:

SELECT 
    *,
    CASE 
        WHEN CommunicationType = 'field' THEN DENSE_RANK() OVER (PARTITION BY Property, CommunicationType ORDER BY [DATE] ASC)
        ELSE NULL
    END AS Rank
FROM #Data

Result:

ActivityID  Property    DATE        CommunicationType   Rank
10971   Blue Property   2019-10-26  calls               NULL
10971   Blue Property   2019-10-26  field               1
10971   Blue Property   2019-10-27  field               2
10765   Green Property  2019-10-23  calls               NULL
10965   Green Property  2019-10-24  calls               NULL
10765   Green Property  2019-10-12  field               1
10765   Green Property  2019-10-15  field               2
10765   Green Property  2019-10-19  field               3
10591   Red Property    2019-10-28  calls               NULL
10467   Red Property    2019-10-29  field               1
1046    Red Property    2019-10-30  field               2

Answer 2

Just try:

CASE WHEN CommunicationType <> 'CAlls' THEN DENSE_RANK() over(partition by activitytable.[Property] ORDER BY activitytable.[Date] ELSE NULL END AS Job rank