Complex query to limit results based date in same table

[DCount]

Not being good with complex sql statements, I'm looking for help with the following: I need to create a report that will display revenue data for accounts that DO NOT have revenue for the previous year. The following query doesn't work.

SELECT DISTINCT a.LDGACT, a.ACCTNAME, SUM(f.REVENUE) AS REVENUE
FROM FORD.dbo.DATA f, FORD.dbo.ACCOUNT a, FORD.dbo.DATE d
WHERE (d.YEAR = '2005') AND (d.YEAR <> '2004')
GROUP BY a.ACCTNBR, a.ACCTNAME

Since the DATE table contains the "year" value, is there a way to create the limitation to show the correct results (only accounts with 2005 data that DID NOT have 2004 data)?

 

[PWise]

anr there no joins in this query ????

 

[Pattycake245]

Table schemas and data with desired results would give us an idea how to solve your issue.

Tim

 

[DCount]

After reading more about joins, I tried this query, but received errors near the WHERE (d.YEAR = '2005') clause. Thanks

SELECT a.LDGACT, a.ACCTNAME, SUM(f.REVENUE) AS REVENUE
FROM FORD.dbo.DATA f JOIN FORD.dbo.ACCOUNT a
on f.idACCOUNT = a.idACCOUNT JOIN FORD.dbo.DATE d
on f.PUBDATE = d.D_DATE
LEFT OUTER JOIN (SELECT a1.LDGACT
FROM FORD.dbo.DATA f1 JOIN
FORD.dbo.ACCOUNT a1
on f1.idACCOUNT = a1.idACCOUNT
JOIN FORD.dbo.DATE d1
on f1.PUBDATE = d1.DATE
WHERE (d1.YEAR = '2004')
GROUP BY a1.LDGACT, a1.ACCTNAME
HAVING SUM(f1.REVENUE)>0 )
WHERE (d.YEAR = '2005')
AND a.LDGACT is null
GROUP BY a.LDGACT, a.ACCTNAME
HAVING SUM(f.REVENUE)>0

SELECT a.LDGACT, a.ACCTNAME, SUM(f.REVENUE) AS REVENUE FROM FORD.dbo.DATA f
JOIN FORD.dbo.ACCOUNT a on f.idACCOUNT = a.idACCOUNT
JOIN FORD.dbo.DATE d on f.PUBDATE = d.DATE
WHERE (d.YEAR = '2004')
GROUP BY a.LDGACT, a.ACCTNAME
HAVING SUM(f.REVENUE)>0

Result Expectation: If ABC Coffee does not have revenue (in the DATA table) for the year 2004, but does have revenue for 2005, I only want to see this data. Similiarly, if ABC Coffee does have revenue in 2004 and 2005, I don't want to see it.