Retrieve Previous Available Date Records

Question

Post reply

Retrieve Previous Available Date Records

vijay.s

SSCrazy

Points: 2161
More actions
May 28, 2010 at 1:15 am

#235711

Dear Gurus,
I have below mentioned data table and I want retrive Previous flow date for those record having Type as 'NoFlow'
Combinition should be ClientCode, TempletID, AssetCode
CREATE TABLE #Flows
(
ClientCode INT,
FlowDate DateTIME,
TempletID INT,
AssetCode VARCHAR(25),
Amount MONEY,
TypeVARCHAR(10),
AccountReturn MONEY
)
GO
INSERT INTO #Flows (ClientCode, FlowDate, TempletID, AssetCode, Amount, Type)
SELECT 1 ClientCode, '1/1/2010' FlowDate, 1 TempletID, 'Equity' AssetCode, 4000 Amount, 'InFlow' Type
UNION
SELECT 1 ClientCode, '1/25/2010' FlowDate, 1 TempletID, 'Equity' AssetCode, 8000 Amount , 'NoFlow' Type
UNION
SELECT 1 ClientCode, '2/13/2010' FlowDate, 1 TempletID, 'Equity' AssetCode, 4000 Amount , 'NoFlow' Type
UNION
SELECT 1 ClientCode, '2/18/2010' FlowDate, 1 TempletID, 'Equity' AssetCode, 4010 Amount , 'InFlow' Type
UNION
SELECT 1 ClientCode, '3/1/2010' FlowDate, 1 TempletID, 'Equity' AssetCode, 4020.025 Amount, 'NoFlow' Type
UNION
SELECT 1 ClientCode, '3/25/2010' FlowDate, 1 TempletID, 'Equity' AssetCode, 4030.075 Amount , 'NoFlow' Type
UNION
SELECT 1 ClientCode, '1/1/2010' FlowDate, 1 TempletID, 'Debt' AssetCode, 1500 Amount , 'InFlow' Type
UNION
SELECT 1 ClientCode, '1/25/2010' FlowDate, 1 TempletID, 'Debt' AssetCode, 750 Amount , 'NoFlow' Type
UNION
SELECT 1 ClientCode, '2/13/2010' FlowDate, 1 TempletID, 'Debt' AssetCode, 1500 Amount , 'NoFlow' Type
UNION
SELECT 1 ClientCode, '2/18/2010' FlowDate, 1 TempletID, 'Debt' AssetCode, 1503.75 Amount , 'NoFlow' Type
UNION
SELECT 1 ClientCode, '3/1/2010' FlowDate, 1 TempletID, 'Debt' AssetCode, 1507.509375 Amount , 'InFlow' Type
UNION
SELECT 1 ClientCode, '3/25/2010' FlowDate, 1 TempletID, 'Debt' AssetCode, 1511.278148 Amount , 'NoFlow' Type
CREATE CLUSTERED INDEX IX_#Flows_CoverUpdate
ON #Flows (ClientCode, TempletID, AssetCode, FlowDate)
Expected Out Put
-------------------
ClientCode FlowDate TempletID AssetCodeTypePrevFlowDate
11-Jan-20101DebtInFlow
125-Jan-20101DebtNoFlow1-Jan-2010
113-Feb-20101DebtNoFlow1-Jan-2010
118-Feb-20101DebtNoFlow1-Jan-2010
11-Mar-20101DebtInFlow
125-Mar-20101DebtNoFlow1-Mar-2010
16-Jan-20101EquityInFlow
125-Jan-20101EquityNoFlow6-Jan-2010
113-Feb-20101EquityNoFlow6-Jan-2010
118-Feb-20101EquityInFlow
11-Mar-20101EquityNoFlow18-Feb-2010
125-Mar-20101EquityNoFlow18-Feb-2010
I am Using This query
SELECT f.ClientCode, f.FlowDate, f.TempletID, f.AssetCode, f.Type, MIN(f1.FlowDate) as PrevFlowDate
FROM #Flows f
LEFT JOIN #Flows f1
ON f.CLientCode = f1.ClientCode
AND f.TempletID = f1.TempletID
AND f.AssetCode = f1.AssetCode
AND f.FlowDate > f1.FlowDate
GROUP BY f.ClientCode, f.FlowDate, f.TempletID, f.AssetCode, f.Type
ORDER By f.ClientCode, f.TempletID, f.AssetCode, f.FlowDate
Thankx

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Thank You for the table & data setup. For the future please use ISO date format as we are not all Americans here 😉

Now the query for you:

;with LastInFlows

as

