How to WHERE on a field from a Sub Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to WHERE on a field from a Sub Query

Author

Topic

psetterfield
Starting Member

7 Posts

Posted - 2011-11-17 : 06:04:19

Hello.

I have a query as follows. I am running a Sub Query to look up a field. I have released I can ORDER BY on this field, but not WHERE on it. Can anyone help please?
The idea is I want to select all customers who have had an invoice between 2 dates. I am finding the last invoice date by doing a SELECT Max Invoice_Date from the Invoices table (called Services).

SELECT c.Customer, c.Firstname, c.LastName, c.Telephone,
c.Mobile, c.Email, Last_Invoiced =
(SELECT Max(Invoice_Date) FROM Services s WHERE s.Customer = c.Customer)
FROM Customers c
--WHERE Last_Invoiced BETWEEN '2011-03-01' AND '2011-11-16'
ORDER BY Last_Invoiced

Sachin.Nand

2937 Posts

Posted - 2011-11-17 : 06:18:56

[code]
SELECT c.Customer, c.Firstname, c.LastName, c.Telephone,
c.Mobile, c.Email, Last_Invoiced =
(SELECT Max(Invoice_Date) FROM Services s WHERE s.Customer = c.Customer and Last_Invoiced BETWEEN '2011-03-01' AND '2011-11-16')
FROM Customers c
ORDER BY Last_Invoiced
[/code]

PBUH

psetterfield
Starting Member

7 Posts

Posted - 2011-11-17 : 07:04:51

Thanks for your reply.

I thought about this, but the problem with this approach is that I will get all customers listed still, even if they wern't invoiced in the date range specified. The Last_Invoiced column will just be NULL.
I would like to only return customers who have had an invoice between the dates.
I thought that as ORDER BY worked, I would try to use HAVING but it still says it doesn't know the column Last_Invoiced.

quote:

Originally posted by Sachin.Nand


SELECT c.Customer, c.Firstname, c.LastName, c.Telephone, 
c.Mobile, c.Email, Last_Invoiced = 
(SELECT Max(Invoice_Date) FROM Services s WHERE s.Customer = c.Customer and Last_Invoiced BETWEEN '2011-03-01' AND '2011-11-16') 
FROM Customers c 
ORDER BY Last_Invoiced

PBUH

Sachin.Nand

2937 Posts

Posted - 2011-11-17 : 10:39:48

[code]
;with cte
as
(
SELECT Max(Invoice_Date)Last_Invoiced,Customer FROM Services Where Last_Invoiced BETWEEN '2011-03-01' AND '2011-11-16'
Group by Customer
)
SELECT c.Customer, c.Firstname, c.LastName, c.Telephone,
c.Mobile, c.Email, cte.Last_Invoiced FROM Customers c inner join cte on c.customer=cte.customer
ORDER BY Last_Invoiced
[/code]

PBUH

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-17 : 10:50:02

Here are some others ways that I think work (hard to test without sample data) and use set-based approaches:

SELECT 
    c.Customer, 
    c.Firstname, 
    c.LastName, 
    c.Telephone, 
    c.Mobile, 
    c.Email, 
    s.Last_Invoiced 
FROM 
    Customers AS c 
INNER JOIN    
    (
        SELECT 
            Customer,
            Max(Invoice_Date) AS Last_Invoiced
        FROM 
            Services
        WHERE 
            Invoice_Date BETWEEN '2011-03-01' AND '2011-11-16'
        GROUP BY
            Customer
    ) AS s
    ON s.Customer = c.Customer
ORDER BY 
    s.Last_Invoiced

-- OR
SELECT
    Customer, 
    Firstname, 
    LastName, 
    Telephone, 
    Mobile, 
    Email, 
    Last_Invoiced
FROM
    (    
        SELECT 
            c.Customer, 
            c.Firstname, 
            c.LastName, 
            c.Telephone, 
            c.Mobile, 
            c.Email, 
            s.Invoice_Date AS Last_Invoiced,
            ROW_NUMBER() OVER (PARTITION BY c.Customer ORDER BY s.Invoice_Date DESC) AS RowNum
        FROM 
            Customers AS c 
        INNER JOIN
            Services AS s
            ON s.Customer = c.Customer
        WHERE 
            s.Invoice_Date BETWEEN '2011-03-01' AND '2011-11-16'
    ) AS t
WHERE    
    RowNum = 1

-- Or

SELECT 
    c.Customer, 
    c.Firstname, 
    c.LastName, 
    c.Telephone, 
    c.Mobile, 
    c.Email, 
    MAX(s.Invoice_Date) AS Last_Invoiced
FROM 
    Customers AS c 
INNER JOIN
    Services AS s
    ON s.Customer = c.Customer
WHERE 
    s.Invoice_Date BETWEEN '2011-03-01' AND '2011-11-16'
GROUP BY
    c.Customer, 
    c.Firstname, 
    c.LastName, 
    c.Telephone, 
    c.Mobile, 
    c.Email

EDIT: It looks like my first query is basically doing what Sachin.Nand's query is doing minus the CTE..

psetterfield
Starting Member

7 Posts

Posted - 2011-11-17 : 11:15:27

Thanks for the responses. I have researched some more and realise that I cannot get a simple SQL query to do this. Something to do with the WHERE clause being evaluated before the SELECT clause.
I have decided that to keep the SQL looking nice and simple that I will SELECT into a temporary table first, and then I will do a SELECT on that table when the Last_Invoiced date will then exist as a column.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-17 : 11:25:29

quote:
Originally posted by psetterfield

Thanks for the responses. I have researched some more and realise that I cannot get a simple SQL query to do this. Something to do with the WHERE clause being evaluated before the SELECT clause.
I have decided that to keep the SQL looking nice and simple that I will SELECT into a temporary table first, and then I will do a SELECT on that table when the Last_Invoiced date will then exist as a column.

I think you're not giving us full picture. As per your requirement, you're good to go with any of suggestions above. Probably you can show us with some sample data what you're trying to achieve.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources