Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Feb 2010
    Posts
    3

    Unanswered: Challange to all Experts

    Hello Please Solve my Problem

    i have one table in Sql Server with Col

    TransectionID,CustomerID,Date,Time,DebitAmout
    1 1 1/1/2009 24,000
    2 1 4/1/2009 36,000
    3 2 1/1/2009 10,000

    i need to extract data with debit amount base records mean if customer told me please show my 50,000 rang invoices then return as

    TransectionID,CustomerID,Date,Time,DebitAmout
    1 1 1/1/2009 24,000
    2 1 4/1/2009 26,000

    mean extract data with value range return all invoice up to deposit Amount

    please suggest solution

    thanks in advance

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    The solution to your problem is an academic class in RDBMS theory and application. :-)))

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is pretty tricky. Can you supply DML & DDL please?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Clarify what is

    'show my 50,000 rang invoices'
    'up to deposit Amount'

    This may be your solution
    SELECT
    tbl.*
    FROM tbl
    JOIN
    (
    SELECT
    CustomerID,
    SUM(DebitAmout)/COUNT(*) DebitMean
    FROM tbl
    GROUPBY CustomerID
    ) tbl_2
    ON tbl.CustomerID = tbl_2.CustomerID
    -- AND tbl.DebitAmout <= tbl_2.DebitMean
    WHERE tbl.DebitAmout <= tbl_2.DebitMean
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  5. #5
    Join Date
    Feb 2010
    Posts
    3

    Please clear it that is not requried query

    problem is that we need to extract all Invoices those payments are not received yet i mean if customer have 4 invoices and invoices data is

    in other word we need to extract recovery data

    TransectionID,CustomerID,Date,DebitAmount,CreditAm ount
    1, 1,1/1/2009, 24,000,0
    2, 1,4/1/2009, 22,000,0
    3, 1,4/1/2009, 16,000,0
    4, 1,4/1/2009, 36,000,0
    4, 1,4/1/2009, 0, 50000

    and Customer Deposit only 50000

    then data show only those Invoices with out 50000 Recovered Amount in FIFO

    mean

    TransectionID,CustomerID,Date,DebitAmount,CreditAm ount, RecoveryBalance


    3, 1,4/1/2009, 12,000,0
    4, 1,4/1/2009, 36,000,0

    Please give us fruitful guide line
    Last edited by ksaddique; 02-04-10 at 10:57. Reason: save with out deciencee

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Oops! wrong forum.
    Last edited by n_i; 02-04-10 at 11:35.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Something like this, may be?
    Code:
    select 
      transactionid
      ,customerid
      ,date
      ,debitamount
    from
      (select 
          transactionid
          ,customerid
          ,date
          ,debitamount
          ,(select sum(debitamount) 
            from yourtable
    	where customerid = t1.customerid and date <= t1.date ) runningtotal
        from 
          (select 
             transactionid
             ,customerid
             ,date
             ,debitamount
           from yourtable) t1
      ) t2
    where
      runningtotal > (
        select sum(creditamount)
        from yourtable
        where customerid = t2.customerid
      )
    order by customerid, date
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not to poop on anyone's parade, because achieving this in SQL is no mean feat, however if this needs to scale well then your best bet would be to use CLR or even (horror of horrors) a cursor. I don't have the Itzik Ben Gan link to hand proving this but can dig it up if required.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by pootle flump View Post
    if this needs to scale well then your best bet would be to use CLR or even (horror of horrors) a cursor. I don't have the Itzik Ben Gan link to hand proving this but can dig it up if required.
    I could agree that in some circumstances that might be the case, however, I highly doubt that anyone can prove that procedures (CLR or T-SQL) generally perform (or scale) better than moderately complex queries. I'd be interested in checking the link you've mentioned.
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    Feb 2010
    Posts
    3

    Hello n_i that is not correct result

    Dear we need only pending Amounts against deposited Amount,but that provide all.

    if Data is
    TransectionID,CustomerID,Date,DebitAmount,CreditAm ount
    1, 1,1/1/2009, 24,000,0
    2, 1,4/1/2009, 22,000,0
    3, 1,4/1/2009, 16,000,0
    4, 1,4/1/2009, 36,000,0
    4, 1,4/1/2009, 0, 50000

    then Correct Result is
    TransectionID,CustomerID,Date,DebitAmount,CreditAm ount
    3, 1,4/1/2009, 12,000,0
    4, 1,4/1/2009, 36,000,0

    but that result is not comming

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ksaddique View Post
    Dear we need only pending Amounts against deposited Amount,but that provide all.
    I'm sure you can figure out subtraction yourself. If not, I will be happy to do your job in exchange for your paycheck.
    ---
    "It does not work" is not a valid problem statement.

  12. #12
    Join Date
    Feb 2009
    Posts
    51
    Quote Originally Posted by ksaddique View Post
    Dear we need only pending Amounts against deposited Amount,but that provide all.

    if Data is
    TransectionID,CustomerID,Date,DebitAmount,CreditAm ount
    1, 1,1/1/2009, 24,000,0
    2, 1,4/1/2009, 22,000,0
    3, 1,4/1/2009, 16,000,0
    4, 1,4/1/2009, 36,000,0
    4, 1,4/1/2009, 0, 50000

    then Correct Result is
    TransectionID,CustomerID,Date,DebitAmount,CreditAm ount
    3, 1,4/1/2009, 12,000,0
    4, 1,4/1/2009, 36,000,0

    but that result is not comming
    oops you selected wrong invoices..
    in FIFO it should be

    1, 1,1/1/2009, 24,000,0
    2, 1,4/1/2009, 22,000,0

    and apply the remaining 4000 to
    3, 1,4/1/2009, 16,000,0
    it will become
    3, 1,4/1/2009, 12,000,4000

    simply you need a good database design and inquiry routine
    nothing complex to achive
    .......
    She drank beer with Coke Cola
    and that's the way I like it...
    .......

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by n_i View Post
    I could agree that in some circumstances that might be the case, however, I highly doubt that anyone can prove that procedures (CLR or T-SQL) generally perform (or scale) better than moderately complex queries. I'd be interested in checking the link you've mentioned.
    I'll dig up the link. The key is in the Theta Join. As the number of rows in the base tables increase so the number of rows processed in the query (and therefore the cost) increases exponentially. When using iterative code the increase is linear. The power of set based processing in 99% of circumstances is the cost increases logarithmically to the number of rows in the base tables.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Here's the link - around page 11 is where the proof is presented.
    http://www.solidq.com/insidetsql/ove...lculations.doc
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Interesting reading, and they are making some good points, but still I doubt that one can directly translate algorithmic complexity into query performance metrics. Also, their benchmark results, without explanation of how they've been achieved, look rather like global warming diagrams...
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •