SQL Query HELP!!!!!!

Question

2.25/5 (4 votes)

See more:

SQL

DECLARE @Today DATETIME 
SET @Today = CONVERT(DATE,GETDATE()) 
Declare @Next datetime
--select @Today 
set @Next = Dateadd(hour,23,@today)
set @Next = DATEADD(minute,59, @Next )
set @Next =DATEADD (second,59,@Next )
--Select @Next 

select EMP.FIRSTNAME + ' ' + EMP.LASTNAME As FULLNAME, MIN(CONVERT(varchar,Eventime,14)) as TIMEIN, max(CONVERT(varchar,Eventime,14)) as TIMEOUT
from EMP inner join EVENTS on EVENTS.EMPID = EMP.ID 
where (EVENTS.EVENTIME  between @Today  and @Next )
group by EMP.FIRSTNAME, EMP.LASTNAME

Hi Friends,

I need some help on the above query. i need to calculate total work time ( TimeIN- TimeOUT) ( i tried with datediff but it gives only hours or only mintue or only second ).

Secondly want i need is to calculate Lunch Time.For this i planned to divede the query in this way

Morning session TIME IN ( min time ) and TIME OUT (max time) ( between 00:00:01 and 12:59:59)
Afternoon session TIME IN (min time) and TIME OUT(max time) ( between 13:00:00 and 23:59:59)

for this i try using union and subquery, but i couldn't :(.

any help in this regards will be highly appreciated. Thank you all.

Posted 28-Oct-12 8:35am

Amaan23

Add a Solution

Comments

Arunprasath Natarajan 29-Oct-12 1:47am

Can you give the format of time, how it is inserted in your table.
In hours or Min etc..?

Amaan23 29-Oct-12 1:54am

Format : '2012-02-26 00:00:00'

Amaan23 29-Oct-12 1:55am

I have work out the TOTALWORK TIME... heres what i have done.

CONVERT(VARCHAR(20),DateAdd(SS,Datediff(ss,MIN(CONVERT(varchar,Eventime,14)), max(CONVERT(varchar,Eventime,14)))%(60*60*24),0),114) AS TOTALWORKTIME

but still lunch time seems mistery to me. I am not understanding how to work out with that.

The current query gives me the Events from 00:00:01 to 23:59:59

what i want is to divide it such as

Morning Session TIMEIN as MTIMEIN and TIMEOUT as MTIMEOUT between 00:00:01 to 12:59:59
Afternoon Session TIMEIN as ATIMEIN and TIMEOUT as ATIMEOUT between 01:00:01 to 23:59:59

sorry my english is not that good.

Richard MacCutchan 29-Oct-12 5:42am

Why are you storing DateTime values as strings? No wonder you are having problems.

Amaan23 29-Oct-12 6:12am

i thought this is the best way, if their is another way, please help me out.

Richard MacCutchan 30-Oct-12 16:53pm

Because it is stupid. A DateTime is a value which is often used in calculations, so why would anyone want to store it as a string. Especially since a string version, e.g. for today, can be written like 30/10/2012, 10/30/2012, 2012/10/30, 30-10-12 ...

Amaan23 29-Oct-12 13:41pm

Help me friends

Maciej Los 29-Oct-12 15:22pm

And the structure of your table is...

Amaan23 30-Oct-12 0:51am

emp table containing all the Employee and i have Events Table which contains all the logs generated from the hardware device. ( relation between them EMP inner join EVENTS on EVENTS.EMPID = EMP.ID). events are stored with date and time.

Maciej Los 30-Oct-12 16:03pm

...and the structure of these 2 tables is...
Which version of MS SQL Server?

Amaan23 30-Oct-12 10:27am

FRIENDS HELP ME!!!

Kschuler 30-Oct-12 14:30pm

You've added some important information in the comments. This isn't ideal. Please click the Improve Question button and add that information to your original question so it's easier for new people reading it to get the full picture. Secondly, could you clearly show us what your table looks like and give us some sample data. For example, how would your table look if it contained data that Joe arrived at work at 9 AM, when to lunch at noon, came back from lunch at 1 PM, and left work at 5:30 PM. And what results for Joe would you expect from your query?

1 solution

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)

Maciej Los · Accepted Answer · 2012-10-30T10:33:00

Solution 2

Try to use something like this:

SQL

DECLARE @tInFrom DATETIME 
DECLARE @tInTo DATETIME 
DECLARE @tOutFrom DATETIME
DECLARE @tOutTo DATETIME
 
SET @tInFrom = CONVERT(DATETIME, CONVERT(NVARCHAR(10),GETDATE(),120) + ' 00:00:00')
SET @tInTo = CONVERT(DATETIME, CONVERT(NVARCHAR(10),GETDATE(),120) + ' 12:59:59')
SET @tOutFrom = CONVERT(DATETIME, CONVERT(NVARCHAR(10),GETDATE(),120) + ' 13:00:00')
SET @tOutTo = CONVERT(DATETIME, CONVERT(NVARCHAR(10),GETDATE(),120) + ' 23:59:59')
 
--SELECT @tInFrom AS TimeInFrom, @tInTo AS TimeInTo, @tOutFrom AS TimeOutFrom, @tOutTo AS TimeOutTo 

DECLARE @cols NVARCHAR(200)
DECLARE @dt NVARCHAR(1000)
DECLARE @pt NVARCHAR(2000)

SET @cols = '[TimeIn],[TimeOut]'

SET @dt = 'SELECT EMP.FIRSTNAME + '' '' + EMP.LASTNAME AS FULLNAME, MIN(Eventime) AS [EventTime], ''TimeIn'' AS [Description] ' +
		'FROM EMP INNER JOIN EVENTS ON EVENTS.EMPID = EMP.ID ' +
		'WHERE (EVENTS.EVENTIME BETWEEN ''' + @tInFrom + ''' AND ''' + @tInTo + ''') ' +
		'GROUP BY EMP.ID ' +
		'UNION ALL ' +
		'SELECT EMP.FIRSTNAME + '' '' + EMP.LASTNAME AS FULLNAME, MAX(Eventime) as [EventTime], ''TimeOut'' AS [Description] ' +
		'FROM EMP INNER JOIN EVENTS ON EVENTS.EMPID = EMP.ID  ' +
		'WHERE (EVENTS.EVENTIME BETWEEN ''' + @tOutFrom + ''' AND ''' + @tOutTo + ''') ' +
		'GROUP BY EMP.ID '
--EXEC(@dt)
SET @pt = 'SELECT FULLNAME, ' + @cols + ' ' +
		'FROM (' + @dt + ') AS DT ' +
		'PIVOT (MAX(EventTime) FOR [Description] IN (' + @cols + ')) AS PT'

EXEC(@pt)

The above example should works with MS SQL Server 2005. Please, read about Date and Time functions[^] corresponding with your version of MS SQL Server.

Posted 30-Oct-12 10:33am

Maciej Los

Updated 4-Nov-12 8:05am

v5

Comments

Amaan23 31-Oct-12 13:29pm

Thanks for your help.
Initially i tried this, but couldnt get it.

The output what i am getting for the above query is

FULLNAME TIMEIN TIMEOUT
ABC 10:23:00 Null
XYZ 12:35:23 Null
ABC Null 17:50:23
XYZ Null 16:58:23

How to make it as
FULLNAME TIMEIN TIMEOUT
ABC 10:23:00 17:50:23
XYZ 12:35:23 16:58:23

Maciej Los 31-Oct-12 15:49pm

See my answer after update. I've decide to change a way to achieve the correct output format.
@dt variable stores a query which returns:
FULLNAME EventTime Description
ABC 10:23:00 TimeIn
ABC 17:50:23 TimeOut
XYZ 12:35:23 TimeIn
XYZ 16:58:23 TimeOut
Then i use Pivot query to pivot rows from Description column as a columns.

Good luck!

Amaan23 4-Nov-12 11:47am

Thanks for the Help but still its not working..

the new query using pivot and Description columns gives error...

I tired a lot to solve it but :(

here's the error

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@tInFrom".
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@tOutFrom".

Maciej Los 4-Nov-12 12:40pm

Please, copy and paste it one more time. It should works!

Amaan23 4-Nov-12 13:33pm

did u modify it ?

now i am getting new error

Msg 241, Level 16, State 1, Line 19
Conversion failed when converting date and/or time from character string.

Maciej Los 4-Nov-12 14:06pm

Yes, i modified it. Please, try it again.

Amaan23 4-Nov-12 14:15pm

thanks let me try it

Amaan23 4-Nov-12 14:18pm

Again Error :(

Msg 241, Level 16, State 1, Line 19
Conversion failed when converting date and/or time from character string.

Maciej Los 4-Nov-12 16:12pm

OK, add another variables:

DECLARE @sInFrom AS NVARCHAR(30)
DECLARE @sInTo AS NVARCHAR(30)

SET @sInFrom = CONVERT(NVARCHAR(30), @tInFrom, 120)
SET @sInTo = CONVERT(NVARCHAR(30), @tInTo, 120)

and replace with datetime variables.

Espen Harlinn 4-Nov-12 15:01pm

Well done, Maciej :-D

Maciej Los 4-Nov-12 15:26pm

Thank you, Espen ;)

Amaan23 4-Nov-12 15:05pm

Espen..

Is it working ?

why i am getting conversion failed message

Amaan23 26-Nov-12 13:17pm

Thanks for all the help...
Can i put some condition on TOTALWORKTIME ... such as. display only the person who has worked(TotalWorkTime) more then 12 hours... without using Pivot.

Maciej Los 26-Nov-12 15:47pm

Yes, you can ;)

Amaan23 26-Nov-12 15:50pm

how ? pls reply

Amaan23 26-Nov-12 16:09pm

i tried but i am not getting.

Maciej Los 26-Nov-12 16:30pm

Create new post and show what have you done so far...
By The Way, it would be nice to see this solution as solved (green button). If it was helpful, rate it.