Hello
I have a table that keeps track of every access to the system. We insert the UserID, TimeOfAccess and TypeOfAccess.
We want to create a report and due to the limitations of Reporting Services for programatically processing, we want to create a temporary table to have the columns already set up for the report with the info that we need.
ORIGINAL TABLE
USERID | TIMEOFACCESS | TYPE OF ACCESS
2323 | 12/15/2007 03:52:54 | CLOCKIN
2323 | 12/15/2007 04:32:54 | CLOCKOUT
2323 | 12/15/2007 05:42:54 | CLOCKIN
2323 | 12/15/2007 07:53:54 | CLOCKOUT
2323 | 12/15/2007 09:18:54 | CLOCKIN
2323 | 12/15/2007 10:24:54 | CLOCKOUT
TEMPORARY TABLE
USERID | CLOCKIN | CLOCKOUT | ELAPSEDTIME
2323 | 03:52:54 | 04:32:54 | 0:40:54
OK. the problem is that I need to read the fields one by one in the SELECT statement so i can insert (and Update) the fields of the temporary table.
I create my temporary table
CREATE #TempTable
(
UserID int,
ClockIN DateTime null,
ClockOUT DateTime null,
ElapsedTime DateTime null,
)
I want to be able to do this in my stored procedure. I can doit in a form but i want to return from my database the datatable already suitable for my report.
Does anyone know how to read in a SELECT statement one field as it's been read?
you can do that using a cursor in your stored procedure.
See: http://www.sqlteam.com/item.asp?ItemID=553
If you dont want to use a cursor, its possible as well:
http://www.sql-server-performance.com/dp_no_cursors.asp
|||
hi,
you can achieve this using a better solution by dong a self join on userid and midnight(dateaccess)
you just have to drop the timepart of the date on the join. you dont even need the temp table.
here's the pseudocode: you just need to imporve this.
select userid, convert( varchar(20),dateaccess,102) as dateaccess, convert( varchar (20),dateaccess,108) as clockin, t2.clockout from table1 t1
where [type of access]='clockin'
join
(select userid, convert( varchar(20),dateaccess,102) as dateaccess,
convert( varchar(20),dateaccess,108) as clockout, from table1
where [type of access]='clockout')
as t2
on t1.userid=t2.userid and t1.dateaccess=t2.dateaccess
regards,
joey
|||
Try selecting the minimum "clockin" and maximum "clockout" per each userid, then calculate the diff in seconds.
Code Snippet
;with cte
as
(
select
userid,
min(case when [type of access] = 'clockin' then [time of access] end) as clockin,
max(case when [type of access] = 'clockout' then [time of access] end) as clockout,
datediff(
seconds,
min(case when [type of access] = 'clockin' then [time of access] end) as clockin,
max(case when [type of access] = 'clockout' then [time of access] end) as clockout
) as elapsed_time_sec
from
dbo.t1
group by
userid
)
select
userid,
clockin,
clockout,
right('00' + ltrim(elapsed_time_sec / 3600), 2) + ':' +
right('00' + ltrim((elapsed_time_sec % 3600) / 60), 2) + ':' +
right('00' + ltrim((elapsed_time_sec % 3600) % 60), 2)
from
cte;
AMB
|||
None of this solutions actually work.
I don't get the desired result. I think this is something impossible to do in a Stored Procedure wich is very disappointing because is not so difficult to do in a form. But that's what i don't want. There has to be a way... i guess i need to study more the t-sql language. I just can't believe t-sql is so poor.
|||
Try:
Code Snippet
;with cte_1
as
(
select
userid,
[TIME OF ACCESS],
[TYPE OF ACCESS],
row_number() over(partition by userid order by [TIME OF ACCESS]) as rn
from
dbo.t1
),
cte_2
as
(
select
a.userid,
a.[TIME OF ACCESS] as CLOCKIN,
b.[TIME OF ACCESS] as CLOCKOUT,
datediff(second, a.[TIME OF ACCESS], b.[TIME OF ACCESS]) as elapsed_time_sec
from
cte_1 as a
inner join
cte_1 as b
on a.userid = b.userid
and a.rn = b.rn - 1
and a.[TYPE OF ACCESS] = 'CLOCKIN'
and b.[TYPE OF ACCESS] = 'CLOCKOUT'
)
select
userid,
CLOCKIN,
CLOCKOUT,
right('00' + ltrim(elapsed_time_sec / 3600), 2) + ':' +
right('00' + ltrim((elapsed_time_sec % 3600) / 60), 2) + ':' +
right('00' + ltrim((elapsed_time_sec % 3600) % 60), 2)
from
cte_2
order by
userid,
CLOCKIN;
go
BTW, I wonder why elapsed time for the first two rows is "00:40:54", is they both have same number of seconds. I think it should be "00:40:00".
AMB
|||
This is it.
It works perfectly fine.
Definitely i am gonna buy a couple of T-SQL books. Any suggestion on the best title or publisher?
Jose
|||
I will suggest the serie "Inside SQL Server 2005".
Inside Microsoft SQL Server 2005: T-SQL Querying
http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735623139/ref=pd_bbs_sr_3/002-2026708-7606405?ie=UTF8&s=books&qid=1179781170&sr=1-3
Inside Microsoft SQL Server 2005: T-SQL Programming
http://www.amazon.com/Inside-Microsoft-Server-2005-Pro-Developer/dp/0735621977/ref=pd_sim_b_1/002-2026708-7606405?ie=UTF8&qid=1179781170&sr=1-3
Inside Microsoft (r) SQL Server (tm) 2005: The Storage Engine
http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055/ref=pd_sim_b_2/002-2026708-7606405?ie=UTF8&qid=1179781170&sr=1-3
AMB
P.S. Your alias exposed you.
PP --> Pepe (Jose)
CUBAN --> from Cuba
Oye chico, vinistes a bailar en casa del trompo.
|||
| |
| AMB P.S. Your alias exposed you. |
As one who belives in transparency, and thinks that a lot of folks are stuck in grade school with their childish and 'cutsy' nom de plumes, exactly how this is a problem in this venue?
Granted, anonymity has it purposes and places -but really, are these forums such place to fear exposure?
|||
good point. I am not affraid of exposure. As a matter of fact, most of the people online knows me by that name. ppcuban@.{most famous emails}.com are my address, the domain ppcuban.com also... hahaha. So, basically everybody call me ppcuban as a nickname even out of internet.
AMB? Tu eres cubano?
|||
Jose,
Nacido y criado en la Vibora. Emigre hacia los Estados Unidos de america hace 9 anios. Vivi por un tiempo en Miami y ahora radico en Carolina del Sur (No pastelitos de guayava y mucho menos masareal).
Saludos,
Alejandro Mesa
