SQL Server DISTINCT OR GROUP BY WITH TEMP TABLES

Question

I have a kind of complicated issue. The idea is, I need the distinct Serv_Acct from a table (This is easy and I already have that):

DECLARE 

 @CustomerID int = 8,
 @UtilityCompanyID int = 1


    SELECT DISTINCT SERV_ACCT, MAX(INV_DATE)
    FROM tblAPSData

    WHERE SERV_ACCT NOT IN (SELECT ServiceAccount
                FROM tblMEP_Meters
                JOIN tblMEP_Sites
                ON tblMEP_Meters.SiteID = tblMEP_Sites.ID

                JOIN tblMEP_Projects
                ON tblMEP_Projects.ID = tblMEP_Sites.ProjectID

                WHERE CustomerID = 8
                AND Type = 1
                )

    AND ACCOUNT IN (SELECT AccountNumber
                    FROM tblMEP_CustomerAccounts
                    WHERE CustomerID = @CustomerID
                    AND UtilityCompanyID = @UtilityCompanyID)

    AND INV_DATE > DATEADD(month, -6, getdate())
    GROUP BY SERV_ACCT

WHEN I run this I get 16 rows:

SERV_ACCT   (No column name)
0289S61288  2013-06-12 00:00:00.000
0492S90281  2013-06-12 00:00:00.000
1303S90280  2013-06-12 00:00:00.000
1435S01282  2013-06-12 00:00:00.000
1440S13289  2013-06-24 00:00:00.000
1548S00286  2013-06-12 00:00:00.000
2498S21288  2013-06-12 00:00:00.000
5384S92284  2013-06-24 00:00:00.000
5538S21284  2013-06-12 00:00:00.000
6109S12286  2013-06-12 00:00:00.000
7358S00281  2013-06-12 00:00:00.000
7488S22289  2013-06-12 00:00:00.000
8058S12287  2013-06-12 00:00:00.000
9058S00288  2013-06-12 00:00:00.000
9168S00282  2013-06-12 00:00:00.000
9645S21281  2013-06-12 00:00:00.000

Now, the part I am not getting is, with each service account there is an account number and some service accounts have more than one account number, I only want THE Service Acount with one account number which is from latest date. See the problem when I try to do this way:

DECLARE 

 @CustomerID int = 8,
 @UtilityCompanyID int = 1


    SELECT DISTINCT SERV_ACCT, ACCOUNT, MAX(INV_DATE)
    FROM tblAPSData

    WHERE SERV_ACCT NOT IN (SELECT ServiceAccount
                FROM tblMEP_Meters
                JOIN tblMEP_Sites
                ON tblMEP_Meters.SiteID = tblMEP_Sites.ID

                JOIN tblMEP_Projects
                ON tblMEP_Projects.ID = tblMEP_Sites.ProjectID

                WHERE CustomerID = 8
                AND Type = 1
                )

    AND ACCOUNT IN (SELECT AccountNumber
                    FROM tblMEP_CustomerAccounts
                    WHERE CustomerID = @CustomerID
                    AND UtilityCompanyID = @UtilityCompanyID)

    AND INV_DATE > DATEADD(month, -6, getdate())
    GROUP BY SERV_ACCT, ACCOUNT
    ORDER BY SERV_ACCT

Now the result is the following:

SERV_ACCT   ACCOUNT (No column name)
0289S61288  117512280   2013-06-12 00:00:00.000
0492S90281  117512280   2013-06-12 00:00:00.000
0492S90281  651412281   2013-04-08 00:00:00.000
1303S90280  117512280   2013-06-12 00:00:00.000
1435S01282  117512280   2013-06-12 00:00:00.000
1440S13289  312937281   2013-06-24 00:00:00.000
1548S00286  117512280   2013-06-12 00:00:00.000
1548S00286  308710287   2013-04-08 00:00:00.000
2498S21288  117512280   2013-06-12 00:00:00.000
5384S92284  979437282   2013-06-24 00:00:00.000
5538S21284  117512280   2013-06-12 00:00:00.000
6109S12286  117512280   2013-06-12 00:00:00.000
7358S00281  117512280   2013-06-12 00:00:00.000
7358S00281  659710281   2013-04-08 00:00:00.000
7488S22289  117512280   2013-06-12 00:00:00.000
8058S12287  117512280   2013-06-12 00:00:00.000
9058S00288  117512280   2013-06-12 00:00:00.000
9168S00282  117512280   2013-06-12 00:00:00.000
9168S00282  570810282   2013-04-08 00:00:00.000
9645S21281  117512280   2013-06-12 00:00:00.000

As you see, the Serv_Acct is not Distinct anymore! My goal is to get only the distinct Serv_Acct (Which is 16) with the latest account. Any help on how to do that? I asked a friend and she told me that I can do it with temp tables! Is that true?

Mike Abramczyk · Accepted Answer · 2013-07-15T22:16:16.540

Try using the first query you listed as a subquery, and join it to tblapsdata. It will look something like this:

DECLARE 

 @CustomerID int = 8,
 @UtilityCompanyID int = 1

SELECT MD.SERV_ACCT, AD.ACCOUNT, MD.MAXINVDATE
FROM
(SELECT DISTINCT SERV_ACCT, MAX(INV_DATE) as MAXINVDATE
FROM tblAPSData

WHERE SERV_ACCT NOT IN (SELECT ServiceAccount
            FROM tblMEP_Meters
            JOIN tblMEP_Sites
            ON tblMEP_Meters.SiteID = tblMEP_Sites.ID

            JOIN tblMEP_Projects
            ON tblMEP_Projects.ID = tblMEP_Sites.ProjectID

            WHERE CustomerID = 8
            AND Type = 1
            )

AND ACCOUNT IN (SELECT AccountNumber
                FROM tblMEP_CustomerAccounts
                WHERE CustomerID = @CustomerID
                AND UtilityCompanyID = @UtilityCompanyID)

AND INV_DATE > DATEADD(month, -6, getdate())
GROUP BY SERV_ACCT) MD
join tblAPSdata AD on MD.SERV_ACCT = AD.SERV_ACCT and MD.MAXINVDATE = AD.INV_DATE

Notice how this will only return records from your original query, but will have the appropriate Account number attached. Note that if you have Multiple account numbers that have the SAME DATE, more records will be returned.

Thank you Mike!! This is what I am looking for exactly, could you please show me how can you do it with temp tables? Thank you again. — Natalia Natalie, Jul 15 '13 at 22:22

SQL Server DISTINCT OR GROUP BY WITH TEMP TABLES

1 Answers1