Cantidad de mayores y menores de edad en SQL Server

En este post vamos a aprender a obtener la edad de los usuarios que tenemos registrado en nuestra base de datos y también determinar cuántos de ellos son mayores de edad y cuantos son menores de edad , para esto vamos utilizar la función DATEDIFF.

Esta función lo que hace es calcular el intervalo de tiempo entre dos fechas dependiendo del primer argumento que puede ser day, month, year el es un entero  que depende de la diferencia entre los dos argumentos Fecha2-Fecha1.

Para nuestro ejemplo vamos a tener una tabla con el nombre Usuario que tendrá estos datos. 

Id es autoincrementa, Nombre un varchar(150), Sexo char(1) y FechaNac es un datetime.

La query muestra el campo Nombre, Sexo y Edad, edad se obtiene de la diferencia entre el campo FechaNac y la fecha actual.

SELECT Nombre
      ,Sexo
      ,DATEDIFF(YEAR, FechaNac, GETDATE()) AS Edad
FROM Usuario

Ahora tenemos la edad de cada uno de los usuarios, con estos nuevos datos podemos determinar quien es mayor de edad y quien es menor de edad, para esto vamos a utilizar la función CASE que lo que hace es comparar dos o más valores y devolver un resultado, por cada valor hay un WHEN y THEN, si hay una coincidencia ejecuta el THEN por el contrario ejecuta el ELSE pero si no ponemos el ELSE devuelve NULL para finalizar el CASE ponemos END.

En la query vamos a mostrar el Nombre, Sexo y Edad a esto agregamos dos columnas una "Menor" y otra "Mayor" si la edad es mayor a 18 años entonces en la columna que corresponde vamos a poner un 1 de lo contrario un 0, para los menores de edad hacemos lo mismo.

SELECT Nombre
      ,Sexo
      ,DATEDIFF(YEAR, FechaNac, GETDATE())  AS Edad
      ,CASE WHEN DATEDIFF(YEAR, FechaNac, GETDATE()) < 18 
       THEN 1 ELSE 0 END AS Menor
      ,CASE WHEN DATEDIFF(YEAR, FechaNac, GETDATE()) >= 18 
       THEN 1 ELSE 0 END AS Mayor
FROM Usuario 

Y para finalizar vamos a utilizar la función SUM para sumar y obtener como resultado la cantidad de mayores y menores.

SELECT SUM(CASE WHEN DATEDIFF(YEAR, FechaNac, GETDATE()) < 18 
     THEN 1 ELSE 0 END) AS Menor
      ,SUM(CASE WHEN DATEDIFF(YEAR, FechaNac, GETDATE()) >= 18 
     THEN 1 ELSE 0 END) AS Mayor
FROM Usuario 

Resultado final

Link del video

Highcharts - Line

Highcharts es la librería que más utilizo para realizar gráficos en mis aplicaciones web, principalmente por la variedad de tipos de gráficos, compatibilidad con los navegadores, es gratuito, tiene muchos ejemplos  y sobre todo  por lo sencillo que es.

Esta escrita en JavaScrip lenguaje que todo programador hoy por hoy tiene que dominar.

En la página oficial de Highcharts hay mucha documentación es importante que le den un a mirada.

Ejemplo de Highcharts - Line

La idea principal es mostrar la cantidad de usuarios que se registraron en una fecha determinada por cada ciudad.

Para el ejemplo vamos a trabajar con Visual Studio 2013 y Sql Server en cualquier versión.

Antes de nada debemos tener listo

1. JQuery porque el framework (Highcharts) depende de él.
2. Descar highcharts.js de la página Highcharts ir a la opción Support -> Donwload hoy están en la versión 6.2.  
3. En ambos casos si no queremos descargar las librerias podemos usar el CDN 

<script src="https://code.jquery.com/jquery-1.9.1.min.js"></script>

<script src="https://code.highcharts.com/highcharts.src.js"></script>

Base de datos.

En Sql Server hay que crear una base de datos con el nombre Highcharts y dentro de ella nuestras tablas. 

Tabla Ciudad 

El campo Id es un entero autoincremental y la Descripcion es un varchar(150)

Tabla Usuarios

Id es un entero autoincremental, Nombre es un varchar(150), Fecha es Datetime y Ciudad_id es un foreign key de la tabla Ciudad.

Stored Procedure

La query obtiene los datos para ser visualizado en el gráfico.

