select * from tbl_log_facturacion_le order by id desc

---
--Generar numero aleatorio
@Ret = FLOOR(RAND(cast(NEWID() AS varbinary))*((@pMax)-1)+1)

CREATE TABLE #temp_sp_who2
    (
      
	  SPID INT,
      Status VARCHAR(1000) NULL,
      Login SYSNAME NULL,
      HostName SYSNAME NULL,
      BlkBy SYSNAME NULL,
      DBName SYSNAME NULL,
      Command VARCHAR(1000) NULL,
      CPUTime INT NULL,
      DiskIO BIGINT NULL, -- int
      LastBatch VARCHAR(1000) NULL,
      ProgramName VARCHAR(1000) NULL,
      SPID2 INT
      , rEQUESTID INT NULL --comment out for SQL 2000 databases
	  )

	  INSERT  INTO #temp_sp_who2
		EXEC sp_who2

---
--Validar inventario LE

SELECT 
	A.Cantidad --[Real]
	,tb.lngCantidadReal , tb.strcodigolinea--Trans 
FROM
	(
		SELECT SP.Sku, COUNT(SP.Sku) Cantidad
		FROM [SRVMPDB01].bd.dbo.tbl_pedcab pc 
			LEFT JOIN [SRVMPDB01].bd.dbo.tbl_FacCab fc ON fc.strTipDoc = 'FC' AND fc.strCodTer = pc.strCodTer AND fc.strCamGen = pc.strCamGen
			INNER JOIN [192.168.10.14].[MP_StockSupply].Picking.PedidosEncabezados PE ON PE.NumeroPedido = pc.strnumdoc 
			INNER JOIN [192.168.10.14].[MP_StockSupply].Picking.PedidosCuerpos PCC ON PCC.PedidoEncabezadoId = PE.PedidoEncabezadoId
			INNER JOIN [192.168.10.14].[MP_StockSupply].Picking.PedidosSkuPickeados SP ON SP.PedidoCuerpoId = PCC.PedidoCuerpoId
		WHERE pc.strCamGen = '201912' AND pc.strTipDoc = 'PD' AND fc.Id IS NULL AND PE.Campana = '201912'
		GROUP BY SP.Sku
	) AS a
	INNER JOIN [SRVMPDB01].bd.dbo.tblubicaciones tb ON tb.strcodigolinea = a.Sku
	WHERE strUbicacion = 'TRANS' and A.Cantidad != tb.lngCantidadReal

SELECT
  sys.columns.name AS ColumnName,
  tables.name AS TableName
FROM
  sys.columns
JOIN sys.tables ON
  sys.columns.object_id = tables.object_id
WHERE
  sys.columns.name LIKE '%Marital%'

---
--Detalles de pickeo de un pedido en LE
select a.sku, b.atributo, d.strDescripcion, a.skupickeado,  a.operario, a.fechapickeo
from [192.168.10.14].[MP_StockSupply].Picking.PedidosSkuPickeados a
inner join [192.168.10.14].[MP_StockSupply].Picking.PedidosCuerpos b on a.PedidoCuerpoId = b.PedidoCuerpoId
inner join tblSku c on c.strCodLin = a.sku
inner join tblproductos d on c.strReferencia = d.strReferencia
where 
	b.PedidoEncabezadoId = 188667
Order by b.atributo, a.sku

---
--Detalles de un pedido en LE
select a.sku, a.Atributo, d.strDescripcion, a.cantidad, a.cantidadpickeada, a.operario  
from [192.168.10.14].[MP_StockSupply].Picking.PedidosCuerpos a 
inner join tblSku c on c.strCodLin = a.sku
inner join tblproductos d on c.strReferencia = d.strReferencia
where PedidoEncabezadoId = 188667 
order by a.Atributo, a.Sku

---
--Datos de un pedido desde documentos
select b.strCodLin, b.strAtributo, d.strDescripcion, b.lngCan, b.lngCanAsg  from tblMov a
inner join tblDetMov b on a.strTipDoc = b.strTipDoc and a.strNumDoc = b.strNumDoc
inner join tblSku c on c.strCodLin = b.strCodLin
inner join tblproductos d on c.strReferencia = d.strReferencia
where a.strCodTer = '01039465559' and a.strTipDoc = 'PD' and a.strCamGen = '201910' 
order by strAtributo, b.strCodLin

/**
SELECT TOP 20
    qs.sql_handle,
    qs.execution_count,
    qs.total_worker_time AS Total_CPU,
    total_CPU_inSeconds = --Converted from microseconds
        qs.total_worker_time/1000000,
    average_CPU_inSeconds = --Converted from microseconds
        (qs.total_worker_time/1000000) / qs.execution_count,
    qs.total_elapsed_time,
    total_elapsed_time_inSeconds = --Converted from microseconds
        qs.total_elapsed_time/1000000,
    st.text,
    qp.query_plan
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY
    sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY 
    qs.total_worker_time DESC
**/

SELECT cast(s.context_info as varchar(128)) as ci, s.session_id,
    r.status,
    r.blocking_session_id 'Blk by',
    r.wait_type,
    wait_resource,
    r.wait_time / (1000 * 60) 'Wait M',
    r.cpu_time,
    r.logical_reads,
    r.reads,
    r.writes,
    r.total_elapsed_time / (1000 * 60) 'Elaps M',
    Substring(st.TEXT,(r.statement_start_offset / 2) + 1,
    ((CASE r.statement_end_offset
WHEN -1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1) AS statement_text,
    Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' +
    Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text,
    r.command,
    s.login_name,
    s.host_name,
    s.program_name,
    s.last_request_end_time,
    s.login_time,
    r.open_transaction_count
FROM sys.dm_exec_sessions AS s
    JOIN sys.dm_exec_requests AS r
ON r.session_id = s.session_id
    CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time desc

/* Kill ending-blocked session
--https://axatoz.wordpress.com/2014/04/10/how-to-kill-a-blocking-session/
KILL 309
*/

-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130); 
DECLARE @indexname nvarchar(130); 
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000); 
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;
    END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO