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

Report Services Bug? -Sub Report and Page break!

When the main report calls the meeting sub report, if the content of the sub
report is longer than the pare space on the current page, it will jump to the
next page without filling the rest space on the current page.
Is it a bug of Report Services?Any ideas?
"CEO" wrote:
> When the main report calls the meeting sub report, if the content of the sub
> report is longer than the pare space on the current page, it will jump to the
> next page without filling the rest space on the current page.
> Is it a bug of Report Services?|||No, this is expected behavior.
When you design your sub-reports, you need to make sure that you set them to
be right WIDTH. If in my main report, the width of my sub-report is 5
inches, then in my sub-report, I set my margins to be ZERO for left, right,
top and bottom. Next, I set the body width of my subreport to be 5 inches.
I hope this helps.
=-Chris
"CEO" <CEO@.discussions.microsoft.com> wrote in message
news:3C48CA63-49E1-48DB-99E5-245B1A0144B4@.microsoft.com...
> When the main report calls the meeting sub report, if the content of the
> sub
> report is longer than the pare space on the current page, it will jump to
> the
> next page without filling the rest space on the current page.
> Is it a bug of Report Services?