Calculating Multiple Break in and out in from single column

up vote
0
down vote

favorite

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

edited Nov 19 at 12:15

asked Nov 19 at 11:15

Moiz

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 using postgresql, oracle, sql-server, db2, ...
– a_horse_with_no_name
Nov 19 at 11:17

You may want to search for "SQL pivot", and find e.g. this or this.
– JimmyB
Nov 19 at 11:19

Try PIVOT TABLE
– Waqas Shabbir
Nov 19 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 at 11:44

@GordonLinoff the break type is checktype ,"B" means Break in and "U" means Break out
– Moiz
Nov 19 at 12:13

add a comment |

up vote
0
down vote

favorite

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

edited Nov 19 at 12:15

asked Nov 19 at 11:15

Moiz

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 using postgresql, oracle, sql-server, db2, ...
– a_horse_with_no_name
Nov 19 at 11:17

You may want to search for "SQL pivot", and find e.g. this or this.
– JimmyB
Nov 19 at 11:19

Try PIVOT TABLE
– Waqas Shabbir
Nov 19 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 at 11:44

@GordonLinoff the break type is checktype ,"B" means Break in and "U" means Break out
– Moiz
Nov 19 at 12:13

add a comment |

up vote
0
down vote

favorite

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

edited Nov 19 at 12:15

asked Nov 19 at 11:15

Moiz

133

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

edited Nov 19 at 12:15

asked Nov 19 at 11:15

Moiz

133

edited Nov 19 at 12:15

asked Nov 19 at 11:15

Moiz

133

edited Nov 19 at 12:15

asked Nov 19 at 11:15

Moiz

133

asked Nov 19 at 11:15

Moiz

133

asked Nov 19 at 11:15

Moiz

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 using postgresql, oracle, sql-server, db2, ...
– a_horse_with_no_name
Nov 19 at 11:17

You may want to search for "SQL pivot", and find e.g. this or this.
– JimmyB
Nov 19 at 11:19

Try PIVOT TABLE
– Waqas Shabbir
Nov 19 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 at 11:44

@GordonLinoff the break type is checktype ,"B" means Break in and "U" means Break out
– Moiz
Nov 19 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 using postgresql, oracle, sql-server, db2, ...
– a_horse_with_no_name
Nov 19 at 11:17

You may want to search for "SQL pivot", and find e.g. this or this.
– JimmyB
Nov 19 at 11:19

Try PIVOT TABLE
– Waqas Shabbir
Nov 19 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 at 11:44

@GordonLinoff the break type is checktype ,"B" means Break in and "U" means Break out
– Moiz
Nov 19 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 at 11:17

You may want to search for "SQL pivot", and find e.g. this or this.
– JimmyB
Nov 19 at 11:19

Try PIVOT TABLE
– Waqas Shabbir
Nov 19 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 at 11:44

@GordonLinoff the break type is checktype ,"B" means Break in and "U" means Break out
– Moiz
Nov 19 at 12:13

add a comment |

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',
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');
});

Post as a guest

Name

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

Name

Required, but never shown

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.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

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');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Argthtjtr