Report three nested tables

Question

Report three nested tables

Navigation

#1 by (0 votes)
#2 by (0 votes)

2

I have the following nested tables, in SQL Server:

## Orden_Trabajo ##
ID   |   TITULO   |   FECHA_INICIO   |   FECHA_TERMINO    |   EQUIPO
---
1   |   FALLA MOTOR   |   1/12/17 8:00   |   2/12/17 12:00   |   100

## Tareas ##
ID   |   TAREA   |   FECHA_INICIO   |   FECHA_TERMINO
---
1   |   DESMONTAR MOTOR   |   1/12/17 8:00   |   1/12/17 12:00
2   |   REPARAR MOTOR   |   1/12/17 12:00   |   2/12/17 8:00
3   |   MONTAR MOTOR   |   2/12/17 8:00  |   2/12/17 12:00

## Tareas_Mecanicos ##
ID   |   TAREA   |   MECANICO
---
1   |   1   |   ABEL
2   |   1   |   BERNARDO
3   |   2   |   ABEL
4   |   2   |   CARLOS
5   |   2   |   DANIEL
6   |   2   |   EDUARDO
7   |   3   |   CARLOS
8   |   3   |   DANIEL

These tables have their Object Class in the system, I need to create the following report:

MANTENEDOR   |   TAREAS   |   SUMA DURACION
---
ABEL   |   1,2   |   24
BERNARDO   |   1   |   4
CARLOS   |   2,3   |   24
DANIEL   |   2,3   |   24
EDUARDO   |   2   |   20

How can I get a list without repetitions from the Maintainers and capture the tasks they participated in at the same time?

With a SELECT in the database, or with ordered lists in system (C #)

I made a Class for each table, and for the report create an ordered List: Sort Order Job:

public class OrdenTrabajo{
   public int IdOT;
   public string Titulo;
   public Datetime FechaInicio;
   public Datetime FechaTermino;
   public Equipo equipo;
}

Task Class:

public class Tarea{
   public int IdTarea;
   public string tarea;
   public Datetime FechaInicio;
   public Datetime FechaTermino;
   public List<Mecanico> ListaMecanicos;

   public double GetDuracion(){
      TimeSpan ts = FechaTermino - FechaInicio;
      return ts.TotalHours;
   }
}

Mechanical Class:

public class Mecanico{
   public int IdMecanico;
   public string NombreMecanico;
}

Turn Class:

public class Turno {
    public Mecanico _Mecanico;
    public List<Tarea> _ListaTareas;

    public AgregarTarea(Tarea tarea){
      _ListaTareas.Add(tarea);
    }
}

I try to make the report using a shift list and a list of mechanics as a pivot:

List<Turno> listaTurno = new List<Turno>();
List<Mecanico> listaMecanicos = new List<Mecanico>();
List<Tarea> tareas = GetListaTareasRangoFechas(fechaInicio,fechaTermino);
            for (int i = 0; i < tareas.Count; i++) {
                for (int j = 0; j < tareas[i].ListaMecanicos.Count; j++) {
                    if (!listaMantenedores.Contains(tareas[i].ListaMantenedores[j])) {
                        listaMecanicos.Add(tareas[i].ListaMecanicos[j]);
                        Turno turno = new Turno(tareas[i].ListaMecanicos[j]);
                        turno.AgregarTarea(tareas[i]);
                        listaTurno.Add(turno);
                    }else {
                        for (int k = 0; k < listaHHTurno.Count; k++) {
                            if(listaTurno[k]._Mecanico.IdMecanico == tareas[i].ListaMecanicos[j].IdMecanico) {
                                listaTurno[k].AgregarTarea(tareas[i]);
                            }
                        }

                    }
                }
            }

Then when printing the list the *** Mechanics *** are repeated and only one Task is assigned.

sql c# sql-server

asked by Luis Pizarro Ramírez 11.12.2017 в 12:43

source

2 answers

CardView Android studio and Spinner Problem with Performance with jquery and the $ .Get method

score 0 · Answer 1

First I would make a column to calculate the hours between FECHA_INICIO and FECHA_TERMINO .

Once you have it, you can get what you ask with something of the style:

SELECT MECANICO AS MANTENEDOR, TAREA AS TAREAS, SUM(NOMBRECOLUMNADURACION) AS SUMADURACION
FROM TAREAS_MECANICOS, TAREAS
WHERE TAREAS_MECANICOS.TAREA = TAREAS.ID
GROUP BY MANTENEDOR, TAREAS

score 0 · Answer 2

I do not know how to use C #, in sql you would get the same report as follows:

DROP TABLE IF EXISTS MANTENEDOR1
SELECT TAREA, MECANICO,'tarea' AS TAREAS,0 AS SUMA_DURACION ,ROW_NUMBER() OVER(PARTITION BY MECANICO ORDER BY TAREA) AS ORDEN_M,ROW_NUMBER() OVER(ORDER BY MECANICO)  AS ORDEN_G
INTO MANTENEDOR1 FROM TAREAS_MECANICOS
DECLARE @TAREAS VARCHAR(50)
DECLARE @DURACION DECIMAL(10,2)
DECLARE @COUNT1 INT
DECLARE @TCOUNT INT
    SET @TCOUNT=(SELECT COUNT(1) FROM MANTENEDOR1)
    SET @COUNT1=1
WHILE   @COUNT1<= @TCOUNT
BEGIN
    SET @TAREAS=(SELECT CASE WHEN ORDEN_M=1 THEN '' ELSE @TAREAS END FROM MANTENEDOR1 WHERE ORDEN_G=@COUNT1) 
    SET @DURACION=(SELECT CASE WHEN ORDEN_M=1 THEN 0 ELSE @DURACION END FROM MANTENEDOR1 WHERE ORDEN_G=@COUNT1)
    UPDATE MANTENEDOR1
    SET  TAREAS=a.TAREAS
    FROM (SELECT CONCAT(@TAREAS,a.TAREA) AS TAREAS  FROM MANTENEDOR1 a WHERE a.ORDEN_G=@COUNT1) a
    WHERE
    MANTENEDOR1.ORDEN_G=@COUNT1
    UPDATE MANTENEDOR1
    SET SUMA_DURACION=c.DURACION
    FROM (SELECT (datediff(hour,(a.Fecha_Inicio),(a.Fecha_Termino))+@DURACION) AS DURACION FROM TAREAS a INNER JOIN MANTENEDOR1 b ON a.ID=b.TAREA AND b.ORDEN_G=@COUNT1) c
    WHERE
    MANTENEDOR1.ORDEN_G=@COUNT1

    SET @TAREAS=(SELECT CONCAT(TAREA,', ') FROM MANTENEDOR1 WHERE ORDEN_G=@COUNT1)
    SET @DURACION=(SELECT datediff(hour,(a.Fecha_Inicio),(a.Fecha_Termino)) FROM TAREAS a INNER JOIN MANTENEDOR1 b ON a.ID=b.TAREA AND ORDEN_G=@COUNT1)

    SET @COUNT1=@COUNT1+1
END

SELECT a.MECANICO,a.TAREAS,a.SUMA_DURACION FROM  MANTENEDOR1 a,(
SELECT mecanico,max(orden_M) orden FROM MANTENEDOR1
group by MECANICO) b
where
a.MECANICO=b.MECANICO
and
a.ORDEN_M=b.orden
order by a.MECANICO

I generate a table from which I am going to extract the report with two counting indicators one helps me to reset the indicators and another to continue with the next line and update the table after I only ask for the maximums per mechanic.