Compare same column in consecutive rows in same table with multiple ID's
I have a user request for a report and I’m too new to SQL programming to know how to approach it.
My user wants to know for each Staff ID what is the min, avg and max number of days between visits. What I don’t know how to figure out is the number of days between Visit 1 and Visit 2; Visit 2 and Visit 3, etc., for each Person ID. Some Person ID’s only have one visit, others (most) have multiple visits (up to 26). Here is a snapshot of some data (the full dataset is over 14k records):
PersonID VisitNo StaffID VisitDate
161 1 42344 06/19/2018
163 1 32987 05/14/2018
163 2 32987 09/17/2018
193 1 42344 04/09/2018
193 2 42344 07/18/2018
193 1 33865 07/18/2018
207 1 32987 10/10/2018
207 2 32987 11/05/2018
329 1 42344 04/15/2018
329 2 42344 05/23/2018
329 3 42344 06/10/2018
329 4 42344 07/18/2018
329 1 33865 06/30/2018
329 2 33865 09/14/2018
My research found a lot of references to comparing rows in the same table and I figured out how to compare one visit to the next for a single PersonID using a self join and datadiff, but how do I get from one PersonID to the next, or skip those PersonID’s with only 1 visit? Or a PersonID who has visits with multiple StaffId's?
Any ideas/suggestions are greatly appreciated as I have two requests that will benefit.
sql string-comparison
edited Nov 27 '18 at 22:04
Gordon Linoff
784k35310415
asked Nov 27 '18 at 21:52
SueCSueC
82
add a comment |
I have a user request for a report and I’m too new to SQL programming to know how to approach it.
My user wants to know for each Staff ID what is the min, avg and max number of days between visits. What I don’t know how to figure out is the number of days between Visit 1 and Visit 2; Visit 2 and Visit 3, etc., for each Person ID. Some Person ID’s only have one visit, others (most) have multiple visits (up to 26). Here is a snapshot of some data (the full dataset is over 14k records):
PersonID VisitNo StaffID VisitDate
161 1 42344 06/19/2018
163 1 32987 05/14/2018
163 2 32987 09/17/2018
193 1 42344 04/09/2018
193 2 42344 07/18/2018
193 1 33865 07/18/2018
207 1 32987 10/10/2018
207 2 32987 11/05/2018
329 1 42344 04/15/2018
329 2 42344 05/23/2018
329 3 42344 06/10/2018
329 4 42344 07/18/2018
329 1 33865 06/30/2018
329 2 33865 09/14/2018
My research found a lot of references to comparing rows in the same table and I figured out how to compare one visit to the next for a single PersonID using a self join and datadiff, but how do I get from one PersonID to the next, or skip those PersonID’s with only 1 visit? Or a PersonID who has visits with multiple StaffId's?
Any ideas/suggestions are greatly appreciated as I have two requests that will benefit.
sql string-comparison
edited Nov 27 '18 at 22:04
Gordon Linoff
784k35310415
asked Nov 27 '18 at 21:52
SueCSueC
82
Tag your question with the database you are using.
– Gordon Linoff
Nov 27 '18 at 22:04
add a comment |
I have a user request for a report and I’m too new to SQL programming to know how to approach it.
My user wants to know for each Staff ID what is the min, avg and max number of days between visits. What I don’t know how to figure out is the number of days between Visit 1 and Visit 2; Visit 2 and Visit 3, etc., for each Person ID. Some Person ID’s only have one visit, others (most) have multiple visits (up to 26). Here is a snapshot of some data (the full dataset is over 14k records):
PersonID VisitNo StaffID VisitDate
161 1 42344 06/19/2018
163 1 32987 05/14/2018
163 2 32987 09/17/2018
193 1 42344 04/09/2018
193 2 42344 07/18/2018
193 1 33865 07/18/2018
207 1 32987 10/10/2018
207 2 32987 11/05/2018
329 1 42344 04/15/2018
329 2 42344 05/23/2018
329 3 42344 06/10/2018
329 4 42344 07/18/2018
329 1 33865 06/30/2018
329 2 33865 09/14/2018
My research found a lot of references to comparing rows in the same table and I figured out how to compare one visit to the next for a single PersonID using a self join and datadiff, but how do I get from one PersonID to the next, or skip those PersonID’s with only 1 visit? Or a PersonID who has visits with multiple StaffId's?
Any ideas/suggestions are greatly appreciated as I have two requests that will benefit.
sql string-comparison
edited Nov 27 '18 at 22:04
Gordon Linoff
784k35310415
asked Nov 27 '18 at 21:52
SueCSueC
82
I have a user request for a report and I’m too new to SQL programming to know how to approach it.
My user wants to know for each Staff ID what is the min, avg and max number of days between visits. What I don’t know how to figure out is the number of days between Visit 1 and Visit 2; Visit 2 and Visit 3, etc., for each Person ID. Some Person ID’s only have one visit, others (most) have multiple visits (up to 26). Here is a snapshot of some data (the full dataset is over 14k records):
PersonID VisitNo StaffID VisitDate
161 1 42344 06/19/2018
163 1 32987 05/14/2018
163 2 32987 09/17/2018
193 1 42344 04/09/2018
193 2 42344 07/18/2018
193 1 33865 07/18/2018
207 1 32987 10/10/2018
207 2 32987 11/05/2018
329 1 42344 04/15/2018
329 2 42344 05/23/2018
329 3 42344 06/10/2018
329 4 42344 07/18/2018
329 1 33865 06/30/2018
329 2 33865 09/14/2018
My research found a lot of references to comparing rows in the same table and I figured out how to compare one visit to the next for a single PersonID using a self join and datadiff, but how do I get from one PersonID to the next, or skip those PersonID’s with only 1 visit? Or a PersonID who has visits with multiple StaffId's?
Any ideas/suggestions are greatly appreciated as I have two requests that will benefit.
sql string-comparison
sql string-comparison
edited Nov 27 '18 at 22:04
Gordon Linoff
784k35310415
asked Nov 27 '18 at 21:52
SueCSueC
82
edited Nov 27 '18 at 22:04
Gordon Linoff
784k35310415
asked Nov 27 '18 at 21:52
SueCSueC
82
edited Nov 27 '18 at 22:04
Gordon Linoff
784k35310415
edited Nov 27 '18 at 22:04
Gordon Linoff
784k35310415
edited Nov 27 '18 at 22:04
Gordon Linoff
784k35310415
784k35310415
asked Nov 27 '18 at 21:52
SueCSueC
82
asked Nov 27 '18 at 21:52
SueCSueC
82
asked Nov 27 '18 at 21:52
SueCSueC
82
82
Tag your question with the database you are using.
– Gordon Linoff
Nov 27 '18 at 22:04
add a comment |
Tag your question with the database you are using.
– Gordon Linoff
Nov 27 '18 at 22:04
Tag your question with the database you are using.
– Gordon Linoff
Nov 27 '18 at 22:04
Tag your question with the database you are using.
– Gordon Linoff
Nov 27 '18 at 22:04
add a comment |
2 Answers
2
active
oldest
votes
You can use analytic function LEAD (myvar,1) OVER ()
example from https://www.techonthenet.com/sql_server/functions/lead.php
SELECT dept_id, last_name, salary,
LEAD (salary,1) OVER (ORDER BY salary) AS next_highest_salary
FROM employees;
answered Nov 27 '18 at 22:03
phili_bphili_b
9611
add a comment |
For the average number of days, you can just use aggregation:
select personid,
(datediff(day, min(visitdate), max(visitdate)) * 1.0 / nullif(count(*) - 1, 0)
from t
group by personid;
I used SQL Server syntax, but the same idea holds in any database. The average is the maximum minus the minimum divided by one less than the number of visits.
answered Nov 27 '18 at 22:06
Gordon LinoffGordon Linoff
784k35310415
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%2f53508757%2fcompare-same-column-in-consecutive-rows-in-same-table-with-multiple-ids%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use analytic function LEAD (myvar,1) OVER ()
example from https://www.techonthenet.com/sql_server/functions/lead.php
SELECT dept_id, last_name, salary,
LEAD (salary,1) OVER (ORDER BY salary) AS next_highest_salary
FROM employees;
answered Nov 27 '18 at 22:03
phili_bphili_b
9611
add a comment |
You can use analytic function LEAD (myvar,1) OVER ()
example from https://www.techonthenet.com/sql_server/functions/lead.php
SELECT dept_id, last_name, salary,
LEAD (salary,1) OVER (ORDER BY salary) AS next_highest_salary
FROM employees;
answered Nov 27 '18 at 22:03
phili_bphili_b
9611
add a comment |
You can use analytic function LEAD (myvar,1) OVER ()
example from https://www.techonthenet.com/sql_server/functions/lead.php
SELECT dept_id, last_name, salary,
LEAD (salary,1) OVER (ORDER BY salary) AS next_highest_salary
FROM employees;
answered Nov 27 '18 at 22:03
phili_bphili_b
9611
You can use analytic function LEAD (myvar,1) OVER ()
example from https://www.techonthenet.com/sql_server/functions/lead.php
SELECT dept_id, last_name, salary,
LEAD (salary,1) OVER (ORDER BY salary) AS next_highest_salary
FROM employees;
answered Nov 27 '18 at 22:03
phili_bphili_b
9611
answered Nov 27 '18 at 22:03
phili_bphili_b
9611
answered Nov 27 '18 at 22:03
phili_bphili_b
9611
answered Nov 27 '18 at 22:03
phili_bphili_b
9611
9611
add a comment |
add a comment |
For the average number of days, you can just use aggregation:
select personid,
(datediff(day, min(visitdate), max(visitdate)) * 1.0 / nullif(count(*) - 1, 0)
from t
group by personid;
I used SQL Server syntax, but the same idea holds in any database. The average is the maximum minus the minimum divided by one less than the number of visits.
answered Nov 27 '18 at 22:06
Gordon LinoffGordon Linoff
784k35310415
add a comment |
For the average number of days, you can just use aggregation:
select personid,
(datediff(day, min(visitdate), max(visitdate)) * 1.0 / nullif(count(*) - 1, 0)
from t
group by personid;
I used SQL Server syntax, but the same idea holds in any database. The average is the maximum minus the minimum divided by one less than the number of visits.
answered Nov 27 '18 at 22:06
Gordon LinoffGordon Linoff
784k35310415
add a comment |
For the average number of days, you can just use aggregation:
select personid,
(datediff(day, min(visitdate), max(visitdate)) * 1.0 / nullif(count(*) - 1, 0)
from t
group by personid;
I used SQL Server syntax, but the same idea holds in any database. The average is the maximum minus the minimum divided by one less than the number of visits.
answered Nov 27 '18 at 22:06
Gordon LinoffGordon Linoff
784k35310415
For the average number of days, you can just use aggregation:
select personid,
(datediff(day, min(visitdate), max(visitdate)) * 1.0 / nullif(count(*) - 1, 0)
from t
group by personid;
I used SQL Server syntax, but the same idea holds in any database. The average is the maximum minus the minimum divided by one less than the number of visits.
answered Nov 27 '18 at 22:06
Gordon LinoffGordon Linoff
784k35310415
answered Nov 27 '18 at 22:06
Gordon LinoffGordon Linoff
784k35310415
answered Nov 27 '18 at 22:06
Gordon LinoffGordon Linoff
784k35310415
answered Nov 27 '18 at 22:06
Gordon LinoffGordon Linoff
784k35310415
784k35310415
add a comment |
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%2f53508757%2fcompare-same-column-in-consecutive-rows-in-same-table-with-multiple-ids%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
Tag your question with the database you are using.
– Gordon Linoff
Nov 27 '18 at 22:04