September 23, 2014 at 6:30 pm
Hi,
I have 2 tables:
CREATE TABLE [dbo].[ACCOUNTS](
[ACCID] [int] NOT NULL,
[Startdate] [datetime] NOT NULL,
[Enddate] [datetime] NOT NULL,
CONSTRAINT [PK_ACCOUNTS] PRIMARY KEY CLUSTERED
(
[ACCID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[BALANCES](
[ACCID] [int] NOT NULL,
[LiveFrom] [datetime] NOT NULL,
[Updated] [datetime] NOT NULL,
[balance] [money] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO BALANCES VALUES (1,'2007-05-05','2007-05-06',2.65),
(1,'2007-05-06','2007-05-23',4.35),
(2,'2007-05-07','2007-06-23',4.66),
(2,'2007-05-08','2007-05-15',3.35),
(2,'2007-05-09','2007-06-15',1.35)
;
INSERT INTO ACCOUNTS VALUES (1,'2007-04-02','2007-06-01'),
(2,'2007-02-02','2007-08-01')
;
I need to find the most recent balance (using the Updated field) for each account where the livefrom date is between the account startdate and enddate.
I have the initial select to get the most recent date, but am unsure of the most efficient way to get the balance.
select max(updated),a.accid
from
accounts a left outer join balances b on a.accid = b.accid
where
b.livefrom between a.startdate and a.enddate
group by a.accid
The actual query is more complex and the tables are large so efficiency is paramount and I have become stuck in nested queries.
Thanks in advance!
September 23, 2014 at 7:04 pm
This kind of problems are usually addressed using ROW_NUMBER() with a CTE.
WITH CTE AS(
SELECT A.ACCID,
b.balance,
Updated,
ROW_NUMBER() OVER( PARTITION BY A.ACCID ORDER BY B.Updated DESC) rn
FROM ACCOUNTS A
JOIN BALANCES B ON A.ACCID = B.ACCID
AND B.LiveFrom BETWEEN A.Startdate AND A.Enddate
)
SELECT ACCID,
balance,
Updated
FROM CTE
WHERE rn = 1;
September 24, 2014 at 12:22 pm
Thanks Luis, that was really helpful.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply