SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Matching Date periods to specific date


Matching Date periods to specific date

Author
Message
Grinja
Grinja
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 618
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


GSquared
GSquared
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24635 Visits: 9730
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
Grinja
Grinja
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 618
Thanks!
There is a null value for the last period though ... will post back once I've understood what you've doneHehe
GSquared
GSquared
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24635 Visits: 9730
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
Grinja
Grinja
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 618
Sorry for late reply ...
This works perfectly, thanks again.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search