Results 1 to 9 of 9

Thread: Help With A Filter

  1. #1
    Join Date
    Dec 2009
    Posts
    79

    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. Im thinking perhaps a CASE WHEN but Im not sure.

    Although my query returns a number of fields, Im 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 Fsome 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 Id be grateful. Here is a screen shot of what Im talking about. Notice customer code 130 same customer code, same FYE so in that case, I would only want the F record returned.
    Attached Images Attached Images

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    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.

  3. #3
    Join Date
    Dec 2009
    Posts
    79
    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.

  4. #4
    Join Date
    Dec 2009
    Posts
    79
    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
    Last edited by Moe1950; 03-01-2010 at 01:08 PM.

  5. #5
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    That is because you have approve.service_type in group by.

  6. #6
    Join Date
    Dec 2009
    Posts
    79
    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.

  7. #7
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    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

  8. #8
    Join Date
    Dec 2009
    Posts
    79
    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.

  9. #9
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •