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

Question

Post reply

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

-OLDdogNEWtricks-

SSC Enthusiast

Points: 171
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:

DocumentID PackageNumber ServiceProvider DocumentCategory

51586 55035/005 MyServiceProvider POD

51587 55035/005 MyServiceProvider COLLECTION/DELIVERY INSTRUCTION

51589 55035/005 MyServiceProvider POD

51590 55035/005 MyServiceProvider COLLECTION/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: 11572 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: 171 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: 11572 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: 171 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.

Keyword DocumentID

222 50770

25885/SC2014-211 50866

25885/SC2014-211 50867

25885/SC2014-212 50867

25885/SC2014-212 50866

25885/SC2014-213 50866

25885/SC2014-213 50867

300227/001 52072

300228/001 52072

300229/001 52072

300230/001 52072

300231/001 52072

300263/001 52241

300821/001 52072

300822/001 52072

300824/001 52072

301787/P_000001 51256

301787/P_000002 51256

303022/001 50780

368 50770

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

Y.B. SSChampion Points: 11572 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