Etiqueta: listar

usar tablas temporales en sql dinamico

Caso: Se tiene una consulta SQL que se genera dinamicamente, se quiere volcar el conjunto de resultados en una tabla temporal. Se tuvo que realizar lo siguiente:


ALTER PROCEDURE [dbo].[SPS_GeneraRptPedidoDetalle]    
    @docFechaInicio [date],
    @docFechaFin [date],
    @codCliente [int],
    @sucursal [char](4),
    @ordenCompra [char](15),
    @fAprobInicio [date],
    @fAprobFin [date],
    @vendedor [int],
    @item [char](15),
    @nroDoc [char](14),
    @linea [char](6),
    @familia [char](6),
    @subfamilia [char](6)
AS
BEGIN

    DECLARE @cadenaTotal [nvarchar](max)
    DECLARE @cadenaSql [varchar](MAX)
    DECLARE @cadenaWhere [nvarchar](MAX)
    DECLARE @cadenaOrderBy [varchar](200)
    DECLARE @cadenaGroupBy [varchar](MAX)
    
    SET @cadenaTotal=N''
    SET @cadenaWhere=N''                 
    
    SET @cadenaSql='SELECT   
             t2.ClienteReferencia,
             t2.ClienteNumero,
             t2.ClienteNombre,
             t1.NumeroDocumento,
             t1.TipoDetalle,
             t1.Linea as LineaDoc,
             [t10].[DescripcionLocal] as Marca,
             t1.ItemCodigo,
             t1.Descripcion,
             t1.UnidadCodigo,
             t1.CantidadPedida,
             t1.Monto,
             t1.DocumentoRelacNumeroDocumento,
             t1.DocumentoRelacLinea,
             t1.ExportacionMarcaPaquete,             
             t2.MonedaDocumento,
             [t9].[DescripcionCorta] AS GlosaMoneda,
             t1.Estado,
             t2.Vendedor,
             [t8].[NombreCompleto] AS AsesorComercial,
             t1.IGVExoneradoFlag,
             t2.Criteria,
             t2.FechaDocumento,
             t2.FechaAprobacion,
             t1.UnidadCodigoDoble,
             t1.CantidadPedidaDoble,
             t2.Comentarios,
             t1.DocumentoRelacTipoDocumento,
             t1.TipoDocumento,
             t1.Lote,
             t1.CantidadEntregada,
             t1.PrecioUnitario,
             t1.AlmacenCodigo,
             t1.Condicion,
             t1.PorcentajeDescuento01,
             t1.PorcentajeDescuento02,
             t1.PorcentajeDescuento03,
             t1.TransferenciaGratuitaFlag,
             t1.PrecioUnitarioGratuito,
             t1.PrecioUnitarioOriginal,
             t1.MontoFinal,
             t1.PrecioUnitarioFinal,
             t2.NumeroInterno,             
             t1.NumeroSerie,
             t2.CentroCosto,
             t3.Linea as LineaItem,
             t3.Familia,
             t3.SubFamilia,
             t3.NumeroDeParte,
             t3.ItemProcedencia,
             t1.ExportacionFechaProgramacion,
             t1.exportacioncomentarios,
             t2.ClienteRUC,
             t2.RequisicionNumero,
             t4.StockActual AS StockActual,
             t4.StockActual - ISNULL(t4.StockComprometido, 0) AS StockDisponible,
             t4.StockComprometido AS StockComprometido,
             t5.StockProceso,
             t6.FechaPrometida,
             t7.ClasificacionRotacion,
             [t2].[ClienteReferencia] AS NroOC,
             (sum(t1.[CantidadPedida] - [t1].[CantidadEntregada])*t1.[PrecioUnitario]) AS Valorizado,
             (sum(t1.[CantidadPedida] - [t1].[CantidadEntregada])*t1.[PrecioUnitario]*(CASE t2.[MonedaDocumento] WHEN ' + char(39) +  'LO' + char(39) + ' THEN (1/[t2].[TipodeCambio]) ELSE 1 END )) AS ValorizadoCambio        
    INTO #tmpTemporal             
    FROM     spring.[dbo].CO_DocumentoDetalle t1
             INNER JOIN spring.[dbo].CO_Documento t2 ON (t2.CompaniaSocio = t1.CompaniaSocio)
                AND (t2.TipoDocumento = t1.TipoDocumento)
                AND (t2.NumeroDocumento = t1.NumeroDocumento)
             LEFT OUTER JOIN spring.[dbo].WH_ItemMast t3 ON t1.ItemCodigo = t3.Item
             LEFT OUTER JOIN spring.[dbo].VW_WH_ItemAlmacenStock t4 ON t1.ItemCodigo = t4.Item
                AND t1.AlmacenCodigo = t4.AlmacenCodigo
             LEFT OUTER JOIN spring.[dbo].VW_WH_ItemComprasEnProceso t5 ON t5.Item = t1.ItemCodigo
                AND t5.Companiasocio = t1.Companiasocio
             LEFT OUTER JOIN spring.[dbo].VW_WH_ItemComprasEnProcesoFecha t6 ON t6.Item = t1.ItemCodigo
                AND t6.Companiasocio = t1.Companiasocio
             LEFT OUTER JOIN spring.[dbo].WH_ItemAlmacen t7 ON t7.AlmacenCodigo = t2.AlmacenCodigo
                AND t7.Item = t1.ItemCodigo
                AND t7.Condicion = ' + char(39) + '0' + char(39) + '
             LEFT OUTER JOIN [Spring].[dbo].[PersonaMast] t8 ON t8.[Persona]=[t2].[Vendedor]
             LEFT OUTER JOIN [spring].[dbo].[MonedaMast] t9 ON t9.[MonedaCodigo]=[t2].[MonedaDocumento]
             LEFT OUTER JOIN [Spring].[dbo].[WH_Marcas] t10 ON t10.[MarcaCodigo]=[t3].[MarcaCodigo]
    WHERE    (t1.TipoDocumento = ' + char(39) + 'PE' + char(39) + ')
             AND (t1.Estado = ' + char(39) + 'PR' + char(39) + ')
             AND t1.CantidadPedida > t1.CantidadEntregada
             AND t2.Estado = ' + char(39) + 'AP' + char(39) + '
             AND (t2.CompaniaSocio = ' + char(39) + '01000000' +  char(39) + ')                          
             AND (t2.FormaFacturacion <> ' + char(39) + 'GF' + char(39) + ')
             AND (t2.TipoVenta <> ' + char(39) + 'SEP' + char(39) + ')' + 
             ' AND (CAST(t2.FechaDocumento AS date) BETWEEN @docFechaInicio AND @docFechaFin)'

    
                 
    IF @codCliente<> 0 BEGIN
        SET @cadenaWhere= @cadenaWhere + N' AND [t2].[ClienteNumero]=@codCliente'    
    END 
    
    IF @sucursal<>'0' BEGIN
        SET @cadenaWhere=@cadenaWhere + N' AND [t2].[Sucursal]=@sucursal'
    END 
    
    IF @ordenCompra<>'0' BEGIN
        SET @cadenaWhere=@cadenaWhere + N' AND [t2].[ClienteReferencia]=@ordenCompra'
    END 
    
    IF YEAR(@fAprobInicio)<>1900 AND YEAR(@fAprobFin)<>1900 BEGIN
        SET @cadenaWhere=@cadenaWhere + N' AND CAST(ISNULL([t2].[FechaAprobacion],' + char(39) + '19000101' + char(39) + ') AS date) BETWEEN @fAprobInicio AND @fAprobFin'
    END 

    IF @vendedor<>0 BEGIN
        SET @cadenaWhere=@cadenaWhere + N' AND [t2].[Vendedor]=@vendedor'
    END 
    
    IF @item<>'0' BEGIN
        SET @cadenaWhere=@cadenaWhere + N' AND [t1].[ItemCodigo]=@item'
    END 
    
    IF @nroDoc<>'0' BEGIN
        SET @cadenaWhere=@cadenaWhere + N' AND [t1].[NumeroDocumento]=@nroDoc'
    END 
    
    IF @linea<>'0' BEGIN
        SET @cadenaWhere=@cadenaWhere + N' AND [t3].[Linea]@linea'
    END 
    
    IF @familia<>'0' BEGIN
        SET @cadenaWhere=@cadenaWhere + N' AND [t3].[Familia]=@familia'
    END 
    
    IF @subfamilia<>'0' BEGIN
        SET @cadenaWhere=@cadenaWhere + N' AND [t3].[SubFamilia]=@subfamilia'
    END     
    
    SET @cadenaGroupBy=N' GROUP BY   
             t1.NumeroDocumento,
             t1.TipoDetalle,
             t1.Linea,
             [t10].[DescripcionLocal],
             t1.ItemCodigo,
             t1.Descripcion,
             t1.UnidadCodigo,
             t1.CantidadPedida,
             t1.Monto,
             t1.DocumentoRelacNumeroDocumento,
             t1.DocumentoRelacLinea,
             t1.ExportacionMarcaPaquete,
             t2.ClienteNumero,
             t2.ClienteNombre,
             t2.MonedaDocumento,
             [t9].[DescripcionCorta],
             t1.Estado,
             t2.Vendedor,
             [t8].[NombreCompleto],
             t1.IGVExoneradoFlag,
             t2.Criteria,
             t2.FechaDocumento,
             t2.FechaAprobacion,
             t1.UnidadCodigoDoble,
             t1.CantidadPedidaDoble,
             t2.Comentarios,
             t1.DocumentoRelacTipoDocumento,
             t1.TipoDocumento,
             t1.Lote,
             t1.CantidadEntregada,
             t1.PrecioUnitario,
             t1.AlmacenCodigo,
             t1.Condicion,
             t1.PorcentajeDescuento01,
             t1.PorcentajeDescuento02,
             t1.PorcentajeDescuento03,
             t1.TransferenciaGratuitaFlag,
             t1.PrecioUnitarioGratuito,
             t1.PrecioUnitarioOriginal,
             t1.MontoFinal,
             t1.PrecioUnitarioFinal,
             t2.NumeroInterno,
             t2.ClienteReferencia,
             t1.NumeroSerie,
             t2.CentroCosto,
             t3.Linea,
             t3.Familia,
             t3.SubFamilia,
             t3.NumeroDeParte,
             t3.ItemProcedencia,
             t1.ExportacionFechaProgramacion,
             t1.exportacioncomentarios,
             t2.ClienteRUC,
             t2.RequisicionNumero,
             t4.StockActual,
             t4.StockComprometido,
             t5.StockProceso,
             t6.FechaPrometida,
             t7.ClasificacionRotacion,
             [t2].[ClienteReferencia],
             t2.[TipodeCambio]'
    
    SET @cadenaOrderBy=' ORDER BY t2.FechaDocumento ASC
           , [t2].[ClienteNombre] ASC          
           , t1.NumeroDocumento ASC
           , t1.Linea ASC
           , t2.MonedaDocumento ASC;'

    DECLARE @sqlTemporal [varchar](500)
    
    SET @sqlTemporal='SELECT *, (SELECT SUM(t2.Monto) FROM #tmpTemporal t2 WHERE ISNULL(t2.ClienteReferencia,' + char(39) + char(39) + ')=ISNULL(t1.ClienteReferencia,' + char(39) + char(39) + ') AND t2.ClienteNumero=t1.ClienteNumero) AS MontoTotal
    FROM #tmpTemporal t1;
    
    DROP TABLE #tmpTemporal;'


    SET @cadenaTotal=@cadenaSql+ @cadenaWhere + @cadenaGroupBy + @cadenaOrderBy + @sqlTemporal
    
        
    EXEC sp_executesql @cadenaTotal, N'@docFechaInicio [date], @docFechaFin [date], @codCliente [int], @sucursal [char](4)
    , @ordenCompra [char](15), @fAprobInicio [date], @fAprobFin [date], @vendedor [int], @item [char](15), @nroDoc [char](14)
    , @linea [char](6), @familia [char](6), @subfamilia [char](6)'
    , @docFechaInicio, @docFechaFin, @codCliente, @sucursal
    , @ordenCompra, @fAprobInicio, @fAprobFin, @vendedor, @item, @nroDoc
    , @linea, @familia, @subfamilia


    

END 

GO

Eliminar duplicados List<> C#

Se tiene la clase item con atributos Name, Code y Price

Se debe de crear una nueva clase que permitirá el filtro de duplicados, por ejemplo

class DistinctItemComparer : IEqualityComparer<Item> {

    public bool Equals(Item x, Item y) {
        return x.Id == y.Id &&
            x.Name == y.Name &&
            x.Code == y.Code &&
            x.Price == y.Price;
    }

    public int GetHashCode(Item obj) {
        return obj.Id.GetHashCode() ^
            obj.Name.GetHashCode() ^
            obj.Code.GetHashCode() ^
            obj.Price.GetHashCode();
    }
}

La lista filtrada se invoca entonces de la siguiente manera

 

var distinctItems = items.Distinct(new DistinctItemComparer());

 

Listar jobs SQL Server

USE msdb
Go


SELECT dbo.sysjobs.Name AS 'Job Name', 
	'Job Enabled' = CASE dbo.sysjobs.Enabled
		WHEN 1 THEN 'Yes'
		WHEN 0 THEN 'No'
	END,
	'Frequency' = CASE dbo.sysschedules.freq_type
		WHEN 1 THEN 'Once'
		WHEN 4 THEN 'Daily'
		WHEN 8 THEN 'Weekly'
		WHEN 16 THEN 'Monthly'
		WHEN 32 THEN 'Monthly relative'
		WHEN 64 THEN 'When SQLServer Agent starts'
	END, 
	'Start Date' = CASE active_start_date
		WHEN 0 THEN null
		ELSE
		substring(convert(varchar(15),active_start_date),1,4) + '/' + 
		substring(convert(varchar(15),active_start_date),5,2) + '/' + 
		substring(convert(varchar(15),active_start_date),7,2)
	END,
	'Start Time' = CASE len(active_start_time)
		WHEN 1 THEN cast('00:00:0' + right(active_start_time,2) as char(8))
		WHEN 2 THEN cast('00:00:' + right(active_start_time,2) as char(8))
		WHEN 3 THEN cast('00:0' 
				+ Left(right(active_start_time,3),1)  
				+':' + right(active_start_time,2) as char (8))
		WHEN 4 THEN cast('00:' 
				+ Left(right(active_start_time,4),2)  
				+':' + right(active_start_time,2) as char (8))
		WHEN 5 THEN cast('0' 
				+ Left(right(active_start_time,5),1) 
				+':' + Left(right(active_start_time,4),2)  
				+':' + right(active_start_time,2) as char (8))
		WHEN 6 THEN cast(Left(right(active_start_time,6),2) 
				+':' + Left(right(active_start_time,4),2)  
				+':' + right(active_start_time,2) as char (8))
	END,
--	active_start_time as 'Start Time',
	CASE len(run_duration)
		WHEN 1 THEN cast('00:00:0'
				+ cast(run_duration as char) as char (8))
		WHEN 2 THEN cast('00:00:'
				+ cast(run_duration as char) as char (8))
		WHEN 3 THEN cast('00:0' 
				+ Left(right(run_duration,3),1)  
				+':' + right(run_duration,2) as char (8))
		WHEN 4 THEN cast('00:' 
				+ Left(right(run_duration,4),2)  
				+':' + right(run_duration,2) as char (8))
		WHEN 5 THEN cast('0' 
				+ Left(right(run_duration,5),1) 
				+':' + Left(right(run_duration,4),2)  
				+':' + right(run_duration,2) as char (8))
		WHEN 6 THEN cast(Left(right(run_duration,6),2) 
				+':' + Left(right(run_duration,4),2)  
				+':' + right(run_duration,2) as char (8))
	END as 'Max Duration',
    CASE(dbo.sysschedules.freq_subday_interval)
		WHEN 0 THEN 'Once'
		ELSE cast('Every ' 
				+ right(dbo.sysschedules.freq_subday_interval,2) 
				+ ' '
				+     CASE(dbo.sysschedules.freq_subday_type)
							WHEN 1 THEN 'Once'
							WHEN 4 THEN 'Minutes'
							WHEN 8 THEN 'Hours'
						END as char(16))
    END as 'Subday Frequency'
FROM dbo.sysjobs 
LEFT OUTER JOIN dbo.sysjobschedules 
ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id 
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
		FROM dbo.sysjobhistory
		GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time = 0

UNION

SELECT dbo.sysjobs.Name AS 'Job Name', 
	'Job Enabled' = CASE dbo.sysjobs.Enabled
		WHEN 1 THEN 'Yes'
		WHEN 0 THEN 'No'
	END,
	'Frequency' = CASE dbo.sysschedules.freq_type
		WHEN 1 THEN 'Once'
		WHEN 4 THEN 'Daily'
		WHEN 8 THEN 'Weekly'
		WHEN 16 THEN 'Monthly'
		WHEN 32 THEN 'Monthly relative'
		WHEN 64 THEN 'When SQLServer Agent starts'
	END, 
	'Start Date' = CASE next_run_date
		WHEN 0 THEN null
		ELSE
		substring(convert(varchar(15),next_run_date),1,4) + '/' + 
		substring(convert(varchar(15),next_run_date),5,2) + '/' + 
		substring(convert(varchar(15),next_run_date),7,2)
	END,
	'Start Time' = CASE len(next_run_time)
		WHEN 1 THEN cast('00:00:0' + right(next_run_time,2) as char(8))
		WHEN 2 THEN cast('00:00:' + right(next_run_time,2) as char(8))
		WHEN 3 THEN cast('00:0' 
				+ Left(right(next_run_time,3),1)  
				+':' + right(next_run_time,2) as char (8))
		WHEN 4 THEN cast('00:' 
				+ Left(right(next_run_time,4),2)  
				+':' + right(next_run_time,2) as char (8))
		WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1) 
				+':' + Left(right(next_run_time,4),2)  
				+':' + right(next_run_time,2) as char (8))
		WHEN 6 THEN cast(Left(right(next_run_time,6),2) 
				+':' + Left(right(next_run_time,4),2)  
				+':' + right(next_run_time,2) as char (8))
	END,
--	next_run_time as 'Start Time',
	CASE len(run_duration)
		WHEN 1 THEN cast('00:00:0'
				+ cast(run_duration as char) as char (8))
		WHEN 2 THEN cast('00:00:'
				+ cast(run_duration as char) as char (8))
		WHEN 3 THEN cast('00:0' 
				+ Left(right(run_duration,3),1)  
				+':' + right(run_duration,2) as char (8))
		WHEN 4 THEN cast('00:' 
				+ Left(right(run_duration,4),2)  
				+':' + right(run_duration,2) as char (8))
		WHEN 5 THEN cast('0' 
				+ Left(right(run_duration,5),1) 
				+':' + Left(right(run_duration,4),2)  
				+':' + right(run_duration,2) as char (8))
		WHEN 6 THEN cast(Left(right(run_duration,6),2) 
				+':' + Left(right(run_duration,4),2)  
				+':' + right(run_duration,2) as char (8))
	END as 'Max Duration',
    CASE(dbo.sysschedules.freq_subday_interval)
		WHEN 0 THEN 'Once'
		ELSE cast('Every ' 
				+ right(dbo.sysschedules.freq_subday_interval,2) 
				+ ' '
				+     CASE(dbo.sysschedules.freq_subday_type)
							WHEN 1 THEN 'Once'
							WHEN 4 THEN 'Minutes'
							WHEN 8 THEN 'Hours'
						END as char(16))
    END as 'Subday Frequency'
FROM dbo.sysjobs 
LEFT OUTER JOIN dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id 
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
		FROM dbo.sysjobhistory
		GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time <> 0

ORDER BY [Start Date],[Start Time]

LISTAR TRIGGERS

SELECT
sysobjects.name AS trigger_name
,USER_NAME(sysobjects.uid) AS trigger_owner
,s.name AS table_schema
,OBJECT_NAME(parent_obj) AS table_name
,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate
,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete
,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert
,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter
,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof
,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects

INNER JOIN sysusers
ON sysobjects.uid = sysusers.uid

INNER JOIN sys.tables t
ON sysobjects.parent_obj = t.object_id

INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id

WHERE sysobjects.type = 'TR'