Results 1 to 1 of 1
  1. #1
    Join Date
    Nov 2006
    Posts
    43

    Unanswered: HELP WITH min(SERVICE)

    I’m looking for some help with my sql.
    • What I’m trying to do is ‘his
    • Take the Date of Service
    • Group all matching Master Account Number

    Have sql look at the group of Master Account = Then find the lowest Date of Service in at Master Account group.

    So the outcome would look like this. I have the sql I’ve been working with, and occasional it does find the min service date. Any help would be appreciated

    http://files.engineering.com/getfile..._with_SQL.docx

    SELECT
    COLLACCT@.RECNUM AS 'PSBAcctNumber',
    (SELECT ACCT.DATEOFSERVICE FROM COLLACCT@ AS ACCT WHERE ACCT.MASTERACCOUNT = MasterAccount ORDER BY ACCT.DATEOFSERVICE LIMIT 1 ) AS 'minDateofService',
    COLLACCT@.DATEOFSERVICE AS 'DateofService',
    COLLACCT@.PRINCIPALBALANCE AS 'PrincipalBalance',
    COLLACCT@.STATUSCODE AS 'StatusCode',
    COLLACCT@.COLLECTORNUMBER AS 'CollectorNumber',
    COLLDEBT@.RECNUM AS 'MasterAccount'
    FROM COLLACCT@
    INNER JOIN COLLDEBT@ ON COLLACCT@.MASTERACCOUNT = COLLDEBT@.RECNUM
    LEFT JOIN AUXECASCORE@ ON COLLDEBT@.RECNUM = AUXECASCORE@.COLLDEBTLINK
    LEFT JOIN COLLCLT@ ON COLLCLT@.RECNUM = COLLACCT@.CLIENTNUMBER
    LEFT JOIN AUXDEBTOR@ ON AUXDEBTOR@.DEBTORMASTER = COLLDEBT@.RECNUM
    LEFT JOIN AUXACCTMISC@ ON AUXACCTMISC@.COLLACCTLINK = COLLACCT@.RECNUM
    WHERE
    StatusCode IN ('TTL')
    AND CollectorNumber IN ('03')
    AND COLLCLT@.TYPEDEBT NOT IN ('7')
    GROUP BY
    PSBAcctNumber
    ORDER BY
    MasterAccount,
    PSBAcctNumber
    Last edited by CVLASAK; 12-18-15 at 20:05.

Posting Permissions

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