Help On Query - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Help On Query

Author

Topic

BigRetina
Posting Yak Master

144 Posts

Posted - 2002-08-18 : 03:43:42

Salute...
I have the following table : TimeMachineTransactions . It has the following columns :
TransId int Identity
GateNo Char(3)
EmpId int
TransDateTime smalldatetime
TransFunction Chr(2)

Four example transaction records are :
1, GT1, 25, 8/15/2002 08:00 A.M. , F1 -- This is a ARRIVAL record
2, GT1, 25, 8/15/2002 16:00 P.M. , F2 -- This is a DEPARTURE record
3, GT1, 25, 8/16/2002 07:55 A.M. , F1 -- This is a ARRIVAL record
4, GT1, 25, 8/16/2002 16:25 P.M. , F2 -- This is a DEPARTURE record

I am doing a report to show the employee timemachine transactions during the month..one record in the output report is as follows:

Date Arrival Departure Hours
8/15/2002 08:00 16:00 8:00
8/16/2002 07:55 16:25 8:30
How can I do that??..preferably without the use of intermediary tables!!..
Thans In Advance Ppl..
Salute Again..

Edited by - BigRetina on 08/19/2002 02:19:17

Edited by - BigRetina on 08/19/2002 02:19:48

Edited by - BigRetina on 08/19/2002 04:34:07

Edited by - BigRetina on 08/19/2002 04:38:40

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-08-18 : 19:03:22

How do you know which records are related? Or will they always occur in order (arrival then departure then arrival then departure)?

I suspect you're going to need a column which links arrivals to departures....? I assume TransId is supposed to do this, and that it should be =2 in the last two records???

if so

select
convert(nvarchar(20), a.TransDateTime, 101) as Date,
convert(nvarchar(10),b.TransDateTime ,108) as Arrival,
convert(nvarchar(10),b.TransDateTime ,108) as Departure,
cast(convert(int, datediff(minute,a.TransDateTime , b.TransDateTime )/60) as char(2)) + ':' + cast(convert(int, datediff(minute,a.TransDateTime , b.TransDateTime )%60) as char(2)) as Hours

from TimeMachineTransactions a, TimeMachineTransactions b

where a.Transfunction = 'F1' and b.Transfunction = 'F2'
and a.Transfunction = b.Transfunction
and a.empid = @employee

order by a.TransDateTime

or something like that...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Edited by - rrb on 08/18/2002 19:19:27

BigRetina
Posting Yak Master

144 Posts

Posted - 2002-08-19 : 02:18:54

First..Thanks for the reply..
BUT .. It didnt work!...Did U mean to do a self join here??..
Second YES the 1 should be 1 in the last two rows.. I meant that the two records belong to the same employee and I want to see his work report...it should give two records for two days containing his arriaval and departure times.

Edited by - BigRetina on 08/19/2002 02:20:58

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-08-19 : 03:10:11

(typo - sorry should have been a.TransId = b.TransId in the second line of the where clause)

OK - so how do you know which arrival matches which departure? ie how do you know that 8/16/2002 16:25 PM matches 8/16/2002 07:55 AM and not 8/15/2002 08:00 AM? Are you matching arrivals and departures by the same date?

<whinge>
I'm also confused coz TransId is supposed to be an identity, and yet every record has the same TransId value?
</whinge>

Anyhow: I'm assuming this is what you want

--create the table
create table #TimeMachineTransactions (
TransId int identity,
GateNo Char(3) ,
EmpId int ,
TransDateTime smalldatetime ,
TransFunction Char(2)
)

insert into #TimeMachineTransactions ( GateNo, EmpId, TransdateTime, TransFunction)
select 'GT1', 25, convert(smalldatetime, '15-Aug-2002 08:00:00') , 'F1'
insert into #TimeMachineTransactions ( GateNo, EmpId, TransdateTime, TransFunction)
select 'GT1', 25, convert(smalldatetime, '15-Aug-2002 16:00:00') , 'F2'
insert into #TimeMachineTransactions ( GateNo, EmpId, TransdateTime, TransFunction)
select 'GT1', 25, convert(smalldatetime, '16-Aug-2002 07:55:00') , 'F1'
insert into #TimeMachineTransactions (GateNo, EmpId, TransdateTime, TransFunction)
select 'GT1', 25, convert(smalldatetime, '16-Aug-2002 16:25:00') , 'F2'

--the join
select
convert(nvarchar(20), a.TransDateTime, 101) as Date,
convert(nvarchar(10),a.TransDateTime ,108) as Arrival,
convert(nvarchar(10),b.TransDateTime ,108) as Departure,
cast(convert(int, datediff(minute,a.TransDateTime , b.TransDateTime )/60) as nvarchar(2))
+ ':' +
right('0' + cast(convert(int, datediff(minute,a.TransDateTime , b.TransDateTime )%60) as nvarchar(2)),2) as Hours

from #TimeMachineTransactions a, #TimeMachineTransactions b

where a.Transfunction = 'F1' and b.Transfunction = 'F2'
and Day(a.TransdateTime) = Day(b.TransdateTime)
and Month(a.TransdateTime) = Month(b.TransdateTime)
and Year(a.TransdateTime) = Year(b.TransdateTime)
and a.empid = 25

order by a.TransDateTime

Hope that one works OK...

Edited by - rrb on 08/19/2002 03:39:27

BigRetina
Posting Yak Master

144 Posts

Posted - 2002-08-19 : 04:33:21

I AM AWEFULLY SORRYYYYY!!!!
TransId SHOULD BE IDENTITY OFCOURSE!!....
It should be 1,2,3,4 ..I corrected it!
Please forgive my mistake..I am too confused myself and getting tired of work!..lol..really need a vacation!!..lol
I appreciate ur help on this!!

BigRetina
Posting Yak Master

144 Posts

Posted - 2002-08-19 : 04:44:11

well..
I Have the following problems concerning the matching.
1.What is an employee checked in TWICE the same day..and he checked out once!..the hours will be messed up!....
So the matching I guess will have to take into consideration the MIN of the two CHECK IN dates for the same day!..
2.The user CHECKS IN ONCE AND CHECKS OUT TWICE!!..

Well I would love to hear suggestions...

and RRB..THANKS AGAIN PAL!!!!!!!!!!!!

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-08-19 : 19:08:07

No worries - I'm glad we're closer. OK here's a version where the employee checks in twice the same day.

drop table #TimeMachineTransactions

declare @task nvarchar(10)
declare @category nvarchar(50)
--create the table
create table #TimeMachineTransactions (
TransId int identity,
GateNo Char(3) ,
EmpId int ,
TransDateTime smalldatetime ,
TransFunction Char(2)
)

insert into #TimeMachineTransactions ( GateNo, EmpId, TransdateTime, TransFunction)
select 'GT1', 25, convert(smalldatetime, '15-Aug-2002 08:00:00') , 'F1'
insert into #TimeMachineTransactions ( GateNo, EmpId, TransdateTime, TransFunction)
select 'GT1', 25, convert(smalldatetime, '15-Aug-2002 09:00:00') , 'F1'
insert into #TimeMachineTransactions ( GateNo, EmpId, TransdateTime, TransFunction)
select 'GT1', 25, convert(smalldatetime, '15-Aug-2002 16:00:00') , 'F2'
insert into #TimeMachineTransactions ( GateNo, EmpId, TransdateTime, TransFunction)
select 'GT1', 25, convert(smalldatetime, '16-Aug-2002 07:55:00') , 'F1'
insert into #TimeMachineTransactions (GateNo, EmpId, TransdateTime, TransFunction)
select 'GT1', 25, convert(smalldatetime, '16-Aug-2002 16:25:00') , 'F2'

--the join
select
convert(nvarchar(20), a.TransDateTime, 101) as Date,
convert(nvarchar(10),a.TransDateTime ,108) as Arrival,
convert(nvarchar(10),b.TransDateTime ,108) as Departure,
cast(convert(int, datediff(minute,a.TransDateTime , b.TransDateTime )/60) as nvarchar(2))
+ ':' +
right('0' + cast(convert(int, datediff(minute,a.TransDateTime , b.TransDateTime )%60) as nvarchar(2)),2) as Hours

from
(select Empid, Day(TransDateTime) as Day, Month(TransDateTime) as Month, Year(TransDateTime) as Year, Min(TransDateTime) as TransDateTime
from #TimeMachineTransactions where TransFunction ='F1'
group by Empid, Day(TransDateTime), Month(TransDateTime), Year(TransDateTime) ) a,

(select Empid, Day(TransDateTime) as Day, Month(TransDateTime) as Month, Year(TransDateTime) as Year, Max(TransDateTime) as TransDateTime
from #TimeMachineTransactions where TransFunction = 'F2'
group by Empid, Day(TransDateTime), Month(TransDateTime), Year(TransDateTime) ) b

where a.Day = b.Day and a.Month = b.Month and a.Year = b.Year
and a.empid = 25

order by a.TransDateTime

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources