LEFT JOIN Using distinct?

Question

Originally, I have 15 employees on my table. Two of them have the same LastName.

If I leave the E.MAT_EMP selection as it is, it works fine and 15 employees will get selected.

If I remove it though (because I don't want it to be shown) except for the row count, Firstname, Lastname and month days. It returns to me 14 rows instead of 15. After checking, It's apparently one of the rows where I have the same LastName/FirstName.

@StartDate date,
@EndDate date

as
begin

Declare @D1 date = @StartDate
Declare @D2 date = @EndDate

Declare @Cols varchar(max) = (Select Stuff((Select ',[' +cast(N as varchar(25))+']' From (Select Top (DateDiff(DAY,@D1,@D2)+1) N=Row_Number() Over (Order By (Select Null)) From  master..spt_values n1) A For XML Path ('')),1,1,'') )
Declare @SQL varchar(max) = '
Declare @D1 date = '''+cast(@D1 as varchar(50))+'''
Declare @D2 date = '''+cast(@D2 as varchar(50))+'''

Select ROW_NUMBER() OVER (ORDER BY LastName ASC) as [N°],*
From  ( 
        Select E.MAT_EMP, NOM_EMP as [LastName],PRENOM_EMP as [FirstName],
              Item      = day(d)
              ,Value     = 
              case when (D between DEBUT_DRC and FIN_DRC) and STATUS_DRC = ''Accepté'' then ''RC'' 
              when (D between DEBUT_DAB and FIN_DAB) and STATUS_DAB = ''Accepté'' then ''ABS'' 
              when (D between DC_DEBUT and DC_FIN) and STATUS_DC = ''Accepté'' then DCon.CODE_TYPE_CONGE
              else '''' 

              end
         From 
         DEMANDE_RECUPERATION DC RIGHT JOIN EMPLOYE E 
         ON DC.MAT_EMP = E.MAT_EMP 

         LEFT JOIN DEMANDE_ABSENCE ABS
         ON E.MAT_EMP = ABS.MAT_EMP 

         LEFT JOIN DEMANDE_CONGE DCon 
         ON E.MAT_EMP = DCon.MAT_EMP 

         Cross Join (
                        Select Top (DateDiff(DAY,@D1,@D2)+1) D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),@D1) From  master..spt_values n1
                    ) B
       ) src
 Pivot (max(value) for Item in ('+@Cols+') ) pvt
'
Exec(@SQL)

The output is as follows:

My goal is to simply remove that column while keeping the 15 rows.

user_4598989 · Answer

Replace the * with the column names you want to show, and don't remove it from the subquery.

    Select ROW_NUMBER() OVER (ORDER BY LastName ASC) as [N°], [LastName], [FirstName] /*other needed columns here*/
From  ( 
        Select E.MAT_EMP, NOM_EMP as [LastName],PRENOM_EMP as [FirstName],
              Item      = day(d)
              ,Value     = 
              case when (D between DEBUT_DRC and FIN_DRC) and STATUS_DRC = ''Accepté'' then ''RC'' 
              when (D between DEBUT_DAB and FIN_DAB) and STATUS_DAB = ''Accepté'' then ''ABS'' 
              when (D between DC_DEBUT and DC_FIN) and STATUS_DC = ''Accepté'' then DCon.CODE_TYPE_CONGE
              else '''' 

              end
         From 
         DEMANDE_RECUPERATION DC RIGHT JOIN EMPLOYE E 
         ON DC.MAT_EMP = E.MAT_EMP 

         LEFT JOIN DEMANDE_ABSENCE ABS
         ON E.MAT_EMP = ABS.MAT_EMP 

         LEFT JOIN DEMANDE_CONGE DCon 
         ON E.MAT_EMP = DCon.MAT_EMP 

         Cross Join (
                        Select Top (DateDiff(DAY,@D1,@D2)+1) D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),@D1) From  master..spt_values n1
                    ) B
       ) src
 Pivot (max(value) for Item in ('+@Cols+') ) pvt

user_10413901 · Answer

At the final query on SELECT statement specify columns' name that you want to show: [N°], [LastName], [FirstName] because the * indicates that you want to show all the columns that you have select previously.

user_4608204 · Answer

Check these 2 pivot examples to see how PIVOT implicitly groups by all non-aggregate columns:

IF OBJECT_ID('tempdb..#EmployeeData') IS NOT NULL
    DROP TABLE #EmployeeData

CREATE TABLE #EmployeeData (
    EmployeeID INT,
    EmployeeName VARCHAR(100),
    Type CHAR(1),
    Number INT)

INSERT INTO #EmployeeData (
    EmployeeID,
    EmployeeName,
    Type,
    Number)
VALUES
    (1, 'Mark', 'A', 10),
    (1, 'Mark', 'A', 10),
    (1, 'Mark', 'B', 15),
    (1, 'Mark', 'C', 5),
    (2, 'Leonard', 'A', 10),
    (2, 'Leonard', 'C', 45),
    (3, 'Mary', 'B', 10),
    (3, 'Mary', 'C', 15),
    (3, 'Mary', 'C', 25),
    (3, 'Mary', 'C', 5),
    (4, 'Mary', 'A', 25), -- Diferent ID!
    (4, 'Mary', 'A', 15),
    (4, 'Mary', 'C', 20),
    (4, 'Mary', 'C', 35)

First pivot:

-- Pivot Groups by EmployeeID + EmployeeName (all non-aggregate columns)
SELECT
    P.*
FROM
    #EmployeeData AS S
    PIVOT (
        SUM(S.Number) FOR S.Type IN ([A], [B], [C])
    ) AS P

Result:

EmployeeID  EmployeeName    A       B       C
2           Leonard         10      NULL    45
1           Mark            20      15      5
3           Mary            NULL    10      45
4           Mary            40      NULL    55

Second pivot:

-- Pivot Groups by EmployeeName (only)
;WITH NoEmployeeName AS
(
    SELECT
        E.EmployeeName,
        E.Type,
        E.Number
    FROM
        #EmployeeData AS E
)
SELECT
    P.*
FROM
    NoEmployeeName AS S
    PIVOT (
        SUM(S.Number) FOR S.Type IN ([A], [B], [C])
    ) AS P

Result:

EmployeeName    A   B       C
Leonard         10  NULL    45
Mark            20  15      5
Mary            40  10      100

When pivoting, the values of the rows that you are pivoting will be converted to the column names. You can reference these column names on your SELECT (you can avoid using *):

SELECT
    P.EmployeeID,
    P.EmployeeName,

    P.A, -- Pivot values as column names
    P.B,
    P.C
FROM
    #EmployeeData AS S
    PIVOT (
        SUM(S.Number) FOR S.Type IN ([A], [B], [C])
    ) AS P

What you need to do is to pivot using your E.MAT_EMP (has to be on the subquery) so your employee data doesn't get grouped by, and then don't list it on the SELECT.

Declare @D1 date = CONVERT(DATE, GETDATE() - 5)
Declare @D2 date = CONVERT(DATE, GETDATE())

Declare @Cols varchar(max) = (Select Stuff((Select ',[' +cast(N as varchar(25))+']' From (Select Top (DateDiff(DAY,@D1,@D2)+1) N=Row_Number() Over (Order By (Select Null)) From  master..spt_values n1) A For XML Path ('')),1,1,'') )

Declare @SQL varchar(max) = '
Declare @D1 date = '''+cast(@D1 as varchar(50))+'''
Declare @D2 date = '''+cast(@D2 as varchar(50))+'''

Select 
    ROW_NUMBER() OVER (ORDER BY LastName ASC) as [N°],

    pvt.LastName, -- Grouping columns of pivot operator (without MAT_EMP)
    pvt.FirstName,

    -- New columns result of pivot:
    ' + @Cols + ' 

From  ( 
    Select 
        E.MAT_EMP, 
        NOM_EMP as [LastName],
        PRENOM_EMP as [FirstName],
        Item = day(d),
        Value = case 
            when (D between DEBUT_DRC and FIN_DRC) and STATUS_DRC = ''Accepté'' then ''RC'' 
            when (D between DEBUT_DAB and FIN_DAB) and STATUS_DAB = ''Accepté'' then ''ABS'' 
            when (D between DC_DEBUT and DC_FIN) and STATUS_DC = ''Accepté'' then DCon.CODE_TYPE_CONGE
            else '''' end
    From 
        DEMANDE_RECUPERATION DC 
        RIGHT JOIN EMPLOYE E ON DC.MAT_EMP = E.MAT_EMP 
        LEFT JOIN DEMANDE_ABSENCE ABS ON E.MAT_EMP = ABS.MAT_EMP 
        LEFT JOIN DEMANDE_CONGE DCon ON E.MAT_EMP = DCon.MAT_EMP 
        Cross Join (
            Select Top (DateDiff(DAY,@D1,@D2)+1) 
                D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),@D1) 
            From  
                master..spt_values n1) B
       ) src
    Pivot (
        max(value) for Item in ('+@Cols+') 
    ) pvt
'

PRINT(@SQL)

-- EXEC (@SQL)

If printed, this is the result (for some example date values I hard-coded):

Declare @D1 date = '2018-12-16'
Declare @D2 date = '2018-12-21'

Select 
    ROW_NUMBER() OVER (ORDER BY LastName ASC) as [N°],

    pvt.LastName, -- Grouping columns of pivot operator (without MAT_EMP)
    pvt.FirstName,

    -- New columns result of pivot:
    [1],[2],[3],[4],[5],[6] 

From  ( 
    Select 
        E.MAT_EMP, 
        NOM_EMP as [LastName],
        PRENOM_EMP as [FirstName],
        Item = day(d),
        Value = case 
            when (D between DEBUT_DRC and FIN_DRC) and STATUS_DRC = 'Accepté' then 'RC' 
            when (D between DEBUT_DAB and FIN_DAB) and STATUS_DAB = 'Accepté' then 'ABS' 
            when (D between DC_DEBUT and DC_FIN) and STATUS_DC = 'Accepté' then DCon.CODE_TYPE_CONGE
            else '' end
    From 
        DEMANDE_RECUPERATION DC 
        RIGHT JOIN EMPLOYE E ON DC.MAT_EMP = E.MAT_EMP 
        LEFT JOIN DEMANDE_ABSENCE ABS ON E.MAT_EMP = ABS.MAT_EMP 
        LEFT JOIN DEMANDE_CONGE DCon ON E.MAT_EMP = DCon.MAT_EMP 
        Cross Join (
            Select Top (DateDiff(DAY,@D1,@D2)+1) 
                D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),@D1) 
            From  
                master..spt_values n1) B
       ) src
    Pivot (
        max(value) for Item in ([1],[2],[3],[4],[5],[6]) 
    ) pvt

LEFT JOIN Using distinct?

3 Answers