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

[Bill]

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_Loc V.DateOpened > '2004-8-01') and
Status not like 'Deleted'
Group By Year(DateOpened ), Month(DateOpene d)
Order By Year(DateOpened ), Month(DateOpene d)

Here were I'm at with SQL Server, TSQL

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

Which yields

Month Opened
=============== =======
Aug 2004 503
Sep 2004 752
Oct 2004 828
Nov 2004 658
Dec 2004 533
Jan 2005 736
Feb 2005 707
Mar 2005 797
Apr 2005 412

And

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

Which yields;

Month Cancelled
=============== ==========
Aug 2004 78
Sep 2004 105
Oct 2004 121
Nov 2004 106
Dec 2004 75
Jan 2005 82
Feb 2005 71
Mar 2005 94
Apr 2005 33

What is desired is

Month Opened Cancelled
=============== =============
Aug 2004 503 78
Sep 2004 752 105
Oct 2004 828 121
Nov 2004 658 106
Dec 2004 533 75
Jan 2005 736 82
Feb 2005 707 71
Mar 2005 797 94
Apr 2005 412 33

Any assistance would be appreciated.
Cheers;

Bill

[Razvan Socol]

I think that you need something like this:

Select Right(Convert(C har (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_Loc V.DateOpened > '2004-8-01') and Status not like
'Deleted'
Group By Year(DateOpened ), Month(DateOpene d)
Order By Year(DateOpened ), Month(DateOpene d)

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

[Bill]

"Razvan Socol" <rs****@gmail.c om> wrote in message news:<11******* *************** @o13g2000cwo.go oglegroups.com> ...

I think that you need something like this:

Select Right(Convert(C har (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_Loc V.DateOpened > '2004-8-01') and Status not like
'Deleted'
Group By Year(DateOpened ), Month(DateOpene d)
Order By Year(DateOpened ), Month(DateOpene d)

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

[Razvan Socol]

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/scrip...le.php/1493101

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

Razvan