Help With A Filter

SQL Server 2000 - I need to apply a filter to a query I have and am not sure how to do it. I’m thinking perhaps a CASE WHEN but I’m not sure.

Although my query returns a number of fields, I’m primarily concerned with three in term of this filter. These three are: Customer Code, Fiscal Year End and Service Type. The service type will always be either a ‘F’ or a ‘P’ – Some customers will only have type ‘F’…some will only have type ‘P’, some will have both.

However, if the customer code and FYE match, and the customer for that FYE has both, I only want to bring back the record with the ‘F’, not both records. I do want both records if the customer code matches, but the FYE does not.

If someone could get me started down the path I’d be grateful. Here is a screen shot of what I’m talking about. Notice customer code 130 – same customer code, same FYE so in that case, I would only want the ‘F’ record returned.

You can group by CUSTOMERCODE and FYE

select customercode, fye, min(servicetype)
from table1
group by customercode, fye

min will return F if the customercode and fye are same and serivice types are F or P.

Thank you for your reply.

I had the field in my GROUP BY....I added the MIN to it, but it did not alter the results, I still got back records with both a 'F' and a 'P' for the same customer code same FYE.

My query

Code:

---

SELECT approve.custcode, customer.custname, approve.fye,
MIN(approve.service_type),
service.payment_due , service.payment_recvd
FROM serviceapproval approve
INNER JOIN servicerendered service ON approve.fye = service.fye
AND approve.custcode = service.custcode
AND approve.effective_date = service.effective_date
AND approve.service_type = service.service_type
INNER JOIN customers customer ON approve.custcode = customer.custcode
AND approve.effective_date = customer.effective_date
WHERE date_of_service BETWEEN '1/1/2009' AND '12/31/2009' 
GROUP BY approve.custcode, customer.custname,
approve.fye, approve.service_type,
service.payment_due , service.payment_recvd
ORDER BY approve.custcode

---

That is because you have approve.service_type in group by.

Ok, what should I have?

It is a field in more than one table so I would need to preface the field name with the proper alias.

One option is to start with your original query as an inline view, then group by customer code and fye.

select a.customercode,a.fye,min(a.servicetype)
from (your original query) as a
group by a.customercode,a.fye

This has potential I think. :)

But, when I do this

Code:

---

select a.custcode, a.fye, min(a.service_type)
from (SELECT approve.custcode, customer.custname, approve.fye,
MIN(approve.service_type),
service.payment_due , service.payment_recvd
FROM serviceapproval approve
INNER JOIN servicerendered service ON approve.fye = service.fye
AND approve.custcode = service.custcode
AND approve.effective_date = service.effective_date
AND approve.service_type = service.service_type
INNER JOIN customers customer ON approve.custcode = customer.custcode
AND approve.effective_date = customer.effective_date
WHERE date_of_service BETWEEN '1/1/2009' AND '12/31/2009') AS A 
group by a.customercode,a.fye

---

I get this

Quote:

---

Originally Posted by Error

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'approve' does not match with a table name or alias name used in the query.

---

First check if this query works

SELECT approve.custcode, customer.custname, approve.fye,
service.payment_due , service.payment_recvd
FROM serviceapproval approve
INNER JOIN servicerendered service ON approve.fye = service.fye
AND approve.custcode = service.custcode
AND approve.effective_date = service.effective_date
AND approve.service_type = service.service_type
INNER JOIN customers customer ON approve.custcode = customer.custcode
AND approve.effective_date = customer.effective_date
WHERE date_of_service BETWEEN '1/1/2009' AND '12/31/2009'

If that works, then do

select a.custcode, a.fye, min(a.service_type)
from (SELECT approve.custcode, customer.custname, approve.fye,
service.payment_due , service.payment_recvd
FROM serviceapproval approve
INNER JOIN servicerendered service ON approve.fye = service.fye
AND approve.custcode = service.custcode
AND approve.effective_date = service.effective_date
AND approve.service_type = service.service_type
INNER JOIN customers customer ON approve.custcode = customer.custcode
AND approve.effective_date = customer.effective_date
WHERE date_of_service BETWEEN '1/1/2009' AND '12/31/2009') AS A
group by a.customercode,a.fye