Need one Query to obtain results I can only get with two queries

I'm trying to devise a query for use on SQL Server 2000 that will do
what was previously done with one query in MS Access. The MS Access
query was like this:

SELECT Count(*) as [Opened],
Abs(Sum([Status] Like 'Cancel*')) As [Cancelled]
FROM Detail_Dir_LocV
Where (Detail_Dir_LocV.DateOpened > '2004-8-01') and
Status not like 'Deleted'
Group By Year(DateOpened), Month(DateOpened)
Order By Year(DateOpened), Month(DateOpened)

Here were I'm at with SQL Server, TSQL

Select Right(Convert(Char (11), Min(DateOpened), 106), 8) as [Month
Opened],
Count(Status) as [Opened]
FROM Detail_Dir_LocV
Where (Detail_Dir_LocV.DateOpened > '2004-8-01') and
Status not like 'Deleted'
Group By Year(DateOpened), Month(DateOpened) Order By
Year(DateOpened), Month(DateOpened)

Which yields

MonthOpened
======================
Aug 2004503
Sep 2004752
Oct 2004828
Nov 2004658
Dec 2004533
Jan 2005736
Feb 2005707
Mar 2005797
Apr 2005412

And

Select Right(Convert(Char (11), Min(DateOpened), 106), 8) as [Month
Opened],
Count(Status) as [Cancelled]
FROM Detail_Dir_LocV
Where (Detail_Dir_LocV.DateOpened > '2004-8-01') and
Status like 'Cancelled%'
Group By Year(DateOpened), Month(DateOpened) Order By
Year(DateOpened), Month(DateOpened)

Which yields;

MonthCancelled
=========================
Aug 200478
Sep 2004105
Oct 2004121
Nov 2004106
Dec 200475
Jan 200582
Feb 200571
Mar 200594
Apr 200533

What is desired is

MonthOpenedCancelled
============================
Aug 200450378
Sep 2004752105
Oct 2004828121
Nov 2004658106
Dec 200453375
Jan 200573682
Feb 200570771
Mar 200579794
Apr 200541233

Any assistance would be appreciated.

Cheers;

BillI think that you need something like this:

Select Right(Convert(Char (11), Min(DateOpened), 106), 8) as [Month
Opened],
Count(Status) as [Opened],
Sum(CASE WHEN [Status] Like 'Cancel%' THEN 1 ELSE 0 END) as
[Cancelled]
FROM Detail_Dir_LocV
Where (Detail_Dir_LocV.DateOpened > '2004-8-01') and Status not like
'Deleted'
Group By Year(DateOpened), Month(DateOpened)
Order By Year(DateOpened), Month(DateOpened)

Razvan

PS. It would have been useful if you would have provided the DDL (in
form of "CREATE TABLE..." statements) and some sample data (in form of
"INSERT INTO ... VALUES ..." statements).|||"Razvan Socol" <rsocol@.gmail.com> wrote in message news:<1113473001.684266.112120@.o13g2000cwo.googlegroups. com>...
> I think that you need something like this:
> Select Right(Convert(Char (11), Min(DateOpened), 106), 8) as [Month
> Opened],
> Count(Status) as [Opened],
> Sum(CASE WHEN [Status] Like 'Cancel%' THEN 1 ELSE 0 END) as
> [Cancelled]
> FROM Detail_Dir_LocV
> Where (Detail_Dir_LocV.DateOpened > '2004-8-01') and Status not like
> 'Deleted'
> Group By Year(DateOpened), Month(DateOpened)
> Order By Year(DateOpened), Month(DateOpened)
> Razvan
> PS. It would have been useful if you would have provided the DDL (in
> form of "CREATE TABLE..." statements) and some sample data (in form of
> "INSERT INTO ... VALUES ..." statements).

Razvan;

Thank you for your assistance. It solved my problem.

Is the purpose of your request for a Create Table and Insert samples
so that you might build the table and populate it locally to test your
proposed solution? I think I know how to get the Create statement
since in fact the query is accessing a view and I can just grab it's
properties. The main table that the view is built upon was built
interactively. The table is also populated via an ASP html form so
building a set of insert into would be a manual process. Is there
anyway to have SQL Server build a sample for your intended purpose
based upon the existing data in the table or view?

Any way, you solved my problem and I appreciate your time.

Thank you.

Cheers;

Bill|||Hi Bill,

Yes, that is the purpose: to enable us to test the solution easily, but
also to make us sure that we properly understood the scenario, to make
all of those who respond use the same column names.

For generating insert scripts from the existing data, see:
http://vyaskn.tripod.com/code.htm#inserts
http://www.databasejournal.com/scri...cle.php/1493101

For in-depth considerations about DDL and sample data, see:
http://www.aspfaq.com/etiquette.asp?id=5006

Razvan