October 11, 2010 at 4:22 pm
Hi Everyone,
I need help in putting together this 4 part of script into one script so I can load in up into 2005 SSRS. I am new in T-sql programming and I would highly appreciate if you could let me know how I could put it into one. Thank you!!!
SELECT
Ev.Mnum,
VM.Location,
VM.Description,
VM.MFR,
E.Description as ErrorDescription,
Count(Ev.EventCode) AS CountOfEventCode
INTO #Table1
FROM View_M as VM
RIGHT JOIN (Event as Ev INNER JOIN EventCode as E ON Ev.EventCode = E.Eventcode) ON VM.Mnum = Ev.Mnum
WHERE ((((E.Description) Like '%error%') OR ((E.PagerDescription) Like '%rejected%'))
OR (((E.PagerDescription) Like '%error%') OR ((E.PagerDescription) Like '%rejected%')))
AND (Ev.Date between dateadd(hh,0,DATEADD(dd, DATEDIFF(dd, 0, GETDATE())-3, 0)) and dateadd(hh,0,DATEADD(dd, DATEDIFF(dd, 0, GETDATE())-1, 0)))
GROUP BY Ev.Mnum, VM.Location, VM.Description, VM.MFR, E.Description
Having count(Ev.EventCode) >= '10' and count(Ev.EventCode) < '20'
ORDER BY Count(Ev.EventCode) DESC;
SELECT
Ev.Mnum,
VM.Location,
VM.Description,
VM.MFR,
E.Description as ErrorDescription,
Count(Ev.EventCode) AS CountOfEventCode
INTO #Table2
FROM View_M as VM
RIGHT JOIN (Event as Ev INNER JOIN EventCode as E ON Ev.EventCode = E.Eventcode) ON VM.Mnum = Ev.Mnum
WHERE ((((E.Description) Like '%error%') OR ((E.PagerDescription) Like '%rejected%'))
OR (((E.PagerDescription) Like '%error%') OR ((E.PagerDescription) Like '%rejected%')))
AND (Ev.Date between dateadd(hh,0,DATEADD(dd, DATEDIFF(dd, 0, GETDATE())-2, 0)) and dateadd(hh,0,DATEADD(dd, DATEDIFF(dd, 0, GETDATE())-1, 0)))
GROUP BY Ev.Mnum, VM.Location, VM.Description, VM.MFR, E.Description
Having count(Ev.EventCode) >= '10' and count(Ev.EventCode) < '20'
ORDER BY Count(Ev.EventCode) DESC;
SELECT
Ev.Mnum,
VM.Location,
VM.Description,
VM.MFR,
E.Description as ErrorDescription,
Count(Ev.EventCode) AS CountOfEventCode
INTO #Table3
FROM View_M as VM
RIGHT JOIN (Event as Ev INNER JOIN EventCode as E ON Ev.EventCode = E.Eventcode) ON VM.Mnum = Ev.Mnum
WHERE ((((E.Description) Like '%error%') OR ((E.PagerDescription) Like '%rejected%'))
OR (((E.PagerDescription) Like '%error%') OR ((E.PagerDescription) Like '%rejected%')))
AND (Ev.Date between dateadd(hh,0,DATEADD(dd, DATEDIFF(dd, 0, GETDATE())-1, 0)) and dateadd(hh,0,DATEADD(dd, DATEDIFF(dd, 0, GETDATE())-1, 0)))
GROUP BY Ev.Mnum, VM.Location, VM.Description, VM.MFR, E.Description
Having count(Ev.EventCode) >= '10' and count(Ev.EventCode) < '20'
ORDER BY Count(Ev.EventCode) DESC;
SELECT A.MNUM, A.LOCATION, A.DESCRIPTION, A.MFR, A.ERRORDESCRIPTION, A.COUNTOFEVENTCODE
FROM #TABLE1 AS A
JOIN #TABLE2 AS B ON A.MNUM = B.MNUM
JOIN #TABLE3 AS C ON A.MNUM = C.MNUM
October 11, 2010 at 5:04 pm
It looks like the only difference between the first three sections is in the where clause - specifically in the # of days being selected. Is this correct?
The dateadd(hh,0,) part isn't doing anything... the DATEADD(dd, DATEDIFF(dd, 0, GETDATE())-1, 0) returns the previous date with no time. So, the DateAdd(hh,0) part isn't adding any hours, and isn't needed.
(Most people don't use the between operator between date clauses; instead, we use: DateField >= @StartDate and DateField < @EndDate. This handles things when you get times in that date field.)
Also, you are comparing an integer to a character in your having clause. If you get 100 as the value, and the 100 is converted to character, then it will pass your having clause, since '100' is >= '10', but also < '20'. Fortunately, it is usually the character field that gets converted to int.
Having the count() run twice in the having can be eliminated.
The end result that I'm seeing is that you are selecting what is in table1, where the MNUM value is also in table2 and table3.
So, you can make each section a CTE, and join off of all of them:
WITH CTE3 AS
(
SELECT
Ev.Mnum,
VM.Location,
VM.Description,
VM.MFR,
E.Description as ErrorDescription,
Count(Ev.EventCode) AS CountOfEventCode
FROM View_M as VM
RIGHT JOIN (Event as Ev INNER JOIN EventCode as E ON Ev.EventCode = E.Eventcode) ON VM.Mnum = Ev.Mnum
WHERE ((((E.Description) Like '%error%') OR ((E.PagerDescription) Like '%rejected%'))
OR (((E.PagerDescription) Like '%error%') OR ((E.PagerDescription) Like '%rejected%')))
AND (Ev.Date between DATEADD(dd, DATEDIFF(dd, 0, GETDATE())-3, 0) and DATEADD(dd, DATEDIFF(dd, 0, GETDATE())-1, 0))
GROUP BY Ev.Mnum, VM.Location, VM.Description, VM.MFR, E.Description
Having count(Ev.EventCode) between 10 and 19
)
, CTE2 AS
(
SELECT
Ev.Mnum,
VM.Location,
VM.Description,
VM.MFR,
E.Description as ErrorDescription,
Count(Ev.EventCode) AS CountOfEventCode
FROM View_M as VM
RIGHT JOIN (Event as Ev INNER JOIN EventCode as E ON Ev.EventCode = E.Eventcode) ON VM.Mnum = Ev.Mnum
WHERE ((((E.Description) Like '%error%') OR ((E.PagerDescription) Like '%rejected%'))
OR (((E.PagerDescription) Like '%error%') OR ((E.PagerDescription) Like '%rejected%')))
AND (Ev.Date between DATEADD(dd, DATEDIFF(dd, 0, GETDATE())-2, 0) and DATEADD(dd, DATEDIFF(dd, 0, GETDATE())-1, 0))
GROUP BY Ev.Mnum, VM.Location, VM.Description, VM.MFR, E.Description
Having count(Ev.EventCode) between 10 and 19
)
, CTE1 AS
(
SELECT
Ev.Mnum,
VM.Location,
VM.Description,
VM.MFR,
E.Description as ErrorDescription,
Count(Ev.EventCode) AS CountOfEventCode
FROM View_M as VM
RIGHT JOIN (Event as Ev INNER JOIN EventCode as E ON Ev.EventCode = E.Eventcode) ON VM.Mnum = Ev.Mnum
WHERE ((((E.Description) Like '%error%') OR ((E.PagerDescription) Like '%rejected%'))
OR (((E.PagerDescription) Like '%error%') OR ((E.PagerDescription) Like '%rejected%')))
AND (Ev.Date between DATEADD(dd, DATEDIFF(dd, 0, GETDATE())-1, 0) and DATEADD(dd, DATEDIFF(dd, 0, GETDATE())-1, 0))
GROUP BY Ev.Mnum, VM.Location, VM.Description, VM.MFR, E.Description
Having count(Ev.EventCode) between 10 and 19
)
SELECT A.MNUM, A.LOCATION, A.DESCRIPTION, A.MFR, A.ERRORDESCRIPTION, A.COUNTOFEVENTCODE
FROM CTE3 AS A
JOIN CTE2 AS B ON A.MNUM = B.MNUM
JOIN CTE1 AS C ON A.MNUM = C.MNUM
So, is this what you're looking for? (The only things that I modified in your code is that I removed the dateadd(hh) section, and I changed the count comparison to be between 10 and 19, as opposed to being evaluated twice, and compared to character values.)
If this doesn't help you, then please realize that the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 11, 2010 at 5:42 pm
Thanks so much! This is what I'm looking for. I really appreciate your recommendations and today I learned about WITH common table expression that I didn't know before. Kidos to you!
October 13, 2010 at 11:25 pm
it also helped me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply