TSQL help needed - 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

Transact-SQL (2000)

TSQL help needed

Author

Topic

TJ
Posting Yak Master

201 Posts

Posted - 2002-10-26 : 11:52:34

I need advice and guidance to code and implement the following:

I'm using a table with the following data:

ntsrsn		seq	hdr
ABC20000010A	00	A
ABC20000010B	00	B
ABC200000101	01	REC1a
ABC200000102	02	REC1b
ABC200000103	01	REC2a
ABC200000104	02	REC2b
ABC200000105	01	REC3
ABC20000010R	00	R
DEF10000030A	00	A
DEF10000030B	00	B
DEF100000303	01	REC1
DEF10000030R	00	R
GHI30000090A	00	A
GHI300000901	01	REC1
GHI30000090R	00	R

I need to 'group' the records by the ntsrsn and verify that the hdr records A, B, and R exist within that group. If they do not, the entire group of records would fail. Ex: groups ABC and DEF would succeed whereas group GHI would fail because hdr B is missing.

I know that a cursor is a very bad thing. I promise not to use one! However, I do not know how to look at the group and do the comparison without one.

Please point me in the right direction.

Thanks!
Teresa
(SQL 7)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-26 : 12:31:15

SELECT A.* FROM myTable A INNER JOIN
(SELECT LEFT(ntsrsn,11) AS ntsrsn FROM myTable WHERE hdr IN ('A','B','R')
GROUP BY LEFT(ntsrsn,11) HAVING Count(DISTINCT hdr)=3) B
ON LEFT(A.ntsrsn,11)=B.ntsrsn

The subquery pulls only the A, B, and R header rows and ensures that all three are accounted for. This is JOINed to the outer SELECT statement by ntsrsn. The LEFT(ntsrsn, 11) takes the part of the code that forms the "group".

TJ
Posting Yak Master

201 Posts

Posted - 2002-10-26 : 12:54:18

quote:

SELECT A.* FROM myTable A INNER JOIN
(SELECT LEFT(ntsrsn,11) AS ntsrsn FROM myTable WHERE hdr IN ('A','B','R')
GROUP BY LEFT(ntsrsn,11) HAVING Count(DISTINCT hdr)=3) B
ON LEFT(A.ntsrsn,11)=B.ntsrsn

The subquery pulls only the A, B, and R header rows and ensures that all three are accounted for. This is JOINed to the outer SELECT statement by ntsrsn. The LEFT(ntsrsn, 11) takes the part of the code that forms the "group".

Thank you very much!

Teresa

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources