Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Matching Date periods to specific date Expand / Collapse
Author
Message
Posted Thursday, April 2, 2009 8:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 5, 2014 8:24 AM
Points: 133, Visits: 617
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.


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

Post #688949
Posted Thursday, April 2, 2009 8:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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
Post #688967
Posted Thursday, April 2, 2009 8:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 5, 2014 8:24 AM
Points: 133, Visits: 617
Thanks!
There is a null value for the last period though ... will post back once I've understood what you've done
Post #688984
Posted Thursday, April 2, 2009 8:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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
Post #689001
Posted Monday, April 6, 2009 8:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 5, 2014 8:24 AM
Points: 133, Visits: 617
Sorry for late reply ...
This works perfectly, thanks again.
Post #691051
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse