Solved

Repeating a query across columns

Posted on 2008-10-02
7
286 Views
Last Modified: 2012-05-05
I have this query which works fine and gives me the data I'm looking for exactly.

SELECT COUNT(DATEDIFF(Day,SLA_Date,GetDate())) AS [0 TO 30 Days]
FROM Vulnerabilities
WHERE Addinfo >= 500 AND CurrentStatus = 'OPEN' AND DATEDIFF(Day,SLA_Date,GetDate()) BETWEEN 0 AND 30

However,  I need to have the same query for BETWEEN 31 AND 60 and BETWEEN 61 and 90 etc.. I want to be able to run the query so that I get 3 columns one named 0 to 30 days, one named 31 to 60 days etc.. I just don't know how to repeat the query in the same results.  
0
Comment
Question by:jclemo
  • 3
  • 2
7 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22631414
SELECT COUNT(case when DATEDIFF(Day,SLA_Date,GetDate()) BETWEEN 0 AND 30 then DATEDIFF(Day,SLA_Date,GetDate()) else 0 end) AS [0 TO 30 Days] ,
COUNT(case when DATEDIFF(Day,SLA_Date,GetDate()) BETWEEN 31 AND 60 then DATEDIFF(Day,SLA_Date,GetDate()) else 0 end) AS [31 TO 60 Days] ,
COUNT(case when DATEDIFF(Day,SLA_Date,GetDate()) BETWEEN 61 AND 90 then DATEDIFF(Day,SLA_Date,GetDate()) else 0 end) AS [61 TO 90 Days]
FROM Vulnerabilities
WHERE Addinfo >= 500 AND CurrentStatus = 'OPEN'
0
 

Author Comment

by:jclemo
ID: 22631451
Actually, it appears that this solution did NOT work.. it gave me three columns with the total count in it..
0
 
LVL 4

Accepted Solution

by:
sijishJohn earned 250 total points
ID: 22631485
try this...

SELECT(
SELECT  COUNT(DATEDIFF(Day,SLA_Date,GetDate()))       
FROM Vulnerabilities  
WHERE Addinfo >= 500  AND CurrentStatus = 'OPEN' AND
DATEDIFF(Day,SLA_Date,GetDate()) BETWEEN 0 AND 30) AS [0 TO 30 Days],

(SELECT  COUNT(DATEDIFF(Day,SLA_Date,GetDate()))       
FROM Vulnerabilities  
WHERE Addinfo >= 500  AND CurrentStatus = 'OPEN' AND
DATEDIFF(Day,SLA_Date,GetDate()) BETWEEN 31 AND 60) AS [31 TO 60 Days],

(SELECT  COUNT(DATEDIFF(Day,SLA_Date,GetDate()))       
FROM Vulnerabilities  
WHERE Addinfo >= 500  AND CurrentStatus = 'OPEN'  AND
DATEDIFF(Day,SLA_Date,GetDate()) BETWEEN 61 AND 90 ) AS [61 TO 90 Days]
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:jclemo
ID: 22631507
Yep, that's it.. I was one "SELECT" off.. thanks a ton... How do I get points reallocated?
0
 
LVL 4

Expert Comment

by:sijishJohn
ID: 22631535
try to reallocate...else mark it as assisted solution....
thanks
0
 

Author Closing Comment

by:jclemo
ID: 31502665
Rockin.. thanks
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now