0

I've documents with several line. For each line on this code there are some products. Those product has each one a category. So they can be mixed inside the same documents, so for example, in the document 4 I've 10 lines, 4 can be for example "fruit" category and the others 6 are for example "ice cream" category. So When I extract this document I retrieve a result where I've have 

--------------------------------
CUSTOMER_NAME  --- DOC NUMBER ------ CATEGORY

CUSTOMER_1    ----   10       ------- FRUIT

CUSTOMER_1    ----   10 ------- ICE CREAM 

----------

My need is to retrieve only the row with the most used category, so in this case, "Ice Cream"

This is my code 

DECLARE @dataa NVARCHAR(MAX) ;
DECLARE @datada NVARCHAR(MAX) ;

SET @datada = DATEADD(DAY, -1, GETDATE());
SET @dataa = DATEADD(DAY, -60, GETDATE());

SELECT 
    DSCCONTO1, TABCATEGORIE.DESCRIZIONE, TESTEDOCUMENTI.NUMERODOC 
FROM  
    .dbo.TESTEDOCUMENTI
INNER JOIN 
    .dbo.ANAGRAFICACF ON CODCLIFOR = CODCONTO
INNER JOIN 
    .dbo.RIGHEDOCUMENTI ON PROGRESSIVO = IDTESTA
INNER JOIN 
    .dbo.ANAGRAFICAARTICOLI ON CODART = ANAGRAFICAARTICOLI.CODICE
INNER JOIN 
    .dbo.TABCATEGORIE ON CATEGORIA = TABCATEGORIE.CODICE 
INNER JOIN 
    .dbo.TABCATEGORIESTAT ON CODCATEGORIASTAT = TABCATEGORIESTAT.CODICE 
WHERE 
    .dbo.TESTEDOCUMENTI.DOCCHIUSO = '0' 
    AND .dbo.TESTEDOCUMENTI.BLOCCATO = '0' 
    AND DATADOC BETWEEN @dataa AND @datada  
    AND CODCLIFOR LIKE '%C%' 
    AND TESTEDOCUMENTI.TIPODOC = 'PCL'  
GROUP BY 
    DSCCONTO1, TABCATEGORIE.DESCRIZIONE, TESTEDOCUMENTI.NUMERODOC
BigBlack
  • 163
  • 2
  • 12
  • Look up `cross apply` it allows you to use table valued functions such as top 1 record per group so return the Top 1 doc#, category and count from your table order by the count) then cross apply this to your data set. Example: https://stackoverflow.com/questions/1139160/when-should-i-use-cross-apply-over-inner-join. Alternatively you could generate a set of data consisting of your doc#, category and count and using window functions assign a row number to it order by count descending then only return records with a row number of 1 and join back to your base set. – xQbert Jul 05 '19 at 17:43
  • The main purpose of a cross apply is to enable table functions with parameters to be executed once per row and then joined to the results – xQbert Jul 05 '19 at 17:50

0 Answers0