SQL Server obteniendo autonumérico por grupo

Bueno pues, a lo que me refiero con el título de este post, creo que quedará mejor explicado si empiezo por describir el problema que me llevo a buscar esta solución.

Tengo una tabla con pagos, donde tengo un identificador que viene siendo el número de mi crédito, un número perteneciente al número de amortización al que se le esta realizando el pago, la fecha en que se realizo y un registro con el monto de cada pago realizado por mi cliente. Tomando esto en cuenta, cuando me piden la ultima fecha de pago, la penultima fecha de pago y la antepenultima fecha de pago, en teoría debería ser bastante sencillo, sin embargo, se complica.

La complicación viene de que cada amortización puede tener mas de un pago, no necesariamente el pago es cronológico (ya que puede haber pagos anticipados) y en la misma fecha pudieron pagarme mas de una amortización.

Con lo anterior tuve que descartar un MAX y tambien descarte la posibilidad de usar mi identificador de amortización, esto porque si usaba el MAX para ultimo, el MAX – 1 para penultimo y el MAX – 2 para antepenultimo, podía pasar que las tres fechas fueran igualitas dado al hecho de que las pagaron el mismo día.

Bueno pues, aqui va lo que hice,

  • De mi tabla obtuve el id del credito, fecha y monto pagado, insertandolos en una tabla temporal que llame #Pagos agrupando por el id del credito y la fecha de pago, de tal manera que tuviera un solo registro por fecha por crédito
  • Cree una tabla que llame #Temp, aqui lo que estoy haciendo en crear mis grupos en base al id_credito y crear un identificador unico para sus elementos en base al fecha_cobro (Order by)

SELECT
SD.id_credito,
SD.Fecha_cobro,
SD.monto,
Rank() OVER
(PARTITION BY SD.id_credito
ORDER BY SD.id_credito,SD.Fecha_cobro ASC) AS ActivityRank
into #temp
FROM #Pagos SD

  • Una vez ya con mi tabla #temp, ahora si obtengo el MAX(Activity Rank) que viene siendo mi campo con el identificador unico por fecha de cobro, y lo ingreso en otra tabla temporal que llame #Rank

select id_credito,max(ActivityRank) as Max
into #RAnk
from
#temp
group by
id_credito

  • Ahora entonces para obtener la Fecha de Ultimo Pago, y en este caso el monto pagado en esa fecha tendríamos

select t.id_credito,t.fecha_cobro as FUP, t.monto
from
#temp as t
left join
#Rank as r
on t.id_credito = r.id_credito and ActivityRank = Max
where
not r.id_credito is null

  • Para la fecha de Penultimo pago y su monto sería

select t.id_credito,t.fecha_cobro as FPP, t.monto
from
#temp as t
left join
#Rank as r
on t.id_credito = r.id_credito and ActivityRank = Max-1
where
not r.id_credito is null

  • Y por último la fecha de Antepenultimo pago y su monto tendríamos

select t.id_credito,t.fecha_cobro as FAP, t.monto
from
#temp as t
left join
#Rank as r
on t.id_credito = r.id_credito and ActivityRank = Max-2
where
not r.id_credito is null

Seguramente hay otras mil formas de hacer esto, quiza incluso optimizar el código, cosa que espero poder hacer algún día, pero como siempre espero que esto le ayude a alguién.

3 responses to “SQL Server obteniendo autonumérico por grupo

  1. Pingback: SQL Server obteniendo autonumérico por grupo « DbRunas – Noticias y Recursos sobre Bases de Datos

  2. Purely to follow up on the up-date of this subject matter on your web site and wish to let you know just how much I loved the time you took to put together this helpful post. In the post, you really spoke of how to definitely handle this issue with all comfort. It would be my pleasure to accumulate some more suggestions from your blog and come as much as offer other folks what I have benefited from you. I appreciate your usual wonderful effort.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s