Where Clause not working - 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 2005 Forums

Transact-SQL (2005)

Where Clause not working

Author

Topic

Stumbler
Starting Member

23 Posts

Posted - 2007-05-23 : 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

319 Posts

Posted - 2007-05-23 : 05:08:45

remove 'AS Timestamp'...why u need an alias name in the where clause's subquery

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-05-23 : 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

23 Posts

Posted - 2007-05-23 : 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

23 Posts

Posted - 2007-05-23 : 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
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-05-23 : 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

23 Posts

Posted - 2007-05-23 : 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
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-05-23 : 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

23 Posts

Posted - 2007-05-24 : 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
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-05-28 : 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)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources