I want to create a conditional int field called Sequence for each group of IDs.
The value 1 is given to Sequence for the first occurrence of a condition, otherwise increment the last count by 1. There are finite list of values for the field condition as illustrated below.
For a new group of ID, Sequence should initialise and start counting from 1.
| ID | Date | Condition | Seq |
|---|---|---|---|
| 01 | 01Jun14 | AAAAAAAAA | 1 |
| 01 | 02Jun14 | AAAAAAAAA | 2 |
| 01 | 03Jun14 | BBBBBBBBB | 1 |
| 01 | 04Jun14 | BBBBBBBBB | 2 |
| 01 | 05Jun14 | AAAAAAAAA | 3 |
| 01 | 06Jun14 | BBBBBBBBB | 3 |
| 01 | 07Jun14 | EEEEEEEEE | 1 |
| 02 | 01Jun14 | AAAAAAAAA | 1 |
| 02 | 02Jun14 | CCCCCCCCC | 1 |
| 02 | 03Jun14 | CCCCCCCCC | 2 |
| 02 | 04Jun14 | BBBBBBBBB | 1 |
| 02 | 05Jun14 | AAAAAAAAA | 2 |
| 02 | 06Jun14 | BBBBBBBBB | 2 |
| 03 | 01Jun14 | FFFFFFFFF | 1 |
| 03 | 02Jun14 | AAAAAAAAA | 1 |
| 03 | 03Jun14 | AAAAAAAAA | 2 |
| 03 | 04Jun14 | CCCCCCCCC | 1 |
CodePudding user response：
I think dense_rank() should do the trick.
select
ID,
[Date],
Condition,
dense_RANK() OVER(PARTITION by ID ORDER BY [Date]) as Seq
from Yourtable
CodePudding user response：
As pointed out in the comments by Squirrel, this is a job for row number Basically
Seq = ROW_NUMBER() OVER (PARTITION BY ID, CONDITION ORDER BY DATE ASC)
If you need to incorporate this into your code while Insert statement, you'd do something like this
DECLARE @Seq INT
SELECT @Seq= ROW_NUMBER() OVER (PARTITION BY ID, CONDITION ORDER BY DATE ASC) FROM <yourtable> WHERE ID=@ID AND CONDITION= @condition
INSERT INTO <yourtable> VALUES
(@ID, @date, @condition, ISNULL(@Seq,1))
CodePudding user response：
You need to use partition by condition, id in your row_number so that the row_numbers start at 1 for each combination of condition and id.
I have created a table with your required numbering so as to check that the calulated values are what you need.
select ID, d "Date" , Condition, Seq seq_required, row_number() over (partition by condition, id order by d) seq_calculated from t order by id, d GOID | Date | Condition | seq_required | seq_calculated -: | :--------- | :-------- | -----------: | -------------: 1 | 2014-06-01 | AAAAAAAAA | 1 | 1 1 | 2014-06-02 | AAAAAAAAA | 2 | 2 1 | 2014-06-03 | BBBBBBBBB | 1 | 1 1 | 2014-06-04 | BBBBBBBBB | 2 | 2 1 | 2014-06-05 | AAAAAAAAA | 3 | 3 1 | 2014-06-06 | BBBBBBBBB | 3 | 3 1 | 2014-06-07 | EEEEEEEEE | 1 | 1 2 | 2014-06-01 | AAAAAAAAA | 1 | 1 2 | 2014-06-02 | CCCCCCCCC | 1 | 1 2 | 2014-06-03 | CCCCCCCCC | 2 | 2 2 | 2014-06-04 | BBBBBBBBB | 1 | 1 2 | 2014-06-05 | AAAAAAAAA | 2 | 2 2 | 2014-06-06 | BBBBBBBBB | 2 | 2 3 | 2014-06-01 | FFFFFFFFF | 1 | 1 3 | 2014-06-02 | AAAAAAAAA | 1 | 1 3 | 2014-06-03 | AAAAAAAAA | 2 | 2 3 | 2014-06-04 | CCCCCCCCC | 1 | 1
db<>fiddle here
CodePudding user response：
SELECT ID,Condition,COUNT(con) as seq FROM "your table name" GROUP BY con
SELECT ID,Condition,COUNT(con) as seq FROM "your table name" where ID=1 GROUP BY con
desc:
1- for counting all type of Conditional in table
2- for counting each type of Conditional in each group of IDs