clonReferencia.sql

/*
if (select count(distinct fechaentrega) from CompraD where id=47024288) >1
	begin
	   print 'mas de dos'
	end
	else
		print 'nel' 

select distinct FechaEntrega from CompraD where id=47024685
update compraD set FechaEntrega='19/04/2017' where id=47024685 and renglon=2048

if (select distinct FechaEntrega, * from compraD where id=47024288) > 1
	print 'webos'
select 


--update compraD set FechaEntrega='29/03/2017' where id=47024288 and renglon=4096


SELECT FechaEntrega, count(*)
FROM compraD where id=47024685
GROUP BY FechaEntrega
HAVING count(*) > 1
*/


--select count(distinct FORMAT(FechaEntrega, 'd', 'af') ) from CompraD where id=47024142
--select top 2(distinct FORMAT(FechaEntrega, 'd', 'af'))  from CompraD where id=47024288   obtener los dos valores
--Create Table #Pizarron1


Select Referencia, Saldo,ID,MovID ,Estatus,CONVERT(VARCHAR(10), FechaRequerida, 105) as FechaRequerida,
       		CONVERT(VARCHAR(10),FechaEntrega,105) as FechaEntrega , Importe, FormaEntrega ,
       		 DATEPART(MONTH,FechaRequerida) AS Mes,
       		 DATEPART(WEEK,  FechaEntrega) as SemanaDelYear,
              DATEPART(WEEK,  FechaRequerida +49) as SemanaMasSiete,
       	   (Select Comentario  From anexomov where id=Compra.id and rama='COMS' And Tipo='Comentario' and Nombre='BACK ORDER') BACKORDER,
       		(Select Comentario From anexomov where id=Compra.id and rama='COMS' And Tipo='Comentario' and Nombre='FECHA ORIGINAL') FECHAORIGINAL,
       		(Select Comentario From anexomov where id=Compra.id and rama='COMS' And Tipo='Comentario' and Nombre='PENDIENTES') PENDIENTES,
       		(Select Comentario From anexomov where id=Compra.id and rama='COMS' And Tipo='Comentario' and Nombre='ESPECIAL') ESPECIAL,
       		(Select count(id) from CompraD where id=Compra.id) PIEZAS,
            (Select Agente from Prov where proveedor=Compra.Proveedor) Agente,
			(select count(distinct FORMAT(FechaEntrega, 'd', 'af') ) from CompraD where id=Compra.id) as DifFechasEntrega,
			(CASE 
			when ((select count(distinct FORMAT(FechaEntrega, 'd', 'af') )  from compraD where id=Compra.id) = 1) then 'solo un registo'
			 when ((select count(distinct FORMAT(FechaEntrega, 'd', 'af')) from compraD where id=Compra.id) > 1) then 'mas de dos'
			end) as MASDEUno
       	   from Compra
               where Mov in('Pedido', 'Pedido Excedente')   and Estatus='PENDIENTE'   and FechaEntrega > '01-01-2017' ORDER BY  SemanaDelYear ASC ,Agente, Referencia