SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query Problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

immad
Posting Yak Master

Pakistan
220 Posts

Posted - 09/18/2013 :  03:37:36  Show Profile  Reply with Quote
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

Sweden
30281 Posts

Posted - 09/18/2013 :  04:16:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The expected output is exactly as the sample data?



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

immad
Posting Yak Master

Pakistan
220 Posts

Posted - 09/18/2013 :  04:25:20  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 09/18/2013 :  04:42:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

immad
Posting Yak Master

Pakistan
220 Posts

Posted - 09/18/2013 :  06:43:10  Show Profile  Reply with Quote
thanks can you explain me what is the meaning of this
DATEADD(DAY, -1, CheckTime)
&
BETWEEN 0 AND 11




immad uddin ahmed
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 09/18/2013 :  07:46:07  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

immad
Posting Yak Master

Pakistan
220 Posts

Posted - 09/18/2013 :  07:52:36  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 09/18/2013 :  08:12:41  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

immad
Posting Yak Master

Pakistan
220 Posts

Posted - 09/19/2013 :  01:01:20  Show Profile  Reply with Quote


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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000