(

select nf.ClientCode, nf.TempletID, nf.AssetCode, nf.FlowDate, max(inf.FlowDate) PrevFlowDate

from #Flows nf

join #Flows inf

on inf.ClientCode = nf.ClientCode

and inf.TempletID = nf.TempletID

and inf.AssetCode = nf.AssetCode

where nf.Type = 'NoFlow'

and inf.Type = 'InFlow'

and inf.FlowDate<nf.FlowDate

Group By nf.ClientCode, nf.TempletID, nf.AssetCode, nf.FlowDate

)

SELECT f.ClientCode, f.FlowDate, f.TempletID, f.AssetCode, f.Type, f1.PrevFlowDate

FROM #Flows f

LEFT JOIN LastInFlows f1

ON f.CLientCode = f1.ClientCode

AND f.TempletID = f1.TempletID

AND f.AssetCode = f1.AssetCode

AND f.FlowDate = f1.FlowDate

AND f.Type = 'NoFlow'

order by ClientCode, TempletID, AssetCode, FlowDate, Type

Just one more thing: in your data setup there are no records with the date of 6 Jan 2010, It would be really unexpected to see this date it in your expected results 😀

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

vijay.s SSCrazy Points: 2161 More actions · Answer 2

vijay.s

SSCrazy

Points: 2161

May 29, 2010 at 1:06 am

#1174214

Yas you are right

Paul White SSC Guru Points: 150467 More actions · Answer 3

This version is more efficient:

SELECT This.ClientCode,

This.FlowDate,

This.TempletID,

This.AssetCode,

This.Type,

COALESCE(PreviousFlow.FlowDate, SPACE(0)) AS PrevFlowDate

FROM #Flows This

OUTER

APPLY (

SELECT TOP (1)

Previous.FlowDate

FROM #Flows Previous

WHERE Previous.ClientCode = This.ClientCode

AND Previous.TempletID = This.TempletID

AND Previous.AssetCode = This.AssetCode

AND Previous.FlowDate < This.FlowDate

AND Previous.Type = 'InFlow'

AND This.Type = 'NoFlow'

ORDER BY

Previous.FlowDate DESC

) PreviousFlow;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

vijay.s SSCrazy Points: 2161 More actions · Answer 4

Paul White NZ (5/29/2010)
This version is more efficient:
SELECT This.ClientCode,
This.FlowDate,
This.TempletID,
This.AssetCode,
This.Type,
COALESCE(PreviousFlow.FlowDate, SPACE(0)) AS PrevFlowDate
FROM #Flows This
OUTER
APPLY (
SELECT TOP (1)
Previous.FlowDate
FROM #Flows Previous
WHERE Previous.ClientCode = This.ClientCode
AND Previous.TempletID = This.TempletID
AND Previous.AssetCode = This.AssetCode
AND Previous.FlowDate < This.FlowDate
AND Previous.Type = 'InFlow'
AND This.Type = 'NoFlow'
ORDER BY
Previous.FlowDate DESC
) PreviousFlow;

Dear Paul,

What exactly thoumb rule you followed with this query.

Can u explain.

Regards

Vijay

Paul White SSC Guru Points: 150467 More actions · Answer 5

vijay.s (5/30/2010)
Dear Paul,
What exactly thoumb rule you followed with this query.
Can u explain.
Regards
Vijay

Sorry I don't understand the question.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

vijay.s SSCrazy Points: 2161 More actions · Answer 6

vijay.s

SSCrazy

Points: 2161

May 31, 2010 at 5:22 am

#1174467

Means what exectly your query processed.

Paul White SSC Guru Points: 150467 More actions · Answer 7

vijay.s (5/31/2010)
Means what exectly your query processed.

I used the sample table and data from your earlier post :unsure:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

vijay.s (5/30/2010)
Paul White NZ (5/29/2010)
This version is more efficient:
SELECT This.ClientCode,
This.FlowDate,
This.TempletID,
This.AssetCode,
This.Type,
COALESCE(PreviousFlow.FlowDate, SPACE(0)) AS PrevFlowDate
FROM #Flows This
OUTER
APPLY (
SELECT TOP (1)
Previous.FlowDate
FROM #Flows Previous
WHERE Previous.ClientCode = This.ClientCode
AND Previous.TempletID = This.TempletID
AND Previous.AssetCode = This.AssetCode
AND Previous.FlowDate < This.FlowDate
AND Previous.Type = 'InFlow'
AND This.Type = 'NoFlow'
ORDER BY
Previous.FlowDate DESC
) PreviousFlow;
Dear Paul,
What exactly thoumb rule you followed with this query.
Can u explain.
Regards
Vijay

See the article at the first link in Paul's signature. That article has a detailed explanation.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)