I want get below described result in SQL query stored procedure

Question

1.00/5 (1 vote)

See more:

Hi
I have a table which contain rows and column as below

emp_no  date         time
00010   01/01/2020   0700
00010   01/01/2020   1200
00010   01/01/2020   1245
00010   02/01/2020   0500

result rows and column should be as below

emp_no   date_in      time_in   date_out   time_out  break_time duration
00010    01/01/2020    0700     02/01/2020   0500      0045      2300

please help me with a query for stored procedure

thanks in advance

Ajith

What I have tried:

I am new to this and looping give me nightmares

Posted 8-Sep-20 4:10am

Member 12931315

Updated 8-Sep-20 5:09am

OriginalGriff

v2

Add a Solution

Comments

Member 12931315 8-Sep-20 10:54am

Hi OriginalGriff

Thanks for answering , It's for my course activity
I am beginner for SQL query , I do not know how to do a while loop
just tell me how to loop through that table that will help me

Thanks

Ajith

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2020-09-08T04:42:00

We are more than willing to help those that are stuck: but that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.

W∴ Balboos, *GHB* · Answer 2 · 2020-09-08T05:10:00

Solution 2

I will give you a massive bit of help - but you'll need to figure out how to do it (as noted in answer 1):

You need to JOIN the table to itself . . .

Posted 8-Sep-20 5:10am

W∴ Balboos, GHB

Comments

Member 12931315 12-Sep-20 3:06am

Hi
thanks for replies

this is what I tried

CREATE TABLE #tempTable1 (
Token_no char(6),
date date,
time char(4))

CREATE TABLE #tempdata (
Token_no char(6),
date date,
time char(4))
insert into #tempdata (Token_no,date, time) values ('00001','2020.01.01','0700')
insert into #tempdata (Token_no,date, time) values ('00001','2020.01.01','1200')
insert into #tempdata (Token_no,date, time) values ('00001','2020.01.01','1245')
insert into #tempdata (Token_no,date, time) values ('00001','2020.01.02','0500')

insert into #tempTable1 (Token_no,date, time)
select #tempdata.token_no,#tempdata.date,#tempdata.time from #tempdata
left join #tempdata t1 on t1.date =#tempdata.date and t1.token_no =#tempdata.token_no

select * from #tempTable1
drop TABLE #tempTable1
drop TABLE #tempdata

please help me

Ajith

W∴ Balboos, GHB 13-Sep-20 15:17pm

Here's how you convert multiple related rows in a table to a single row.
SELECT A.commonality, A.val, B.val, C.val
FROM ATable as A
INNER JOIN ATable B
On A.commonality = B.commonality
INNER JOIN ATable C
ON A.commonality = C.commonality
-- The WHERE clause, although optional in theory will be necessary
-- to pick up the data you actually want
WHERE A.something is something
AND B.something is something_else
AND C.something is another_thing

Another point: if you will not always get a match for all columns and you want the results anyway then you will need to do things like LEFT JOIN or RIGHT JOIN to pick up the sets that are incomplete. Now if the value isn't always present for all of the 'columns' you are making they'd still show with a NULL for the missing values.

Experiment on something simple - once you get it you'll know the power!