Need help in making sql query

Question

0.00/5 (No votes)

See more:

Hello,
I have two rows corresponding to employee attendace in my table say Table1 as shown below. First row records the In Time and second row records the Out Time.
EmpId ,AttendanceTime
1 07:30
1 04:30
2 07:35
2 04:45

Now i want to read data from Table1 and store each employee data in the columns Empid,Intime,OutTime in single row of Table2. How to make sql query for this purpose

Posted 1-Sep-15 0:37am

xpertzgurtej

Add a Solution

Comments

Andy Lanng 1-Sep-15 6:46am

Are there any other reference points like table id or date?

xpertzgurtej 1-Sep-15 7:00am

yes there is date column in both tables

Andy Lanng 1-Sep-15 7:04am

how about an am / pm or is time stored as a dbType time?

basically, is there a column you can Order By to get the in / out in the correct order?

xpertzgurtej 1-Sep-15 7:16am

yes time is stored as datetime

xpertzgurtej 1-Sep-15 7:40am

there is also a column AttendanceType which stores 0 for intime and 1 for outime in Table1

4 solutions

Solution 4

Hello,

You can try this sql query

SQL

SELECT EmpID AS EmployeeID
	, AttendanceTime AS InTime
	, (SELECT AttendanceTime FROM Employee WHERE EmpID = EmployeeID 
           AND InTime < AttendanceTime) AS OutTime
FROM Employee
GROUP BY EmpID;

Posted 3-Sep-15 22:43pm

Nayana Narkhede

Solution 2

Create Table2 with required column name and follow this code.

SQL

INSERT INTO table2 
SELECT * FROM table1;

Posted 1-Sep-15 2:24am

TVS1309

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)

Schatak · Accepted Answer · 2015-09-01T03:17:00

i am not much clear but i think you need similar. this is just an example.

SQL

create table Attendance
(
	Emp varchar(10), 
	LoginDateTime datetime, 
	AttendanceType tinyint
)

insert into Attendance values ('John', GETDATE()-1,0)
insert into Attendance values ('John', GETDATE(), 1)

SELECT Emp as 'Employee', [0] as 'Intime', [1] as 'Outtime'
FROM (
    SELECT
    AttendanceType,
        LoginDateTime, Emp
    FROM Attendance
) as temp
PIVOT
(
    max(LoginDateTime)
    FOR AttendanceType in ([0], [1])
)AS pvoit

AndrewCharlz · Accepted Answer · 2015-09-01T02:20:00

Solution 1

Hope this helps

http://streetrat.in/Articles/Content/Sample-Sql-Server-Pivot-Query104.aspx?RatHistory=104[^]

This is similar to the context of your's.

insert into table2 (username, Intime, outtime)
select * from pvresult
--pivotquery result

hope u get this

Posted 1-Sep-15 2:20am

AndrewCharlz

Updated 1-Sep-15 2:27am

v2