Math in select statements - 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 2000 Forums

SQL Server Development (2000)

Math in select statements

Author

Topic

Movak
Starting Member

11 Posts

Posted - 2010-04-22 : 17:07:49

I have a table that contains 2 columns; receipt# and flag.
Multiple rows may have the same receipt number.

I need to select a row the the previous receipt has the flag set. The receipt numbers may not be contiguous.
Sample:
Receipt # Flag
1 false
1 true
1 True
3 False
3 False
4 false
4 True

Expected output
3 False
3 False

The receipt 3 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 previous receipt
Receipt 4 rows are not selected because there are no receipt 3 rows with the flag set

I have a solution that works in server 2005 but not 2000

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-22 : 17:36:20

You just need to change it around a bit:

SELECT col1, col2
FROM
	(
		SELECT T.*,
			(SELECT MAX(col1) AS col1 FROM @Table AS A WHERE A.Col1 < T.Col1) AS Acol1,
			(SELECT MAX(col1) AS col1 FROM @Table AS B WHERE B.Col1 < T.Col1 AND B.Col2 = 'true') AS BCol1

		FROM @Table AS T
	) AS D
WHERE
	Acol1 = Bcol1

Movak
Starting Member

11 Posts

Posted - 2010-04-22 : 19:23:56

The requirements were changed on me a bit so here is the final question.

It is similar to the one but instead of just one flag there are two and the criteria is that I want both transaction where one transaction has flag1 set and the next transaction has flag 2 set. There is also a register # that throws a wrench into it in that the transactions must be from the same register to be compared.

Example

R T F1 f2
2 1 1 0
2 1 1 0
1 2 0 0
1 3 1 0
1 3 0 1
2 4 0 1
1 5 0 1
1 5 0 0
1 6 0 1

Result
R T F1 F2
2 1 1 0
2 1 1 0
1 3 1 0
1 3 0 1
2 4 0 1
1 5 0 1
1 5 0 0

Transactions 1 and 4 are from the same register and are flag1 followed by flag 2
Transactions 3 and 5 are from the same register and are flag1 followed by flag 2

My brain hurts.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources