SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Where Clause not working
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Stumbler
Starting Member

Belgium
23 Posts

Posted - 05/23/2007 :  04:18:20  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
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
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 05/23/2007 :  05:12:08  Show Profile  Reply with Quote
"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"
Go to Top of Page

Stumbler
Starting Member

Belgium
23 Posts

Posted - 05/23/2007 :  07:31:04  Show Profile  Reply with Quote
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
Go to Top of Page

Stumbler
Starting Member

Belgium
23 Posts

Posted - 05/23/2007 :  07:49:53  Show Profile  Reply with Quote
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
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 05/23/2007 :  08:12:07  Show Profile  Reply with Quote
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.
Go to Top of Page

Stumbler
Starting Member

Belgium
23 Posts

Posted - 05/23/2007 :  10:29:06  Show Profile  Reply with Quote
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
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 05/23/2007 :  12:04:43  Show Profile  Reply with Quote
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
Go to Top of Page

Stumbler
Starting Member

Belgium
23 Posts

Posted - 05/24/2007 :  03:23:39  Show Profile  Reply with Quote
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
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 05/28/2007 :  05:58:56  Show Profile  Reply with Quote
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)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000