Math inside a 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 2005 Forums

Transact-SQL (2005)

Math inside a query

Author

Topic

Movak
Starting Member

11 Posts

Posted - 2010-04-22 : 13:35:56

I have a table that contains 2 columns; receipt# and flag.
Multiple rows may have the same receipt number.
I need to do a query that returns the rows where there exists a row with the previous receipt# and the flag set.

We can not use a trigger to create a table that would hold that relation.

Any help would be appreciated.
Joe Klovance

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-04-22 : 13:37:18

quote:
I need to do a query that returns the rows where there exists a row with the previous receipt# and the flag set.

Can you show us some sample data and your expected output.

Movak
Starting Member

11 Posts

Posted - 2010-04-22 : 13:55:01

Receipt # Flag
1 false
1 true
1 True
2 False
2 False
3 false
3 True

Expected output
2
2

The receipt 2 rows are selected because there is a row with 1 as the receipt # and the flag set
Receipt 1 rows are not selected because there is no prevous receipt
Receipt 3 rows are not selected because there are no receipt 2 rows with the flag set

Movak

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-04-22 : 14:38:41

DECLARE @table table (col1 int, col2 varchar(5))

insert into @table
SELECT 1,'false' union all
SELECT 1,'true' union all
SELECT 1,'True' union all
SELECT 2,'False' union all
SELECT 2,'False' union all
SELECT 3,'false' union all
SELECT 3,'True'

select * from @table

select t1.*
from @table t1
where exists( select 1 from @table t2 where t1.col1 = t2.col1 + 1 and t2.col2 = 'true')

jim

Everyday I learn something that somebody else already knew

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-22 : 15:39:02

I just did a quick hack, but if the Col1 numbers should have any gaps, then this should work for that scenario:

SELECT T.*
FROM @Table AS T
CROSS APPLY
	(SELECT MAX(col1) AS col1 FROM @Table AS A WHERE A.Col1 < T.Col1) AS A
CROSS APPLY
	(SELECT MAX(col1) AS col1 FROM @Table AS B WHERE B.Col1 < T.Col1 AND B.Col2 = 'true') AS B
WHERE
	A.col1 = B.col1

Movak
Starting Member

11 Posts

Posted - 2010-04-22 : 16:45:18

The second solution turn out to be what I need but I tried it and got the following errors.

Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near 'APPLY'.
Server: Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'AS'.

line 17 is the first cross apply

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-04-22 : 16:56:14

Can you please show the full query? Also, you are using SQL Server 2005 right?

Movak
Starting Member

11 Posts

Posted - 2010-04-22 : 17:00:32

Oops, my bad, we are using SQL Server 2000 (don't as me why ;) )

Movak
Starting Member

11 Posts

Posted - 2010-04-22 : 17:09:46

I have re-posted on the 2000 forum