November 26, 2010 at 8:27 am
Hi guys,
relatively new to T-sql (using MSSQL2005/2008) and I am having trouble creating some t-sql to pull a specific set of records form a table. the basic tabel structure is like this:
DOC_NO LINE STATUS
1 A QA
1 B QV
1 C O
1 D A
1 E AI
2 F QE
2 G QE
2 H QE
3 I S
3 J QV
3 K QE
4 L QE
4 M QV
4 N O
4 O A
4 P S
So, as you can see a specif DOC_NO can have multiple lines, in various statusses.
Now I am looking for a piece of SQL that gives me all DOC_NO's that have ONLY lines with status QE (in this example, only DOC_NO 2.
As stated I am fairly new to SQL and I can usually manage to find the answers to simple issues myself, but in this case I am experiencing a blackout 😉 There is probably an easy solution I am overlooking, but i can't think of a solution.
Can any of you help?
November 26, 2010 at 9:22 am
Something like this. The INSERT statement will only work in 2008.
CREATE TABLE #Docs (DocNo int, Line char(1), LStatus varchar(2))
INSERT INTO #Docs VALUES
(1, 'A', 'QA'),
(1, 'B', 'QV'),
(1, 'C', 'O'),
(1, 'D', 'A'),
(1, 'E', 'AI'),
(2, 'F', 'QE'),
(2, 'G', 'QE'),
(2, 'H', 'QE'),
(3, 'I', 'S'),
(3, 'J', 'QV'),
(3, 'K', 'QE'),
(4, 'L', 'QE'),
(4, 'M', 'QV'),
(4, 'N', 'O'),
(4, 'O', 'A'),
(4, 'P', 'S')
;WITH QEExist AS ( -- Lists all doc nos that have a QE code
SELECT DISTINCT DocNo
FROM #Docs
WHERE LStatus = 'QE'
)
, OthersExist AS ( -- Lists all doc nos that have any non-QE codes
SELECT DISTINCT DocNo
FROM #Docs
WHERE LStatus <> 'QE'
)
-- Gets all doc numbers that are in the first set but not in the second
SELECT DocNo FROM QEExist EXCEPT
SELECT DocNo FROM OthersExist
John
November 26, 2010 at 9:42 am
John Mitchell-245523 (11/26/2010)
Something like this. The INSERT statement will only work in 2008.
Gosh, John... this is an SQL Server 2000 forum. My rhetorical question is "Why would you do that to someone?" Even the code you wrote isn't compatible with 2K.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 26, 2010 at 9:43 am
Jeff Moden (11/26/2010)
John Mitchell-245523 (11/26/2010)
Something like this. The INSERT statement will only work in 2008.Gosh, John... this is an SQL Server 2000 forum. My rhetorical question is "Why would you do that to someone?" Even the code you wrote isn't compatible with 2K.
Never mind... I see it in the original post. Sorry about that.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 26, 2010 at 9:46 am
Jeff
Yes, I did think about asking why he posted in this forum, but it such things seem to happen so much these days that I don't usually bother!
John
November 26, 2010 at 3:13 pm
hm...the reason I posted it in this forum is because I obviously overlooked some of the structure on this board 😉 sorry for that!
I probably should have mentioned I can not make any changes to the sctructure of the database (adding tables, views etc.) or it will have consequences for the supportedour erp-supplier is willing to give. Select-type statements only unfortunatley...
Thansk for replying so far anyway...
November 28, 2010 at 4:49 pm
New tools make solutions more complex and less effective.
😉
Here how would it look with "classic" SQL:
SELECT DocNo
FROM #Docs QE
WHERE LStatus = 'QE'
AND NOT EXISTS (
SELECT * FROM #Docs NQE
WHERE NQE.DocNo = QE.DocNo
AND NQE.LStatus <> QE.LStatus
)
GROUP BY DocNo
_____________
Code for TallyGenerator
November 28, 2010 at 10:05 pm
Sergiy (11/28/2010)
New tools make solutions more complex and less effective.😉
Here how would it look with "classic" SQL:
SELECT DocNo
FROM #Docs QE
WHERE LStatus = 'QE'
AND NOT EXISTS (
SELECT * FROM #Docs NQE
WHERE NQE.DocNo = QE.DocNo
AND NQE.LStatus <> QE.LStatus
)
GROUP BY DocNo
In this case, my trusted ol' friend, it's not the "new" tools that made it complex and slow... As we all know, "It Depends"... the devil is in the data and the indexes.
Test code...
--=============================================================================
-- Create a million row test table where all document #'s that are evenly
-- divisible by 100 are guaranteed to be all "QE" status for a lines.
-- Each document in this test data has an average of 5 lines similar to
-- the original request. The creation of this test data is NOT a part of
-- the solution. Also note that not all DocNo's will be present owing to
-- the randomized data.
--=============================================================================
PRINT REPLICATE('=',80);
PRINT 'DocNo''s evenly divisible by 100 meet "QE only" spec for following tests.';
PRINT REPLICATE('=',80);
--===== Conditionally drop the test table to make reruns easier.
IF OBJECT_ID('TempDB..#Docs','U') IS NOT NULL
DROP TABLE #Docs
;
--===== Create the test data using randomized data with a later condition that
-- forces documents evenly divisible by 50 (
WITH
cteCreateData AS
(
SELECT TOP 1000000
DocNo = ABS(CHECKSUM(NEWID()))%200000+1,
Line = CHAR(ABS(CHECKSUM(NEWID()))%26+64),
LStatus = CHAR(ABS(CHECKSUM(NEWID()))%26+64)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+64)
FROM sys.All_Columns rowsource1
CROSS JOIN sys.All_Columns rowsource2
)
SELECT DocNo,
Line,
LStatus = CASE WHEN DocNo % 100 <> 0 THEN LStatus ELSE 'QE' END
INTO #Docs
FROM cteCreateData
;
-- CREATE CLUSTERED INDEX IX_#Docs_DocNo_LStatus
-- ON #Docs (DocNo,LStatus)
--;
--=============================================================================
-- Test the two methods given for solving this problem and a 3rd method.
--=============================================================================
PRINT '========== The "slower" way to use EXCEPT ==========';
SET STATISTICS TIME ON;
WITH QEExist AS ( -- Lists all doc nos that have a QE code
SELECT DISTINCT DocNo
FROM #Docs
WHERE LStatus = 'QE'
)
, OthersExist AS ( -- Lists all doc nos that have any non-QE codes
SELECT DISTINCT DocNo
FROM #Docs
WHERE LStatus <> 'QE'
)
-- Gets all doc numbers that are in the first set but not in the second
SELECT DocNo FROM QEExist EXCEPT
SELECT DocNo FROM OthersExist
;
SET STATISTICS TIME OFF;
-------------------------------------------------------------------------------
PRINT '========== The "old" way ==========';
SET STATISTICS TIME ON;
SELECT DocNo
FROM #Docs QE
WHERE LStatus = 'QE'
AND NOT EXISTS (
SELECT * FROM #Docs NQE
WHERE NQE.DocNo = QE.DocNo
AND NQE.LStatus <> QE.LStatus
)
GROUP BY DocNo
;
SET STATISTICS TIME OFF;
-------------------------------------------------------------------------------
PRINT '========== A different way to use EXCEPT ==========';
SET STATISTICS TIME ON;
SELECT DocNo
FROM #Docs
WHERE LStatus = 'QE'
EXCEPT
SELECT DocNo
FROM #Docs
WHERE LStatus <> 'QE'
;
SET STATISTICS TIME OFF;
--=============================================================================
-- Now, update the number of docs that meet the "QE only" spec by ten fold
-- and rerun the same code.
--=============================================================================
PRINT REPLICATE('=',80);
PRINT 'DocNo''s evenly divisible by 10 meet "QE only" spec for following tests.';
PRINT REPLICATE('=',80);
UPDATE #Docs
SET LStatus = 'QE'
WHERE DocNo % 10 = 0
;
--=============================================================================
-- ReTest the two methods given for solving this problem and a 3rd method.
--=============================================================================
PRINT '========== The "slower" way to use EXCEPT ==========';
SET STATISTICS TIME ON;
WITH QEExist AS ( -- Lists all doc nos that have a QE code
SELECT DISTINCT DocNo
FROM #Docs
WHERE LStatus = 'QE'
)
, OthersExist AS ( -- Lists all doc nos that have any non-QE codes
SELECT DISTINCT DocNo
FROM #Docs
WHERE LStatus <> 'QE'
)
-- Gets all doc numbers that are in the first set but not in the second
SELECT DocNo FROM QEExist EXCEPT
SELECT DocNo FROM OthersExist
;
SET STATISTICS TIME OFF;
-------------------------------------------------------------------------------
PRINT '========== The "old" way ==========';
SET STATISTICS TIME ON;
SELECT DocNo
FROM #Docs QE
WHERE LStatus = 'QE'
AND NOT EXISTS (
SELECT * FROM #Docs NQE
WHERE NQE.DocNo = QE.DocNo
AND NQE.LStatus <> QE.LStatus
)
GROUP BY DocNo
;
SET STATISTICS TIME OFF;
-------------------------------------------------------------------------------
PRINT '========== A different way to use EXCEPT ==========';
SET STATISTICS TIME ON;
SELECT DocNo
FROM #Docs
WHERE LStatus = 'QE'
EXCEPT
SELECT DocNo
FROM #Docs
WHERE LStatus <> 'QE'
;
SET STATISTICS TIME OFF;
Here are the results on my 8 year old desktop...
================================================================================
DocNo's evenly divisible by 100 meet "QE only" spec for following tests.
================================================================================
(1000000 row(s) affected)
========== The "slower" way to use EXCEPT ==========
(1996 row(s) affected)
SQL Server Execution Times:
CPU time = 2063 ms, elapsed time = 2567 ms.
========== The "old" way ==========
(1996 row(s) affected)
SQL Server Execution Times:
CPU time = 1390 ms, elapsed time = 1696 ms.
========== A different way to use EXCEPT ==========
(1996 row(s) affected)
SQL Server Execution Times:
CPU time = 1547 ms, elapsed time = 1872 ms.
================================================================================
DocNo's evenly divisible by 10 meet "QE only" spec for following tests.
================================================================================
(100066 row(s) affected)
========== The "slower" way to use EXCEPT ==========
(19877 row(s) affected)
SQL Server Execution Times:
CPU time = 2110 ms, elapsed time = 2712 ms.
========== The "old" way ==========
(19877 row(s) affected)
SQL Server Execution Times:
CPU time = 2375 ms, elapsed time = 2845 ms.
========== A different way to use EXCEPT ==========
(19877 row(s) affected)
SQL Server Execution Times:
CPU time = 1453 ms, elapsed time = 1960 ms.
Of course, that's not the end of the story... if you have the need for speed and add just the right index, you get results that look like the following on my box... the right index can sometimes make even "bad" code run quicker...
================================================================================
DocNo's evenly divisible by 100 meet "QE only" spec for following tests.
================================================================================
(1000000 row(s) affected)
========== The "slower" way to use EXCEPT ==========
(2003 row(s) affected)
SQL Server Execution Times:
CPU time = 344 ms, elapsed time = 466 ms.
========== The "old" way ==========
(2003 row(s) affected)
SQL Server Execution Times:
CPU time = 406 ms, elapsed time = 584 ms.
========== A different way to use EXCEPT ==========
(2003 row(s) affected)
SQL Server Execution Times:
CPU time = 312 ms, elapsed time = 409 ms.
================================================================================
DocNo's evenly divisible by 10 meet "QE only" spec for following tests.
================================================================================
(99956 row(s) affected)
========== The "slower" way to use EXCEPT ==========
(19867 row(s) affected)
SQL Server Execution Times:
CPU time = 687 ms, elapsed time = 1019 ms.
========== The "old" way ==========
(19867 row(s) affected)
SQL Server Execution Times:
CPU time = 1547 ms, elapsed time = 1853 ms.
========== A different way to use EXCEPT ==========
(19867 row(s) affected)
SQL Server Execution Times:
CPU time = 782 ms, elapsed time = 943 ms.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 29, 2010 at 1:07 am
thanks guys! that should do it!
Funny how you can sometimes get stuck in a specific way of thinking, especially when the correct answer can be that simple 😉
November 29, 2010 at 2:12 pm
Hi Jeff,
No argument about adjusting indexes to queries - it really works.
Don't have time to analize the data patterns, but from the first glance - "A different way to use EXCEPT" does not have DISTINCT or GROUP BY, so it possibly returns different result sets.
😉
_____________
Code for TallyGenerator
November 29, 2010 at 4:26 pm
Hi Sergiy,
Glad to "see" you still come around once in a while. 🙂
I understand the basic misgiving there but EXCEPT does only return "distinct" values much like a GROUP BY would. From BOL...
EXCEPT returns any distinct values from the left query that are not also found on the right query.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 2, 2010 at 5:19 pm
Hi Jeff,
Will have look at your samples.
As soon as I've got my computer back from the workshop.
🙂
_____________
Code for TallyGenerator
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply