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.

TABLE

---- 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?

Please show the results that you want. – Gordon Linoff Oct 31 '19 at 12:15 — Gordon Linoff, Oct 31 '19 at 12:15

Zhorov · Answer 1 · 2019-10-31T12:45:04.153

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

@shevchenko2020 I hope it helps. If you think that this or any other answer is the best solution to your problem, you may accept it (only one answer can be accepted). Thanks. — Zhorov, Oct 31 '19 at 14:17

score 1 · Answer 2 · answered Oct 31 '19 at 12:08

1

Just try:

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

answered Oct 31 '19 at 12:08

gotqn

42,737
46
157
243

I think you need to partition by `CommunicationType ` as well. – Alex Oct 31 '19 at 12:55
@Alex Basically, I just copy the OP clause - the key point here is to use `CASE WHEN` or `IIF` to exclude some of the rows from ranking - the ranking clause may be as he likes. – gotqn Oct 31 '19 at 12:57
I believe you don't exclude rows from ranking this way, you only hide the value in Select i.e. the value is counted in the rank, just not displayed. Same as `CASE ... SUM` is different to `SUM( CASE ...`. – Alex Oct 31 '19 at 13:02
Thanks for your answer, almost works but this doesn't complete ignores the nulls and just skips over it like. 1,2,3 Null, 5 etc – shevchenko2020 Oct 31 '19 at 14:00

score 1 · Answer 3 · answered Oct 31 '19 at 12:14

You have no duplicate dates, so just use a cumulative sum:

sum(case when communicationtype <> 'Calls'
         then 1 else 0
    end) over (partition by activitytable.[Property] order by activitytable.[Date]
              ) as Job_rank

Presumably, you also have a column that represents the "person", if you want this ranking per person.

If you do need dense_rank() because there are duplicates, then the answer is more challenging. The following should do what you want:

select (case when communicationtype = 'Visits'
             then dense_rank() over (partition by activitytable.[Property], communicationtype
                                     order by activitytable.[Date]
                                    )
        end) as job_rank

Note: the value will not appear on the rows with Calls. It is not clear if that is necessary.

Thanks, this looks like it works. The key thing here is to add the communication type to the partition by with the case when statement. Cheers — shevchenko2020, Oct 31 '19 at 13:58

Adding Conditional Clause(Where) to Dense Rank Function

3 Answers3