Report to show current year revenue only if last year has no reven

I need to create a report that correctly displays ONLY accounts that have
revenue for the current year and no revenue at all for the previous year
(calendar year). The sql below does not do it.
SELECT DISTINCT a.ACCTNBR, 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
Please Help
--
DCounttry something like:
SELECT a.ACCTNBR, a.ACCTNAME, SUM(f.REVENUE) AS REVENUE
FROM FORD.dbo.DATA f
join FORD.dbo.ACCOUNT a on whatever
join FORD.dbo.DATE d on whatever
left outer join (
SELECT a1.ACCTNBR
FROM FORD.dbo.DATA f1
join FORD.dbo.ACCOUNT a1 on whatever
join FORD.dbo.DATE d1 on whatever
WHERE (d1.YEAR = '2004')
GROUP BY a1.ACCTNBR, a1.ACCTNAME
having SUM(f1.REVENUE)>0) oj on a.ACCTNBR=oj.ACCTNBR --this is a
subquery that returns all the accounts you don't want
WHERE (d.YEAR = '2005')
and oj.ACCTNBR is null --this gets rid of the unwanted accounts
GROUP BY a.ACCTNBR, a.ACCTNAME
having SUM(f.REVENUE)>0
SELECT a.ACCTNBR, a.ACCTNAME, SUM(f.REVENUE) AS REVENUE
FROM FORD.dbo.DATA f
join FORD.dbo.ACCOUNT a on whatever
join FORD.dbo.DATE d on whatever
WHERE (d.YEAR = '2004')
GROUP BY a.ACCTNBR, a.ACCTNAME
having SUM(f.REVENUE)>0
--
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"DCount17" <DCount17@.discussions.microsoft.com> wrote in message
news:469690F5-456F-4B07-9610-29646B7A81A5@.microsoft.com...
>I need to create a report that correctly displays ONLY accounts that have
> revenue for the current year and no revenue at all for the previous year
> (calendar year). The sql below does not do it.
> SELECT DISTINCT a.ACCTNBR, 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
> Please Help
> --
> DCount|||Thanks for your help Mary. However, I'm not sure how to use the "oj"
statement you placed in the sql statement (see below). I thought you meant
outer join, but I get an error when doing that. I'm not good at complex
queries, so I really appreciate your help. Thanks again.
having SUM(f1.REVENUE)>0) oj on a.ACCTNBR=oj.ACCTNBR --this is a
subquery that returns all the accounts you don't want
WHERE (d.YEAR = '2005')
and oj.ACCTNBR is null --this gets rid of the unwanted accounts
GROUP BY a.ACCTNBR, a.ACCTNAME
having SUM(f.REVENUE)>0
"Mary Bray" wrote:
> try something like:
> SELECT a.ACCTNBR, a.ACCTNAME, SUM(f.REVENUE) AS REVENUE
> FROM FORD.dbo.DATA f
> join FORD.dbo.ACCOUNT a on whatever
> join FORD.dbo.DATE d on whatever
> left outer join (
> SELECT a1.ACCTNBR
> FROM FORD.dbo.DATA f1
> join FORD.dbo.ACCOUNT a1 on whatever
> join FORD.dbo.DATE d1 on whatever
> WHERE (d1.YEAR = '2004')
> GROUP BY a1.ACCTNBR, a1.ACCTNAME
> having SUM(f1.REVENUE)>0) oj on a.ACCTNBR=oj.ACCTNBR --this is a
> subquery that returns all the accounts you don't want
> WHERE (d.YEAR = '2005')
> and oj.ACCTNBR is null --this gets rid of the unwanted accounts
> GROUP BY a.ACCTNBR, a.ACCTNAME
> having SUM(f.REVENUE)>0
> SELECT a.ACCTNBR, a.ACCTNAME, SUM(f.REVENUE) AS REVENUE
> FROM FORD.dbo.DATA f
> join FORD.dbo.ACCOUNT a on whatever
> join FORD.dbo.DATE d on whatever
> WHERE (d.YEAR = '2004')
> GROUP BY a.ACCTNBR, a.ACCTNAME
> having SUM(f.REVENUE)>0
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "DCount17" <DCount17@.discussions.microsoft.com> wrote in message
> news:469690F5-456F-4B07-9610-29646B7A81A5@.microsoft.com...
> >I need to create a report that correctly displays ONLY accounts that have
> > revenue for the current year and no revenue at all for the previous year
> > (calendar year). The sql below does not do it.
> >
> > SELECT DISTINCT a.ACCTNBR, 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
> >
> > Please Help
> > --
> > DCount
>
>