Help with Select query

Transact-SQL
1

Hey all,

I have this table i'm trying to query that contains customer paperwork requirements. Our invoice workflow automatically sends out invoices and other paperwork by e-mail based on the data from this table called BillDocTypes. The table consists of columns like CompanyID, docType , docRequired, docAttach.

Here's a small data sample to give you an idea:

CompanyID   |    docType     |    docRequired     |      docAttach
Company12           POD                     Y                Y
company12            BOL                    N                Y
company12            ACC                    N                Y
company12            INV                    N                Y
company34            POD                    Y                Y
company34            BOL                    N                Y
company34            ACC                    N                Y
company34            INV                    N                Y
company56            POD                    Y                Y
company56            BOL                    N                Y
company56            ACC                    N                Y
company 56           INV                    N                Y

I'm trying to select a list of companies that DON'T have the above details concerning the docRequired and docAttach values, but i need to check the docRequired and docAttach value for each docType.

I wrote this so far, but it's returning 1 row for each doctype as NULL, i want to only have 1 record per company ID:

select DISTINCT cmp_id, case when  bdt_doctype='POD'  then bdt_inv_attach  END as 'POD Attach',
case when bdt_doctype='ACC' then bdt_inv_attach  END as 'ACC Attach',
case when bdt_doctype='CNF' then bdt_inv_attach  END as 'CNF Attach',
case when bdt_doctype='LUMPER' then bdt_inv_attach  END as 'LUMPER Attach',
case when bdt_doctype='POD'  then bdt_inv_required  END as 'POD Required',
case when bdt_doctype='ACC' then bdt_inv_required  END as 'ACC Required',
case when bdt_doctype='CNF' then bdt_inv_required  END as 'CNF Required',
case when bdt_doctype='LUMPER' then bdt_inv_required  END as 'LUMPER Required'
from billdoctypes
order by cmp_id asc
2

declare @test table (CompanyID varchar(255),docType varchar(50),docRequired varchar(5),docAttach varchar(5))
insert into @test values
('Company12','POD','N','Y')
,('Company12','BOL','N','Y')
,('Company12','ACC','N','Y')
,('Company12','INV','N','Y')
,('Company34','POD','Y','Y')
,('Company34','BOL','N','Y')
,('Company34','ACC','N','Y')
,('Company34','INV','N','Y')
,('Company56','POD','Y','Y')
,('Company56','BOL','N','Y')
,('Company56','ACC','N','Y')
,('Company56','INV','N','Y')

--Your Original table
select * from @test

--YOUR FINAL TABLE
SELECT * FROM (
SELECT
COMPANYID
,DOCTYPE+'_'+STUFF(DOCTY,1,3,'') AS DOCTYPE
,OUTCOME
FROM (
SELECT * FROM @TEST UNPIVOT( OUTCOME FOR [DOCTY] IN (DOCREQUIRED, DOCATTACH))G
) F
)MAIN
PIVOT(MAX(OUTCOME) FOR DOCTYPE IN( [ACC_ATTACH],[ACC_REQUIRED],[BOL_ATTACH],[BOL_REQUIRED],[INV_ATTACH],[INV_REQUIRED],[POD_ATTACH],[POD_REQUIRED]))T

4

Thanks for the reply;
I guess i should mention that i'm working on a server with level 80 compatability, so i can't use PIVOT O_o

5 
SELECT	cmp_id, 
	MAX(CASE WHEN bdt_doctype='POD' THEN bdt_inv_attach ELSE '' END) as 'POD Attach',
	MAX(CASE WHEN bdt_doctype='ACC' THEN bdt_inv_attach ELSE '' END) as 'ACC Attach',
	MAX(CASE WHEN bdt_doctype='CNF' THEN bdt_inv_attach ELSE '' END) as 'CNF Attach',
	MAX(CASE WHEN bdt_doctype='LUMPER' THEN bdt_inv_attach ELSE '' END) as 'LUMPER Attach',
	MAX(CASE WHEN bdt_doctype='POD'  THEN bdt_inv_required ELSE '' END) as 'POD Required',
	MAX(CASE WHEN bdt_doctype='ACC' THEN bdt_inv_required ELSE '' END) as 'ACC Required',
	MAX(CASE WHEN bdt_doctype='CNF' THEN bdt_inv_required ELSE '' END) as 'CNF Required',
	MAX(CASE WHEN bdt_doctype='LUMPER' THEN bdt_inv_required ELSE '' END) as 'LUMPER Required'
FROM	billdoctypes
GROUP BY cmp_id
ORDER BY cmp_id ASC
1 Like
6

I like @Kristen's answer but here is what I might try
select DISTINCT cmp_id,
POD.bdt_inv_attach as 'POD Attach',
ACC.bdt_inv_attach as 'ACC Attach',
CNF.bdt_inv_attach as 'CNF Attach',
LUMPER.bdt_inv_attach as 'LUMPER Attach',
POD.bdt_inv_required as 'POD Required',
ACC.bdt_inv_required as 'ACC Required',
CNF.bdt_inv_required as 'CNF Required',
LUMPER.bdt_inv_required as 'LUMPER Required'
from billdoctypes Main
LEFT JOIN billdoctypes POD ON Main.cmp_id = POD.cmp_id AND POD.bdt_doctype = 'POD'
LEFT JOIN billdoctypes ACC ON Main.cmp_id = POD.cmp_id AND POD.bdt_doctype = 'ACC'
LEFT JOIN billdoctypes CNF ON Main.cmp_id = POD.cmp_id AND POD.bdt_doctype = 'CNF'
LEFT JOIN billdoctypes LUMPER ON Main.cmp_id = POD.cmp_id AND POD.bdt_doctype = 'LUMPER'
order by cmp_id asc

7

That's the only part of your solution that concerns me ... might be a lot of rows in [billdoctypes] - although no idea if the difference (in performance) would be significant.

If there could ever be multiple rows for a given [cmp_id] & [doctype] then using my solution the O/P will be hosed!

8

P.S. If [cmp_id] was got from a different (i.e. "parent") table, and thus didn't need the DISTINCT, then that would work well I reckon :slight_smile:

9

I'll try this, as i can take the company ID from the actual company Table and just join it. I'll go over the suggestions posted and will update shortly.

Thanks again everyone for the quick replies!

10

Oops. Copy past of original and not looking at what I had.:unamused:

11

That will show up any client that has NO entries in [billdoctypes]. That's probably a good thing :slight_smile: but the other route would only get you Company IDs that do have data in [billdoctypes].

12

This worked great! I forgot about using MAX for Case functions like that; That's again!