CODEWITHSUNDEEP
sql-server
Istaley
Istaley

Reputation: 399

Selecting values which are not returned in a SQL select statement

I am using SQL Server and my task is to compile a list of stores for which we did not receive all 3 files from (file1, File2, File3). In other words each store needs to send in those files. If a store does not send in a file(s), I need to be able to identify that store and the particular file(s) that are missing.

For example,

StoreName|StoreID|NumberReceived|Missing Files
StoreABC   1234    2             File1
SuperStore  4321   1             File1, File2

This is what I have so far. I am stuck on trying to identify the missing files and putting them in a signle column seperated by a comma

SELECT 
ds.StoreName,
StoreID,
COUNT(DISTINCT FileType) as 'NumberReceived'
from ETL.CustomerFileExtractLog cfe
    inner join Final.DimStore ds
        on cfe.StoreID = ds.NABPNumber
where Customer ='ComputerRx'
and TimeStamp >= DATEADD(DAY, -1, GETDATE())
Group by StoreName,
StoreID
having COUNT(DISTINCT FileType) < 3  
order by StoreName

Upvotes: 0

Views: 162

Answers (1)

mohan111
mohan111

Reputation: 8865

Using STUFF and XML PATH() we can get comma seperated just modify according to your requirement it is assumed code 

;WITH CTE AS (
    SELECT 
        ds.StoreName,
        StoreID,
         FileType 
        FROM fILETYPE t
        INNER JOIN ETL.CustomerFileExtractLog cfe
            inner join Final.DimStore ds
                on cfe.StoreID = ds.NABPNumber
        where Customer ='ComputerRx'
        and TimeStamp >= DATEADD(DAY, -1, GETDATE())
        Group by StoreName,
        StoreID
        having COUNT(DISTINCT FileType) < 3  )

Select 
    StoreName,
    StoreID,
    COUNT(DISTINCT FileType) as 'NumberReceived',
    STUFF((SELECT ', ' + CAST(Files AS VARCHAR(50)) FileNAmes
FROM CTE  
WHERE StoreID = C.StoreID
FOR XML PATH(''), TYPE)
.value('.','NVARCHAR(MAX)'),1,2,' ')
FROM CTE C

Upvotes: 1

Related Questions