Medias raras las condiciones jejejeje
Código SQL:
Ver originalCREATE TABLE #rut
(
rut VARCHAR(20),
dia INT,
cont INT
)
INSERT INTO #rut VALUES('10000136',1,3)
INSERT INTO #rut VALUES('10000136',5,1)
INSERT INTO #rut VALUES('10000136',6,1)
INSERT INTO #rut VALUES('10004692',17,1)
INSERT INTO #rut VALUES('10007610',4,2)
INSERT INTO #rut VALUES('10007610',9,1)
INSERT INTO #rut VALUES('10007610',11,1)
INSERT INTO #rut VALUES('10007610',14,1)
INSERT INTO #rut VALUES('10009703',1,1)
INSERT INTO #rut VALUES('10012309',12,1)
INSERT INTO #rut VALUES('10012309',30,1)
INSERT INTO #rut VALUES('10017575',4,1)
INSERT INTO #rut VALUES('10017575',10,1)
INSERT INTO #rut VALUES('10017575',22,1)
INSERT INTO #rut VALUES('10011111',4,5)
INSERT INTO #rut VALUES('10011111',10,20)
INSERT INTO #rut VALUES('10011111',12,1)
CREATE TABLE #resultados
(
rut VARCHAR(20),
dia INT,
cont INT
)
SELECT DISTINCT(rut), IDENTITY(INT,1,1) rn INTO #temp FROM #rut
DECLARE @x INT
DECLARE @rut VARCHAR(20)
DECLARE @mayor INT
DECLARE @nuevo INT
DECLARE @y INT
DECLARE @dia INT
DECLARE @tot INT
SET @x=1
SET @y=1
SET @mayor=0
SET @nuevo=0
while @x<=(SELECT COUNT(*) FROM #temp)
BEGIN
SET @nuevo=0
SET @mayor=0
SET @y=1
SET @tot=0
SELECT @rut=rut FROM #temp WHERE rn=@x
SELECT *,IDENTITY(INT,1,1) rn INTO #temp2 FROM #rut WHERE rut=@rut
-- select * from #rut where rut='10007610'
while @y<=(SELECT COUNT(*) FROM #temp2)
BEGIN
IF @y=1
SELECT @nuevo=cont FROM #temp2 WHERE rn=@y
SELECT @mayor=cont FROM #temp2 WHERE rn=@y
IF @nuevo<@mayor
SET @nuevo=@mayor
IF @nuevo=@mayor
SET @tot=@tot+1
SET @y=@y+1
END
IF @tot=(SELECT COUNT(*) FROM #temp2)
SELECT @dia=MIN(dia) FROM #temp2
ELSE
SELECT @dia=dia FROM #temp2 WHERE cont=@nuevo
print @tot
print @rut
print @nuevo
print @dia
INSERT INTO #resultados
SELECT * FROM #rut WHERE rut=@rut AND cont=@nuevo AND dia=@dia
DROP TABLE #temp2
SET @x=@x+1
END
SELECT * FROM #resultados
-- delete from #resultados
DROP TABLE #resultados
DROP TABLE #temp
DROP TABLE #rut
--------------------------
saludos!