Calculating Multiple Break in and out in from single column
0
Hi i am new to Sql i have one table containing user id Time stamp and Punch type like(in, out m Break in, break out) i would like to have multiple break in and break out to be displayed in one row group by date below is the table structure
USERID CHECKTIME CHECKTYPE VERIFYCODE SENSORID Memoinfo WorkCode sn UserExtFmt
2175 2018-04-12 17:51:17.000 0 57 120 NULL 0 7408670 0
2175 2018-04-12 17:55:24.000 1 53 120 NULL 0 7408670 0
2175 2018-04-12 17:56:13.000 0 52 120 NULL 0 7408670 0
2175 2018-04-12 17:56:49.000 0 52 120 NULL 0 7408670 0
2175 2018-04-12 17:57:05.000 0 52 120 NULL 0 7408670 0
2175 2018-04-12 18:00:31.000 U 50 120 NULL 0 7408670 0
2175 2018-04-12 18:04:02.000 B 53 120 NULL 0 7408670 0
2175 2018-04-13 05:50:36.000 U 50 120 NULL 0 7408670 0
2175 2018-04-13 05:56:09.000 0 52 120 NULL 0 7408670 0
2175 2018-04-13 05:58:32.000 U 52 120 NULL 0 7408670 0
Desired Output
Date Break Time1 Breaktime2 Breaktime3 Userid
sql
sql-server pivot asked Nov 19 '18 at 11:15
MoizMoiz
133
add a comment |
0
Hi i am new to Sql i have one table containing user id Time stamp and Punch type like(in, out m Break in, break out) i would like to have multiple break in and break out to be displayed in one row group by date below is the table structure
USERID CHECKTIME CHECKTYPE VERIFYCODE SENSORID Memoinfo WorkCode sn UserExtFmt
2175 2018-04-12 17:51:17.000 0 57 120 NULL 0 7408670 0
2175 2018-04-12 17:55:24.000 1 53 120 NULL 0 7408670 0
2175 2018-04-12 17:56:13.000 0 52 120 NULL 0 7408670 0
2175 2018-04-12 17:56:49.000 0 52 120 NULL 0 7408670 0
2175 2018-04-12 17:57:05.000 0 52 120 NULL 0 7408670 0
2175 2018-04-12 18:00:31.000 U 50 120 NULL 0 7408670 0
2175 2018-04-12 18:04:02.000 B 53 120 NULL 0 7408670 0
2175 2018-04-13 05:50:36.000 U 50 120 NULL 0 7408670 0
2175 2018-04-13 05:56:09.000 0 52 120 NULL 0 7408670 0
2175 2018-04-13 05:58:32.000 U 52 120 NULL 0 7408670 0
Desired Output
Date Break Time1 Breaktime2 Breaktime3 Userid
sql
sql-server pivot asked Nov 19 '18 at 11:15
MoizMoiz
133
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are usingpostgresql,oracle,sql-server,db2, ...
– a_horse_with_no_name
Nov 19 '18 at 11:17
You may want to search for "SQL pivot", and find e.g. this or this.
– JimmyB
Nov 19 '18 at 11:19
Try PIVOT TABLE
– Waqas Shabbir
Nov 19 '18 at 11:27
And how do you define a "break"? Nothing in the table says "break". Are you always limited to three?
– Gordon Linoff
Nov 19 '18 at 11:44
@GordonLinoff the break type is checktype ,"B" means Break in and "U" means Break out
– Moiz
Nov 19 '18 at 12:13
add a comment |
0
0
0
Hi i am new to Sql i have one table containing user id Time stamp and Punch type like(in, out m Break in, break out) i would like to have multiple break in and break out to be displayed in one row group by date below is the table structure
USERID CHECKTIME CHECKTYPE VERIFYCODE SENSORID Memoinfo WorkCode sn UserExtFmt
2175 2018-04-12 17:51:17.000 0 57 120 NULL 0 7408670 0
2175 2018-04-12 17:55:24.000 1 53 120 NULL 0 7408670 0
2175 2018-04-12 17:56:13.000 0 52 120 NULL 0 7408670 0
2175 2018-04-12 17:56:49.000 0 52 120 NULL 0 7408670 0
2175 2018-04-12 17:57:05.000 0 52 120 NULL 0 7408670 0
2175 2018-04-12 18:00:31.000 U 50 120 NULL 0 7408670 0
2175 2018-04-12 18:04:02.000 B 53 120 NULL 0 7408670 0
2175 2018-04-13 05:50:36.000 U 50 120 NULL 0 7408670 0
2175 2018-04-13 05:56:09.000 0 52 120 NULL 0 7408670 0
2175 2018-04-13 05:58:32.000 U 52 120 NULL 0 7408670 0
Desired Output
Date Break Time1 Breaktime2 Breaktime3 Userid
sql
sql-server pivot asked Nov 19 '18 at 11:15
MoizMoiz
133
Hi i am new to Sql i have one table containing user id Time stamp and Punch type like(in, out m Break in, break out) i would like to have multiple break in and break out to be displayed in one row group by date below is the table structure
USERID CHECKTIME CHECKTYPE VERIFYCODE SENSORID Memoinfo WorkCode sn UserExtFmt
2175 2018-04-12 17:51:17.000 0 57 120 NULL 0 7408670 0
2175 2018-04-12 17:55:24.000 1 53 120 NULL 0 7408670 0
2175 2018-04-12 17:56:13.000 0 52 120 NULL 0 7408670 0
2175 2018-04-12 17:56:49.000 0 52 120 NULL 0 7408670 0
2175 2018-04-12 17:57:05.000 0 52 120 NULL 0 7408670 0
2175 2018-04-12 18:00:31.000 U 50 120 NULL 0 7408670 0
2175 2018-04-12 18:04:02.000 B 53 120 NULL 0 7408670 0
2175 2018-04-13 05:50:36.000 U 50 120 NULL 0 7408670 0
2175 2018-04-13 05:56:09.000 0 52 120 NULL 0 7408670 0
2175 2018-04-13 05:58:32.000 U 52 120 NULL 0 7408670 0
Desired Output
Date Break Time1 Breaktime2 Breaktime3 Userid
sql
sql-server pivot sql
sql-server pivot asked Nov 19 '18 at 11:15
MoizMoiz
133
asked Nov 19 '18 at 11:15
MoizMoiz
133
edited Nov 19 '18 at 12:15
Moiz
asked Nov 19 '18 at 11:15
MoizMoiz
133
asked Nov 19 '18 at 11:15
MoizMoiz
133
asked Nov 19 '18 at 11:15
MoizMoiz
133
133
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are usingpostgresql,oracle,sql-server,db2, ...
– a_horse_with_no_name
Nov 19 '18 at 11:17
You may want to search for "SQL pivot", and find e.g. this or this.
– JimmyB
Nov 19 '18 at 11:19
Try PIVOT TABLE
– Waqas Shabbir
Nov 19 '18 at 11:27
And how do you define a "break"? Nothing in the table says "break". Are you always limited to three?
– Gordon Linoff
Nov 19 '18 at 11:44
@GordonLinoff the break type is checktype ,"B" means Break in and "U" means Break out
– Moiz
Nov 19 '18 at 12:13
add a comment |
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are usingpostgresql,oracle,sql-server,db2, ...
– a_horse_with_no_name
Nov 19 '18 at 11:17
You may want to search for "SQL pivot", and find e.g. this or this.
– JimmyB
Nov 19 '18 at 11:19
Try PIVOT TABLE
– Waqas Shabbir
Nov 19 '18 at 11:27
And how do you define a "break"? Nothing in the table says "break". Are you always limited to three?
– Gordon Linoff
Nov 19 '18 at 11:44
@GordonLinoff the break type is checktype ,"B" means Break in and "U" means Break out
– Moiz
Nov 19 '18 at 12:13
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using
postgresql, oracle, sql-server, db2, ...– a_horse_with_no_name
Nov 19 '18 at 11:17
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using
postgresql, oracle, sql-server, db2, ...– a_horse_with_no_name
Nov 19 '18 at 11:17
You may want to search for "SQL pivot", and find e.g. this or this.
– JimmyB
Nov 19 '18 at 11:19
You may want to search for "SQL pivot", and find e.g. this or this.
– JimmyB
Nov 19 '18 at 11:19
Try PIVOT TABLE
– Waqas Shabbir
Nov 19 '18 at 11:27
Try PIVOT TABLE
– Waqas Shabbir
Nov 19 '18 at 11:27
And how do you define a "break"? Nothing in the table says "break". Are you always limited to three?
– Gordon Linoff
Nov 19 '18 at 11:44
And how do you define a "break"? Nothing in the table says "break". Are you always limited to three?
– Gordon Linoff
Nov 19 '18 at 11:44
@GordonLinoff the break type is checktype ,"B" means Break in and "U" means Break out
– Moiz
Nov 19 '18 at 12:13
@GordonLinoff the break type is checktype ,"B" means Break in and "U" means Break out
– Moiz
Nov 19 '18 at 12:13
add a comment |
0
active
oldest
votes
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
});
}
});
draft saved
draft discarded
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%2f53373432%2fcalculating-multiple-break-in-and-out-in-from-single-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53373432%2fcalculating-multiple-break-in-and-out-in-from-single-column%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
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using
postgresql,oracle,sql-server,db2, ...– a_horse_with_no_name
Nov 19 '18 at 11:17
You may want to search for "SQL pivot", and find e.g. this or this.
– JimmyB
Nov 19 '18 at 11:19
Try PIVOT TABLE
– Waqas Shabbir
Nov 19 '18 at 11:27
And how do you define a "break"? Nothing in the table says "break". Are you always limited to three?
– Gordon Linoff
Nov 19 '18 at 11:44
@GordonLinoff the break type is checktype ,"B" means Break in and "U" means Break out
– Moiz
Nov 19 '18 at 12:13