Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2005
    Posts
    6

    Angry Unanswered: MSQuery quandary

    Ok,

    I need to pull out of the database the oldest invoice for each of my clients. Yet i think either or both of myself and MSQuery criteria are limited in this capacity.

    please help.

    Effectively

    ClientA inv00001
    ClientA inv00004
    ClientA inv00007
    ClientA inv00008
    ClientB inv00002
    ClientB inv00003
    ClientB inv00006
    ClientC inv00005
    ETC...

    Where i need to pull out just the bold records... sql script syntax or MSQuery criteria advice is appreciated.

    cheers,
    Oosters

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select client, min(invoice) as min_invoice from daTable group by client
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2005
    Posts
    6

    Angry

    Darn the socks of my bosses, they've shot me down again.

    oldest invoice won't do now...

    Also not sure if i understood you, please elaborate...


    Here's my SQL with your remarks...

    SELECT ClientView.AccountManagerCode, ClientView.Code, Min(InvoiceView.InceptionDate) AS 'Min of InceptionDate', InvoiceView.InvoicePrintDate
    FROM pno.dbo.ClientView ClientView, pno.dbo.InvoiceView InvoiceView, pno.dbo.PolicyView PolicyView
    WHERE ClientView.ClientID = PolicyView.ClientID AND InvoiceView.PolicyID = PolicyView.PolicyID AND ((ClientView.FSGDate Is Null) AND (InvoiceView.Category='n/b') AND (InvoiceView.InceptionDate>{ts '2003-12-31 00:00:00'}))
    GROUP BY ClientView.AccountManagerCode, ClientView.Code, InvoiceView.InvoicePrintDate
    ORDER BY ClientView.AccountManagerCode


    It's not working, as above effectively i need the earliest New Business Invoice since 1/1/2004 for each client, and to cross reference those invoices with their client codes to determine whether an FSG has been issued or not.

    Presently the script is still pulling multiple invoices for some clients. My last resort is to run the final filter through a macro, but i'd prefer a one step option.

    Thankyou for your advice so far.

    Oosters

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's probably the GROUP BY that isn't working for you, you have more columns in there than what you originally told us

    try this --
    Code:
    SELECT C.AccountManagerCode
         , C.Code
         , I.InceptionDate AS Min_InceptionDate
         , I.InvoicePrintDate
      FROM pno.dbo.ClientView ClientView as C
         , pno.dbo.InvoiceView InvoiceView as I
         , pno.dbo.PolicyView PolicyView as P
     WHERE C.ClientID = P.ClientID 
       AND I.PolicyID = P.PolicyID 
       AND C.FSGDate Is Null 
       AND I.Category = 'n/b' 
       AND I.InceptionDate
         = ( select min(InceptionDate)
               from InvoiceView
              where PolicyID = P.PolicyID
                and Category = 'n/b'
                and InceptionDate
                  > {ts '2003-12-31 00:00:00'} )
    GROUP 
        BY C.AccountManagerCode
         , C.Code
         , I.InvoicePrintDate
    ORDER 
        BY C.AccountManagerCode
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2005
    Posts
    6
    Something's not right...

    I copied and pasted your statements, and reviewed the logic. It all looks good, but when i run it....

    "SQL query cannot be represented graphically. Continue anyway?"
    [OK, Cancel, Help]

    so i click ok and pray....

    "Column InvoiceView.InceptionDate is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Atatement(s) could not be prepared."
    [OK, Help]

    So, not knowing anything about aggregate functions, i left that bit alone and added InvoiceView.InceptionDate to the GROUP BY clause, just after ClientView.Code

    This permitted the statement to display a result set, however i'm still getting multiple invoices for each client.

    Understandable if you can't help me any further, it's hard to teach an old noobie new tricks afterall. So thanks heaps for taking the time to review my Quandary nonetheless.

    kind regards,
    Oosters

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my sincere apologies

    i was a bit too quick on the copy/paste

    by introducing the subquery, i did away with the need for the GROUP BY, but i forgot to actually remove the GROUP BY from the query!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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