I currently have a query that shows all the transactions that a customer has made with a credit card, all good, but I want to add a field in which it tells me how long there is between one transaction and another, for example if I make a transaction with said card at 4 PM and another at 6 PM, I don't know if there will be any way to show that between those 2 transactions 2 hours passed, or if it is between days and so on, this is the query
SELECT
tr.tn_referencia AS NumeroTransaccion,
tr.tn_fchtrx AS Fecha,
tr.description AS Descripcion,
UPPER(cl.FullnameLocal) AS PropietarioTarjeta,
tr.tn_total AS Monto
FROM dbo.transacciones AS tr
INNER JOIN dbo.tc_tarjetas AS tar ON tar.ta_tarjeta=tr.tn_tarjeta
INNER JOIN dbo.cl_clientesT AS cli ON cli.cl_cliente =tar.ta_cliente
INNER JOIN dbo.Clients AS cl ON cl.ClientId=cli.cl_mis
which gives me the following
(This is an example of how the result of a single client shows)
But how can I add the field that tells me how much time has passed between one transaction and another, I was reading that with DATEIFF but I don't know how to do it or if I should use temporary tables
if someone help me
The simplest solution is to use the Lag window function, which allows you to get the result of the previous row in the current row.
Now the exposed data:
A first approach, to check its operation and see how we can then apply the dateDiff
Lag returns the result of the previous row, for each
PropietarioTarjeta
, ordered by the descending date, and by the transaction. Two important things. The card owner should not be the fullname, but the primary key of cl, but since it is not in the query, I have put the fullname, because if two clients had the same name....we would mess it up.In addition, the order by must be deterministic, hence, in addition to the date, the transaction is placed, because if the same client had two operations with the same date (something that seems difficult a priori), it ensures that two operations return always the same.
As you can see in the image, the column
Ant
satisfies the requirement.Now we use DateDiff, to get the difference, in time units, between two dates.
Here we have already added the datediff function, which has the three parameters it requires:
Second: In what unit of time do you want the difference. This is where you have to decide what you need. DateDiff
Date: Column with the value.
Lag...: The Date of the previous row, which we had seen in the previous query.
So if you want hours, you just have to adapt the first parameter second , by hour . If you want days, by day .
lag/lead