sql - Return Function using Cursor -
My current schedule is:
USERID Department NAME Real Estimated 522 Xyz Department John Wayne 2/1/14/14 8:34 522 Investigation Department John Wayne 2/17/1411: 21 78 9Except Department Bill Smith 2/17/14 9:41 78 9 Excise Department Bill Smith 2/17/14 11:16 78 9 Excise Department Bill Smith 2 / 17/14 11:50 78 9 Excise Department Bill Smith 2/17/14 12: 18 856 Explore Department Raul Castill 2/17/14 9:25 I have to create a function which enables me to enter a user ID and that Allows the user to return data from the person. However, I want to take the first line and want to hold the actual date and then put it in the 'column' check and I also carry the last row and store it in 'Check Out' in the column call.
Basically, for every day, I want to record the records in the user's time and want to take the time for each day.
This is what I have done so far "
return @RtnValue table (useridd integer, deptname varchar (max), emplname varchar (100), actualDate datetime, Checkin datetime, create function [dbo] [GetEmployeeTime] (@useridd integer) - The first line of actual. The date goes here checkout datetime - the last line of actual date goes here) Date checkin is declared as starting @userid integer, @ , Datetime @checkout;) @useridAndDate varchar (max announces @ useridAndDate2 varchar (max); for curs cursor announcement ch.USERID select useridd as Eg ch.CHECKTIME CHECKINOUT from realdate ui.USERID = ch.USERID to join userInfo ui left on dep.DEPTID = ui.DEFAULTDEPTID open Curs department left to join DEE; @useridd check Get the next from Curs, @actualdate; while @@ FETCH_STATUS = 0 --- then set rows confused here to loop through only to record the first and last line @ useridAndDate = @useridd + @actualdate --if @ UseridAndDate = @user. IdAndDate2 --begin - FETCH from users in forward @ useridd, @ stop actual date; Assigning the Cursor; Return; END I want to see the last result like this:
USERID department NAME real checkin checkout 522xyz department John Wayne 2/17/14 8: 34 2 / 17/14 8:34 2/17/14 11:21 78 9 Excise Department Bill Smith 2/17/14 9:41 2/17/14 9:41 2/17/14 12:18 856 Press Department Raul cast 2/17/14 9:25 2/17/14 9:25
< P> There is no need for cursor for this you just need to maximize the maximum partition of each user and just separate from them DISTINCT USERID, department, name, FIR checktime, case when the FIR = SEC is then zero and SEC-end checkout (* select, min (cast time as CHECKTIME) by ( USERID): FIR, Max (CHECKTIME as a time) more than partition (partition (USERID)) than the SEC with TimePunch) tab
Comments
Post a Comment