Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 157
  • Last Modified:

Calculation on multiple dates

Table:
Inv_No. , Inv_Date, Client, Type, Discount, 1st Pay, 1st Date, 2nd Pay, 2nd Date, 3rd Pay, 3rd Date

Now I want to calculate the amount according to dates. Suppose :

I have Invoice # 001 of 30 Dollar.
I received amount 10 on 1st Date(2/2/2003)
I received amount 10 on 2nd date(5/2/2003)

Another invoice # 002 of 50 Dollar
I received amount 25 on 1st Date(2/2/2003)
I received amount 10 on 2nd date(5/2/2003)


Now i want to calculate amount according to datewise, like
I have date 2/2/2003 and that total should be 25+10 = 35
I have date 5/2/2003 and that total should be 10+10 = 20

I dont know how to solve it, please help me.
Thanking you in advance
0
AbidAli
Asked:
AbidAli
  • 2
1 Solution
 
JennyRoCommented:


SELECT SUM(1stPay) FROM Table

where 1stPay is the name of the column you're wanting to add up and Table is the name of your table.

If 1stPay is always 2/2/2003 as it seems to be with your table, then you don't need to specify date.

But if you had a column called Date with different dates then it would be:

SELECT SUM(1stPay) FROM Table WHERE Date=2/2/2003
0
 
Garion789Commented:
Actually, if you have time, it almost seems that your Invoice_No and Invoice_Date should be in one table and then Pay_Amount and Pay_Date should be in a second table.  Think of the second table like a history table.  For example, what happens if it takes your customers 4 or 5 times to pay?  Anyway, let me know if you would like some assistance in setting up the relationships.  It truly would appear to be a much more efficient way of setting up your system.  Let me know your thoughts.

Thanks,
Greg
0
 
AbidAliAuthor Commented:
Thank you for your answer and comment:
but how can i give the criteria of one input date and give the result of three same dates. I dont want to specify any date like : WHERE Date=2/2/2003
0
 
JennyRoCommented:
I think this is what you mean:

If you have a table like this:

PaymentsTable
-------------
PayDate   Amount
-------   ------
2/2/2003  20
2/2/2003  50
5/2/2003  10
5/2/2003  10

Then this SQL:

SELECT PayDate,SUM(Amount) FROM PaymentsTable
GROUP BY PayDate

would return this:

PayDate   Amount
-------   ------
2/2/2003  70
5/2/2003  20

Thus no need for any actual dates given in code.

Hope this helps a bit more!


0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now