//SqlConnection cn = new SqlConnection("Data Source=118.247.29.138\\SQLEXPRESS; Initial Catalog=EES.BBVA.2018v1; User ID = SA; Password=everis@2017;"); private void ListarData() { try { string query = "SELECT wf.[Name] as 'WorkFlow', (SELECT [Name] FROM [EES.BBVA.2018v1].[dbo].[States] WHERE [Id] = (SELECT [StateId] FROM [EES.BBVA.2018v1].[dbo].[StateActions] WHERE [Id] = (SELECT [StateActionId] FROM [EES.BBVA.2018v1].[dbo].[TicketHistories] WHERE [Id] = (SELECT MAX(th.[Id]) FROM [EES.BBVA.2018v1].[dbo].[TicketHistories] th WHERE th.[TicketId] = tk.[Id] AND th.[HistoryType] = 2)))) as 'Ultimo Estado','Tarea: ' as 'Ticket',tk.[Id],'Exped: ' as 'Exp',tk.[Description],st.[Name],tk.[StateId],tk.[ParentId],tk.[UserId],tk.[RobotVirtualMachineId]" + ",case when((tk.StateId = 1061) or(tk.StateId = 1135)) then concat(datediff(SECOND, dateadd(hour, -5, tk.[CreationDate]), dateadd(hour, -5, tk.[LastModified])) / 60, ':', datediff(SECOND, dateadd(hour, -5, tk.[LastModified]), dateadd(hour, -5, tk.[LastModified])) % 60) Else concat(datediff(SECOND, dateadd(hour, -5, tk.[CreationDate]), getdate()) / 60, ':', datediff(SECOND, dateadd(hour, -5, tk.[CreationDate]), getdate()) % 60) End as 'Creación' " + ",concat(datediff(SECOND, dateadd(hour, -5, tk.[LastModified]), getdate()) / 60, ':', datediff(SECOND, dateadd(hour, -5, tk.[LastModified]), getdate()) % 60) as 'Ejecución'" + ",tv.[Value] as 'Error',convert(varchar(20),dateadd(hour,-5, tk.[CreationDate]), 100) as 'CreationDate',convert(varchar(20),dateadd(hour,-5, tk.[LastModified]), 100) as 'LastModified',convert(varchar(10),tk.[CreationDate],103) as 'FechaCreacion',tk.[Priority] FROM [EES.BBVA.2018v1].[dbo].[Tickets] tk INNER JOIN [EES.BBVA.2018v1].[dbo].[States] st on st.[Id] = tk.[StateId] INNER JOIN [EES.BBVA.2018v1].[dbo].[Workflows] wf on wf.[Id] = st.[WorkflowId] LEFT JOIN [EES.BBVA.2018v1].[dbo].[TicketValues] tv on (tv.[TicketId] = tk.[Id] AND tv.[FieldId] = 34) " + "WHERE wf.[Id] in (8,9) " + "ORDER BY tk.id desc "; p = new Paginar(query, "DataMember1", maximo_x_pagina); dgvResultados.DataSource = p.cargar(); dgvResultados.DataMember = "datamember1"; actualizar(); /* * SqlCommand cmd = new SqlCommand("SELECT wf.[Name] as 'WorkFlow', (SELECT [Name] FROM [EES.BBVA.2018v1].[dbo].[States] WHERE [Id] = (SELECT [StateId] FROM [EES.BBVA.2018v1].[dbo].[StateActions] WHERE [Id] = (SELECT [StateActionId] FROM [EES.BBVA.2018v1].[dbo].[TicketHistories] WHERE [Id] = (SELECT MAX(th.[Id]) FROM [EES.BBVA.2018v1].[dbo].[TicketHistories] th WHERE th.[TicketId] = tk.[Id] AND th.[HistoryType] = 2)))) as 'Ultimo Estado','Tarea: ' as 'Ticket',tk.[Id],'Exped: ' as 'Exp',tk.[Description],st.[Name],tk.[StateId],tk.[ParentId],tk.[UserId],tk.[RobotVirtualMachineId]" + * ",case when((tk.StateId = 1061) or(tk.StateId = 1135)) then concat(datediff(SECOND, dateadd(hour, -5, tk.[CreationDate]), dateadd(hour, -5, tk.[LastModified])) / 60, ':', datediff(SECOND, dateadd(hour, -5, tk.[LastModified]), dateadd(hour, -5, tk.[LastModified])) % 60) Else concat(datediff(SECOND, dateadd(hour, -5, tk.[CreationDate]), getdate()) / 60, ':', datediff(SECOND, dateadd(hour, -5, tk.[CreationDate]), getdate()) % 60) End as 'Creación' " + * ",concat(datediff(SECOND, dateadd(hour, -5, tk.[LastModified]), getdate()) / 60, ':', datediff(SECOND, dateadd(hour, -5, tk.[LastModified]), getdate()) % 60) as 'Ejecución'" + * ",tv.[Value] as 'Error',convert(varchar(20),dateadd(hour,-5, tk.[CreationDate]), 100) as 'CreationDate',convert(varchar(20),dateadd(hour,-5, tk.[LastModified]), 100) as 'LastModified',tk.[Priority] FROM [EES.BBVA.2018v1].[dbo].[Tickets] tk INNER JOIN [EES.BBVA.2018v1].[dbo].[States] st on st.[Id] = tk.[StateId] INNER JOIN [EES.BBVA.2018v1].[dbo].[Workflows] wf on wf.[Id] = st.[WorkflowId] LEFT JOIN [EES.BBVA.2018v1].[dbo].[TicketValues] tv on (tv.[TicketId] = tk.[Id] AND tv.[FieldId] = 34) " + * "WHERE wf.[Id] in (8,9) AND CAST(tk.[CreationDate] AS DATE) = CAST('2018-08-20' AS DATE) ORDER BY tk.id desc ", cn); * SqlDataAdapter da = new SqlDataAdapter(cmd); * DataTable dt = new DataTable(); * da.Fill(0,5,dt); * dgvResultados.DataSource = dt; * cargarGrid(); * cn.Close(); */ } catch (Exception ex) { MessageBox.Show(ex.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Warning); } }