Questions

My name is Muhammad irfan ansari, from Pakistan. I am working on an Acc

+
0 Votes
Locked

My name is Muhammad irfan ansari, from Pakistan. I am working on an Acc

irfanansari85
My name is Muhammad irfan ansari, from Pakistan.
I am working on an Account maintain software.
This had some tables and store proc and views for crystal report.
I am making this software for a shop where some costumer comes and buy some things, and then they paid some money and some money not given by them, they said I will pay after some days.
Example:-
Original table or records are Bellow
Namee Buy _date Bought _some _cloth paid _money remaining _balance
Jon 10/08/2010 30000 10000 20000
Ali 23/08/2010 10000 2000 8000
Ali 14/08/2010 20000 12000 8000
Jon 8/08/2010 50000 20000 30000
Salvia 30/08/2010 100000 50000 50000
Salvia 19/08/2010 20000 20000 0
Ali 10/08/2010 10000 10000 0

And I want to record like this. Mean when I select one particular record so it should be like this as bellow.

Namee Buy_ date Bought _some_ cloth paid _money Remaining_ balance
Jon 10/08/2010 30000 10000 20000
Jon 11/08/2010 50000 20000 30000

Total reaming balance
50000


Please tell me how it possible?
I tried it as in SQL server query.
Select name,buy_date,sum(Remaining _balance) from table1
where name=’jon’
Group by nmaee,buy_date
When I tried this so result as bellow, see

Namee Buy_ date Bought _some_ cloth paid _money Remaining_ balance
Jon 10/08/2010 30000 10000 20000
Jon 11/08/2010 50000 20000 30000

Not give total as this is
Namee Buy_ date Bought _some_ cloth paid _money Remaining_ balance
Jon 10/08/2010 30000 10000 20000
Jon 11/08/2010 50000 20000 30000

Total reaming balance
50000
  • +
    0 Votes
    drowningnotwaving

    Then you bill the money, collect the money, pay your tax, put it in the bank, reconcile your bank account, produce your monthly account statements, hide some cash from you wife, spend the money, get drunk and have a hangover on Saturday morning.

    And you never have to worry about Reaming Your Balance ever again.

    You will probably find by searching on the internet you can download a simple, free accounting package that works in Pakistan.

    Or use a spreadsheet like 200,000,000 small businesses around the world. Much easier than SQL.

    +
    0 Votes
    santeewelding

    You grow tired of this.

    How tired?

    +
    0 Votes
    drowningnotwaving

    Just up to my armpits in biz, at present. Available time is less. Like you, but obviously from different directions, I am more interested in cause rather than symptom. Not so much to comment upon lately.

    One thing I can be quite specific upon is that anyone who chooses SQL to develop their own basic business transactional solution, have rockee in headee.

    It's a laff! :)

    +
    0 Votes
    Tony Hopkinson

    unless it's extraordinarily simple and never ever ever going to change.

    There again a document / object based solution isn't for the faint hearted either.

    Unfortunately far too many confuse a database table with a rectangular collection of cells....

    And besides I'd want more than a thumb for the book length answers to this sort of question...

    Money would be good...

    +
    0 Votes
    drowningnotwaving

    Perhaps this is an assignment for a SQL class. Perhaps Muhammad is an 8 year old prodigy, working on his write-vs-buy thesis.

    One suspects that at whatever end of the pendulum, hard currency may be difficult to come by!

    I promise to split mine with you, Tony. Roolly troolly.

    :)

    +
    0 Votes
    Tony Hopkinson

    from a maintenance contract....

    He's doing well on that front.

    I'd be adding zeros to the quote after a quick glance at the sql...

    +
    0 Votes
    santeewelding

    One of the prime, original reasons I began investigating this place and its people (like yourself): how can I turn all this to business advantage.

    I discovered early on...zilch. So, I persist in my analog, antiquated ways making money. Speaking of which, I am experiencing spells of business lately, too. Cuts into my time here as it does yours. I hope that "up to [the] armpits" is remunerative?

    It has been for me. Almost like the old days. The bonus being, still time to yammer here.

    +
    0 Votes
    drowningnotwaving

    ... and I hope this is the harbinger of filthy lucre.

    Decisions are taking longer, with little impetus for the client to meet "deadlines".

    Funny world - "Old" systems aren't really that old. They work. They integrate. They're used. They work on modern platforms with strong database backends. Why change?

    Clients are getting wiser on the cost impact of the New. Realising that measuring a benefit for something that their existing system can perform, isn't really a benefit at all. Realising that the smaller, incremental benefits of modern solutions are actually very expensive and require acute validation.

    There is a huge cost benefit of ignorance: I know your system can do it, but you don't know. Is it immoral to present an alternative solution when someone wants to change and wants to spend??

    But for all of that, the list of people and companies that wish to spend money on these elemental increments continues to grow. We may or may not be doing a good job, but the message is still getting out.

    It's my goal in life one day to be a Client. Well, okay, one of the lesser business hours only goals when you put it like that ....

    Was laffing today with gents over lunch - 26 years ago #1 requirement was "better business reporting". Hasn't really cnanged. Don't think it will for next 20 years 'til I don't give a fliying fark. Who knows ...

    +
    0 Votes
    Tony Hopkinson

    A query either returns a single variable or a recordset.
    That means you can have two separate quesries, one for transactions and one for total, or one result set where the total is repeated for each record.

    You've got the transactions.

    Total would be
    Select Sum(Remaining_Balance) as [Total emaining balance] from WhateverTableName Where Namee_Buy = 'Jon'

    If you wanted one record set then
    something like

    Select t.*,tt.RemainingAmount From Transactions t
    left join (Select [Namee Buy], SUM(Remaining) as RemainingAmount From Transactions Group By [Namee Buy]) tt
    On t.[Namee Buy] = tt.[Namee Buy]
    Where t.[Namee Buy] = 'Jon'

    would give

    Namee Buy _date Bought Paid Remaining RemainingAmount
    --------- ----------------------- ---------------------- ---------------------- ---------------------- ----------------------
    Jon 2010-08-10 00:00:00.000 30000 10000 20000 50000
    Jon 2010-08-08 00:00:00.000 50000 20000 30000 50000

    PS Tell me you are not using [Namee Buy] as your key, what happens if you have more than one Ali?

    PPS
    Notice the date format I've used, It won't get confused on a system set up as mm-dd-yyyy.

    PPPS
    Why don't you pick legal names for the columns, spaces are a pain in the arse as are underscores, but iof you are going to use one of them, do that one not a mixture.

    Big things are built from little things, get the little things wrong and .....

    +
    0 Votes

    ...names are not important here in TR, but marks are. Please remember to mark Tony's reply as the answer, if it works for you. Then we can bring back the Tech Q&A as it was originally meant to be.

    +
    0 Votes
    Tony Hopkinson

    join would work with a query in parentheses, now I am, so I learnt something even if the OP hasn't. The sum query had to be the inner, because of the group by but other than it worked as I thought it should, which can be a bit of a departure with SQL2008.

    Good job really, if I was answering moderately complex database query questions for thumbs, I'd have given it up as a bad job ages ago.

    +
    0 Votes

    ...I think it's the principle of the thing that drives us. The fact that knowledge is a thing to be shared.

    The fact that you went out of your way to answer the question, and with a sensible answer, means that at the very least a thumbs-up is called for.

    Sums up broken Britain, doesn't it? No one does it for the principle anymore. You, me and a few others on TR are the only ones left.

    We know who we are people!

    (Deduct 2 cents.)

    +
    0 Votes
    Tony Hopkinson

    The polite ones make up for the ingrates.

    Hmmm off back to your discussion.

  • +
    0 Votes
    drowningnotwaving

    Then you bill the money, collect the money, pay your tax, put it in the bank, reconcile your bank account, produce your monthly account statements, hide some cash from you wife, spend the money, get drunk and have a hangover on Saturday morning.

    And you never have to worry about Reaming Your Balance ever again.

    You will probably find by searching on the internet you can download a simple, free accounting package that works in Pakistan.

    Or use a spreadsheet like 200,000,000 small businesses around the world. Much easier than SQL.

    +
    0 Votes
    santeewelding

    You grow tired of this.

    How tired?

    +
    0 Votes
    drowningnotwaving

    Just up to my armpits in biz, at present. Available time is less. Like you, but obviously from different directions, I am more interested in cause rather than symptom. Not so much to comment upon lately.

    One thing I can be quite specific upon is that anyone who chooses SQL to develop their own basic business transactional solution, have rockee in headee.

    It's a laff! :)

    +
    0 Votes
    Tony Hopkinson

    unless it's extraordinarily simple and never ever ever going to change.

    There again a document / object based solution isn't for the faint hearted either.

    Unfortunately far too many confuse a database table with a rectangular collection of cells....

    And besides I'd want more than a thumb for the book length answers to this sort of question...

    Money would be good...

    +
    0 Votes
    drowningnotwaving

    Perhaps this is an assignment for a SQL class. Perhaps Muhammad is an 8 year old prodigy, working on his write-vs-buy thesis.

    One suspects that at whatever end of the pendulum, hard currency may be difficult to come by!

    I promise to split mine with you, Tony. Roolly troolly.

    :)

    +
    0 Votes
    Tony Hopkinson

    from a maintenance contract....

    He's doing well on that front.

    I'd be adding zeros to the quote after a quick glance at the sql...

    +
    0 Votes
    santeewelding

    One of the prime, original reasons I began investigating this place and its people (like yourself): how can I turn all this to business advantage.

    I discovered early on...zilch. So, I persist in my analog, antiquated ways making money. Speaking of which, I am experiencing spells of business lately, too. Cuts into my time here as it does yours. I hope that "up to [the] armpits" is remunerative?

    It has been for me. Almost like the old days. The bonus being, still time to yammer here.

    +
    0 Votes
    drowningnotwaving

    ... and I hope this is the harbinger of filthy lucre.

    Decisions are taking longer, with little impetus for the client to meet "deadlines".

    Funny world - "Old" systems aren't really that old. They work. They integrate. They're used. They work on modern platforms with strong database backends. Why change?

    Clients are getting wiser on the cost impact of the New. Realising that measuring a benefit for something that their existing system can perform, isn't really a benefit at all. Realising that the smaller, incremental benefits of modern solutions are actually very expensive and require acute validation.

    There is a huge cost benefit of ignorance: I know your system can do it, but you don't know. Is it immoral to present an alternative solution when someone wants to change and wants to spend??

    But for all of that, the list of people and companies that wish to spend money on these elemental increments continues to grow. We may or may not be doing a good job, but the message is still getting out.

    It's my goal in life one day to be a Client. Well, okay, one of the lesser business hours only goals when you put it like that ....

    Was laffing today with gents over lunch - 26 years ago #1 requirement was "better business reporting". Hasn't really cnanged. Don't think it will for next 20 years 'til I don't give a fliying fark. Who knows ...

    +
    0 Votes
    Tony Hopkinson

    A query either returns a single variable or a recordset.
    That means you can have two separate quesries, one for transactions and one for total, or one result set where the total is repeated for each record.

    You've got the transactions.

    Total would be
    Select Sum(Remaining_Balance) as [Total emaining balance] from WhateverTableName Where Namee_Buy = 'Jon'

    If you wanted one record set then
    something like

    Select t.*,tt.RemainingAmount From Transactions t
    left join (Select [Namee Buy], SUM(Remaining) as RemainingAmount From Transactions Group By [Namee Buy]) tt
    On t.[Namee Buy] = tt.[Namee Buy]
    Where t.[Namee Buy] = 'Jon'

    would give

    Namee Buy _date Bought Paid Remaining RemainingAmount
    --------- ----------------------- ---------------------- ---------------------- ---------------------- ----------------------
    Jon 2010-08-10 00:00:00.000 30000 10000 20000 50000
    Jon 2010-08-08 00:00:00.000 50000 20000 30000 50000

    PS Tell me you are not using [Namee Buy] as your key, what happens if you have more than one Ali?

    PPS
    Notice the date format I've used, It won't get confused on a system set up as mm-dd-yyyy.

    PPPS
    Why don't you pick legal names for the columns, spaces are a pain in the arse as are underscores, but iof you are going to use one of them, do that one not a mixture.

    Big things are built from little things, get the little things wrong and .....

    +
    0 Votes

    ...names are not important here in TR, but marks are. Please remember to mark Tony's reply as the answer, if it works for you. Then we can bring back the Tech Q&A as it was originally meant to be.

    +
    0 Votes
    Tony Hopkinson

    join would work with a query in parentheses, now I am, so I learnt something even if the OP hasn't. The sum query had to be the inner, because of the group by but other than it worked as I thought it should, which can be a bit of a departure with SQL2008.

    Good job really, if I was answering moderately complex database query questions for thumbs, I'd have given it up as a bad job ages ago.

    +
    0 Votes

    ...I think it's the principle of the thing that drives us. The fact that knowledge is a thing to be shared.

    The fact that you went out of your way to answer the question, and with a sensible answer, means that at the very least a thumbs-up is called for.

    Sums up broken Britain, doesn't it? No one does it for the principle anymore. You, me and a few others on TR are the only ones left.

    We know who we are people!

    (Deduct 2 cents.)

    +
    0 Votes
    Tony Hopkinson

    The polite ones make up for the ingrates.

    Hmmm off back to your discussion.