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
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(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;
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 3 1256
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 Socol" <rs****@gmail.com> wrote in message news:<11**********************@o13g2000cwo.googleg roups.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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: lawrence |
last post by:
I've been bad about documentation so far but I'm going to try to be
better. I've mostly worked alone so I'm the only one, so far, who's
suffered from my bad habits. But I'd like other programmers...
|
by: Alexandre MELARD |
last post by:
Hi,
My name is alexandre, I am 4th year student at the Napier university
of edinburgh.
I am finishing my year and do a presentation of my honours project
next wednesday (the 5th of May).
I am...
|
by: Alicia |
last post by:
Hello everyone based on the data, I created a union query which
produces this.
SELECT ,,, 0 As ClosedCount
FROM
UNION SELECT
,, 0 AS OpenedCount,
FROM
ORDER BY , ;
|
by: Luisa Lopes |
last post by:
Dear colleagues:
I read somewhere that you could obtain the results of a query as a form.
I have design a database for my books and waht I would like is to obtain the
results of my queries...
|
by: Raposa Velha |
last post by:
Olá Luisa ;-)
If I understood correctly, you would like to display the results of a
query in a form. No problem, just go to the "Create form by using
wizard" and at the first step just choose...
|
by: rockyptc |
last post by:
greetings. first, i apologize for asking an old question. it appears
that i'm looking for a solution that was already given but it don't
seem to fit my scenario. so thanks for putting up with me.
...
|
by: Brian |
last post by:
Hello,
Basically, I'm running a query on a form's activation, and I'd like to
have the results of the query be placed into other fields on the same
form automatically.
Does anybody know how...
|
by: Chuck36963 |
last post by:
Hi all,
I've been working on a listing problem and I can't figure out how to work it out. I have looked far and wide on the web to find answers, but I'd like other peoples input on my project in...
|
by: bips2005 |
last post by:
i have got two queries,
$sql1 = "select cust_id from customer where comp_name = '$compname'";
$result = $DB->query($sql1);
$result->fetchInto($row);
$sql = "select expirydate from...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |