Max() query problem 1

[madiaz91911]

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

 

[gmmastros]

The problem with your query is that it's not taking the CustomerId in to account. I'm specifically referring to the subquery part.

There are several ways to approach this query. Personally, I would a common table expression. First I would build a query that returns the "inner most" data that I need.

Select CustomerId,
       InvAmount,
       InvDate,
       Row_Number() Over (Partition By CustomerId Order By InvDate DESC) As RowId
From   Invoices
Order By CustomerId, InvAmount

When you run the query shown above, you will see one row in the output for each row in the table. Take a look at the RowId column. This column will be a simple counter for each customer (the number resets when the customerid changes). Also notice that when the RowId column shows the value 1, this represents the newest invoice.

The real "magic" here is done with this line.

[!]Row_Number() Over (Partition By CustomerId Order By InvDate DESC) As RowId[/!]

Row_Number() basically says... give me an incrementing counter.
The Partition By part basically says... start over whenever CustomerId changes.
The Order By part basically says... Assign 1 to the largest InvDate, and work up from there.

Please run this and let me know if it produces the correct "intermediate" results. IF it does, I will show you how to do the next part.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[madiaz91911]

gmmastros,

Worked on the first execution. Yes, I can see how the rows with a Rowid=1 are the latest invoice. I need to get the last invoices where the customer BillCurrent column = 'Yes'. I don't care about the customers that now longer subscribe to the service. The magic line you added scares me.

 

[gmmastros]

If I'm not mistaken, the only thing left to do is to filter the rows such that only the ones with the RowId = 1 are returned. You can do that with the following query:

Select CustomerId,
       InvAmount,
       InvDate
From   (
       Select CustomerId,
              InvAmount,
              InvDate,
              Row_Number() Over (Partition By CustomerId Order By InvDate DESC) As RowId
       From   Invoices
       ) As AliasName
Where  AliasName.RowId = 1
Order By CustomerId, InvAmount

This still doesn't take in to account the criteria BillCurrent = 'Y'. To do this, we'll need to join to the Customers table, like this:

Select AliasName.CustomerId,
       AliasName.InvAmount,
       AliasName.InvDate
From   (
       Select CustomerId,
              InvAmount,
              InvDate,
              Row_Number() Over (Partition By CustomerId Order By InvDate DESC) As RowId
       From   Invoices
       ) As AliasName
       Inner Join Customers
         On AliasName.CustomerId = Customers.CustomerId
Where  AliasName.RowId = 1
       And Customers.BillCurrent = 'Y'
Order By CustomerId, InvAmount

This query can be accomplished without the "Magic" line, but it would be significantly more complex. Can you tell me what bothers you about this? If it's simply because, "You've never used it before", I can accept that and would encourage you to read up about it. There's a lot of information on the internet regarding this.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[madiaz91911]

Awsome! Thank you very much. I'll now read up on partions to learn more about it. I've run into that before. Thanks agian, you were very helpfull.