Query to do exception report based on 2 values in a specified field

Question

Query to do exception report based on 2 values in a specified field

-OLDdogNEWtricks-

SSC Enthusiast

Points: 176
More actions
April 14, 2015 at 5:01 am

#316638

Hi,
I need to run an exception report on the following data where [Field4] has a value of 'COLLECTION/DELIVERY INSTRUCTION' and 'POD' for each unique reference number contained in [Field6] to determine where I am missing a POD.
My Query to list all records that have either 'COLLECTION/DELIVERY INSTRUCTION' or 'POD' listed in [Field6]
SELECT [DocumentID]
,[Field6] AS PackageNumber
,[Field1] AS ServiceProvider
,[Field4] AS DocumentCategory
,[Field16] AS LoadedDate
,[Field24] AS EmailDate
,[EmailTo]
,[EmailCC]
FROM [MyDatabase].[dbo].[Documents] WHERE Field4 = 'POD' or Field4 = 'COLLECTION/DELIVERY INSTRUCTION'
Output:
DocumentIDPackageNumberServiceProviderDocumentCategory
5158655035/005MyServiceProviderPOD
5158755035/005MyServiceProviderCOLLECTION/DELIVERY INSTRUCTION
5158955035/005MyServiceProviderPOD
5159055035/005MyServiceProviderCOLLECTION/DELIVERY INSTRUCTION
Comments:
You will see that there could be duplicates in the system that I need to take into account. Bottom line: I need to find "Package Numbers" where they have a "COLLECTION/DELIVERY INSTRUCTION" as [DocumentCategory] but are missing a "POD".
Hope someone can point me in the right direction.
-OLDdogNEWtricks:hehe:

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Y.B. SSChampion Points: 11642 More actions · Answer 1

You need to realize that there are NO duplicates in your sample output. The different DocumentID's make the rows completely unique no matter how much other data appears to repeat itself. so that being said what is the significance of DocumentID? To do what you want is relatively simple so long as you know which 'COLLECTION' belong with which POD.

SELECT quote FROM brain WHERE original = 1
0 rows returned

-OLDdogNEWtricks- SSC Enthusiast Points: 176 More actions · Answer 2

Hi, thanks for the reply.

The identifier linking the POD with the CDI (Collection/Delivery Instruction) is the Package Number (2nd Field). In the example the POD is present (twice) but I need to find records where the Package number relates to a CDI but not to a POD.

I hope I answered your question - it not, please let me know and I will provide further information / examples / code.

Tx.

Y.B. SSChampion Points: 11642 More actions · Answer 3

Ok so if you only care about locating Package Numbers that have a CDI but does not have a POD this should work.

SELECT

Field6 AS PackageNumber,

MAX(CASE WHEN Field4 = 'COLLECTION/DELIVERY INSTRUCTION' THEN 1 ELSE 0 END) AS HasCDI,

MAX(CASE WHEN Field4 = 'POD' THEN 1 ELSE 0 END) AS HasPOD

INTO #TEMP

FROM

Documents

GROUP BY

Field6

SELECT PackageNumber FROM #TEMP WHERE HasCDI = 1 AND HasPOD = 0

SELECT quote FROM brain WHERE original = 1
0 rows returned

-OLDdogNEWtricks- SSC Enthusiast Points: 176 More actions · Answer 4

Thank you for that...it works fine for a single execution. If you try and run it again it reports that the object '#Temp' already exists in the database.

I did however find another workaround - I created 2 views that contain all the CDI records in the one and all the PODs in the other. I then joined the 2 views in a statement to produce the output desired.

SELECT MyDatabase.dbo.DocType_Keyword_CDI.Keyword, DocumentID

FROM MyDatabase.dbo.DocType_Keyword_CDI LEFT OUTER JOIN

MyDatabase.dbo.DocType_Keyword_POD ON MyDatabase.dbo.DocType_Keyword_CDI.Keyword = MyDatabase.dbo.DocType_Keyword_POD.Keyword

WHERE (MyDatabase.dbo.DocType_Keyword_POD.Field4 IS NULL)

The results are also then displayed with the keywords in separate lines and not delimited in one cell.

KeywordDocumentID

22250770

25885/SC2014-21150866

25885/SC2014-21150867

25885/SC2014-21250867

25885/SC2014-21250866

25885/SC2014-21350866

25885/SC2014-21350867

300227/00152072

300228/00152072

300229/00152072

300230/00152072

300231/00152072

300263/00152241

300821/00152072

300822/00152072

300824/00152072

301787/P_00000151256

301787/P_00000251256

303022/00150780

36850770

Thanks for the input, hopefully this post is helpful to others also.

Y.B. SSChampion Points: 11642 More actions · Answer 5

-OLDdogNEWtricks- (4/14/2015)
Thank you for that...it works fine for a single execution. If you try and run it again it reports that the object '#Temp' already exists in the database.

I just did that for simplicity but I should have added DROP statement at the bottom. Look up how to use a WITH statement which could have been used instead.

The results are also then displayed with the keywords in separate lines and not delimited in one cell.

That was just a working table to get your unique values of PackageID's that met your criteria. Once you have that you can use it to select/join the data any way you want.

SELECT quote FROM brain WHERE original = 1
0 rows returned