April 2, 2009 at 8:19 am
Hi all, I need some help.
I have two tables. The first table (Account) holds account details and a second table (SupervisedStatus) holds a status for a period relevant to an account.
Essentially I need to join the two tables to display the status per Account ValueDate but I'm struggling to match the Status period to a specific Account ValueDate. :ermm:
Below is code and expected result. Let me know if I need to clarify anything.
CREATE TABLE #Account
(
AccountID smallint,
AccountType varchar(20),
AccountValue decimal (15,2),
ValueDate Datetime
)
INSERT INTO #Account
SELECT 1, 'Maxi', 200.00, '01/01/2009'
UNION ALL SELECT 1, 'Maxi', 500.00, '02/01/2009'
UNION ALL SELECT 1, 'Maxi', 1000.00, '03/01/2009'
UNION ALL SELECT 2, 'Mini', 200.00, '01/01/2009'
UNION ALL SELECT 2, 'Mini', 500.00, '02/01/2009'
UNION ALL SELECT 2, 'Mini', 1000.00, '03/01/2009'
UNION ALL SELECT 2, 'Mini', 1200.00, '04/01/2009'
UNION ALL SELECT 2, 'Mini', 1300.00, '05/01/2009'
CREATE TABLE #SupervisedStatus
(
SupervisedID smallint,
AccountID smallint,
SupervisedStatus varchar(20),
FromDate datetime
)
INSERT INTO #SupervisedStatus
SELECT 1, 1, 'Supervised', '01/01/2009'
UNION ALL SELECT 2, 1, 'Not Supervised', '03/01/2009'
UNION ALL SELECT 3, 2, 'Supervised', '01/01/2009'
UNION ALL SELECT 4, 2, 'Not Supervised', '03/01/2009'
UNION ALL SELECT 5, 2, 'Supervised', '05/01/2009'
Expected Result:
AccountID AccountType AccountValue ValueDate SupervisedStatus
-----------------------------------------------------------------------------
1 maxi 200 01/01/2009 Supervised
1 maxi 500 01/02/2009 Supervised
1 maxi 100 01/03/2009 Not Supervised
2 mini 200 01/01/2009 Supervised
2 mini 500 01/02/2009 Supervised
2 mini 1000 01/03/2009 Not Supervised
2 mini 1200 01/04/2009 Not Supervised
2 mini 1300 01/05/2009 Supervised
April 2, 2009 at 8:31 am
Try this:
select AccountID, AccountType, AccountValue, ValueDate,
(select SupervisedStatus
from
(select AccountID, SupervisedStatus, FromDate,
isnull((select min(fromdate)
from #SupervisedStatus s2
where fromdate > s1.fromdate
and accountid = s1.accountid), getdate()) as ToDate
from #SupervisedStatus s1) Sub1
where AccountID = #Account.AccountID
and FromDate <= #Account.ValueDate
and ToDate > #Account.ValueDate)
from #Account;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2009 at 8:40 am
Thanks!
There is a null value for the last period though ... will post back once I've understood what you've done:hehe:
April 2, 2009 at 8:50 am
If the Value date is in the future, it will end up with a Null value for that sub-query, because of the IsNull(..., getdate()), that I used in the sub-query for EndDate. Try replacing getdate() with something later, like 1 Jan 3000, and see if that fixes what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 6, 2009 at 8:19 am
Sorry for late reply ...
This works perfectly, thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply