CODEWITHSUNDEEP
sqlsql-servert-sql
user3169144
user3169144

Reputation: 37

T-SQL for subtracting total loan due from monthly loan return

Please I am new to T-SQL and I have been finding it difficult to achieve this goal. How can I keep subtracting two columns to update a column for holding the difference between two column, starting from a specific date to another date..

Upvotes: 0

Views: 101

Answers (2)

imaCoden
imaCoden

Reputation: 477

Are you looking for a running total? This is easier said than done but you need to recursively reference all the records before the current one. Looks like this:

declare @ledger table (LedgerId int, LedgerDate datetime, Amount decimal(2))

insert into @ledger (LedgerId, LedgerDate, Amount) values
(1, dateadd(hour, -1, getdate()), 1.00),
(2, dateadd(hour, -2, getdate()), 2.00),
(3, dateadd(hour, -3, getdate()), 3.00),
(4, dateadd(hour, -4, getdate()), 4.00),
(5, dateadd(hour, -5, getdate()), 5.00),
(6, dateadd(hour, -6, getdate()), 6.00)
;

declare @startDate datetime = dateadd(hour, -5, getdate()), 
    @endDate datetime = dateadd(hour, -2, getdate())


;
with orderedByDate as (
    select 
            LedgerId, 
            LedgerDate, 
            Amount, 
            ROW_NUMBER() over (order by LedgerDate desc) rowNum 
        from @ledger
        where LedgerDate between @startDate and @endDate
), runningTotal as (
    select 
            orderedByDate.LedgerId,
            max(case when orderedByDate.rowNum = previousValues.rowNum then orderedByDate.LedgerDate else 0 end) LedgerDate,
            max(case when orderedByDate.rowNum = previousValues.rowNum then orderedByDate.Amount else 0 end) Amount,
            sum(previousValues.Amount) Total
        from orderedByDate 
            inner join orderedByDate previousValues
                on orderedByDate.rowNum >= previousValues.rowNum
        group by orderedByDate.LedgerId

)
select
        LedgerId, 
        LedgerDate, 
        Amount, 
        Total
    from runningTotal

This will give you something like:

LedgerId | LedgerDate | Amount | Total
---------|------------|--------|-------
2        |2016-11-03  |2       |2
3        |2016-11-03  |3       |5
4        |2016-11-03  |4       |9
5        |2016-11-03  |5       |14

Upvotes: 0

PlsqlDev
PlsqlDev

Reputation: 236

You can use Between operator or Datediff function

UPDATE table_name 
SET column1 = column2-column3 
WHERE dateColumn BETWEEN '01-01-2016' AND '12-31-2016'

Upvotes: 1

Related Questions