SQL query to select previous month record in SQL Server

Last Reply on Apr 16, 2018 11:01 PM By nagaraju60

Posted on Apr 16, 2018 12:09 AM

Dear Sir , i wants to select passed month from sql table where current month 8(august ) than it select 4,5,6,7,8 month and if current month january 2019 it select last months where IsPaid =0 (false)

my table dat is here

FeeStudentId Student_Id Month_Name MonthNo Feeyear Amount IsPaid PaymentDate
1 1251 April 4 2018 545 0 NULL
2 1251 May 5 2018 545 0 NULL
3 1251 June 6 2018 545 0 NULL
4 1251 July 7 2018 545 0 NULL
5 1251 August 8 2018 545 0 NULL
6 1251 September 9 2018 545 0 NULL
7 1251 October 10 2018 545 0 NULL
8 1251 November 11 2018 545 0 NULL
9 1251 December 12 2018 545 0 NULL
10 1251 January 1 2019 545 0 NULL
11 1251 February 2 2019 545 0 NULL
12 1251 March 3 2019 545 0 NULL

my table structure is here 

FeeStudentId int
Student_Id int
Month_Name varchar(50)
Feeyear int
Amount int
IsPaid bit
PaymentDate date
MonthNO int

my selct statement is here 

SELECT [FeeStudentId],[Student_Id],[Month_Name],[MonthNo],[Feeyear],[Amount],[IsPaid],[PaymentDate]FROM 
[onlineit_mcps].[dbo].[FeeStudent] where Student_Id =1251 and IsPaid=0 and 
MONTHno <=MONTH(getdate())

 

Posted on Apr 16, 2018 11:01 PM Modified on on Apr 16, 2018 11:01 PM

Dear @yogeshc, 

       Please refer below query. it will solve your problem..

 

SELECT [FeeStudentId],[Student_Id],[Month_Name],[MonthNo],[Feeyear],[Amount],[IsPaid],[PaymentDate] FROM
[onlineit_mcps].[dbo].[FeeStudent] WHERE Student_Id =1251 AND IsPaid=0 AND 
 CAST(CONVERT(varchar(4), Feeyear)+'-'+CONVERT(varchar(2), MonthNO)+'-'+'01' as date) <=  CONVERT(date, GETDATE())