Help with Query

Question

Help with Query

vijay.s

SSCrazy

Points: 2161
More actions
May 6, 2010 at 10:09 am

#224213

Dear Team,
Kindly look into my problem
DECLARE @Table TABLE
(
id int,
ClientIdINT,
bnmk_dt DATETIME,
AmountINT
)
INSERT @Table VALUES(1, 1, '2009-01-01', 100)
INSERT @Table VALUES(2, 1, '2009-01-01',95)
INSERT @Table VALUES(3, 1, '2009-01-01',99)
INSERT @Table VALUES(1, 2 ,'2009-01-01',100)
INSERT @Table VALUES(2, 2, '2009-01-01',100)
--Select * from @Table
Rule : value of the Amount / value of the Amount for the previous previous
I need the selected record like previous row amount/next row amount for client wise
so my output will be
/*
12009-01-01 00:00:00.000100 1
12009-01-01 00:00:00.00095 .95
12009-01-01 00:00:00.00099 1.04
22009-01-01 00:00:00.000100.99
22009-01-01 00:00:00.0001001
*/
Kindly provide me the way for achieve this

Viewing 15 posts - 1 through 15 (of 21 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

vijay.s (5/6/2010)
Dear Team,
Kindly look into my problem
DECLARE @Table TABLE
(
id int,
ClientIdINT,
bnmk_dt DATETIME,
AmountINT
)
INSERT @Table VALUES(1, 1, '2009-01-01', 100)
INSERT @Table VALUES(2, 1, '2009-01-01',95)
INSERT @Table VALUES(3, 1, '2009-01-01',99)
INSERT @Table VALUES(1, 2 ,'2009-01-01',100)
INSERT @Table VALUES(2, 2, '2009-01-01',100)
--Select * from @Table
Rule : value of the Amount / value of the Amount for the previous previous
I need the selected record like previous row amount/next row amount for client wise
so my output will be
/*
12009-01-01 00:00:00.000100 1
12009-01-01 00:00:00.00095 .95
12009-01-01 00:00:00.00099 1.04
22009-01-01 00:00:00.000100.99
22009-01-01 00:00:00.0001001
*/
Kindly provide me the way for achieve this

To clarify, do we divide the current value by the previous value if the previous value is a different client id? If there is no previous value for the current client id, do we use the current value for the previous value?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

vijay.s (5/6/2010)
Dear Team,
Kindly look into my problem
DECLARE @Table TABLE
(
id int,
ClientIdINT,
bnmk_dt DATETIME,
AmountINT
)
INSERT @Table VALUES(1, 1, '2009-01-01', 100)
INSERT @Table VALUES(2, 1, '2009-01-01',95)
INSERT @Table VALUES(3, 1, '2009-01-01',99)
INSERT @Table VALUES(1, 2 ,'2009-01-01',100)
INSERT @Table VALUES(2, 2, '2009-01-01',100)
--Select * from @Table
Rule : value of the Amount / value of the Amount for the previous previous
I need the selected record like previous row amount/next row amount for client wise
so my output will be
/*
12009-01-01 00:00:00.000100 1
12009-01-01 00:00:00.00095 .95
12009-01-01 00:00:00.00099 1.04
22009-01-01 00:00:00.000100.99
22009-01-01 00:00:00.0001001
*/
Kindly provide me the way for achieve this

Please provide a sample of what you have tried thus far.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Came up with this waiting for clarification:

DECLARE @Table TABLE

(

id int,

ClientId INT,

bnmk_dt DATETIME,

Amount INT

);

INSERT @Table VALUES(1, 1, '2009-01-01', 100);

INSERT @Table VALUES(2, 1, '2009-01-01',95);

INSERT @Table VALUES(3, 1, '2009-01-01',99);

INSERT @Table VALUES(1, 2 ,'2009-01-01',100);

INSERT @Table VALUES(2, 2, '2009-01-01',100);

--Select * from @Table

select

t1.id,

t1.ClientId,

t1.bnmk_dt,

t1.Amount,

t1.Amount / (isnull(t2.Amount, t1.Amount) * 1.0)

from

@Table t1

left outer join @Table t2

on (t1.ClientId = t2.ClientId

and t1.id = t2.id + 1);

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

vijay.s

SSCrazy

Points: 2161

May 6, 2010 at 12:49 pm

#1163530

thankx all for your responce

Garadin One Orange Chip Points: 29613 More actions · Answer 5

CirquedeSQLeil (5/6/2010)
Please provide a sample of what you have tried thus far.

I was thinking the same thing... but it may just be the language barrier. Aside from the request itself, the sample data was better than usual.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup.

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jeff Moden SSC Guru Points: 1004581 More actions · Answer 6

Between the sample data, the sample output, and the description, it was a pleasant change for me. I'll tolerate nuances of the language barrier for postings like this one.

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Still could have ued an answer to my question, it really is a vital question based on the sample data and expected output. More like a running division than a grouped one.

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

I want to calculate account return for client wise, TempletID wise, AssetCode and flowdate wise

(

ClientCodeINT,

FlowDate DateTIME,

TempletID INT,

AssetCodeVARCHAR(25),

Amount MONEY,

AccountReturn MONEY

)

GO

INSERT INTO #Flows (ClientCode, FlowDate, TempletID, AssetCode, Amount)

SELECT 1 ClientCode, '1/1/2010' FlowDate, 1 TempletID, 'Equity' AssetCode, 4000 Amount

UNION

SELECT 1 ClientCode, '1/25/2010' FlowDate, 1 TempletID, 'Equity' AssetCode, 8000 Amount

UNION

SELECT 1 ClientCode, '2/13/2010' FlowDate, 1 TempletID, 'Equity' AssetCode, 4000 Amount

UNION

SELECT 1 ClientCode, '2/18/2010' FlowDate, 1 TempletID, 'Equity' AssetCode, 4010 Amount

UNION

SELECT 1 ClientCode, '3/1/2010' FlowDate, 1 TempletID, 'Equity' AssetCode, 4020.025 Amount

UNION

SELECT 1 ClientCode, '3/25/2010' FlowDate, 1 TempletID, 'Equity' AssetCode, 4030.075 Amount

UNION

SELECT 1 ClientCode, '1/1/2010' FlowDate, 1 TempletID, 'Debt' AssetCode, 1500 Amount

UNION

SELECT 1 ClientCode, '1/25/2010' FlowDate, 1 TempletID, 'Debt' AssetCode, 750 Amount

UNION

SELECT 1 ClientCode, '2/13/2010' FlowDate, 1 TempletID, 'Debt' AssetCode, 1500 Amount

UNION

SELECT 1 ClientCode, '2/18/2010' FlowDate, 1 TempletID, 'Debt' AssetCode, 1503.75 Amount

UNION

SELECT 1 ClientCode, '3/1/2010' FlowDate, 1 TempletID, 'Debt' AssetCode, 1507.509375 Amount

UNION

SELECT 1 ClientCode, '3/25/2010' FlowDate, 1 TempletID, 'Debt' AssetCode, 1511.278148 Amount

CREATE CLUSTERED INDEX IX_#Flows_CoverUpdate

ON #Flows (ClientCode, TempletID, AssetCode, FlowDate)

After calculation my result should be

1|2010-01-01 00:00:00.000|1|Debt|1500.0000|1.0000

1|2010-01-25 00:00:00.000|1|Debt|750.0000|.5000

1|2010-02-13 00:00:00.000|1|Debt|1500.0000|2.0000

1|2010-02-18 00:00:00.000|1|Debt|1503.7500|1.0025

1|2010-03-01 00:00:00.000|1|Debt|1507.5094|1.0025

1|2010-03-25 00:00:00.000|1|Debt|1511.2781|1.0025

1|2010-01-01 00:00:00.000|1|Equity|4000.0000|1.0000

1|2010-01-25 00:00:00.000|1|Equity|8000.0000|2.0000

1|2010-02-13 00:00:00.000|1|Equity|4000.0000|.5000

1|2010-02-18 00:00:00.000|1|Equity|4010.0000|1.0025

1|2010-03-01 00:00:00.000|1|Equity|4020.0250|1.0025

1|2010-03-25 00:00:00.000|1|Equity|4030.0750|1.0025

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

So Rule in Return Amont should be Next Amount / Previous Amount

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

Is it what you want?

;WITH PrevDate

AS

(

SELECT f.ClientCode, f.FlowDate, f.TempletID, f.AssetCode, 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

)

SELECT f.ClientCode, f.FlowDate, f.TempletID, f.AssetCode, f.Amount

,f.Amount / ISNULL(pf.Amount,f.Amount) as AccountReturn

,p.PrevFlowDate -- just for validation

FROM #Flows f

JOIN PrevDate p

ON f.CLientCode = p.ClientCode

AND f.TempletID = p.TempletID

AND f.AssetCode = p.AssetCode

AND f.FlowDate = p.FlowDate

LEFT JOIN #Flows pf

ON pf.CLientCode = p.ClientCode

AND pf.TempletID = p.TempletID

AND pf.AssetCode = p.AssetCode

AND pf.FlowDate = p.PrevFlowDate

ORDER BY f.ClientCode, f.TempletID, f.AssetCode,f.FlowDate

If you have large datasets, you might consider using temp tables to store PrevDate data for performance benefits...

_____________________________________________
"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 11

Its Giving me wromg output as You can see the current output in me previous post.

U are taking first flow amount and devide other flow amount with that amount but i want increment devide.

;WITH PrevDate

AS

(

Select Row_number() OVER (PARTITION BY ClientCode, TempletID, AssetCode ORDER BY ClientCode, TempletID, AssetCode) Row, *

from #Flows

)

SELECT t1.* , t1.Amount / (isnull(t2.Amount, t1.Amount) * 1.0)

from PrevDate t1

left outer join PrevDate t2

on t1.ClientCode = t2.ClientCode

and t1.AssetCode = t2.AssetCode

and t1.TempletID = t2.TempletID

AND t1.Row = t2.Row + 1

I want same output with out CTE using only flow table, Is Problem can be solve using "Quirky" Update if Yes then How.

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

I had typo in my query. New one should give you your expected results:

;WITH PrevDate

AS

(

SELECT f.ClientCode, f.FlowDate, f.TempletID, f.AssetCode, MAX(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

)

SELECT f.ClientCode, f.FlowDate, f.TempletID, f.AssetCode, f.Amount

,ROUND(1.0 * f.Amount / ISNULL(pf.Amount,f.Amount),4) as AccountReturn

,p.PrevFlowDate -- just for validation

FROM #Flows f

JOIN PrevDate p

ON f.CLientCode = p.ClientCode

AND f.TempletID = p.TempletID

AND f.AssetCode = p.AssetCode

AND f.FlowDate = p.FlowDate

LEFT JOIN #Flows pf

ON pf.CLientCode = p.ClientCode

AND pf.TempletID = p.TempletID

AND pf.AssetCode = p.AssetCode

AND pf.FlowDate = p.PrevFlowDate

ORDER BY f.ClientCode, f.TempletID, f.AssetCode,f.FlowDate

I will have a look Quirky update option. You will need to inforce the order of your data there...

_____________________________________________
"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]

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

"Quirky Update" way

Here we are:

1. I would not use it if I would be you.

2. If you deside to use it, you must read

"The RULES" section of the

Solving the "Running Total" & "Ordinal Rank" Problems

article By Jeff Moden

http://www.sqlservercentral.com/articles/T-SQL/68467/

3. Add required precausions (eg. MAXDOP)

Now:

In order to get the Quirky Update to work, first you will need a clustered index to enforce update order. In your case I believe it should be that:

CREATE CLUSTERED INDEX indx_tmp_Flows ON #flows (ClientCode, TempletID, AssetCode,FlowDate)

then, here the update itself:

DECLARE @AR MONEY

DECLARE @PrevAmt MONEY

DECLARE @ClientCode INT

DECLARE @TempletID INT

DECLARE @AssetCode VARCHAR(100)

SET @PrevAmt = 0

UPDATE #Flows

SET

@AR = AccountReturn = CASE WHEN (@ClientCode = ClientCode AND @TempletID = TempletID AND @AssetCode = AssetCode) THEN

ROUND(1.0 * Amount/ISNULL(NULLIF(@PrevAmt,0),Amount), 4)

ELSE

1

END

,@PrevAmt = Amount

,@ClientCode = ClientCode

,@TempletID = TempletID

,@AssetCode = AssetCode

FROM #Flows

SELECT * FROM #Flows

Is the above code reliable? You will find the answers (or more questions) in the Jeff Moden article!

_____________________________________________
"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 14

Thankx for your post,

I tried Quarily Update from My self and but i gets failed as I am not able to debug the problem.

Still I want to know thwt which approch is better for solving these type of queries.

CTE OR Rank Number OR Quarily Update???

If you have any idea then share it.

Thankx:-)

Help with Query

Cookies on SQLServerCentral