SQL Query return results for non-existant entries

Hello,

I'm trying to write a query that will do a couple things, first it will pull results from the database and format it by adding all the rows together (for a certain time frame). Second if a certain Category doesn't exist it will label it as 0. These entries won't be there at all if an event didn't happen (so no 0 or null entries, just non-existant). For example, it will search for critical events, if none exist it won't make any entry. If one does exist it will make an entries with a number.

Here's a slightly better scenerio, it's for Symantec Critical System Protection. It makes an entries for a "Warning, Major, Information, Critical" events. I want to grab all those from the SQL data base to show the following for the last 7 days in table format:

Type of Event | Number of Occurances


If there is no entries for a type of event (say no critical happened) it will enter Critical | 0, I have to have it show this for audit reasons saying "it's not there because none happened" isn't good enough. I will be doing this in MSSQL Management Studio (since I can't seem to do it inside the program itself for those events).

I would like to export this to a excel / pdf any type of common format. I hope this makes sense.
LVL 8
Seth_zinAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post some data sample?
How the actual rows looks alike and the expected result.
0
Seth_zinAuthor Commented:
Basically that shows Time, the even count and the severity.

What I would like is for there to be one entry for each type of severity, for example W (Warning) would have Event_cnt added for the last 7 days so there is one entry and the count would be like 60,000. I would have 10 etc. The table format would be the same (a date column not needed just for example). So like:

Event_Severity | Event_cnt
Warning            | 62,405
Information      | 38
Major                 | 0
Critical               | 0

Warning is W, Critical is C (which most the time won't have an entry as they don't occur often) etc (first letter of each severity). Also the select top 200 is just for example, I want it to pull all of them for the last 7 days.
sqlq.jpg
0
ste5anSenior DeveloperCommented:
Basically you need to LEFT JOIN your data to your category table.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Seth_zinAuthor Commented:
It's all in one table, but I don't understand SQL syntax that well, but isn't join for two different tables (I know I wasn't exactly clear, I haven't had to do SQL queries that weren't built-in for years).
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this one:
SELECT Event_Severity, COUNT(Event_cnt)
FROM CSPEVENT
WHERE Event_dt > GETDATE()-7
GROUP BY Event_Severity

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Seth_zinAuthor Commented:
I'm able to use that code to grab the events that do exist. The one biggest issue we have is I also need for it to check for "C" (Critical) and if it is not there to show it as 0. In this case the event will not be in the database as all as no critical events happened so there is no entry, not even null. That's where my biggest hurdle comes into play.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can create a table Events where you'll add all known event types and the join with CSPEVENT so you can have the pretended result.
SELECT T.Event_Severity, COUNT(ISNULL(E.Event_cnt,0))
FROM EventType T
LEFT JOIN CSPEVENT E ON T.Event_Severity = E.Event_Severity
WHERE E.Event_dt > GETDATE()-7
GROUP BY T.Event_Severity

Open in new window

0
ste5anSenior DeveloperCommented:
E.g.

WITH    Category ( Category )
          AS ( SELECT DISTINCT
                        EVENT_SEVERITY
               FROM     CSPEVENT
             )
    SELECT  Cat.Category ,
            COUNT(CE.Event_cnt)
    FROM    Category Cat
            LEFT JOIN CSPEVENT CE ON Cat.Category = CE.Event_Severity
    WHERE   E.Event_dt > GETDATE() - 7
    GROUP BY Cat.Category;

Open in new window


btw, I have a bad feeling about your mix between talking about 'category', 'severity' and 'type' of event. These are often not the same..

p.s. and PLEASE post code using the code tag or embed code block feature and not as image.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
SELECT DISTINCT
EVENT_SEVERITY
FROM     CSPEVENT
I would prefer to use a table instead of this select. CSPEvent can store dozens or hundreds of thousand records.
0
ste5anSenior DeveloperCommented:
Or..

SELECT  Cat.Category ,
        COUNT(CE.Event_cnt)
FROM    ( VALUES ( 'C', 'I', 'M', 'W' ) ) Cat ( Category )
	LEFT JOIN CSPEVENT CE ON Cat.Category = CE.Event_Severity
WHERE   E.Event_dt > GETDATE() - 7
GROUP BY Cat.Category;

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Better :)
But don't forget that COUNT doesn't count null values and that's why in my version I used COUNT(ISNULL(E.Event_cnt,0)).
0
ste5anSenior DeveloperCommented:
Saying that a non-existing row equals 0 is a violation of the ternary logic, imho. I don't like that. It's a formatting (front-end) issue.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Well, this is a well known trick to achieve this kind of requirements.
How would you do it (count null values)?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
And don't forget that isn't stored in the table. It's only for reporting. Zero here means no record with that severity has found.
0
ste5anSenior DeveloperCommented:
hmm, just a short test:

SELECT  T.name ,
        COUNT(C.object_id)
FROM    sys.types T
        LEFT JOIN sys.columns C ON C.user_type_id = T.user_type_id
WHERE   T.name LIKE 'i%'
GROUP BY T.name;

Open in new window


NULL is autmatically count'd as zero... using

SELECT  T.name ,
        COUNT(ISNULL(C.object_id, 0))
FROM    sys.types T
        LEFT JOIN sys.columns C ON C.user_type_id = T.user_type_id
WHERE   T.name LIKE 'i%'
GROUP BY T.name;

Open in new window


yields a 1 instead of 0.
0
Seth_zinAuthor Commented:
Thanks I was able to modify what you provided me to get some aditional results I needed!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.