| Author |
 Topic  |
|
|
Stumbler
Starting Member
Belgium
23 Posts |
 Posted - 05/23/2007 : 04:18:20
|
Hello,
can anybody tell me what is wrong with this where clause?
WHERE
(tout.TimeOut=
(
select
min
(
CAST(
SUBSTRING([UnitTS], 1, 4) + '-' +
SUBSTRING([UnitTS], 5, 2) + '-' +
SUBSTRING([UnitTS], 7, 2) + ' ' +
SUBSTRING([UnitTS], 9, 2) + ':' +
SUBSTRING([UnitTS], 11, 2) + ':00'
AS datetime)
) AS Timestamp
from
VSKwin.dbo.History t2
where
t2.crdnr = tin.crdnr and
t2.Timestamp > tin.TimeIn
)
)
I keep getting the error message: invalid columnname 'Timestamp'
Hans |
|
|
pbguy
Constraint Violating Yak Guru
India
319 Posts |
Posted - 05/23/2007 : 05:08:45
|
| remove 'AS Timestamp'...why u need an alias name in the where clause's subquery |
Edited by - pbguy on 05/23/2007 05:09:23 |
 |
|
|
AndrewMurphy
Flowing Fount of Yak Knowledge
Ireland
2915 Posts |
Posted - 05/23/2007 : 05:12:08
|
"remove as AS Timestamp...."...wrong
you can't use an alias in a where clause...you need to repeat the code which contributes to the alias...or else put the alias into a derived table...something like...
"select * from (select a+b as c from table1) d where c = x" |
|
|
|
Stumbler
Starting Member
Belgium
23 Posts |
Posted - 05/23/2007 : 07:31:04
|
I've changed my where clause, but now I come up with another error message: only one expression can be specified in the select list when the subquery is not introduced with EXISTS
This is my complete select clause
SELECT DISTINCT
tin.day, tin.crdnr, tin.crdname,tin.id,tin.extra3,tin.extra4, tin.TimeIn, tout.TimeOut,
(tout.TimeOut-tin.TimeIn)*24 AS Timespent
FROM
(
SELECT
LEFT(h.UnitTS, 8) AS day,
h.CrdNr,
h.CrdName,
c.ID,
c.Extra3,
c.Extra4,
CAST(
SUBSTRING([UnitTS], 1, 4) + '-' +
SUBSTRING([UnitTS], 5, 2) + '-' +
SUBSTRING([UnitTS], 7, 2) + ' ' +
SUBSTRING([UnitTS], 9, 2) + ':' +
SUBSTRING([UnitTS], 11, 2) + ':00'
AS datetime)
AS TimeIn
FROM
VSKwin.dbo.History h
INNER JOIN
VSKwin.dbo.Cards c ON h.CrdNr = c.Nr
WHERE
h.Type = 'A'AND
h.UnitNr IN ('2', '3') AND
LEFT(h.UnitTS, 8) = '20070522' AND
(
h.RdrName = 'Tourniquet 1 IN' OR
h.RdrName = 'Tourniquet 1 IN Code' OR
h.RdrName = 'Tourniquet 2 IN Code' OR
h.RdrName = 'Tourniquet 2 IN' OR
h.RdrName = 'IN'
)
) AS tin,
(
SELECT
h.CrdNr,
CAST(
SUBSTRING([UnitTS], 1, 4) + '-' +
SUBSTRING([UnitTS], 5, 2) + '-' +
SUBSTRING([UnitTS], 7, 2) + ' ' +
SUBSTRING([UnitTS], 9, 2) + ':' +
SUBSTRING([UnitTS], 11, 2) + ':00'
AS datetime)
AS TimeOut
FROM
VSKwin.dbo.History h
INNER JOIN
VSKwin.dbo.Cards c ON h.CrdNr = c.Nr
WHERE
h.Type = 'A'AND
h.UnitNr IN ('2', '3') AND
LEFT(h.UnitTS, 8) <= '20070522' AND
(
h.RdrName = 'Tourniquet 1 UIT' OR
h.RdrName = 'Tourniquet 1 UIT Code' OR
h.RdrName = 'Tourniquet 2 UIT Code' OR
h.RdrName = 'Tourniquet 2 UIT' OR
h.RdrName = 'UIT'
)
) AS tout
WHERE
(tout.TimeOut=
(
select
t2.crdnr,
min
(
CAST(
SUBSTRING([UnitTS], 1, 4) + '-' +
SUBSTRING([UnitTS], 5, 2) + '-' +
SUBSTRING([UnitTS], 7, 2) + ' ' +
SUBSTRING([UnitTS], 9, 2) + ':' +
SUBSTRING([UnitTS], 11, 2) + ':00'
AS datetime)
)
from
VSKwin.dbo.History t2
where
t2.crdnr = tin.crdnr and
min
(
CAST(
SUBSTRING([UnitTS], 1, 4) + '-' +
SUBSTRING([UnitTS], 5, 2) + '-' +
SUBSTRING([UnitTS], 7, 2) + ' ' +
SUBSTRING([UnitTS], 9, 2) + ':' +
SUBSTRING([UnitTS], 11, 2) + ':00'
AS datetime)
)
> tin.TimeIn
)
)
AND tout.crdnr=tin.crdnr
ORDER BY tin.id,tin.crdname, tin.TimeIn;
The whole select is probally way too complex for me, but I started building and kept on adding more criteria.
Now I come up with something that isn't working and I don't know why. 
All help is more than welcome
Hans |
|
|
|
Stumbler
Starting Member
Belgium
23 Posts |
Posted - 05/23/2007 : 07:49:53
|
Code adjusted to
SELECT DISTINCT
tin.day, tin.crdnr, tin.crdname,tin.id,tin.extra3,tin.extra4, tin.TimeIn, tout.TimeOut,
(tout.TimeOut-tin.TimeIn)*24 AS Timespent
FROM
(
SELECT
LEFT(h.UnitTS, 8) AS day,
h.CrdNr,
h.CrdName,
c.ID,
c.Extra3,
c.Extra4,
CAST(
SUBSTRING([UnitTS], 1, 4) + '-' +
SUBSTRING([UnitTS], 5, 2) + '-' +
SUBSTRING([UnitTS], 7, 2) + ' ' +
SUBSTRING([UnitTS], 9, 2) + ':' +
SUBSTRING([UnitTS], 11, 2) + ':00'
AS datetime)
AS TimeIn
FROM
VSKwin.dbo.History h
INNER JOIN
VSKwin.dbo.Cards c ON h.CrdNr = c.Nr
WHERE
h.Type = 'A'AND
h.UnitNr IN ('2', '3') AND
LEFT(h.UnitTS, 8) = '20070522' AND
(
h.RdrName = 'Tourniquet 1 IN' OR
h.RdrName = 'Tourniquet 1 IN Code' OR
h.RdrName = 'Tourniquet 2 IN Code' OR
h.RdrName = 'Tourniquet 2 IN' OR
h.RdrName = 'IN'
)
) AS tin,
(
SELECT
h.CrdNr,
CAST(
SUBSTRING([UnitTS], 1, 4) + '-' +
SUBSTRING([UnitTS], 5, 2) + '-' +
SUBSTRING([UnitTS], 7, 2) + ' ' +
SUBSTRING([UnitTS], 9, 2) + ':' +
SUBSTRING([UnitTS], 11, 2) + ':00'
AS datetime)
AS TimeOut
FROM
VSKwin.dbo.History h
INNER JOIN
VSKwin.dbo.Cards c ON h.CrdNr = c.Nr
WHERE
h.Type = 'A'AND
h.UnitNr IN ('2', '3') AND
LEFT(h.UnitTS, 8) <= '20070522' AND
(
h.RdrName = 'Tourniquet 1 UIT' OR
h.RdrName = 'Tourniquet 1 UIT Code' OR
h.RdrName = 'Tourniquet 2 UIT Code' OR
h.RdrName = 'Tourniquet 2 UIT' OR
h.RdrName = 'UIT'
)
) AS tout
WHERE
(tout.TimeOut=
(
select
min
(
CAST(
SUBSTRING([UnitTS], 1, 4) + '-' +
SUBSTRING([UnitTS], 5, 2) + '-' +
SUBSTRING([UnitTS], 7, 2) + ' ' +
SUBSTRING([UnitTS], 9, 2) + ':' +
SUBSTRING([UnitTS], 11, 2) + ':00'
AS datetime)
)
from
VSKwin.dbo.History t2
where
t2.crdnr = tin.crdnr and
min
(
CAST(
SUBSTRING([UnitTS], 1, 4) + '-' +
SUBSTRING([UnitTS], 5, 2) + '-' +
SUBSTRING([UnitTS], 7, 2) + ' ' +
SUBSTRING([UnitTS], 9, 2) + ':' +
SUBSTRING([UnitTS], 11, 2) + ':00'
AS datetime)
)
> tin.TimeIn
)
)
AND tout.crdnr=tin.crdnr
Now I get the message:
An aggregate may not appear in the WHERE clause unless it is a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference
Hans |
|
|
|
AndrewMurphy
Flowing Fount of Yak Knowledge
Ireland
2915 Posts |
Posted - 05/23/2007 : 08:12:07
|
I think we're going to chase our tails on this one, unless we go back a bit.
Can you supply us with sample data, and "matching" expected results...and also sample DDL for the tables involed and then tell us what you want to achieve.
You are now hitting a problem because of the "MIN" keyword...and the code is beginning to get complicated and 'unreadable (without effort)'
We need to break the problem down and fix up 1 section at a time, but to do so wisely means we need the "bigger picture" to see if there is a simpler approach. |
|
|
|
Stumbler
Starting Member
Belgium
23 Posts |
Posted - 05/23/2007 : 10:29:06
|
Okay lets start from the begining:
There is a batchsystem installed that puts all data into a history table.
people can come in and go out in different ways. Each pass at the batch system collect data like this
SystemTS UnitTS UnitNr UnitType Sign Zone Point Type RdrNr RdrHead RdrName CrdNr CrdName
20060411074455240 200604110744 3 1 + Z001 A 0 A Tourniquet 1 IN 14686628 John DOE
20060412164127705 200604121641 3 1 + Z000 A 4 A Tourniquet 2 UIT 14686628 John DOE
20060412164127707 200604121641 3 1 + Z000 A 4 A Tourniquet 2 UIT 14686628 John DOE
20060413074338484 200604130743 3 1 + Z001 A 0 A Tourniquet 1 IN 14686628 John DOE
20060413163946153 200604131639 3 1 + Z000 A 4 A Tourniquet 2 UIT 14686628 John DOE
20060414073953895 200604140739 3 1 + Z001 A 2 A IN 14686628 John DOE
20060414073953897 200604140739 3 1 + Z001 A 2 A Tourniquet 2 IN 14686628 John DOE
20060414073954898 200604140739 3 1 + Z001 A 2 A Tourniquet 2 IN 14686628 John DOE
20060414073954900 200604140739 3 1 + Z001 A 2 A Tourniquet 2 IN 14686628 John DOE
20060414163829456 200604141638 3 1 + Z000 A 5 A Tourniquet 2 UIT Code 14686628 John DOE
We need to store data in a table containing for each person on every day when he entered and when he has left. the problem is that som workers start in day 1 at 22:00 pm en work till next day at 6:00 AM.
we want a table with the following information
CrdNr
CrdName
Day (the activity started)
TimeIn
TimeOut
TimeSpent (TimeOut-TimeIN)
I hope this is clear enough and want to thank for any help in advance.
Hans |
|
|
|
AndrewMurphy
Flowing Fount of Yak Knowledge
Ireland
2915 Posts |
Posted - 05/23/2007 : 12:04:43
|
nearly there..
can you follow the style of the 1st section in the link below...? ie...supply table ddl, with insert statements (so we can recreate your data easily at our end) ... and then supply the matching expected output...
then you may get a faster response....(and not necessarily from me)...as it is trying to work out the problem may put people off suggesting a solution.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74221 |
|
|
|
Stumbler
Starting Member
Belgium
23 Posts |
Posted - 05/24/2007 : 03:23:39
|
I will give it another try:
As you can see in the expected output, everything containing the word 'IN' generates an tin.timestamp. If it contains the word 'UIT' we get a tout.timestamp. In some occasion we also get to IN's before we have an 'OUT'. than we take the first and discard the second. with the out's its the opposit (on 2 outs: discard the first)
CREATE TABLE history (
UnitTS varchar(12) NOT NULL,
RdrName varchar(30) NULL,
CrdNr int NULL)
INSERT INTO history (UnitTS , RdrName, CrdNr)
SELECT '200705220537', 'Tourniquet 1 IN Code' ,14321330 UNION ALL
SELECT '200705220753', 'Magazijn' ,14321330 UNION ALL
SELECT '200705221412', 'Tourniquet 1 UIT Code' ,14321330 UNION ALL
SELECT '200705232144', 'Tourniquet 2 IN Code' ,14321330 UNION ALL
SELECT '200705240059', 'Magazijn' ,14321330 UNION ALL
SELECT '200705240604', 'Tourniquet 1 UIT Code' ,14321330 UNION ALL
SELECT '200705211130', 'IN' ,14374256 UNION ALL
SELECT '200705211258', 'UIT' ,14374256 UNION ALL
SELECT '200705220748', 'Tourniquet 1 IN' ,14374256 UNION ALL
SELECT '200705221233', 'Tourniquet 1 UIT' ,14374256 UNION ALL
SELECT '200705221638', 'Parking IN Code' ,14374256 UNION ALL
SELECT '200705221639', 'Tourniquet 1 IN' ,14374256 UNION ALL
SELECT '200705221841', 'Tourniquet 1 UIT' ,14374256 UNION ALL
SELECT '200705221843', 'Parking UIT Code' ,14374256 UNION ALL
SELECT '200705230744', 'Tourniquet 1 IN' ,14374256 UNION ALL
SELECT '200705231142', 'Tourniquet 1 UIT' ,14374256 UNION ALL
SELECT '200705231325', 'IN' ,14374256 UNION ALL
SELECT '200705231627', 'Tourniquet 1 UIT' ,14374256
SELECT * FROM history
order by clt_num,autbeg_dte
Expected result:
day crdnr tin.Timestamp tout.Timestamp Timespent
20070522 14321330 22/05/2007 05:37 22/05/2007 14:12 8.58
20070523 14321330 23/05/2007 21:44 24/05/2007 06:04 8.33
20070521 14374256 21/05/2007 11:30 21/05/2007 12:58 1.47
20070522 14374256 22/05/2007 07:48 22/05/2007 12:33 4.75
20070522 14374256 22/05/2007 16:39 22/05/2007 18:41 2.03
20070523 14374256 23/05/2007 07:44 23/05/2007 11:42 3.97
20070523 14374256 23/05/2007 13:25 23/05/2007 16:27 3.03
Hope this is information enough.
If not, please fire your questions!!
Hans |
|
|
|
AndrewMurphy
Flowing Fount of Yak Knowledge
Ireland
2915 Posts |
Posted - 05/28/2007 : 05:58:56
|
1 point of order...."SELECT * FROM history order by clt_num,autbeg_dte" doesn't work - the "order by" column names don't exist.
Can you group the "in"'s by day, and take the minimum (first)...or apply an "order by x ASC" clause and implement a "TOP 1" clause
and the group by "out"'s by day, and take the maximum (last)...or apply an "order by x DESC" clause and implement a "TOP 1" clause
and then union the 2 result sets together....or join them together in some planned manner.
I've had a go....see if the following gets you close to what you want
SELECT crdnr, indate, DATEDIFF(MI, ain, auit) FROM
(
SELECT a.crdnr, a.indate,
CAST(
SUBSTRING(A.intime, 1, 4) + '-' +
SUBSTRING(A.intime, 5, 2) + '-' +
SUBSTRING(A.intime, 7, 2) + ' ' +
SUBSTRING(A.intime, 9, 2) + ':' +
SUBSTRING(A.intime, 11, 2) + ':00'
AS datetime) AS ain,
CAST(
SUBSTRING(b.uittime, 1, 4) + '-' +
SUBSTRING(b.uittime, 5, 2) + '-' +
SUBSTRING(b.uittime, 7, 2) + ' ' +
SUBSTRING(b.uittime, 9, 2) + ':' +
SUBSTRING(b.uittime, 11, 2) + ':00'
AS datetime) AS auit
FROM
(SELECT 'in' AS ain, crdnr, Left(unitts,8) AS indate, MIN(unitts) AS intime FROM history
WHERE rdrname LIKE '%in%'
GROUP BY crdnr, LEFT(unitts,8)) a
LEFT JOIN
(SELECT 'uit' AS auit, crdnr, Left(unitts,8) AS uitdate, Max(unitts) AS uittime FROM history
WHERE rdrname LIKE '%uit%'
GROUP BY crdnr, LEFT(unitts,8)) b
ON a.crdnr = b.crdnr AND a.indate = b.uitdate
) c
the value returned will be in minutes....(you may have to convert same to hours + mins yourself) |
|
|
| |
Topic |
|
|
|
Where Clause not working
