Query Problem - 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

Query Problem

Author

Topic

immad
Posting Yak Master

230 Posts

Posted - 2013-09-18 : 03:37:36

Hello,

i have a problem regarding query,

my data is like this

EID------checktime-------------status-----
123-----7/1/2013 08:00:00 PM-----I---------
123-----7/2/2013 02:00:00 AM-----O---------

I means in and O means Out

employee enter in a factory on 7/1/2013 08:00:00 PM
and out in a factory on 7/2/2013 02:00:00 AM at night becouse after 12:00 date is change.

i want a query that shows data like this

EID------checktime-------------status-----
123-----7/1/2013 08:00:00 PM-----I---------
123-----7/1/2013 02:00:00 AM-----O---------

Please Help me out.
Thanks

immad uddin ahmed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-18 : 04:16:29

The expected output is exactly as the sample data?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

immad
Posting Yak Master

230 Posts

Posted - 2013-09-18 : 04:25:20

quote:
Originally posted by SwePeso

The expected output is exactly as the sample data?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

no its chanage checktime field of expected data are change from sample data

immad uddin ahmed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-18 : 04:42:11

UPDATE dbo.Table1
SET CheckTime = DATEADD(DAY, -1, CheckTime)
WHERE Status = 'O' AND HOUR(CheckTime) BETWEEN 0 AND 11

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

immad
Posting Yak Master

230 Posts

Posted - 2013-09-18 : 06:43:10

thanks can you explain me what is the meaning of this
DATEADD(DAY, -1, CheckTime)
&
BETWEEN 0 AND 11

immad uddin ahmed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-18 : 07:46:07

DATEADD function
http://msdn.microsoft.com/en-US/library/ms186819.aspx

HOUR function
http://msdn.microsoft.com/en-us/library/ms174420.aspx

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

immad
Posting Yak Master

230 Posts

Posted - 2013-09-18 : 07:52:36

quote:
Originally posted by SwePeso

DATEADD function
http://msdn.microsoft.com/en-US/library/ms186819.aspx

HOUR function
http://msdn.microsoft.com/en-us/library/ms174420.aspx

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

can u tell me what this query do i mean to say if a person in on 18 sep and out on 19 sep so this query update his 19sep data to 18sep

like this

EID------checktime-------------status-----
123-----18/9/2013 08:00:00 PM-----I---------
123-----18/9/2013 02:00:00 AM-----O---------

if data is like this

EID------checktime-------------status-----
123-----18/9/2013 08:00:00 PM-----I---------
123-----19/9/2013 02:00:00 AM-----O---------

immad uddin ahmed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-18 : 08:12:41

Well, the only business rule I've figured out (you haven't written anyh of the rules yet) is that if the out-time is after midnight, you should "roll back" the time 12 hours.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

immad
Posting Yak Master

230 Posts

Posted - 2013-09-19 : 01:01:20

i having a problem regarding query

UPDATE dbo.cheeck
SET CheckTime = DATEADD(DAY, -1, CheckTime)
WHERE Status = 'O' and datepart(hh,CheckTime) BETWEEN 0 AND 11

this is my actual data

EID--------Checktime---------------Status
123--------9/1/2013 9:09:00 PM-------I
123--------9/2/2013 4:09:00 AM-------O

when i exceute this query first time its give me accurate result.

EID--------Checktime---------------Status
123--------9/1/2013 9:09:00 PM-------I
123--------9/1/2013 4:09:00 AM-------O

but when i exceute again its give me this result

EID--------Checktime---------------Status
123--------9/1/2013 9:09:00 PM--------I
123--------8/31/2013 4:09:00 AM-------O

is it possible that if i execute this query only current date data change

immad uddin ahmed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources