May 5, 2008 at 8:47 am
Hi All
I need some help writing a query, I have a table which I am using for reporting. Its a helpdesk which is currently in use and populated with data. There is a column of data type bit which holds 1 for call closed and 0 for not closed. The table also has regions. So I need to do a count of the table of calls closed and calls not closed and group by region. It needs to basically look like this:
Region Closed NotClosed
Inland 100 25
Southern 99 30
Easter 20 15
The table structure is like this:
USE [tbl]
GO
/****** Object: Table [dbo].[hdRequests] Script Date: 05/05/2008 16:31:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[hdRequests](
[ReqID] [int] IDENTITY(1,1) NOT NULL,
[CreateDate] [datetime] NULL,
[CreatedBy] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[AffectedUser] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[CurrentStatus] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[CurrentTech] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[Category] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[Item] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[Closed] [bit] NULL,
[ClosedDate] [datetime] NULL,
[LastActionDate] [datetime] NULL,
[Region] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[UserVer] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[Rejected] [bit] NULL,
[ExtendReason] [varchar](250) COLLATE Latin1_General_CI_AS NULL,
[ExtendDate] [datetime] NULL,
CONSTRAINT [PK_hdRequests] PRIMARY KEY CLUSTERED
(
[ReqID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
I have tried to google this and search forums and tried unions and subqueries, but have had no luck.
Your help will be greatly appreciated.
May 5, 2008 at 9:16 am
how about
Select Region
, sum(case Closed when 1 then 1 else 0 end) as Closed
, sum(case Closed when 1 then 0 else 1 end) as NotClosed
from [dbo].[hdRequests]
group by Region
order by Region
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 5, 2008 at 9:22 am
Shaun,
Not to put you off, but this appears to be similar to an exam or homework question. If you show some work, we'd be happy to help.
Here's the basic idea:
you want to count the 1s and 0s. You can use the SUM() function to do this and a CASE statement to pick which ones you are choosing.
You can GROUP BY the regions.
May 5, 2008 at 12:55 pm
Hi There Guys
Thanks alot for the help! Its working like a dream. I appreciate it, sorry if it seems as though this was for an exam or something, but I have been thrown into the deep end with .Net dev, SQL and Lotus Domino development, coming from a desktop/server support environment, dont get me wrong, I am loving it! I am pretty new to all of it, but learning as I go along. I wouldnt ask unless I was really unsure of what to do. I have tried many different approaches over the last couple of days to this solution, basically I was using the count function and not adding a case statement, I was getting some really funky results coming out.
Anyways, thanks for the help much appreciated.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply