Can someone help me with some SQL code? I cannot figure out why this won't work. I'm trying to get the last history for every contact in the database. I can use this code in Access connect via ODBC and it works just fine. I can also run each select statement by itself in the Dashboard and it works, but when I try to join them together I get a blank dashboard. Any ideas? Thanks.
<item>
<key>
<string>Last History</string>
</key>
<value>
<string>
SELECT LH.END_DATE_TIME, LH.REGARDING, LH.CONTACTID FROM
(SELECT CLH.CONTACTID, HLH.END_DATE_TIME, HLH.REGARDING FROM
(dbo.CONTACT_HISTORY AS CHLH INNER JOIN dbo.CONTACT AS CLH ON CHLH.CONTACTID = CLH.CONTACTID)
INNER JOIN dbo.HISTORY AS HLH ON CHLH.HISTORYID = HLH.HISTORYID) AS LH
INNER JOIN (SELECT DISTINCT CLHD.CONTACTID, Max(HLHD.END_DATE_TIME) AS MaxOfENDTIME FROM
(dbo.CONTACT_HISTORY AS CHLHD INNER JOIN dbo.CONTACT AS CLHD ON CHLHD.CONTACTID = CLHD.CONTACTID)
INNER JOIN dbo.HISTORY AS HLHD ON CHLHD.HISTORYID = HLHD.HISTORYID
GROUP BY CLHD.CONTACTID) AS LHD
ON (LH.CONTACTID = LHD.CONTACTID) AND (LH.END_DATE_TIME = LHD.MaxOfENDTIME)
;0;TRUE
</string>
</value>
</item>
<item>
Todd Viau
Aviva LLC
St. Louis MO