CREATE PROCEDURE CantidadRegistros_PorCiudad
AS
BEGIN
 DECLARE @Fecha_Reg TABLE ( Fecha DATETIME )

 INSERT INTO @Fecha_Reg
     SELECT DISTINCT(CONVERT(VARCHAR(10), Fecha, 103))
     FROM Usuarios 

 SELECT CONVERT(VARCHAR(10), U.Fecha, 103) AS 'Fecha'
       ,'Santa Cruz' = SUM(CASE WHEN U.Ciudad_id = 3 THEN 1
                                  ELSE 0 END)
       ,'LaPaz' = SUM(CASE WHEN U.Ciudad_id = 2 THEN 1
                                  ELSE 0 END)
       ,'Cochabamba' = SUM(CASE WHEN U.Ciudad_id = 1 THEN 1
                                  ELSE 0 END)
 FROM Usuarios U 
 WHERE CONVERT(VARCHAR(10), U.Fecha, 103) IN (SELECT Fecha
                                              FROM @Fecha_Reg )
 GROUP BY CONVERT(VARCHAR(10), U.Fecha, 103)
END

Creación del proyecto.

Una vez creado nuestro proyecto vamos al Web.config y ubicamos <connectionStrings /> aquí debemos configurar nuestra cadena de conexión.

    <connectionStrings>
      <add name="MiConnectionString" connectionString="Server=localhost; Database=Highcharts; Trusted_Connection=true"/>
    </connectionStrings>

Para el acceso a los datos vamos a crear una clase que se llame Ado. 

Esta clase genérica la utilizo cada vez que voy acceder a la base de datos. El método Retrieve devuelve un DataTable y recibe como parámetro el nombre del stored a ejecutar.

    public class Ado
    {
        string con = ConfigurationManager.ConnectionStrings["MiConnectionString"].ToString();
        SqlConnection conexion;

        public Ado(){
        }

        public DataTable Retrieve(string stored)
        {
            DataTable dt = new DataTable();

            try
            {
                SqlCommand com = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();
                Conectar();
                com.Connection = conexion;
                com.CommandType = CommandType.StoredProcedure; 
                com.CommandText = stored;                  
                adapter.SelectCommand = com;
                adapter.Fill(dt);
                return dt;
            }
            catch (SqlException ex)
            {
                SqlError err = ex.Errors[0];
                string mensaje = string.Empty;

                return dt = null;
            }
            finally
            {
                Desconcestar();
            }
        }   

        private void Conectar()
        {
            conexion = new SqlConnection(con);
            conexion.Open();
        }

        private void Desconcestar()
        {
            conexion.Close();
            conexion = null;
        }
    }

La clase datos va tener un solo método getDatos y le vamos a decir que ejecute el stored que tenemos arriba CantidadRegistros_PorCiudad.

    public class Datos
    {
        Ado ado;
     
        public Datos()
        {
            ado = new Ado();
        }

        public DataTable getDatos()
        {
            return ado.Retrieve("CantidadRegistros_PorCiudad");
        }
    }

Creo que hasta aquí todo va bien, ya tenemos casi todo listo para empezar a pensar en crear otras clases que nos permitan cumplir con nuestro objetivo.

Debemos crear otra clase con el nombre SeriesItem, el primer atributo es un string que almacenará el nombre de la Ciudad y el segundo atributo es un vector de enteros, más adelante veremos lo importante de este atributo.

    public class SeriesItem
    {
        public string name { get; set; }
        public int[] data { get; set; }       
    }

También vamos a crear la clase LineaCharts al igual que la anterior clase tiene dos atributos el primero es una lista de SeriesItem y el segundo es un vector de string, en este vector almacenaremos las fechas de registro de los usuarios, si le dan una mirada al stored las fechas son únicas porque en una misma fecha se pueden registrar más de un usuario.

    public class LineaCharts
    {
        public List<SeriesItem> series { get; set; }
        public string[] fechas { get; set; }
        public LineaCharts(List<SeriesItem> iSeries, string[] iFechas)
        {
            series = iSeries;
            fechas = iFechas;
        }
    }

Por último vamos a crear un item de tipo Web Form al que pondremos de nombre GraficarLineaChart. 

En el CodeBehind escribimos este código, no olvidar la linea using System.Web.Services; de lo contrario no va salir error en el [WebMethod].

Mediante ajax se hace una llamada asíncrona al método CantidadRegistros_PorCiudad el cual devuelve un objeto de tipo LineaCharts.

El método getDato devuelve los datos obtenidos de la base de datos, almacenamos en un DateTable. 

    public partial class GraficarLineaChart : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        [WebMethod]
        public static LineaCharts CantidadRegistros_PorCiudad()
        {
            DataTable dt = new Datos().getDatos();

            if (dt != null && dt.Rows.Count > 0)
            {
                List<SeriesItem> series = new List<SeriesItem>();

                string[] fechas = new string[dt.Rows.Count];
                int[] sc = new int[dt.Rows.Count];
                int[] lp = new int[dt.Rows.Count];
                int[] cb = new int[dt.Rows.Count];

                int i = 0;

                foreach (DataRow dr in dt.Rows)
                {
                    fechas[i] = dr[0].ToString();

                    sc[i] = Convert.ToInt32(dr[1].ToString());
                    lp[i] = Convert.ToInt32(dr[2].ToString());
                    cb[i] = Convert.ToInt32(dr[3].ToString());

                    i++;
                }

                series.Add(new SeriesItem() { name = "Santa Cruz", data = sc });
                series.Add(new SeriesItem() { name = "La Paz", data = lp });
                series.Add(new SeriesItem() { name = "Cochabamba", data = cb });

                return new LineaCharts(series,  fechas);
                
            }
            else
            {
                return null;
            }
        }
    }

