Help with Query

  • 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

  • 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?

  • 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

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

  • thankx all for your responce

  • 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]

  • 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)
    Intro to Tally Tables and Functions

  • 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.

  • 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

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

  • 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]

  • 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.

  • 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]

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

  • 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