Calculate Spend Time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculate Spend Time

Author

Topic

immad
Posting Yak Master

230 Posts

Posted - 2013-05-30 : 00:59:14

Can any one help me?

my data is like this:

---Date-------Timein------Timeout-----Spend time--Shift---excess/short
2-May-2013----8:00AM------10:34AM------ ?----------A1--------?-------
2-May-2013----10:40AM-----5:50PM

there is an employee who works in a factory he swap his card in 8:00 AM for timein then 10:34 AM he swap his card for timeout then in 10:40AM he again swap his card for timein and in 5:50PM he swap his card for timeout and go home.i want to calculate his spendtime that how much time he spend in factory.

Please Help me

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 01:35:47

[code]
SELECT [date],
CONVERT(varchar(8),DATEADD(ss,SUM(DATEDIFF(ss,TimeIn,TimeOut)),0),108) AS SpendTime
FROM Table
GROUP BY [date]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

immad
Posting Yak Master

230 Posts

Posted - 2013-05-30 : 02:23:53

it give me this error
Column 'trans.Time IN' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

when i use this column whos written in red
SELECT [date],[Time In],
CONVERT(varchar(8),DATEADD(ss,SUM(DATEDIFF(ss,[Time In],[Time Out])),0),108) AS SpendTime
FROM trans where employeecode='17090'
GROUP BY [date]

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 02:29:53

you asked for total spendtime which is aggregated for a day . then whats the purpose of including TimeIN in output?

if you want to include it you can do this


SELECT [date],[Time In],[Time Out],
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Time In],[Time Out]),0),108) AS SpendTime
FROM trans where employeecode='17090'

which is just details without any agregation

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

immad
Posting Yak Master

230 Posts

Posted - 2013-05-30 : 07:36:16

Thanks :)
can u tell me how to calculate excess/short i mean

i make a shift table every shift declare its time in and time out if a person time out after its actual shift time out then its say excess and
how much excess he stay that hour and minute come if he time out early with actual time out then its say short and show its hour and minute

this is how i want show a data.

---Date-------Timein------Timeout-----Spend time--Shift---excess/short
1-Apr-2013-----9:00AM-----6:22PM------09:22--------G------00:22 Excess

this is my data my actual shift timein is 9:00am and actual
timeout is 6:00pm.

my senior developer told me that calcutale its timein and timeout and show in excess or short that how much time he excess or short in factory and calculate from timein and timeout

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 08:30:11

[code]
SELECT [date],[Time In],[Time Out],
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Time In],[Time Out]),0),108) AS SpendTime,
CONVERT(varchar(8),DATEADD(ss,0,DATEDIFF(ss,'09:00',TimeIn) + DATEDIFF(ss,'18:00',Timeout)),108) AS Excess
FROM trans where employeecode='17090'

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

immad
Posting Yak Master

230 Posts

Posted - 2013-05-31 : 09:17:55

Dear Sir,

the query that u give me

SELECT [date],[Time In],[Time Out],
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Time In],[Time Out]),0),108) AS SpendTime,
CONVERT(varchar(8),DATEADD(ss,0,DATEDIFF(ss,[Time In],[Time In]) + DATEDIFF(ss,[Time Out],[Time Out])),108) AS Excess
FROM trans where employeecode='17090'

its giving me that result

---Date-------Timein----Timeout--Spendtime-------Excess/short
2013-01-01----09:14------19:06-----09:52-----------00:00:00

i want this type of data that writren in red

---Date--------Timein----Timeout--Spendtime-------Excess/short
2-May-2013----9:05AM---6:00PM---08:55---------- -00:05

immad uddin ahmed

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-31 : 10:25:54

Try this:
[CODE]

