SQL Server: Get record from one Table based on Max Date in another Table

makumbi

SOLVED CLOSED ACTIVE ANSWERED
User: makumbi
Posted: on Mar 30, 2023 01:31 AM
Forum: SQL Server
Answer: 1
Views: 2063

I am having this table but i am unable to display results

account	Names	Term	Sessiond	Dates
05-00002	MAKUMBI SULAIMAN	TERM1	Both	00:00.0

SELECT account,Names,Term,Sessiond,Dates FROM StudentBilling  WHERE Dates = (SELECT max(Dates) FROM StudentBilling) and account='05-00002'

USE [STMISWEB]
GO
/****** Object:  StoredProcedure [dbo].[CheckMinimumdate]    Script Date: 30/03/2023 03:27:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CheckMinimumdate] @k nvarchar(50) as 
SELECT account,Names,Term,Sessiond,Dates FROM StudentBilling
WHERE Dates = (SELECT max(Dates) FROM StudentBilling) and account=@k

account	Names	Dates	Particulars	Category	Sessiond	Term	ReFno	Amount	Postatus	Username
05-00002	MAKUMBI SULAIMAN	08/01/2023	Nsangi Central	Double	Both	TERM1		190000	POSTED	Username
05-00005	MUKISA ELIZABETH	08/01/2023	Nsangi Manja	Single	Morning	TERM1		100000	POSTED	Username
05-00003	SSEMBATYA ABBEY	08/01/2023	Nsangi Manja	Double	Both	TERM1		200000	POSTED	Username

AjayV

0 9

Reply
ANSWER
User: AjayV
Replied: on Mar 30, 2023 06:42 AM
Report

Hi makumbi,

Refer below sample query.

SQL

CREATE TABLE StudentBilling(
	[Account] [NVARCHAR](50) NULL,
	[Names] [VARCHAR](50) NULL,
	[Term] [VARCHAR](50) NULL,
	[Sessiond] [VARCHAR](50) NULL,
	[Dates] [VARCHAR](50) NULL
)
INSERT INTO StudentBilling VALUES('05-00002','MAKUMBI SULAIMAN','TERM1','Both','00:00.0')

CREATE PROCEDURE [CheckMinimumdate] 
    @k NVARCHAR(50)
AS
Begin
    SELECT account,Names,Term,Sessiond,Dates FROM StudentBilling
    WHERE Dates = (SELECT max(Dates) FROM StudentBilling) and account=@k
END

EXEC CheckMinimumdate '05-00002'

Output

Account	Names	Term	Sessiond	Dates
05-00002	MAKUMBI SULAIMAN	TERM1	Both	00:00.0

SQL Server: Get record from one Table based on Max Date in another Table

Company

Explore

Follow us

Contact Us