Como vamos a hacer una llamada asíncrona mediante Ajax de JQuery esta tiene varias propiedades.

• type: tipo de llamada que por defecto es GET.
• url: ruta a la cual le vamos hacer el pedido.
• data: datos que se envían al servidor, se envía un string con formato JSON.
• contentType: tipo de datos que van a ser enviados al servidor.
• dataType: tipo de dato que se espera recibir desde el servidor JQuery los interpreta con los MIMES types que el soporta. Los valores válidos son (xml, json. html, text, script, jsop). 
• success: función que va ejecutar si fue exitoso.

Dentro del <head> ingresamos este código.

    <script src="Scripts/jquery-1.7.1.min.js"></script>
    <script src="Scripts/highcharts.js"></script>

    <script type="text/javascript">
        $(document).ready(function () {
            $("#btnCreatePieChart").click("click", function () {
                GraficarDatos();
            });
        });

        function GraficarDatos() {
            $.ajax({
                type: "POST",
                url: "GraficarLineaChart.aspx/CantidadRegistros_PorCiudad",
                dataType: "json",
                contentType: "application/json; charset=utf-8",
                data: {}
            })
            .done(function (data) {
                console.log(data.d);
                LineChart(data.d.series, data.d.fechas);
            });
        }

        function LineChart(Series, Fecha) {
            $('#contenedor').highcharts({
                chart: {
                    type: 'line'
                },
                title: {
                    text: ''
                },
                xAxis: {
                    categories: Fecha
                },
                yAxis: {
                    min: 0,
                    title: {
                        text: ''
                    }
                },
                plotOptions: {
                    line: {
                        dataLabels: {
                            enabled: true
                        },
                        enableMouseTracking: false
                    }
                },
                series: Series
            });
        }
    </script>

En nuestro caso vamos hacer un POST, se ejecutará en nuestra misma página (le pasamos el nombre de nuestra página y el método), el tipo de dato es JSON.

Dentro del body

     <button type="button" id="btnCreatePieChart" >Ver Gráfico</button>    
     <div id="contenedor" style="width: 900px; height: 550px">
     </div>

Y por último vamos a generar el gráfico desde un botón btnCreatePieChart, en el script se muestra que está esperando un click de este botón para ejecutar el método GraficarDatos

Resultado.

Dejo aquí el video 

Eliminar filas repetidas de una tabla Sql Server

En muchas ocaciones tenemos datos repetidos en nuestra tabla y es necesario eliminarlos. En este post vamos explicar cómo hacerlo, para esto tendremos la siguiente tabla a la que llamaremos Usuario.

Para nuestro ejemplo debemos tener estos registros, nótese que la columna Dni está remarcado con color rojo lo que indica que son los registros repetidos.

Como solo tenemos 13 registros pues nos resulta fácil determinar cuales son los repetidos, el problema es cuando tenemos muchos registros, entonces lo que tenemos que hacer es una query que nos muestre cuáles son los que se están repitiendo.

Algo rápido que podemos hacer es escribir una query que nos dé como resultado la cantidad de veces que están repetidos.

Como estamos haciendo uso de una función de agregado (COUNT) necesitamos agrupar por las columnas a mostrar, en este caso agrupamos por Nombre y Dni. Ahora ya sabemos la cantidad de veces que se repite un registro.

Necesitamos determinar cuáles son los registros que se repiten, para esto debemos hacer un Inner Join entre la misma tabla y devolver aquellos Id que están en ambas con la condición que el Id de la tabla Usuario A sea mayor al de la B.

SELECT A.Id
FROM Usuario A INNER JOIN Usuario B ON A.Nombre = B.Nombre
                                   AND A.Dni = B.Dni
                                   AND A.Id > B.Id

Ahora recién procedemos a eliminar los registros repetidos, ejecutamos esta query y verificamos los resultados.

DELETE Usuario 
WHERE Id IN (SELECT A.Id
      FROM Usuario A INNER JOIN Usuario B ON A.Nombre = B.Nombre
                                         AND A.Dni = B.Dni
                                         AND A.Id > B.Id)

Select *
FROM Usuario

Bueno y con esto terminamos.

Aquí dejo el enlace del video.