Last date ordered 1

[dwg23]

Hello,
I am attempting to pull information from two different tables. one of which has the date an item was ordered.
What I am trying to do is get just the Item number, schedule Id, Customer Number, customer name and the last invoice date.
All of the prior are in table 1 with the exception of Customer name and invoice date which are in table two.
Where I am having the problem is getting just the last invoice date not all of them as there are close to a million records to go through.

Please Help!

Thanks,
Dwg23

 

[gmmastros]

Do you store just the date, or is it a DateTime value?

I ask because there are multiple ways to write the query. If you are storing just the date, and it's possible for a customer to have purchased multiple times within the same day, then we'll need to take another approach.

-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

 

[gmmastros]

It would be helpful if you posted some sample data and expected results.

-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

 

[dwg23]

gmmastros,
It is just a date, I think I figured it out. I was not using Group By.
Here is my statement now an it seems to work.

select dci.item, dci.scheduleID, dci.customer, c.customername, max(c.invoicedate) as 'Latest'
from tblsoDiscountCustomerItem as dci inner join vwsninvoicelinesales as c on dci.customer = c.customer group by
dci.item, dci.scheduleID, dci.customer, c.customername

I am curious as to why Latest shows up in the results even though it is not in the group by.

Thanks,
dwg23

 

[dwg23]

Here are the results.
item scheduleID customer customername Latest
08F23C2140ACF 10 000014-0 Gaylord Industries 05/17/13

Thank again,
Dwg23

 

[gmmastros]

It shows up in the output because it appears in the select clause.

When you use a group by query, you are guaranteed to get distinct values for those in the group by clause. More specifically, when you have multiple columns in the group by, you will get a row in the output for each distinct *combination* of columns in the group by.

When I write queries like this, I try to write it in a way that minimizes the number of columns in the group by clause. If you are careful, and write the query properly, you will get the same results but the query will take less time to execute.

In this case, I would first construct a query that returns the last invoice date for each customer, like this:

Select Customer,
       CustomerName,
       Max(InvoiceDate) As [Latest]
From   vwsninvoicelinesales
Group By Customer, CustomerName

This will return a single row for each customer along with the max invoice date. I would then combine this with the other query using a derived table technique or a common table expression. Like this:

Derived table method

{code]
select dci.item,
dci.scheduleID,
dci.customer,
LastInvoiceDate.customername,
LastInvoiceDate.[Latest]
from tblsoDiscountCustomerItem as dci
Inner Join (
Select Customer,
CustomerName,
Max(InvoiceDate) As [Latest]
From vwsninvoicelinesales
Group By Customer, CustomerName
) As LastInvoiceDate
On dci.customer = LastInvoiceDate.customer
[/code]

Common table expression method:

; With LastInvoiceDate As
(
  Select Customer,
         CustomerName,
         Max(InvoiceDate) As [Latest]
  From   vwsninvoicelinesales
  Group By Customer, CustomerName
)
select  dci.item, 
        dci.scheduleID, 
        dci.customer, 
        LastInvoiceDate.customername, 
        LastInvoiceDate.[Latest]
from    tblsoDiscountCustomerItem as dci 
        Inner Join LastInvoiceDate
          On dci.customer = LastInvoiceDate.customer

Both of the queries I show will return the same data. Both should execute ever so slightly faster than your method because there are less columns in the group by clause.

Please understand that I am not trying to say that your method is wrong. I'm not. I'm trying to help you to understand while also showing an alternate method that may be a little better.


-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

 

[gmmastros]

I messed up the code block in my previous post.

select dci.item, 
       dci.scheduleID, 
       dci.customer, 
       LastInvoiceDate.customername, 
       LastInvoiceDate.[Latest]
from   tblsoDiscountCustomerItem as dci 
       Inner Join (
         Select Customer,
                CustomerName,
                Max(InvoiceDate) As [Latest]
         From   vwsninvoicelinesales
         Group By Customer, CustomerName
         ) As LastInvoiceDate
         On dci.customer = LastInvoiceDate.customer

-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

 

[dwg23]

I totally understand and appreciate the information. I always want to try to do things better.
Now I am back to the drawing board as my query only showed the last time the customer bought something not the last time the bought a particular part.

I did find the below in another query that shows the last date a particular part was sold, so I will try to incorporate that into my new query.

The object to all of this is that we have certain customers that get special pricing on certain parts. The owner wants to know when the customers last bought said parts to see if they are worthy of continuing to get the special pricing.

Here is the code from another query I found.

(select max(ar.InvoiceDate)
from tblarInvoice ar inner join tblarInvoiceLine ari on ar.InvoiceNumber = ari.InvoiceNumber
Where sp.Item in (ari.Item)) as 'Last Date Sold'

Thanks,
Dwg23

 

[gmmastros]

It's not clear to me.

Do you want a row in the output for each item ordered by each customer and the date that the item was last ordered?

-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

 

[dwg23]

correct!

 

[gmmastros]

I'm afraid you'll need to provide more information. Specifically, what columns exist in your tables and how they are linked together.

select ar.Customer,
       ari.Item,
       max(ar.InvoiceDate) As [LastDateSold]
From   tblarInvoice ar 
       inner join tblarInvoiceLine ari 
         on ar.InvoiceNumber = ari.InvoiceNumber
Group By ar.Customer,
       ari.Item

The query above assumes that there is a customer column in the tblarInvoice table. You may need to adjust this query until it returns the correct information. Once you get this query running properly, then replace it in the queries I showed earlier.... the CTE or Derived Table query.


-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

 

[dwg23]

That's what I needed! It is working great now.

Thanks!
Dwg23