repeatin value in my procedure

  • Hi friends,

    I m write procedure for the problem is

    account trans date amount

    ==========================

    E2674 Cr 02apr02 3000

    R7890 Dr 06jun11 5000

    E9700 Cr 7feb09 5000 ===> actual table

    R4546 Dr 7aug09 6000

    E4545 Cr 9Jan11 8888

    R8679 Dr 10jul11 9999

    MYQUERY:

    =========

    ALTER procedure TVRY_POSTINGS

    (

    @x datetime,

    @y datetime

    )

    as

    begin

    select

    account,

    DATENAME(mm,date) "PR_MONS",

    Credit=

    sum(

    casetrans

    when 'Cr' then fs_post_amt else 0 end)

    into #temp1

    from

    @table (nolock)

    where date between @x and @y

    and left(account1) in ('E','R')

    group by

    account,

    date

    select

    account

    DATENAME(mm,date) "PR_MONS",

    Debit=

    sum(

    casetrans

    when 'Dr' then fs_post_amt else 0 end)

    into #tempr

    from

    @table (nolock)

    where date between @x and @y

    and left(account1) in ('E','R')

    group by

    account,

    date

    select

    x.account

    y.PR_MONS,

    x.Debit,

    y.Credit,

    Closing_Bal=sum

    (

    case

    when left(x.account1) in ('E') then x.Debit-y.Credit

    when left(y.account1) in ('R') then -x.Debit+y.Credit

    end

    )

    from

    #tempr x(nolock)

    right outer join

    #temp1 y(nolock)

    on

    x.fs_account_no=y.fs_account_no

    group by

    x.account

    y.PR_MONS,

    x.Debit,

    y.Credit

    end

    OUTPUT:

    =================

    account PR_Mons credit debit closinbal

    =======================================

    E2674 apr 3000 00000 -3000

    R7890 jun 0000 50000 5000

    E9700 feb 5000 0000 5000

    E2674 apr 3000 00000 -3000

    R7890 jun 0000 50000 5000

    E9700 feb 5000 0000 5000

    the vale of account is repeating in my output..... how to make summarise the account....i wanna one account ve to display for one time for one month,some "account " appreared many times in actual table,that account also display for one time....help me

  • raghuldrag (7/25/2012)


    Hi friends,

    I m write procedure for the problem is

    <SNIP>

    Hello and welcome to SSC!

    I'd like to be able to help you, but it seems you've forgot to post readily consumable sample data and ddl scripts. Simply put, we haven't got enough information to help you. For example, in your query you refer to "fs_post_amt", which doesn't appear in your sample data at all.

    If you could read this article[/url] about the best way to post DDL and sample data then edit your post to include it, it would be extremely useful for the unpaid volunteers of this site.

    To get you started, here is a better way to show your sample data: -

    SELECT account,trans,date,amount

    INTO #yourTable

    FROM (VALUES

    ('E2674','Cr','02apr02',3000),

    ('R7890','Dr','06jun11',5000),

    ('E9700','Cr','7feb09',5000),

    ('R4546','Dr','7aug09',6000),

    ('E4545','Cr','9Jan11',8888),

    ('R8679','Dr','10jul11',9999))a(account,trans,date,amount);

    Anyone using SQL Server 2008 (this forum section) can execute that and have a table containing the sample data that you have shown. That way, they can provide you with tested, working solutions.

    If you don't have time to prepare this information, I'd advise you to look up aggregate functions and grouping 😉

    Thanks!


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Good luck getting sample data, DDL and expected results from this user.

    We have asked a number of times on different topics all related to the same problem the OP is having and we are still waiting.

  • create table report

    (

    account varchar(30),

    trans char(30),

    trans_date date,

    amount number(50)

    )

    insert into report(account,trans,trans_date,amount) values('E2674','Cr','02apr02',300)

    insert into report(account,trans,trans_date,amount) values('R7890','Dr','06jun11',5000)

    insert into report(account,trans,trans_date,amount) values('E9700','Cr','7feb09',5000).......

    insert into report(account,trans,trans_date,amount) values('R7890','Cr','06jun11',5900)

    insert into report(account,trans,trans_date,amount) values('E9700','Dr','7feb09',8000)

    now i need the output of display for account number only one time permonth wheather its appears on many times of the actual table

    how to make sum the account......

  • raghuldrag (7/26/2012)


    create table report

    (

    account varchar(30),

    trans char(30),

    trans_date date,

    amount number(50)

    )

    insert into report(account,trans,trans_date,amount) values('E2674','Cr','02apr02',300)

    insert into report(account,trans,trans_date,amount) values('R7890','Dr','06jun11',5000)

    insert into report(account,trans,trans_date,amount) values('E9700','Cr','7feb09',5000).......

    insert into report(account,trans,trans_date,amount) values('R7890','Cr','06jun11',5900)

    insert into report(account,trans,trans_date,amount) values('E9700','Dr','7feb09',8000)

    now i need the output of display for account number only one time permonth wheather its appears on many times of the actual table

    how to make sum the account......

    OMG!!! I can't believe this:pinch:

    The sample data you have provided is good but not error free

    I got an error for the data type number( you have number in Oracle, not SQL Server )

    It would have been great if you had included the expected results too

    Nevertheless, its a good start and I am glad you finally understood what we want to help you 🙂

    I have given the solution as per my understanding of your issue

    If the results are not what you desire, please post the desired results

    declare @report table

    (

    account varchar(30),

    trans char(30),

    trans_date date,

    amount numeric(18,2)

    )

    insert into @report(account,trans,trans_date,amount) values('E2674','Cr','02apr02',300)

    insert into @report(account,trans,trans_date,amount) values('R7890','Dr','06jun11',5000)

    insert into @report(account,trans,trans_date,amount) values('E9700','Cr','7feb09',5000)

    insert into @report(account,trans,trans_date,amount) values('R7890','Cr','06jun11',5900)

    insert into @report(account,trans,trans_date,amount) values('E9700','Dr','7feb09',8000)

    SELECTaccount, DATENAME(YEAR, trans_date) Year, DATENAME(MONTH, trans_date) Month,

    SUM(CASE WHEN trans = 'Cr' THEN amount ELSE amount * -1 END) amount

    FROM@report

    GROUP BY account, DATENAME(YEAR, trans_date), DATENAME(MONTH, trans_date)


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • account PR_Mons credit debit closinbal

    =======================================

    E2674 apr 3000 00000 -3000

    R7890 jun 0000 50000 5000

    E9700 feb 5000 0000 5000

    E2674 apr 3000 00000 -3000 ======>expextingh output

    R7890 jun 0000 50000 5000

    E9700 feb 5000 0000 5000

    condition:

    **while account "E" means=cr-dr inclosing bal

    as the same account "R" means=dr-cr inclosing bal

    how to make query friend..... suppose I m giving from one month to another month it display the month in ouput of account......

  • raghuldrag (7/27/2012)


    account PR_Mons credit debit closinbal

    =======================================

    E2674 apr 3000 00000 -3000

    R7890 jun 0000 50000 5000

    E9700 feb 5000 0000 5000

    E2674 apr 3000 00000 -3000 ======>expextingh output

    R7890 jun 0000 50000 5000

    E9700 feb 5000 0000 5000

    condition:

    **while account "E" means=cr-dr inclosing bal

    as the same account "R" means=dr-cr inclosing bal

    how to make query friend..... suppose I m giving from one month to another month it display the month in ouput of account......

    The sample data you provided and the expected output don't match

    You have given 5 rows in the sample data and are expecting 6 rows in the output

    Even the amounts are not matching either

    Can you explain the logic a bit more for your expected result?


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • hey friend my actual table ve lakhs of data per month,after making the query,I m expexting ve to display account only one time per month and its has been summarize value of that month.

  • raghuldrag (7/27/2012)


    hey friend my actual table ve lakhs of data per month,after making the query,I m expexting ve to display account only one time per month and its has been summarize value of that month.

    The expected result that you have provided has complete duplicate rows which makes me feel that it is not summarized

    Provide us the expected output based on the sample data that you have provided so that we correlate both of them and provide you tested solution


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Hi,

    Above the my procedure query giving output,i m splitting the amount column has credit and debit,closing_bal column is sum of account is E=>debit-credit,

    account is R=>-debit+credit,

    OUTPUT:

    =================

    account PR_Mons credit debit closinbal

    =======================================

    E2674 apr 3000 00000 -3000

    E2674 apr 3900 00000 -3000

    E2674 apr 4000 00000 -4000

    E2674 apr 5000 00000 5000

    E2674 apr 0000 3900 3900

    E2674 apr 6300 00000 6300

    if account "E" appears on april month no of times means,ve to display the single time summarise value of one month

    is it possible???

  • raghuldrag (7/27/2012)


    Hi,

    Above the my procedure query giving output,i m splitting the amount column has credit and debit,closing_bal column is sum of account is E=>debit-credit,

    account is R=>-debit+credit,

    OUTPUT:

    =================

    account PR_Mons credit debit closinbal

    =======================================

    E2674 apr 3000 00000 -3000

    E2674 apr 3900 00000 -3000

    E2674 apr 4000 00000 -4000

    E2674 apr 5000 00000 5000

    E2674 apr 0000 3900 3900

    E2674 apr 6300 00000 6300

    if account "E" appears on april month no of times means,ve to display the single time summarise value of one month

    is it possible???

    The output you have provided is changing with every post and still doesn't match with the sample data you provided

    We want the expected output to be based on the sample data for better understanding of the issue

    From whatever I have understood, all that I can suggest you is to have a look at "GROUP BY" Clause Books Online.


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • for above that for E2674 account total value of credit, total value of debit for each month

  • Hello raghuldrag,

    I think your first need is help analyzing what the problem is.

    Your process proceed in three steps:

    - Summarize data from @table into #temp1.

    - Summarize data from @table into #tempr.

    - Join #temp1 and #tempr giving the result.

    You detected duplicate values for the E2674 account. So you should check the intermediate tables and answer these questions:

    - There are duplicate rows in #temp1 for the E2674 account?

    - There are duplicate rows in #tempr for the E2674 account?

    - There are duplicate rows for the E2674 account only in the result?

    The answer to these questions will hint you if the problem is in the first, the second or the third process. Please do it and tell us your results.

    Francesc

Viewing 13 posts - 1 through 12 (of 12 total)

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