Hello,
I'm using S2k to illustrate a solution and I've
added some additional data.I'm being expedient
with the date(s) as that is a detail not penitent
to the problem.It's only necessary that the data
be understandable
I have also omitted the LAT
and LONG columns for the same reason.
create table #Willem([ID] int primary key,CLIENT int,
[DATE] char(5),[TIME] char(5),BOUND varchar(3))
go
insert #Willem values(1,1,'06/07','03:03','IN')
insert #Willem values(2,1,'06/07','03:15','IN')
insert #Willem values(3,1,'06/07','03:23','OUT')
insert #Willem values(4,1,'06/07','03:43','IN')
insert #Willem values(5,1,'07/07','07:03','IN')
insert #Willem values(6,1,'07/07','07:15','OUT')
insert #Willem values(7,1,'07/07','07:23','OUT')
insert #Willem values(8,1,'07/07','07:43','IN')
-- Additional data for client 2
insert #Willem values(9,2,'06/07','03:03','IN')
insert #Willem values(10,2,'06/07','03:15','IN')
insert #Willem values(11,2,'06/07','03:23','OUT')
insert #Willem values(12,2,'06/07','03:43','IN')
insert #Willem values(13,2,'07/07','06:23','IN')
insert #Willem values(14,2,'07/07','07:15','OUT')
This is a good example of why most procedural developers
think sql s..ks
With the data given there is no combination of columns
(as you have found out) that you can GROUP BY that will
take into account the sequence of BOUND within CLIENT and DATE.
Without taking the order of BOUND into account its therefore
not possible to use a GROUP BY query to obtain your result.
But if you could simply *glue* together the order of
BOUND within CLIENT and DATE then you could easily use a
GROUP BY query for the solution.
Here's the data with some *glue* (the Brank column) taking into
account the sequence of the BOUND column.
Call it table ##Willem (a S2k global temporary table):
select * from ##Willem order by [ID]
ID CLIENT DATE TIME BOUND *Brank*
----------- ----------- ----- ----- ----- -----------
1 1 06/07 03:03 IN 1
2 1 06/07 03:15 IN 1
3 1 06/07 03:23 OUT 2
4 1 06/07 03:43 IN 3
5 1 07/07 07:03 IN 4
6 1 07/07 07:15 OUT 5
7 1 07/07 07:23 OUT 5
8 1 07/07 07:43 IN 6
9 2 06/07 03:03 IN 1
10 2 06/07 03:15 IN 1
11 2 06/07 03:23 OUT 2
12 2 06/07 03:43 IN 3
13 2 07/07 06:23 IN 4
14 2 07/07 07:15 OUT 5
Brank is an integer counter or rank.It's actual values are not
important.What is important is that BOUND's within CLIENT and DATE
that have the same value (ie. IN or OUT) have the same rank
and when a BOUND changes, Brank is changed (incremented by some
value (ie. 1)).It is not even necessary that Brank be reset to
1 when the CLIENT changes.It could just as well increment ascending
over the entire table.You can think of Brank as a virtual grouping
column derived from the table.
Now, Brank can be used in a GROUP BY query to obtain the solution.
Obviously Brank is necessary for the solution (grouping) but not
part of it, ie. it is not needed as a returned column in Select.
Select CLIENT,[DATE],Min([TIME]) as [FIRST],
Max([TIME]) as [LAST],BOUND
From ##Willem
Group By CLIENT,[DATE],Brank,BOUND
Order by CLIENT,Brank
Solution:
CLIENT DATE FIRST LAST BOUND
----------- ----- ----- ----- -----
1 06/07 03:03 03:15 IN
1 06/07 03:23 03:23 OUT
1 06/07 03:43 03:43 IN
1 07/07 07:03 07:03 IN
1 07/07 07:15 07:23 OUT
1 07/07 07:43 07:43 IN
2 06/07 03:03 03:15 IN
2 06/07 03:23 03:23 OUT
2 06/07 03:43 03:43 IN
2 07/07 06:23 06:23 IN
2 07/07 07:15 07:15 OUT
Visulization of this type of solution is one thing,
*simply* generating the Brank column is another
Given either S2k or Access, an sql query to generate
Brank is not that simple.It would be much easier to do with
cursors/procedurally.With Yukon this type of rank should be simpler
to obtain using new sql features or the CLR.Unfortuneately,I
don't think Access will have the new sql functions
Alternatively, Brank can be easily obtained on S2k using the RAC utility.
Here is the RAC execute statement to obtain table ##Willem:
Exec Rac @transform='_dummy_',
@rows='CLIENT & [DATE] & BOUND & [TIME] & [ID]',
@rowsort='[ID]',------or CLIENT & [DATE] & TIME & [ID]
@pvtcol='Report Mode',
@from='#Willem',
@grand_totals='n',@rowbreak='n',
-- The @rowindicators parameter constructs the rank column (Brank)
-- based on BOUND within CLIENT AND [DATE] (as specified in
-- the @rows parameter).The columns are sorted by [ID] assuming
-- [ID] reflects the true ordering of the data.If [ID] is arbitrary
-- then the table could be sorted by CLIENT,[DATE],[TIME] and [ID].
-- Brank is essentially a byproduct of the group/partition of
-- CLIENT,[DATE] and BOUND ordered from left to right or by [ID] if
-- it is equivalent.This is how sql99 functions will work in Yukon.
-- They should make life easier by eliminating obsolete,complicated,
-- resource intensive and non-scaling sql sql89/92 coding.
@rowindicators='BOUND{Brank}',@counterdatatype='int',
-- You can check out the RAC Help file for all the features of
-- its @select parameter.
@select='Select 1*[ID] as [ID],1*CLIENT as CLIENT,[DATE],
[TIME],BOUND,Brank into ##Willem
from rac
order by rd'
Notice no messy sql code necessary
Perhaps someone can give you an Access method of obtaining
Brank or,better yet, a simpler solution
I have posted ways of doing this using Access sql.If no one
jumps in perhaps I'll dig around.But be warned its not
pretty, inefficient and nothing that will scale
It's examples like this that have many MS users waiting for Yukon.
But regardless, there's always RAC
.In fact the whole problem
could be solved within a single RAC execute by simply moving
the GROUP BY query to the RAC @select.
Have you given any thought to migrating your data to at least
S2k MSDE and simply linking your tables into Access?
HTH
RAC v2.2 and QALite @
www.rac4sql.net
