Group Customers by Status in T-SQL
I have a table like this:
customer_id mis_date status
----------------------------
10003 2014-01-01 1
10003 2014-01-02 1
10003 2014-01-03 0
10003 2014-01-04 0
10003 2014-01-05 0
10003 2014-01-06 1
10003 2014-01-07 1
10003 2014-01-08 1
10003 2014-01-09 1
10003 2014-01-10 0
10003 2014-01-11 0
10003 2014-01-12 0
10003 2014-01-13 1
10003 2014-01-14 1
10003 2014-01-15 1
I'm trying to build the "group" column:
customer_id mis_date status group
----------------------------------
10003 2014-01-01 1 1
10003 2014-01-02 1 1
10003 2014-01-03 0 NULL
10003 2014-01-04 0 NULL
10003 2014-01-05 0 NULL
10003 2014-01-06 1 2
10003 2014-01-07 1 2
10003 2014-01-08 1 2
10003 2014-01-09 1 2
10003 2014-01-10 0 NULL
10003 2014-01-11 0 NULL
10003 2014-01-12 0 NULL
10003 2014-01-13 1 3
10003 2014-01-14 1 3
10003 2014-01-15 1 3
Does anyone know how I can build this group column?
The logic: each day I'm tracking after the customer status and I want to know in each day what is the number of times that that status happened in the customer history but only when he is in the status.
For example: first_time - 1, second_time - 2 etc.
I am kicking my head off and can't find a solution. I guess it's not so complex.
Thanks!
sql sql-server
add a comment |
I have a table like this:
customer_id mis_date status
----------------------------
10003 2014-01-01 1
10003 2014-01-02 1
10003 2014-01-03 0
10003 2014-01-04 0
10003 2014-01-05 0
10003 2014-01-06 1
10003 2014-01-07 1
10003 2014-01-08 1
10003 2014-01-09 1
10003 2014-01-10 0
10003 2014-01-11 0
10003 2014-01-12 0
10003 2014-01-13 1
10003 2014-01-14 1
10003 2014-01-15 1
I'm trying to build the "group" column:
customer_id mis_date status group
----------------------------------
10003 2014-01-01 1 1
10003 2014-01-02 1 1
10003 2014-01-03 0 NULL
10003 2014-01-04 0 NULL
10003 2014-01-05 0 NULL
10003 2014-01-06 1 2
10003 2014-01-07 1 2
10003 2014-01-08 1 2
10003 2014-01-09 1 2
10003 2014-01-10 0 NULL
10003 2014-01-11 0 NULL
10003 2014-01-12 0 NULL
10003 2014-01-13 1 3
10003 2014-01-14 1 3
10003 2014-01-15 1 3
Does anyone know how I can build this group column?
The logic: each day I'm tracking after the customer status and I want to know in each day what is the number of times that that status happened in the customer history but only when he is in the status.
For example: first_time - 1, second_time - 2 etc.
I am kicking my head off and can't find a solution. I guess it's not so complex.
Thanks!
sql sql-server
Seems like you want a partition, not a group
– Diego Rafael Souza
8 hours ago
I think this is the expected result, we need to know actual data and logic behind it
– Simonare
8 hours ago
I've edit my post.
– Jordan1200
7 hours ago
please explain more
– Dumidu Udayanga
7 hours ago
add a comment |
I have a table like this:
customer_id mis_date status
----------------------------
10003 2014-01-01 1
10003 2014-01-02 1
10003 2014-01-03 0
10003 2014-01-04 0
10003 2014-01-05 0
10003 2014-01-06 1
10003 2014-01-07 1
10003 2014-01-08 1
10003 2014-01-09 1
10003 2014-01-10 0
10003 2014-01-11 0
10003 2014-01-12 0
10003 2014-01-13 1
10003 2014-01-14 1
10003 2014-01-15 1
I'm trying to build the "group" column:
customer_id mis_date status group
----------------------------------
10003 2014-01-01 1 1
10003 2014-01-02 1 1
10003 2014-01-03 0 NULL
10003 2014-01-04 0 NULL
10003 2014-01-05 0 NULL
10003 2014-01-06 1 2
10003 2014-01-07 1 2
10003 2014-01-08 1 2
10003 2014-01-09 1 2
10003 2014-01-10 0 NULL
10003 2014-01-11 0 NULL
10003 2014-01-12 0 NULL
10003 2014-01-13 1 3
10003 2014-01-14 1 3
10003 2014-01-15 1 3
Does anyone know how I can build this group column?
The logic: each day I'm tracking after the customer status and I want to know in each day what is the number of times that that status happened in the customer history but only when he is in the status.
For example: first_time - 1, second_time - 2 etc.
I am kicking my head off and can't find a solution. I guess it's not so complex.
Thanks!
sql sql-server
I have a table like this:
customer_id mis_date status
----------------------------
10003 2014-01-01 1
10003 2014-01-02 1
10003 2014-01-03 0
10003 2014-01-04 0
10003 2014-01-05 0
10003 2014-01-06 1
10003 2014-01-07 1
10003 2014-01-08 1
10003 2014-01-09 1
10003 2014-01-10 0
10003 2014-01-11 0
10003 2014-01-12 0
10003 2014-01-13 1
10003 2014-01-14 1
10003 2014-01-15 1
I'm trying to build the "group" column:
customer_id mis_date status group
----------------------------------
10003 2014-01-01 1 1
10003 2014-01-02 1 1
10003 2014-01-03 0 NULL
10003 2014-01-04 0 NULL
10003 2014-01-05 0 NULL
10003 2014-01-06 1 2
10003 2014-01-07 1 2
10003 2014-01-08 1 2
10003 2014-01-09 1 2
10003 2014-01-10 0 NULL
10003 2014-01-11 0 NULL
10003 2014-01-12 0 NULL
10003 2014-01-13 1 3
10003 2014-01-14 1 3
10003 2014-01-15 1 3
Does anyone know how I can build this group column?
The logic: each day I'm tracking after the customer status and I want to know in each day what is the number of times that that status happened in the customer history but only when he is in the status.
For example: first_time - 1, second_time - 2 etc.
I am kicking my head off and can't find a solution. I guess it's not so complex.
Thanks!
sql sql-server
sql sql-server
edited 7 hours ago
marc_s
574k12811091256
574k12811091256
asked 8 hours ago
Jordan1200Jordan1200
17110
17110
Seems like you want a partition, not a group
– Diego Rafael Souza
8 hours ago
I think this is the expected result, we need to know actual data and logic behind it
– Simonare
8 hours ago
I've edit my post.
– Jordan1200
7 hours ago
please explain more
– Dumidu Udayanga
7 hours ago
add a comment |
Seems like you want a partition, not a group
– Diego Rafael Souza
8 hours ago
I think this is the expected result, we need to know actual data and logic behind it
– Simonare
8 hours ago
I've edit my post.
– Jordan1200
7 hours ago
please explain more
– Dumidu Udayanga
7 hours ago
Seems like you want a partition, not a group
– Diego Rafael Souza
8 hours ago
Seems like you want a partition, not a group
– Diego Rafael Souza
8 hours ago
I think this is the expected result, we need to know actual data and logic behind it
– Simonare
8 hours ago
I think this is the expected result, we need to know actual data and logic behind it
– Simonare
8 hours ago
I've edit my post.
– Jordan1200
7 hours ago
I've edit my post.
– Jordan1200
7 hours ago
please explain more
– Dumidu Udayanga
7 hours ago
please explain more
– Dumidu Udayanga
7 hours ago
add a comment |
5 Answers
5
active
oldest
votes
Something like this should work:
;WITH CTE AS (
SELECT customer_id, mis_date, status,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY mis_date) -
ROW_NUMBER() OVER (PARTITION BY customer_id, status ORDER BY mis_date) AS grp
FROM mytable
), CTE2 AS (
SELECT customer_id, status, grp,
ROW_NUMBER() OVER (ORDER BY MIN(mis_date)) AS rn
FROM CTE
WHERE status = 1
GROUP BY customer_id, status, grp
)
SELECT c.customer_id, c.mis_date, c.status, rn
FROM CTE c
LEFT JOIN CTE2 c2
ON c.customer_id = c2.customer_id AND c.status = c2.status AND c.grp = c2.grp
ORDER BY mis_date
CTE
identifies islands of consecutive records having the same status
value. CTE2
enumerates status = 1
subgroups.
add a comment |
Another approach of doing it without CTE
is like following query.
SELECT customer_id, mis_date, status,
CASE WHEN status = 0 THEN NULL ELSE Dense_rank() OVER (ORDER BY rc) END grp
FROM (SELECT *,
(SELECT CASE WHEN status = 0 THEN 0
ELSE (SELECT Count(status) FROM table1 t2
WHERE t2.mis_date <= t1.mis_date AND status = 0) END grp)rc
FROM table1 t1) t2
ORDER BY mis_date
Output:
+-------------+-------------------------+--------+------+
| customer_id | mis_date | status | grp |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-01 00:00:00.000 | 1 | 1 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-02 00:00:00.000 | 1 | 1 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-03 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-04 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-05 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-06 00:00:00.000 | 1 | 2 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-07 00:00:00.000 | 1 | 2 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-08 00:00:00.000 | 1 | 2 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-09 00:00:00.000 | 1 | 2 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-10 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-11 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-12 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-13 00:00:00.000 | 1 | 3 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-14 00:00:00.000 | 1 | 3 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-15 00:00:00.000 | 1 | 3 |
+-------------+-------------------------+--------+------+
Online Demo
add a comment |
You can identify each group of "1" by the number of non-zero statuses before them. If you don't care that the group numbers are sequential:
select t.*,
(case when status = 1
then sum(case when status = 0 then 1 else 0 end) over (partition by order by customer_id mis_date)
end) as grp
from t;
No subqueries, joins or aggregation.
However, you probably want the numbers sequential (as in your example). For that, a subquery is needed:
select t.*,
(case when status = 1
then dense_rank() over (partition by customer_id order by grp1)
end) as grp
from (select t.*,
sum(case when status = 0 then 1 else 0 end) over (partition by order by customer_id mis_date) as grp1
from t
) t
just as FYI, I think there are some syntax error partition by order by customer_id mis_date
– Avi
43 mins ago
add a comment |
Check this solution please. This adds grouping as per your need
with cte0 as
(
select [customer_id], [mis_date], [status],
COALESCE(LAG(status) over (order by mis_date), status) oldstatus
FRom Table1
),
cte1 as (
select cte0.*,
case when status = 0 then
null
else
COUNT( case when status != oldStatus and status = 0 then 1 else null end) OVER (ORDER BY mis_date)
end + 1 grp
from cte0
)
select * from cte1
GO
customer_id | mis_date | status | oldstatus | grp
----------: | :------------------ | -----: | --------: | ---:
10003 | 01/01/2014 00:00:00 | 1 | 1 | 1
10003 | 02/01/2014 00:00:00 | 1 | 1 | 1
10003 | 03/01/2014 00:00:00 | 0 | 1 | null
10003 | 04/01/2014 00:00:00 | 0 | 0 | null
10003 | 05/01/2014 00:00:00 | 0 | 0 | null
10003 | 06/01/2014 00:00:00 | 1 | 0 | 2
10003 | 07/01/2014 00:00:00 | 1 | 1 | 2
10003 | 08/01/2014 00:00:00 | 1 | 1 | 2
10003 | 09/01/2014 00:00:00 | 1 | 1 | 2
10003 | 10/01/2014 00:00:00 | 0 | 1 | null
10003 | 11/01/2014 00:00:00 | 0 | 0 | null
10003 | 12/01/2014 00:00:00 | 0 | 0 | null
10003 | 13/01/2014 00:00:00 | 1 | 0 | 3
10003 | 14/01/2014 00:00:00 | 1 | 1 | 3
10003 | 15/01/2014 00:00:00 | 1 | 1 | 3
Working Fiddle
add a comment |
You can use the ALTER TABLE statement in SQL Server to add a column to a table.
Syntax
The syntax to add a column in a table in SQL Server (Transact-SQL) is:
ALTER TABLE table_name
ADD column_name column_definition;
Let's look at an example that shows how to add a column in an SQL Server table using the ALTER TABLE statement.
For example:
ALTER TABLE customer
ADD group VARCHAR(10);
This SQL Server ALTER TABLE example will add a column to the customer table called group.
Thank's but you missed understood me. I want to generate the values in the column.
– Jordan1200
7 hours ago
Can't understand, could you please explain more
– Dumidu Udayanga
7 hours ago
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54387610%2fgroup-customers-by-status-in-t-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
5 Answers
5
active
oldest
votes
5 Answers
5
active
oldest
votes
active
oldest
votes
active
oldest
votes
Something like this should work:
;WITH CTE AS (
SELECT customer_id, mis_date, status,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY mis_date) -
ROW_NUMBER() OVER (PARTITION BY customer_id, status ORDER BY mis_date) AS grp
FROM mytable
), CTE2 AS (
SELECT customer_id, status, grp,
ROW_NUMBER() OVER (ORDER BY MIN(mis_date)) AS rn
FROM CTE
WHERE status = 1
GROUP BY customer_id, status, grp
)
SELECT c.customer_id, c.mis_date, c.status, rn
FROM CTE c
LEFT JOIN CTE2 c2
ON c.customer_id = c2.customer_id AND c.status = c2.status AND c.grp = c2.grp
ORDER BY mis_date
CTE
identifies islands of consecutive records having the same status
value. CTE2
enumerates status = 1
subgroups.
add a comment |
Something like this should work:
;WITH CTE AS (
SELECT customer_id, mis_date, status,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY mis_date) -
ROW_NUMBER() OVER (PARTITION BY customer_id, status ORDER BY mis_date) AS grp
FROM mytable
), CTE2 AS (
SELECT customer_id, status, grp,
ROW_NUMBER() OVER (ORDER BY MIN(mis_date)) AS rn
FROM CTE
WHERE status = 1
GROUP BY customer_id, status, grp
)
SELECT c.customer_id, c.mis_date, c.status, rn
FROM CTE c
LEFT JOIN CTE2 c2
ON c.customer_id = c2.customer_id AND c.status = c2.status AND c.grp = c2.grp
ORDER BY mis_date
CTE
identifies islands of consecutive records having the same status
value. CTE2
enumerates status = 1
subgroups.
add a comment |
Something like this should work:
;WITH CTE AS (
SELECT customer_id, mis_date, status,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY mis_date) -
ROW_NUMBER() OVER (PARTITION BY customer_id, status ORDER BY mis_date) AS grp
FROM mytable
), CTE2 AS (
SELECT customer_id, status, grp,
ROW_NUMBER() OVER (ORDER BY MIN(mis_date)) AS rn
FROM CTE
WHERE status = 1
GROUP BY customer_id, status, grp
)
SELECT c.customer_id, c.mis_date, c.status, rn
FROM CTE c
LEFT JOIN CTE2 c2
ON c.customer_id = c2.customer_id AND c.status = c2.status AND c.grp = c2.grp
ORDER BY mis_date
CTE
identifies islands of consecutive records having the same status
value. CTE2
enumerates status = 1
subgroups.
Something like this should work:
;WITH CTE AS (
SELECT customer_id, mis_date, status,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY mis_date) -
ROW_NUMBER() OVER (PARTITION BY customer_id, status ORDER BY mis_date) AS grp
FROM mytable
), CTE2 AS (
SELECT customer_id, status, grp,
ROW_NUMBER() OVER (ORDER BY MIN(mis_date)) AS rn
FROM CTE
WHERE status = 1
GROUP BY customer_id, status, grp
)
SELECT c.customer_id, c.mis_date, c.status, rn
FROM CTE c
LEFT JOIN CTE2 c2
ON c.customer_id = c2.customer_id AND c.status = c2.status AND c.grp = c2.grp
ORDER BY mis_date
CTE
identifies islands of consecutive records having the same status
value. CTE2
enumerates status = 1
subgroups.
answered 7 hours ago
Giorgos BetsosGiorgos Betsos
61.6k73064
61.6k73064
add a comment |
add a comment |
Another approach of doing it without CTE
is like following query.
SELECT customer_id, mis_date, status,
CASE WHEN status = 0 THEN NULL ELSE Dense_rank() OVER (ORDER BY rc) END grp
FROM (SELECT *,
(SELECT CASE WHEN status = 0 THEN 0
ELSE (SELECT Count(status) FROM table1 t2
WHERE t2.mis_date <= t1.mis_date AND status = 0) END grp)rc
FROM table1 t1) t2
ORDER BY mis_date
Output:
+-------------+-------------------------+--------+------+
| customer_id | mis_date | status | grp |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-01 00:00:00.000 | 1 | 1 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-02 00:00:00.000 | 1 | 1 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-03 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-04 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-05 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-06 00:00:00.000 | 1 | 2 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-07 00:00:00.000 | 1 | 2 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-08 00:00:00.000 | 1 | 2 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-09 00:00:00.000 | 1 | 2 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-10 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-11 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-12 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-13 00:00:00.000 | 1 | 3 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-14 00:00:00.000 | 1 | 3 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-15 00:00:00.000 | 1 | 3 |
+-------------+-------------------------+--------+------+
Online Demo
add a comment |
Another approach of doing it without CTE
is like following query.
SELECT customer_id, mis_date, status,
CASE WHEN status = 0 THEN NULL ELSE Dense_rank() OVER (ORDER BY rc) END grp
FROM (SELECT *,
(SELECT CASE WHEN status = 0 THEN 0
ELSE (SELECT Count(status) FROM table1 t2
WHERE t2.mis_date <= t1.mis_date AND status = 0) END grp)rc
FROM table1 t1) t2
ORDER BY mis_date
Output:
+-------------+-------------------------+--------+------+
| customer_id | mis_date | status | grp |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-01 00:00:00.000 | 1 | 1 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-02 00:00:00.000 | 1 | 1 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-03 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-04 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-05 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-06 00:00:00.000 | 1 | 2 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-07 00:00:00.000 | 1 | 2 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-08 00:00:00.000 | 1 | 2 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-09 00:00:00.000 | 1 | 2 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-10 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-11 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-12 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-13 00:00:00.000 | 1 | 3 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-14 00:00:00.000 | 1 | 3 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-15 00:00:00.000 | 1 | 3 |
+-------------+-------------------------+--------+------+
Online Demo
add a comment |
Another approach of doing it without CTE
is like following query.
SELECT customer_id, mis_date, status,
CASE WHEN status = 0 THEN NULL ELSE Dense_rank() OVER (ORDER BY rc) END grp
FROM (SELECT *,
(SELECT CASE WHEN status = 0 THEN 0
ELSE (SELECT Count(status) FROM table1 t2
WHERE t2.mis_date <= t1.mis_date AND status = 0) END grp)rc
FROM table1 t1) t2
ORDER BY mis_date
Output:
+-------------+-------------------------+--------+------+
| customer_id | mis_date | status | grp |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-01 00:00:00.000 | 1 | 1 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-02 00:00:00.000 | 1 | 1 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-03 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-04 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-05 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-06 00:00:00.000 | 1 | 2 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-07 00:00:00.000 | 1 | 2 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-08 00:00:00.000 | 1 | 2 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-09 00:00:00.000 | 1 | 2 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-10 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-11 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-12 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-13 00:00:00.000 | 1 | 3 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-14 00:00:00.000 | 1 | 3 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-15 00:00:00.000 | 1 | 3 |
+-------------+-------------------------+--------+------+
Online Demo
Another approach of doing it without CTE
is like following query.
SELECT customer_id, mis_date, status,
CASE WHEN status = 0 THEN NULL ELSE Dense_rank() OVER (ORDER BY rc) END grp
FROM (SELECT *,
(SELECT CASE WHEN status = 0 THEN 0
ELSE (SELECT Count(status) FROM table1 t2
WHERE t2.mis_date <= t1.mis_date AND status = 0) END grp)rc
FROM table1 t1) t2
ORDER BY mis_date
Output:
+-------------+-------------------------+--------+------+
| customer_id | mis_date | status | grp |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-01 00:00:00.000 | 1 | 1 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-02 00:00:00.000 | 1 | 1 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-03 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-04 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-05 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-06 00:00:00.000 | 1 | 2 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-07 00:00:00.000 | 1 | 2 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-08 00:00:00.000 | 1 | 2 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-09 00:00:00.000 | 1 | 2 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-10 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-11 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-12 00:00:00.000 | 0 | NULL |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-13 00:00:00.000 | 1 | 3 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-14 00:00:00.000 | 1 | 3 |
+-------------+-------------------------+--------+------+
| 10003 | 2014-01-15 00:00:00.000 | 1 | 3 |
+-------------+-------------------------+--------+------+
Online Demo
edited 7 hours ago
answered 7 hours ago
PSKPSK
8,29431532
8,29431532
add a comment |
add a comment |
You can identify each group of "1" by the number of non-zero statuses before them. If you don't care that the group numbers are sequential:
select t.*,
(case when status = 1
then sum(case when status = 0 then 1 else 0 end) over (partition by order by customer_id mis_date)
end) as grp
from t;
No subqueries, joins or aggregation.
However, you probably want the numbers sequential (as in your example). For that, a subquery is needed:
select t.*,
(case when status = 1
then dense_rank() over (partition by customer_id order by grp1)
end) as grp
from (select t.*,
sum(case when status = 0 then 1 else 0 end) over (partition by order by customer_id mis_date) as grp1
from t
) t
just as FYI, I think there are some syntax error partition by order by customer_id mis_date
– Avi
43 mins ago
add a comment |
You can identify each group of "1" by the number of non-zero statuses before them. If you don't care that the group numbers are sequential:
select t.*,
(case when status = 1
then sum(case when status = 0 then 1 else 0 end) over (partition by order by customer_id mis_date)
end) as grp
from t;
No subqueries, joins or aggregation.
However, you probably want the numbers sequential (as in your example). For that, a subquery is needed:
select t.*,
(case when status = 1
then dense_rank() over (partition by customer_id order by grp1)
end) as grp
from (select t.*,
sum(case when status = 0 then 1 else 0 end) over (partition by order by customer_id mis_date) as grp1
from t
) t
just as FYI, I think there are some syntax error partition by order by customer_id mis_date
– Avi
43 mins ago
add a comment |
You can identify each group of "1" by the number of non-zero statuses before them. If you don't care that the group numbers are sequential:
select t.*,
(case when status = 1
then sum(case when status = 0 then 1 else 0 end) over (partition by order by customer_id mis_date)
end) as grp
from t;
No subqueries, joins or aggregation.
However, you probably want the numbers sequential (as in your example). For that, a subquery is needed:
select t.*,
(case when status = 1
then dense_rank() over (partition by customer_id order by grp1)
end) as grp
from (select t.*,
sum(case when status = 0 then 1 else 0 end) over (partition by order by customer_id mis_date) as grp1
from t
) t
You can identify each group of "1" by the number of non-zero statuses before them. If you don't care that the group numbers are sequential:
select t.*,
(case when status = 1
then sum(case when status = 0 then 1 else 0 end) over (partition by order by customer_id mis_date)
end) as grp
from t;
No subqueries, joins or aggregation.
However, you probably want the numbers sequential (as in your example). For that, a subquery is needed:
select t.*,
(case when status = 1
then dense_rank() over (partition by customer_id order by grp1)
end) as grp
from (select t.*,
sum(case when status = 0 then 1 else 0 end) over (partition by order by customer_id mis_date) as grp1
from t
) t
answered 5 hours ago
Gordon LinoffGordon Linoff
767k35300402
767k35300402
just as FYI, I think there are some syntax error partition by order by customer_id mis_date
– Avi
43 mins ago
add a comment |
just as FYI, I think there are some syntax error partition by order by customer_id mis_date
– Avi
43 mins ago
just as FYI, I think there are some syntax error partition by order by customer_id mis_date
– Avi
43 mins ago
just as FYI, I think there are some syntax error partition by order by customer_id mis_date
– Avi
43 mins ago
add a comment |
Check this solution please. This adds grouping as per your need
with cte0 as
(
select [customer_id], [mis_date], [status],
COALESCE(LAG(status) over (order by mis_date), status) oldstatus
FRom Table1
),
cte1 as (
select cte0.*,
case when status = 0 then
null
else
COUNT( case when status != oldStatus and status = 0 then 1 else null end) OVER (ORDER BY mis_date)
end + 1 grp
from cte0
)
select * from cte1
GO
customer_id | mis_date | status | oldstatus | grp
----------: | :------------------ | -----: | --------: | ---:
10003 | 01/01/2014 00:00:00 | 1 | 1 | 1
10003 | 02/01/2014 00:00:00 | 1 | 1 | 1
10003 | 03/01/2014 00:00:00 | 0 | 1 | null
10003 | 04/01/2014 00:00:00 | 0 | 0 | null
10003 | 05/01/2014 00:00:00 | 0 | 0 | null
10003 | 06/01/2014 00:00:00 | 1 | 0 | 2
10003 | 07/01/2014 00:00:00 | 1 | 1 | 2
10003 | 08/01/2014 00:00:00 | 1 | 1 | 2
10003 | 09/01/2014 00:00:00 | 1 | 1 | 2
10003 | 10/01/2014 00:00:00 | 0 | 1 | null
10003 | 11/01/2014 00:00:00 | 0 | 0 | null
10003 | 12/01/2014 00:00:00 | 0 | 0 | null
10003 | 13/01/2014 00:00:00 | 1 | 0 | 3
10003 | 14/01/2014 00:00:00 | 1 | 1 | 3
10003 | 15/01/2014 00:00:00 | 1 | 1 | 3
Working Fiddle
add a comment |
Check this solution please. This adds grouping as per your need
with cte0 as
(
select [customer_id], [mis_date], [status],
COALESCE(LAG(status) over (order by mis_date), status) oldstatus
FRom Table1
),
cte1 as (
select cte0.*,
case when status = 0 then
null
else
COUNT( case when status != oldStatus and status = 0 then 1 else null end) OVER (ORDER BY mis_date)
end + 1 grp
from cte0
)
select * from cte1
GO
customer_id | mis_date | status | oldstatus | grp
----------: | :------------------ | -----: | --------: | ---:
10003 | 01/01/2014 00:00:00 | 1 | 1 | 1
10003 | 02/01/2014 00:00:00 | 1 | 1 | 1
10003 | 03/01/2014 00:00:00 | 0 | 1 | null
10003 | 04/01/2014 00:00:00 | 0 | 0 | null
10003 | 05/01/2014 00:00:00 | 0 | 0 | null
10003 | 06/01/2014 00:00:00 | 1 | 0 | 2
10003 | 07/01/2014 00:00:00 | 1 | 1 | 2
10003 | 08/01/2014 00:00:00 | 1 | 1 | 2
10003 | 09/01/2014 00:00:00 | 1 | 1 | 2
10003 | 10/01/2014 00:00:00 | 0 | 1 | null
10003 | 11/01/2014 00:00:00 | 0 | 0 | null
10003 | 12/01/2014 00:00:00 | 0 | 0 | null
10003 | 13/01/2014 00:00:00 | 1 | 0 | 3
10003 | 14/01/2014 00:00:00 | 1 | 1 | 3
10003 | 15/01/2014 00:00:00 | 1 | 1 | 3
Working Fiddle
add a comment |
Check this solution please. This adds grouping as per your need
with cte0 as
(
select [customer_id], [mis_date], [status],
COALESCE(LAG(status) over (order by mis_date), status) oldstatus
FRom Table1
),
cte1 as (
select cte0.*,
case when status = 0 then
null
else
COUNT( case when status != oldStatus and status = 0 then 1 else null end) OVER (ORDER BY mis_date)
end + 1 grp
from cte0
)
select * from cte1
GO
customer_id | mis_date | status | oldstatus | grp
----------: | :------------------ | -----: | --------: | ---:
10003 | 01/01/2014 00:00:00 | 1 | 1 | 1
10003 | 02/01/2014 00:00:00 | 1 | 1 | 1
10003 | 03/01/2014 00:00:00 | 0 | 1 | null
10003 | 04/01/2014 00:00:00 | 0 | 0 | null
10003 | 05/01/2014 00:00:00 | 0 | 0 | null
10003 | 06/01/2014 00:00:00 | 1 | 0 | 2
10003 | 07/01/2014 00:00:00 | 1 | 1 | 2
10003 | 08/01/2014 00:00:00 | 1 | 1 | 2
10003 | 09/01/2014 00:00:00 | 1 | 1 | 2
10003 | 10/01/2014 00:00:00 | 0 | 1 | null
10003 | 11/01/2014 00:00:00 | 0 | 0 | null
10003 | 12/01/2014 00:00:00 | 0 | 0 | null
10003 | 13/01/2014 00:00:00 | 1 | 0 | 3
10003 | 14/01/2014 00:00:00 | 1 | 1 | 3
10003 | 15/01/2014 00:00:00 | 1 | 1 | 3
Working Fiddle
Check this solution please. This adds grouping as per your need
with cte0 as
(
select [customer_id], [mis_date], [status],
COALESCE(LAG(status) over (order by mis_date), status) oldstatus
FRom Table1
),
cte1 as (
select cte0.*,
case when status = 0 then
null
else
COUNT( case when status != oldStatus and status = 0 then 1 else null end) OVER (ORDER BY mis_date)
end + 1 grp
from cte0
)
select * from cte1
GO
customer_id | mis_date | status | oldstatus | grp
----------: | :------------------ | -----: | --------: | ---:
10003 | 01/01/2014 00:00:00 | 1 | 1 | 1
10003 | 02/01/2014 00:00:00 | 1 | 1 | 1
10003 | 03/01/2014 00:00:00 | 0 | 1 | null
10003 | 04/01/2014 00:00:00 | 0 | 0 | null
10003 | 05/01/2014 00:00:00 | 0 | 0 | null
10003 | 06/01/2014 00:00:00 | 1 | 0 | 2
10003 | 07/01/2014 00:00:00 | 1 | 1 | 2
10003 | 08/01/2014 00:00:00 | 1 | 1 | 2
10003 | 09/01/2014 00:00:00 | 1 | 1 | 2
10003 | 10/01/2014 00:00:00 | 0 | 1 | null
10003 | 11/01/2014 00:00:00 | 0 | 0 | null
10003 | 12/01/2014 00:00:00 | 0 | 0 | null
10003 | 13/01/2014 00:00:00 | 1 | 0 | 3
10003 | 14/01/2014 00:00:00 | 1 | 1 | 3
10003 | 15/01/2014 00:00:00 | 1 | 1 | 3
Working Fiddle
edited 7 hours ago
answered 7 hours ago
SimonareSimonare
10.1k11737
10.1k11737
add a comment |
add a comment |
You can use the ALTER TABLE statement in SQL Server to add a column to a table.
Syntax
The syntax to add a column in a table in SQL Server (Transact-SQL) is:
ALTER TABLE table_name
ADD column_name column_definition;
Let's look at an example that shows how to add a column in an SQL Server table using the ALTER TABLE statement.
For example:
ALTER TABLE customer
ADD group VARCHAR(10);
This SQL Server ALTER TABLE example will add a column to the customer table called group.
Thank's but you missed understood me. I want to generate the values in the column.
– Jordan1200
7 hours ago
Can't understand, could you please explain more
– Dumidu Udayanga
7 hours ago
add a comment |
You can use the ALTER TABLE statement in SQL Server to add a column to a table.
Syntax
The syntax to add a column in a table in SQL Server (Transact-SQL) is:
ALTER TABLE table_name
ADD column_name column_definition;
Let's look at an example that shows how to add a column in an SQL Server table using the ALTER TABLE statement.
For example:
ALTER TABLE customer
ADD group VARCHAR(10);
This SQL Server ALTER TABLE example will add a column to the customer table called group.
Thank's but you missed understood me. I want to generate the values in the column.
– Jordan1200
7 hours ago
Can't understand, could you please explain more
– Dumidu Udayanga
7 hours ago
add a comment |
You can use the ALTER TABLE statement in SQL Server to add a column to a table.
Syntax
The syntax to add a column in a table in SQL Server (Transact-SQL) is:
ALTER TABLE table_name
ADD column_name column_definition;
Let's look at an example that shows how to add a column in an SQL Server table using the ALTER TABLE statement.
For example:
ALTER TABLE customer
ADD group VARCHAR(10);
This SQL Server ALTER TABLE example will add a column to the customer table called group.
You can use the ALTER TABLE statement in SQL Server to add a column to a table.
Syntax
The syntax to add a column in a table in SQL Server (Transact-SQL) is:
ALTER TABLE table_name
ADD column_name column_definition;
Let's look at an example that shows how to add a column in an SQL Server table using the ALTER TABLE statement.
For example:
ALTER TABLE customer
ADD group VARCHAR(10);
This SQL Server ALTER TABLE example will add a column to the customer table called group.
answered 7 hours ago
Dumidu UdayangaDumidu Udayanga
1016
1016
Thank's but you missed understood me. I want to generate the values in the column.
– Jordan1200
7 hours ago
Can't understand, could you please explain more
– Dumidu Udayanga
7 hours ago
add a comment |
Thank's but you missed understood me. I want to generate the values in the column.
– Jordan1200
7 hours ago
Can't understand, could you please explain more
– Dumidu Udayanga
7 hours ago
Thank's but you missed understood me. I want to generate the values in the column.
– Jordan1200
7 hours ago
Thank's but you missed understood me. I want to generate the values in the column.
– Jordan1200
7 hours ago
Can't understand, could you please explain more
– Dumidu Udayanga
7 hours ago
Can't understand, could you please explain more
– Dumidu Udayanga
7 hours ago
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54387610%2fgroup-customers-by-status-in-t-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Seems like you want a partition, not a group
– Diego Rafael Souza
8 hours ago
I think this is the expected result, we need to know actual data and logic behind it
– Simonare
8 hours ago
I've edit my post.
– Jordan1200
7 hours ago
please explain more
– Dumidu Udayanga
7 hours ago