CODEWITHSUNDEEP
sqlsql-server
Mohanad Shamsneh
Mohanad Shamsneh

Reputation: 49

How to Increment count based on condition

I'm trying to extract a new column based on a condition so the new column will act as a counter and it will just count if (MessageDate1- MessageDate2).TotalMinutes > 10.

For example if i've the following input:

╔════════════════╦═══════════════════════╦═══════════════════════╗
║ ConversationId ║     MessageDate1      ║     MessageDate2      ║
╠════════════════╬═══════════════════════╬═══════════════════════╣
║              1 ║ 2/15/2012 11:53:16 AM ║ 1/1/0001 12:00:00 AM  ║
║              1 ║ 2/16/2012 11:53:20 AM ║ 2/15/2012 11:53:16 AM ║
║              1 ║ 2/16/2012 11:54:01 AM ║ 2/16/2012 11:53:20 AM ║
║              1 ║ 2/16/2012 11:05:03 PM ║ 2/16/2012 11:54:01 AM ║
║              1 ║ 2/16/2012 11:09:03 PM ║ 2/16/2012 11:05:03 PM ║
║              1 ║ 2/16/2012 11:11:03 PM ║ 2/16/2012 11:09:03 PM ║
║              1 ║ 2/16/2012 11:11:04 PM ║ 2/16/2012 11:11:03 PM ║
║              1 ║ 2/16/2012 11:11:05 PM ║ 2/16/2012 11:11:04 PM ║
║              1 ║ 2/16/2012 11:22:06 PM ║ 2/16/2012 11:11:05 PM ║
║              1 ║ 2/17/2012 11:05:03 PM ║ 2/16/2012 11:22:06 PM ║
║              1 ║ 2/17/2012 11:09:03 PM ║ 2/17/2012 11:05:03 PM ║
║              1 ║ 2/17/2012 11:11:03 PM ║ 2/17/2012 11:09:03 PM ║
║              2 ║ 2/15/2012 11:53:18 AM ║ 1/1/0001 12:00:00 AM  ║
║              2 ║ 2/16/2012 11:54:01 AM ║ 2/15/2012 11:53:18 AM ║
║              2 ║ 2/16/2012 11:54:02 AM ║ 2/16/2012 11:54:01 AM ║
║              3 ║ 2/16/2012 11:53:50 AM ║ 1/1/0001 12:00:00 AM  ║
║              3 ║ 2/16/2012 11:54:50 AM ║ 2/16/2012 11:53:50 AM ║
║              4 ║ 2/16/2012 11:54:04 AM ║ 1/1/0001 12:00:00 AM  ║
╚════════════════╩═══════════════════════╩═══════════════════════╝

So the output would be:

╔════════════════╦═══════════════════════╦═══════════════════════╦═════════╗
║ ConversationID ║     MessageDate1      ║     MessageDate2      ║ Counter ║
╠════════════════╬═══════════════════════╬═══════════════════════╬═════════╣
║              1 ║ 2/15/2012 11:53:16 AM ║ 1/1/0001 12:00:00 AM  ║       1 ║
║              1 ║ 2/16/2012 11:53:20 AM ║ 2/15/2012 11:53:16 AM ║       2 ║
║              1 ║ 2/16/2012 11:54:01 AM ║ 2/16/2012 11:53:20 AM ║       2 ║
║              1 ║ 2/16/2012 11:05:03 PM ║ 2/16/2012 11:54:01 AM ║       3 ║
║              1 ║ 2/16/2012 11:09:03 PM ║ 2/16/2012 11:05:03 PM ║       3 ║
║              1 ║ 2/16/2012 11:11:03 PM ║ 2/16/2012 11:09:03 PM ║       3 ║
║              1 ║ 2/16/2012 11:11:04 PM ║ 2/16/2012 11:11:03 PM ║       3 ║
║              1 ║ 2/16/2012 11:11:05 PM ║ 2/16/2012 11:11:04 PM ║       3 ║
║              1 ║ 2/16/2012 11:22:06 PM ║ 2/16/2012 11:11:05 PM ║       4 ║
║              1 ║ 2/17/2012 11:05:03 PM ║ 2/16/2012 11:22:06 PM ║       5 ║
║              1 ║ 2/17/2012 11:09:03 PM ║ 2/17/2012 11:05:03 PM ║       5 ║
║              1 ║ 2/17/2012 11:11:03 PM ║ 2/17/2012 11:09:03 PM ║       5 ║
║              2 ║ 2/15/2012 11:53:18 AM ║ 1/1/0001 12:00:00 AM  ║       6 ║
║              2 ║ 2/16/2012 11:54:01 AM ║ 2/15/2012 11:53:18 AM ║       7 ║
║              2 ║ 2/16/2012 11:54:02 AM ║ 2/16/2012 11:54:01 AM ║       7 ║
║              3 ║ 2/16/2012 11:53:50 AM ║ 1/1/0001 12:00:00 AM  ║       8 ║
║              3 ║ 2/16/2012 11:54:50 AM ║ 2/16/2012 11:53:50 AM ║       8 ║
║              4 ║ 2/16/2012 11:54:04 AM ║ 1/1/0001 12:00:00 AM  ║       9 ║
╚════════════════╩═══════════════════════╩═══════════════════════╩═════════╝