SELECT [date],[Time In],[Time Out],
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Time In],[Time Out]),0),108) AS SpendTime,
(CASE WHEN (DATEDIFF(ss, [Time In], '09:00') + DATEDIFF(ss,'18:00',[Time Out])) < 0 THEN
'-'+ CONVERT(varchar(8),DATEADD(ss,ABS(DATEDIFF(ss, [Time In], '09:00') + DATEDIFF(ss,'18:00',[Time Out])), 0), 108)
ELSE
CONVERT(varchar(8),DATEADD(ss,ABS(DATEDIFF(ss, [Time In], '09:00') + DATEDIFF(ss,'18:00',[Time Out])), 0), 108) END) AS [Short/Excess]
FROM trans
where employeecode='17090'
[/CODE]

immad
Posting Yak Master

230 Posts

Posted - 2013-06-01 : 01:37:45

query is working but sorry there is a mistake of mine let me share it with you.

this type of data i want:

Date------------Time in----Time out-----Spent time-------excess/short
2013-01-01-------09:14-----19:06-----------09:52-------------00:52
2013-01-02-------09:52-----18:36-----------08:44-------------00:16
2013-01-03-------09:15-----18:56-----------09:41-------------00:41

excess short comes from spent time mean a employee spent 9 hours 52 minute then its excess 00:52 minutes and if employee spent 8 hours 44 minutes then its short 00:16 minutes we have 9 to 6 hours timing shift.

the query that u give me

SELECT [date],convert(varchar(10),[Time In],8) Timein,convert(varchar(10),[Time Out],8) timeout,
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Time In],[Time Out]),0),108) AS SpendTime,
(CASE WHEN (DATEDIFF(ss, [Time In], '2013-01-01 09:14:00.000') + DATEDIFF(ss,'2013-01-01 19:06:00.000',[Time Out])) < 0 THEN
''+ CONVERT(varchar(8),DATEADD(ss,ABS(DATEDIFF(ss, [Time In], '2013-01-01 09:14:00.000') + DATEDIFF(ss,'2013-01-01 19:06:00.000',[Time Out])), 0), 108)
ELSE
CONVERT(varchar(8),DATEADD(ss,ABS(DATEDIFF(ss, [Time In], '2013-01-01 09:14:00.000') + DATEDIFF(ss,'2013-01-01 19:06:00.000',[Time Out])), 0), 108) END) AS [Short/Excess]
FROM trans
where employeecode='17090'

its giving me that result:

Date-----------Time in--time out---Spent time------excess/Short
2013-01-01-----09:14-----19:06-------09:52------------00:00
2013-01-02-----09:52-----18:36-------08:44------------01:08
2013-01-03-----09:15-----18:56-------09:41------------00:11

sir i hope u understand what i want.

immad uddin ahmed

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-01 : 02:00:10

You have to run the query as shown without altering it :
Try this:
[CODE]

SELECT [date],[Time In],[Time Out],
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Time In],[Time Out]),0),108) AS SpendTime,
(CASE WHEN (DATEDIFF(ss, [Time In], '09:00') + DATEDIFF(ss,'18:00',[Time Out])) < 0 THEN
'-'+ CONVERT(varchar(8),DATEADD(ss,ABS(DATEDIFF(ss, [Time In], '09:00') + DATEDIFF(ss,'18:00',[Time Out])), 0), 108)
ELSE
CONVERT(varchar(8),DATEADD(ss,ABS(DATEDIFF(ss, [Time In], '09:00') + DATEDIFF(ss,'18:00',[Time Out])), 0), 108) END) AS [Short/Excess]
FROM trans
where employeecode='17090'
[/CODE]

immad
Posting Yak Master

230 Posts

Posted - 2013-06-01 : 02:11:28

if i dont alter the query its give me this error

Msg 535, Level 16, State 0, Line 2
Difference of two datetime columns caused overflow at runtime.

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-01 : 03:12:10

quote:
Originally posted by immad

if i dont alter the query its give me this error

Msg 535, Level 16, State 0, Line 2
Difference of two datetime columns caused overflow at runtime.

immad uddin ahmed

it wont if values are what you showed us

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

immad
Posting Yak Master

230 Posts

Posted - 2013-06-01 : 03:48:10

Thanks my problem is solve
your query is fine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources