Solved

SQL 2008 R2 Query count

Posted on 2013-06-17
3
340 Views
Last Modified: 2013-06-17
Hello

I have a the following count Query

Select COUNT (*) as 'Closed' from [macom].[dbo].[dt_HDTicket] Join [macom].[dbo].[dt_HDStatus] on HDTicket_HDStatus_id=HDStatus_ID 
where HDTicket_IsClosed = '1' And HDTicket_Closed >= Dateadd(WW, -1, getdate()) and HDStatus_HDProject_id = '7' and HDStatus_Name <> 'Duplicate' 

Select count (*) as 'Opened' from [macom].[dbo].[dt_HDTicket] Join [macom].[dbo].[dt_HDStatus] on HDTicket_HDStatus_id=HDStatus_ID 
where  HDTicket_Created >= Dateadd(WW, -1, getdate()) and HDStatus_HDProject_id = '7' and HDStatus_Name <> 'Duplicate'

Select count (*) as 'Idle' from [macom].[dbo].[dt_HDTicket] Join [macom].[dbo].[dt_HDStatus] on HDTicket_HDStatus_id=HDStatus_ID 
where HDTicket_IsClosed = '0' And HDTicket_Modified < Dateadd(ww, -1, getdate()) and HDStatus_HDProject_id = '7' and HDStatus_Name <> 'Duplicate'
 
Select count (*) as 'Modified' from [macom].[dbo].[dt_HDTicket] Join [macom].[dbo].[dt_HDStatus] on HDTicket_HDStatus_id=HDStatus_ID 
where HDTicket_IsClosed = '0' And HDTicket_Modified >= Dateadd(ww, -1, getdate()) and HDStatus_HDProject_id = '7' and HDStatus_Name <> 'Duplicate'

Open in new window


Currently this query outputs 4 different tables, I would like to have them displayed in one table like this:
 
Closed, Opened,  Idle, Modified
13,10,15,1

Thanks in advanced.
0
Comment
Question by:macomsupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 6

Expert Comment

by:BurundiLapp
ID: 39253538
--

Sorry I've misread your question, hang on I'll post code that should do what you are after.
0
 
LVL 6

Accepted Solution

by:
BurundiLapp earned 500 total points
ID: 39253553
Give this a try:

SELECT 
(SELECT COUNT (*) from [macom].[dbo].[dt_HDTicket] Join [macom].[dbo].[dt_HDStatus] on HDTicket_HDStatus_id=HDStatus_ID 
where HDTicket_IsClosed = '1' And HDTicket_Closed >= Dateadd(WW, -1, getdate()) and HDStatus_HDProject_id = '7' and HDStatus_Name <> 'Duplicate') as Closed, 

(Select count (*) from [macom].[dbo].[dt_HDTicket] Join [macom].[dbo].[dt_HDStatus] on HDTicket_HDStatus_id=HDStatus_ID 
where  HDTicket_Created >= Dateadd(WW, -1, getdate()) and HDStatus_HDProject_id = '7' and HDStatus_Name <> 'Duplicate') as Opened,

(Select count (*) from [macom].[dbo].[dt_HDTicket] Join [macom].[dbo].[dt_HDStatus] on HDTicket_HDStatus_id=HDStatus_ID 
where HDTicket_IsClosed = '0' And HDTicket_Modified < Dateadd(ww, -1, getdate()) and HDStatus_HDProject_id = '7' and HDStatus_Name <> 'Duplicate') as Idle,

(Select count (*) from [macom].[dbo].[dt_HDTicket] Join [macom].[dbo].[dt_HDStatus] on HDTicket_HDStatus_id=HDStatus_ID 
where HDTicket_IsClosed = '0' And HDTicket_Modified >= Dateadd(ww, -1, getdate()) and HDStatus_HDProject_id = '7' and HDStatus_Name <> 'Duplicate') as Modified

Open in new window

0
 

Author Closing Comment

by:macomsupport
ID: 39254069
Perfect. Thanks!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question