Upvotes: 1

Views: 2172

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

Assuming you have a column that specifies the ordering, then you can use lag() and a cumulative sum():

select t.*,
       sum(case when prev_md2 > dateadd(minute, -10, messagedate1) and
                     prev_md2 < dateadd(minute, 10, messagedate1)
                then 0 else 1
           end) over (order by <ordering col>) as grp
from (select t.*,
             lag(messagedate2) over (order by <ordering col>) as prev_md2
      from t
     ) t;

Upvotes: 1

3N1GM4
3N1GM4

Reputation: 3351

Setting up some dummy data to mirror your stated question:

create table some_data
(
    id int,
    MessageDate1 datetime,
    MessageDate2 datetime
)

insert into some_data values
(1,'2012-02-15 11:53:16', '2012-02-15 00:00:00'),
(2,'2012-02-16 11:53:20', '2012-02-15 11:53:16'),
(3,'2012-02-16 11:54:01', '2012-02-15 11:53:20'),
(4,'2012-02-16 23:05:03', '2012-02-16 11:54:01'),
(5,'2012-02-16 23:09:03', '2012-02-16 23:05:03'),
(6,'2012-02-16 23:11:03', '2012-02-16 23:09:03'),
(7,'2012-02-16 23:11:04', '2012-02-16 23:11:03'),
(8,'2012-02-16 23:11:05', '2012-02-16 23:11:04'),
(9,'2012-02-16 23:22:06', '2012-02-16 23:11:05'),
(10,'2012-02-17 23:05:03', '2012-02-16 23:22:06'),
(11,'2012-02-17 23:09:03', '2012-02-17 23:05:03'),
(12,'2012-02-17 23:11:03', '2012-02-17 23:09:03'),
(13,'2012-02-15 11:53:18', '2012-02-15 00:00:00'),
(14,'2012-02-16 11:54:01', '2012-02-15 11:53:18'),
(15,'2012-02-16 11:54:02', '2012-02-16 11:54:01'),
(16,'2012-02-16 11:53:50', '2012-02-16 12:00:00'),
(17,'2012-02-16 11:54:50', '2012-02-16 11:53:50'),
(18,'2012-02-16 11:54:04', '2012-02-16 00:00:00')

I've included an id field to allow me to order the records as I could not see any particular logic to how your sample data was ordered and the way the count works is dependent on the order of the records. I also replaced date values you had for the year 0001.

This query gives what I believe the desired output is, where the Count is incremented each time a record is encountered where the difference between the MessageDate1 and MessageDate2 fields is more than 10 minutes:

select
    MessageDate1,
    MessageDate2,
    sum(GT_10M) over (order by id) as [Count]
from
(
    select
        id,
        MessageDate1,
        MessageDate2,
        case when abs(datediff(minute,MessageDate2,MessageDate1)) > 10 then 1 else 0 end as GT_10M
    from some_data
) r

Results:

/-----------------------------------------------------------\
|      MessageDate1       |      MessageDate2       | Count |
|-------------------------|-------------------------|-------|
| 2012-02-15 11:53:16.000 | 2012-02-15 00:00:00.000 |   1   |
| 2012-02-16 11:53:20.000 | 2012-02-15 11:53:16.000 |   2   |
| 2012-02-16 11:54:01.000 | 2012-02-15 11:53:20.000 |   3   |
| 2012-02-16 23:05:03.000 | 2012-02-16 11:54:01.000 |   4   |
| 2012-02-16 23:09:03.000 | 2012-02-16 23:05:03.000 |   4   |
| 2012-02-16 23:11:03.000 | 2012-02-16 23:09:03.000 |   4   |
| 2012-02-16 23:11:04.000 | 2012-02-16 23:11:03.000 |   4   |
| 2012-02-16 23:11:05.000 | 2012-02-16 23:11:04.000 |   4   |
| 2012-02-16 23:22:06.000 | 2012-02-16 23:11:05.000 |   5   |
| 2012-02-17 23:05:03.000 | 2012-02-16 23:22:06.000 |   6   |
| 2012-02-17 23:09:03.000 | 2012-02-17 23:05:03.000 |   6   |
| 2012-02-17 23:11:03.000 | 2012-02-17 23:09:03.000 |   6   |
| 2012-02-15 11:53:18.000 | 2012-02-15 00:00:00.000 |   7   |
| 2012-02-16 11:54:01.000 | 2012-02-15 11:53:18.000 |   8   |
| 2012-02-16 11:54:02.000 | 2012-02-16 11:54:01.000 |   8   |
| 2012-02-16 11:53:50.000 | 2012-02-16 12:00:00.000 |   8   |
| 2012-02-16 11:54:50.000 | 2012-02-16 11:53:50.000 |   8   |
| 2012-02-16 11:54:04.000 | 2012-02-16 00:00:00.000 |   9   |
\-----------------------------------------------------------/

Upvotes: 0

Related Questions