Self Join subquery approach

Question

I have a attendance list table, where I need a list identifying who was present:

Table structure:

create table lista_presenca_usuario
(
   id int identity(1,1),
   usuarioId int,
   dia int,
   turno int,
   presente int
)

insert into lista_presenca_usuario 
(usuarioId, dia, turno, presente)
values
 ( 10,1, 1, 1)
,( 10,1, 2, 0)
,( 10,2, 1, 1)
,( 10,2, 2, 1)
,( 20,1, 1, 1)
,( 20,1, 2, 1)
,( 20,2, 1, 1)
,( 20,2, 2, 1)

Approach Nº 1:

I make a time table, and update the lines:

select 
  usuarioId
  ,cast('' as varchar(20)) as primeiro_dia_manha
  ,cast('' as varchar(20)) as primeiro_dia_tarde
  ,cast('' as varchar(20)) as segundo_dia_manha
  ,cast('' as varchar(20)) as segundo_dia_tarde
 into #tmpDadosExport3
 from lista_presenca_usuario
 group by usuarioId

update t
  set t.primeiro_dia_manha = case when lpu.presente = 0 then 'ausente' else 'presente' end
 from #tmpDadosExport3 t
 join lista_presenca_usuario lpu(nolock) on t.usuarioId = lpu.usuarioId
 and lpu.turno = 1 and lpu.Dia = 1 

update t
  set t.primeiro_dia_tarde = case when lpu.presente = 0 then 'ausente' else 'presente' end
 from #tmpDadosExport3 t
 join lista_presenca_usuario lpu(nolock) on t.usuarioId = lpu.usuarioId
 and lpu.turno = 2 and lpu.Dia = 1 


update t
  set t.segundo_dia_manha = case when lpu.presente = 0 then 'ausente' else 'presente' end
 from #tmpDadosExport3 t
 join lista_presenca_usuario lpu(nolock) on t.usuarioId = lpu.usuarioId
 and lpu.turno = 1 and lpu.Dia = 2

update t
  set t.segundo_dia_tarde = case when lpu.presente = 0 then 'ausente' else 'presente' end
 from #tmpDadosExport3 t
join lista_presenca_usuario lpu(nolock) on t.usuarioId = lpu.usuarioId
 and lpu.turno = 2 and lpu.Dia = 2

It works, but it’s expensive, I am trying now this query

SELECT usuarioid, x.primeiro_dia_manha, x.primeiro_dia_tarde FROM lista_presenca_usuario y
inner join (
  select
   id
   ,case when turno = 1 and Dia = 1 then
                    case when presente = 0 then 'ausente' else 'presente' end
                    end AS primeiro_dia_manha
  ,case when turno = 2 and Dia = 1 then
                    case when presente = 0 then 'ausente' else 'presente' end
                    end AS primeiro_dia_tarde
  from lista_presenca_usuario
)x on x.id = y.id
group by usuarioid, x.primeiro_dia_manha, x.primeiro_dia_tarde

but unfortunately, it returns duplicated lines, even though I do a group by.

Expected result:

usuarioId|primeiro_dia_manha|primeiro_dia_tarde
10       |presente          |ausente           
20       |presente          |presente          

The result that is coming:

usuarioId|primeiro_dia_manha|primeiro_dia_tarde
10       |presente          |Null           
20       |Null              |presente          
10       |presente          |Null          
20       |Null              |presente             

I made a sql fiddle to illustrate: http://sqlfiddle.com/#! 18/fdb69/7

Answer 1

The first step is to sort the columns by grouping by usuarioId, dia and turno using a CASE to define the description that will be used in the columns later. Then use the PIVOT to turn descriptions into columns. The result of these steps is the following:

SELECT y.usuarioId,
       y.primeiro_dia_manha,
       y.primeiro_dia_tarde,
       y.segundo_dia_manha,
       y.segundo_dia_tarde
  FROM (SELECT lpu.usuarioId,
               CASE
                 WHEN lpu.dia = 1 AND lpu.turno = 1 THEN 'primeiro_dia_manha'
                 WHEN lpu.dia = 1 AND lpu.turno = 2 THEN 'primeiro_dia_tarde'
                 WHEN lpu.dia = 2 AND lpu.turno = 1 THEN 'segundo_dia_manha'
                 WHEN lpu.dia = 2 AND lpu.turno = 2 THEN 'segundo_dia_tarde'
               END AS classificacao,
               CASE MAX(presente) WHEN 1 THEN 'presente' ELSE 'ausente' END AS presenca
          FROM lista_presenca_usuario lpu
         GROUP BY lpu.usuarioId,
                  lpu.dia,
                  lpu.turno
  ) x
 PIVOT(MAX(x.presenca) FOR x.classificacao IN (primeiro_dia_manha,
                                               primeiro_dia_tarde,
                                               segundo_dia_manha,
                                               segundo_dia_tarde)) y;

---

Another way is to group by usuarioId and use a CASE to check the content. With the result of this check can be classified in presente and ausente:

SELECT lpu.usuarioId,
       CASE MAX(CASE WHEN lpu.dia = 1 AND lpu.turno = 1 THEN lpu.presente END) WHEN 1 THEN 'presente' ELSE 'ausente' END AS 'primeiro_dia_manha',
       CASE MAX(CASE WHEN lpu.dia = 1 AND lpu.turno = 2 THEN lpu.presente END) WHEN 1 THEN 'presente' ELSE 'ausente' END AS 'primeiro_dia_tarde',
       CASE MAX(CASE WHEN lpu.dia = 2 AND lpu.turno = 1 THEN lpu.presente END) WHEN 1 THEN 'presente' ELSE 'ausente' END AS 'segundo_dia_manha',
       CASE MAX(CASE WHEN lpu.dia = 2 AND lpu.turno = 2 THEN lpu.presente END) WHEN 1 THEN 'presente' ELSE 'ausente' END AS 'segundo_dia_tarde'
  FROM lista_presenca_usuario lpu
 GROUP BY lpu.usuarioId;

---

Anyway I believe that a column by day/shift composition is something that, using a large period, will be difficult to maintain and little adaptable. Depending on your need it may be ideal to review the solution.

---

Using PIVOT and UNPIVOT

You can use relational operators PIVOT and UNPIVOT to change an expression with table value in another table. PIVOT rotates a table value expression by transforming the unique values of a column into multiple columns in the output, and executing aggregations where they are needed at any remaining column values that are desired in the final output. UNPIVOT performs the opposite operation to PIVOT, rotating columns of an expression with table value in column values.