May 6, 2010 at 10:09 am
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
May 6, 2010 at 10:19 am
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?
May 6, 2010 at 10:27 am
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
May 6, 2010 at 10:36 am
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);
May 6, 2010 at 12:49 pm
thankx all for your responce
May 6, 2010 at 3:57 pm
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.
May 6, 2010 at 7:17 pm
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
Change is inevitable... Change for the better is not.
May 6, 2010 at 7:42 pm
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.
May 12, 2010 at 1:33 am
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
ClientCode|FlowDate|TempletID|AssetCode|Amount|AccountReturn
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
May 12, 2010 at 3:01 am
So Rule in Return Amont should be Next Amount / Previous Amount
May 12, 2010 at 4:24 am
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...
May 12, 2010 at 5:32 am
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.
May 12, 2010 at 7:19 am
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...
May 12, 2010 at 8:03 am
"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!
May 12, 2010 at 9:42 am
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:-)
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply