How to Write Query for the Below Requirement?

  • Hi everyone

    Can Any Body Help me how to write Query for the below requirement.

    I have Table in my Attachment

    I want a query to get

    1.party_code

    2.Total Inst_amt

    3.Total Paid_amt where paid_date<'27/11/2012'

    how to write Query?

    Can Any body please Help me.

  • Please use the below query.

    select party_code,sum(inst_amt) as Total_instamt,sum(paid_amt) as paid from @table1 where paid_date is null or paid_date <'2012-07-23'

    group by party_code[/b]

  • Thanks for your reply sir.

    But it was not fulfill my request.

    please check my attachment again sir.

    i am waiting for your reply

    Thanks

  • It isn't really clear what you are trying to do. Is this what you want:

    SELECT party_code, sum(inst_amt) as total_insts, sum(paid_amt) as total_paid

    FROM @table WHERE paid_date < '2012-11-23T00:00:00'

    GROUP BY party_code

    That would make commercial sense of a sort, but what you asked for didn't restrict the inst amounts to those before 2012-11-23, only the paid amounts, so the person who replied previously suggested code that didn't make that restriction. Presumably that was because he wanted to suggest code that did what you actually asked for rather than code that did something sensible - - thinking that people actually mean what they say is the cause of a lot of software problems.

    But even the above may not be what you wanted, because you may want to restrict the inst amounts based on inst_date rather than paid_date. That would be different code yet again.

    Of course as you haven't named the table you will have to change @table to be whatever the table is called for the code to make sense.

    Tom

  • perireddy.arikatla (9/21/2013)


    But it was not fulfill my request.

    select party_code, sum(inst_amt) as Total_instamt, sum(paid_amt) as paid

    from @table1

    where paid_date < '2012-11-27'

    group by party_code

    The previously posted query is very close what you asked for except for the NULL check and the wrong date. (Where does the 23rd keep coming from? Is it not < '2012-11-27')

    If your requirements are not being met, you need to restate then and your desired end results so that they make sense. Stating that "It does not work" is not a good description of the problem. A picture is a start, but posting DLL and easily consumable insert statements will go a long ways in us helping you.

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Thanks for your Reply sir.

    But With the below Query what you suggested

    SELECT party_code, sum(inst_amt) as total_insts, sum(paid_amt) as total_paid

    FROM @table WHERE paid_date < '2012-11-27'

    GROUP BY party_code

    with this query we will get inst_amt total also<'2012-11-27'. but irrespective of paid_date i want total inst_amt as instTotal But PaidTotal must be <Given Date i.e('2012-11-27')

    I Think I Explained This Time Clearly

    Any Body Please Provide me Solution.

    Thanks

  • perireddy.arikatla (9/21/2013)


    I Think I Explained This Time Clearly

    A bit more clearer:rolleyes:. But again, this would be easier to come up with a solution if DLL and insert statements are provided!

    This might work, though it won't be a speed demon.

    SELECT party_code, sum(inst_amt) as total_insts,

    (Select Sum(paid_amt) as total_paid

    From @table t2

    Where t2.paid_date < '2012-11-27' and t2.party_code = t1.party_code

    Group by t2.party_code) as TotalPaid

    FROM @table t1

    GROUP BY party_code

    EDIT: Only air code. Test it!

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Thanks For your Valuable Reply Sir.

    It works when i give tha Party_code in the where clause

    i.e

    SELECT party_code, sum(inst_amt) as total_insts,

    (Select Sum(paid_amt) as total_paid

    From party_payments

    Where paid_date < '11/27/2012' and party_code='HA20076'

    Group by party_code) as TotalPaid

    FROM party_payments where party_code='HA20076'

    GROUP BY party_code

    When i Run the query without giving party_code in where clause

    i.e

    SELECT party_code, sum(inst_amt) as total_insts,

    (Select Sum(paid_amt) as total_paid

    From party_payments

    Where paid_date < '11/27/2012'

    Group by party_code) as TotalPaid

    FROM party_payments

    GROUP BY party_code

    It was Giving Following Error

    Msg 512, Level 16, State 1, Line 1

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    Warning: Null value is eliminated by an aggregate or other SET operation.

    But My Requirement is Party Wise Total Inst_amt Value irrespective of Given Date and Total Paid< '11/27/2012' (i.e Given Date)

    Can Any body Help me Please

  • perireddy.arikatla (9/22/2013)


    When i Run the query without giving party_code in where clause

    i.e

    SELECT party_code, sum(inst_amt) as total_insts,

    (Select Sum(paid_amt) as total_paid

    From party_payments

    Where paid_date < '11/27/2012'

    Group by party_code) as TotalPaid

    FROM party_payments

    GROUP BY party_code

    It was Giving Following Error. . .

    Why are you not trying and testing out the code that was actually posted?? After I put together my OWN DLL and sample data, the query itself became trivial. NOTE: It is very close to the air code posted earlier.

    SELECT t1.party_code, sum(t1.inst_amt) as total_insts,

    (Select Sum(t2.paid_amt)

    From @party_payments t2

    WHERE t2.paid_date < '2012-11-27' and t2.party_code = t1.party_code

    Group by t2.party_code) as TotalPaid

    FROM @party_payments t1

    GROUP BY t1.party_code

    Since you are unwilling or unable to provide the requested information, DLL and insert statements, below is an example of what should be provided for any future questions.

    declare @party_payments table

    (

    party_code char(7),

    inst_amt int,

    inst_date datetime,

    paid_amt int,

    paid_date datetime

    )

    Insert @party_payments (party_code, inst_amt, inst_date, paid_amt, paid_date) values

    ('HA20076',3000,'2012-08-26 00:00:00.000', 3000, '2012-08-23 00:00:00.000')

    ,('HA20076',2917,'2012-09-15 00:00:00.000', 2900, '2012-09-17 00:00:00.000')

    ,('HA20076',2917,'2012-10-15 00:00:00.000', 2900, '2012-10-18 00:00:00.000')

    ,('HA20076',2917,'2012-11-15 00:00:00.000', 2900, '2012-11-27 00:00:00.000')

    ,('HA20076',2917,'2012-12-15 00:00:00.000', 2900, '2012-12-31 00:00:00.000')

    ,('HA20076',2917,'2013-01-15 00:00:00.000', 0, NULL)

    ,('HA20076',2917,'2013-02-15 00:00:00.000', 0, NULL)

    ,('HA20076',2917,'2013-03-15 00:00:00.000', 0, NULL)

    ,('HA20076',2917,'2013-04-15 00:00:00.000', 0, NULL)

    ,('HA20076',2917,'2013-05-15 00:00:00.000', 0, NULL)

    ,('HA20076',2917,'2013-06-15 00:00:00.000', 0, NULL)

    ,('HA20077',3000,'2012-08-26 00:00:00.000', 3000, '2012-08-23 00:00:00.000')

    ,('HA20077',3000,'2012-09-15 00:00:00.000', 3000, '2012-09-17 00:00:00.000')

    ,('HA20077',3000,'2012-11-15 00:00:00.000', 3000, '2012-11-27 00:00:00.000')

    Expected Output:

    party_codetotal_instsTotalPaid

    HA20076 32170 8800

    HA20077 9000 6000

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • perireddy.arikatla (9/22/2013)


    Thanks For your Valuable Reply Sir.

    It works when i give tha Party_code in the where clause

    i.e

    SELECT party_code, sum(inst_amt) as total_insts,

    (Select Sum(paid_amt) as total_paid

    From party_payments

    Where paid_date < '11/27/2012' and party_code='HA20076'

    Group by party_code) as TotalPaid

    FROM party_payments where party_code='HA20076'

    GROUP BY party_code

    When i Run the query without giving party_code in where clause

    i.e

    SELECT party_code, sum(inst_amt) as total_insts,

    (Select Sum(paid_amt) as total_paid

    From party_payments

    Where paid_date < '11/27/2012'

    Group by party_code) as TotalPaid

    FROM party_payments

    GROUP BY party_code

    It was Giving Following Error

    Msg 512, Level 16, State 1, Line 1

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    Warning: Null value is eliminated by an aggregate or other SET operation.

    But My Requirement is Party Wise Total Inst_amt Value irrespective of Given Date and Total Paid< '11/27/2012' (i.e Given Date)

    Can Any body Help me Please

    Yes, we can but you have to provide some better data instead of going back and forth with questions that make everyone guess. Help us help you. For future posts, please use the methods for posting readily consumable found in the article at the first link under "Helpful Links in my signature line below .

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

  • Thank u So Much Sir.

    It works for me.

    I am New to This forms.So I don't know how to Post.

    Thank u so much for all your patient replies.

    Thanks

  • Using CTE's, the logic of certain queries can be broken down in to simpler steps:

    [Code="sql"]

    WITH pd(zParty_Code, zSumPaid_Amt)

    AS (

    SELECT party_code, SUM(paid_amt)

    FROM dbo.tPartyData

    WHERE paid_date < '11/27/2012'

    GROUP BY party_code

    )

    SELECT party_code

    , SUM(inst_amt) AS Total_Inst_Amt

    , zSumPaid_Amt AS Total_Paid_Amt

    FROM dbo.tPartyData

    JOIN pd

    ON party_Code = zParty_Code

    GROUP BY party_code, zSumPaid_Amt

    [/code]

  • perireddy.arikatla (9/23/2013)


    I am New to This forms.So I don't know how to Post.

    Please don't use that as an excuse. You were told how to post 3 times, twice by myself (with an example) and once by Mr. Moden, who provided a link to an article on "How to Post".

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • LinksUp (9/22/2013)


    perireddy.arikatla (9/22/2013)


    When i Run the query without giving party_code in where clause

    i.e

    SELECT party_code, sum(inst_amt) as total_insts,

    (Select Sum(paid_amt) as total_paid

    From party_payments

    Where paid_date < '11/27/2012'

    Group by party_code) as TotalPaid

    FROM party_payments

    GROUP BY party_code

    It was Giving Following Error. . .

    Why are you not trying and testing out the code that was actually posted?? After I put together my OWN DLL and sample data, the query itself became trivial. NOTE: It is very close to the air code posted earlier.

    SELECT t1.party_code, sum(t1.inst_amt) as total_insts,

    (Select Sum(t2.paid_amt)

    From @party_payments t2

    WHERE t2.paid_date < '2012-11-27' and t2.party_code = t1.party_code

    Group by t2.party_code) as TotalPaid

    FROM @party_payments t1

    GROUP BY t1.party_code

    +1 to LinksUp for providing the DDL and sample data for this question.

    However, the solution above requires 3 scans of the @party_payments table (and R. Brush's CTE version requires 2 scans), which is trivial up to a few thousand rows or so, but after that can get expensive. This code gives the same result with only a single scan:

    SELECT party_code

    ,SUM(inst_amt) AS total_insts

    ,SUM(CASE WHEN paid_date < '2012-11-27' THEN paid_amt ELSE 0 END) AS TotalPaid

    FROM @party_payments

    GROUP BY party_code

    Jason Wolfkill

  • wolfkillj (9/23/2013)


    +1 to LinksUp for providing the DDL and sample data for this question.

    However, the solution above requires 3 scans of the @party_payments table (and R. Brush's CTE version requires 2 scans), which is trivial up to a few thousand rows or so, but after that can get expensive. This code gives the same result with only a single scan:

    SELECT party_code

    ,SUM(inst_amt) AS total_insts

    ,SUM(CASE WHEN paid_date < '2012-11-27' THEN paid_amt ELSE 0 END) AS TotalPaid

    FROM @party_payments

    GROUP BY party_code

    Your code is indeed more efficient!

    I just ran all 3 queries and checked the Execution plan on each one and I am only seeing 2 Table Scans on both my solution and the cte. Whereas your solution does only have 1. What are you looking at to determine 3 Table Scans?

    (See attached jpg for execution plan)

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

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