I have 2 tables:
Table "TAG" lists all the TAG numbers used to identify items of an order. In the displayed code underneath TAG is Table "Y"
TAG ----- IT 00123 IT 00124 ...
Table "Files" lists all certificates names that belong to these items. The file names are typed in manually by the worker and therefore often carry typos or are uploaded multiple times. The filenames are composed by serial number + TAG numbers. In the displayed code underneath FILES is Table "Z"
Files ------ X1234/IT00123 X54321/IT 00124 ...
Goal: Build a query that:
- finds all TAGs that are not mapped by any of the files
- finds all files with multiple mapping to the same TAG number
- find those files that are not mapped to any TAG number
I do this:
;with Score as ( Select WS_TAGNUMBER, z.NAME from ( Select WS_TAGNUMBER from WS_TagNoSalesLine Where WS_SALESLINEREFSALESID = @Salesid)y full outer join ( Select NAME from ( Select b.name FROM SALESLINE a inner join DOCUREF b on a.RECID = b.REFRECID AND b.ACTUALCOMPANYID = 'ES75' and b.REFTABLEID = '359' inner join DOCUVALUE c on b.VALUERECID = c.RECID inner join DOCUPARAMETERS d on d.DATAAREAID = 'ES75' WHERE a.SALESID = @SALESID and a.DATAAREAID = 'ES75' and c.FILETYPE = 'DOC')x)z on REPLACE(z.NAME,' ','')+'XYZ' like '%' + REPLACE(y.WS_TAGNUMBER,@TAGsInvariable,'') +'XYZ'+'%') select *, ROW_NUMBER() OVER (ORDER BY Cnt desc, WS_TAGNUMBER) AS ord from ( select WS_TAGNUMBER, COUNT(WS_TAGNUMBER) as Cnt, stuff((select ', ' + NAME from Score a WHERE a.WS_TAGNUMBER = Score.WS_TAGNUMBER ORDER BY NAME for xml path('')),1,1,'') AS FileNames from Score GROUP BY WS_TAGNUMBER)X
Here comes the question:
This does Query the job in 12 seconds for 1250 TAG numbers; the problem is that any orphaned FileName is not displayed, as it does not correspond to a TAG number and therefore looses its content if I group by TAGNUMBER.
Therefore I substitute WS_TAGNUMBER in the second line by COALESCE(WS_TAGNUMBER,''), but then all of the sudden the query takes more then 2 hours to execute.
Anyone has a bright idea, or a different approach?