CODEWITHSUNDEEP
sqlsql-server
Ryan Abarquez
Ryan Abarquez

Reputation: 307

Query all dates less than the given date (Month and Year)

I have a table: tblperson

There are three columns in tblperson

id             amort_date        total_amort
C000000004     12/30/2015        4584.00
C000000004     01/31/2016        4584.00
C000000004     02/28/2016        4584.00

The user will have to provide a billing date @bill_date

I want to sum all the total amort of all less than the date given by the user on month and year basis regardless of the date

For example

 @bill_date = '1/16/2016'

 Result should:

 ID            sum_total_amort
 C000000004    9168.00

Regardless of the date i want to sum all amort less than January 2016

This is my query but it only computes the date January 2016, it does not include the dates less than it:

 DECLARE @bill_date DATE
 SET @bill_date='1/20/2016'

 DECLARE @month AS INT=MONTH(@bill_date)
 DECLARE @year AS INT=YEAR(@bill_date)

 SELECT id,sum(total_amort)as'sum_total_amort' FROM webloan.dbo.amort_guide
 WHERE loan_no='C000000004'
 AND MONTH(amort_date) = @month  
 AND YEAR(amort_date) = @year

 GROUP BY id

Upvotes: 1

Views: 3377

Answers (3)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can get the start of the month using:

DATEADD(MONTH, DATEDIFF(MONTH, 0, @bill_date), 0)

So to get the SUM(total_amort), your query should be:

SELECT 
    id,
    SUM(total_amort) AS sum_total_amort
FROM webloan.dbo.amort_guide
WHERE 
    loan_no='C000000004'
    AND amort_date < DATEADD(MONTH, DATEDIFF(MONTH, 0, @bill_date) + 1, 0)

Upvotes: 1

Raj
Raj

Reputation: 10843

You are checking for

AND MONTH(amort_date) < @month  
AND YEAR(amort_date) < @year

Hence the query will return only results where MONTH(amort_date)=MONTH(input_variable) AND YEAR(amort_date)=YEAR(input_variable)

Try this DECLARE @bill_date DATE SET @bill_date='1/20/2016'

 DECLARE @month AS INT=MONTH(@bill_date)
 DECLARE @year AS INT=YEAR(@bill_date)

 SELECT id,sum(total_amort)as'sum_total_amort' FROM webloan.dbo.amort_guide
 WHERE loan_no='C000000004'
 AND MONTH(amort_date) < @month  
 AND YEAR(amort_date) < @year

 GROUP BY id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You would use aggregation and inequalities:

select id, sum(total_amort)
from webloan.dbo.amort_guide
where loan_no = 'C000000004' and
      year(amort_date) * 12 + month(amort_date) <= @year * 12 + @month
group by id;

Alternatively, in SQL Server 2012+, you can just use EOMONTH():

select id, sum(total_amort)
from webloan.dbo.amort_guide
where loan_no = 'C000000004' and
      amort_date <= EOMONTH(@bill_date)
group by id;

Upvotes: 2

Related Questions