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