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