madiaz91911
Technical User
I just cant seem to get the results I want. I tried going from simple to complex and its just killing me. Can you please help.
I have two tables:
Customers, Invoices
my invoices are for service so I only have one entry per invoice.
I want to get the total revenues. That is the total of each of the last invoice for each subscriber I have. I don't want info from those that are not current 'BillCurrent', or those that have free service 'PaidService'. I want to get one value "total revenues"
Customers
- CustomerID
- Company
- BillCurrent = Yes
- PaidService= Yes
Invoices
-InvoiceID
-InvDate
-InvAmount
-CustomerID
I tried to do a simple.. just give me the most recent of each customer (to try and build on it) but got nowhere.
Here is what I tried (I know its not what I described above)
select
InvDate,
InvAmount,
CustomerID
FROM Invoices
Where
InvDate IN
(select Max(InvDate) from Invoices Group By CustomerID)
Thanks...
I have two tables:
Customers, Invoices
my invoices are for service so I only have one entry per invoice.
I want to get the total revenues. That is the total of each of the last invoice for each subscriber I have. I don't want info from those that are not current 'BillCurrent', or those that have free service 'PaidService'. I want to get one value "total revenues"
Customers
- CustomerID
- Company
- BillCurrent = Yes
- PaidService= Yes
Invoices
-InvoiceID
-InvDate
-InvAmount
-CustomerID
I tried to do a simple.. just give me the most recent of each customer (to try and build on it) but got nowhere.
Here is what I tried (I know its not what I described above)
select
InvDate,
InvAmount,
CustomerID
FROM Invoices
Where
InvDate IN
(select Max(InvDate) from Invoices Group By CustomerID)
Thanks...