SQL Query gives bloated time values when changing to new search conditions

I'm trying to update an SQL search query to be used in a pie chart. This requires 2 tables. Machines and EvtDur.

Machine:
 Mac.JPG
Event:
Event.JPG
Previously I created a search query that gets me the sum for all Event time for specific machines within a data range:

SELECT 'Core' as Core, Z.EventName, ISNULL(DIFF,0) AS DIFF FROM 
(select distinct EventName from dbo.tblMachine a 

join 
(SELECT DISTINCT EVENTNAME FROM dbo.tblEvtDur) b 
on b.EventName <> '' and eventname in ('BLK', 'MDL', 'MTL', 'OFF', 'RPR', 'RUN') 
and MacID in ('C01','C02', 'C03', 'D01', 'D02','D03','D04', 'D05')) Z 

LEFT JOIN (SELECT A.EventName, SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) as diff, 
round(SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) / cast(aVG(Tdiff) as decimal(30,8)),4) * 100 AS PER 
FROM dbo.tblEvtDur  A 

LEFT JOIN ( SELECT EVENTNAME, SUM(DATEDIFF(SECOND, STARTdt, eNDdt)) as Tdiff 
FROM dbo.tblEvtDur  WHERE DayID between '20180401' and '20180430' GROUP BY EVENTNAME ) B 
ON  A.EVENTNAME = B.EVENTNAME 

WHERE DayID between '20180401' and '20180430' 
AND A.MacID in ('C01','C02', 'C03', 'D01', 'D02','D03','D04', 'D05') 
and A.eventname in ('BLK', 'MDL', 'MTL', 'OFF', 'RPR', 'RUN') 
group by A.EventName) a ON A.EVENTNAME = Z.EVENTNAME and Z.EVENTNAME  = a.EVENTNAME order by  Z.EventName

Open in new window


Although it works, it was not very convenient for a pie chart because the machines are hard coded into the query, making adding new machines tedious. But the results are still accurate when I initially tested it.
result1.JPG
So the next plan was to have query check based on MacNm instead of previously using MacID, because those of the same group are using the same first 3 letters. (COR machines are one group)

For the updated SQL:
SELECT 'Core' as Core, z.EventName, ISNULL(DIFF,0) AS DIFF FROM 
 (select distinct EventName from dbo.tblMachine a 
 join 
 (SELECT DISTINCT EVENTNAME FROM dbo.tblEvtDur where EventName <> 'ON') b 
 on b.EventName <> '' and eventname in ('BLK', 'MDL', 'MTL', 'OFF', 'RPR', 'RUN') 
 and MacNm like 'COR%') Z 
 
 LEFT JOIN (SELECT A.EventName, SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) as diff, 
 round(SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) / cast(aVG(Tdiff) as decimal(30,8)),4) * 100 AS PER 
 FROM dbo.tblEvtDur  A 
 
 LEFT JOIN ( SELECT distinct EVENTNAME, MacNm, a.MacID, SUM(DATEDIFF(SECOND, STARTdt, eNDdt)) 
 as Tdiff FROM dbo.tblEvtDur  a left join (select macid, MacNm from dbo.tblmachine where MacNm like 'COR%') b
 on a.MacID = b.MacID WHERE DayID between '20180401' and '20180430' group by eventname, macnm, a.macid 
 ) B ON  A.EVENTNAME = B.EVENTNAME 
 
 WHERE DayID between '20180401' and '20180430' 
 AND MacNm like 'COR%' and A.eventname in ('BLK', 'MDL', 'MTL', 'OFF', 'RPR', 'RUN') 
 group by A.EventName) a ON A.EVENTNAME = Z.EVENTNAME and Z.EVENTNAME  = a.EVENTNAME order by  Z.EventName

Open in new window


I noticed that the sum I got was bloated compared to the original and the percentage doesn't match.
result2.JPG
I tried to figure out the cause all day and can't find how to solve it.
Hans J.HauAsked:
Who is Participating?
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.

ste5anSenior DeveloperCommented:
hmm, I have to admit, that I don't understand the need for the complexity of your SQL statement at all. Does this simplified version work?

SELECT   E.EventName ,
         SUM(DATEDIFF(SECOND, E.StartDT, E.EndDT)) AS DurationInSec
FROM     dbo.tblEvtDur E
         INNER JOIN dbo.tblMachine M ON E.MacID = M.MacID
WHERE    E.DayID
         BETWEEN '20180401' AND '20180430'
         AND M.MacNm LIKE 'COR%'
GROUP BY E.EventName;

Open in new window

0
Hans J.HauAuthor Commented:
SQL.JPG
Mostly. I still need it to show BLK as 0. The code you gave only shows time value for events used within the date range.
Just need that one row on top and then it will be perfect.
0
ste5anSenior DeveloperCommented:
E.g.

WITH AllEvents AS 
    ( 
        SELECT   E.EventName
        FROM     dbo.tblEvtDur E
        GROUP BY E.EventName
    ) ,
    EventDurations AS 
    ( 
        SELECT   E.EventName ,
                 SUM(DATEDIFF(SECOND, E.StartDT, E.EndDT)) AS DurationInSec
        FROM     dbo.tblEvtDur E
                 INNER JOIN dbo.tblMachine M ON E.MacID = M.MacID
        WHERE    E.DayID
                 BETWEEN '20180401' AND '20180430'
                 AND M.MacNm LIKE 'COR%'
        GROUP BY E.EventName
    )
SELECT AE.EventName ,
       ED.DurationInSec
FROM   AllEvents AE
       LEFT JOIN EventDurations ED ON AE.EventName = ED.EventName;

-- or

SELECT AE.EventName ,
       ED.DurationInSec
FROM   (   SELECT   E.EventName
           FROM     dbo.tblEvtDur E
           GROUP BY E.EventName ) AE
       LEFT JOIN (   SELECT   E.EventName ,
                              SUM(DATEDIFF(SECOND, E.StartDT, E.EndDT)) AS DurationInSec
                     FROM     dbo.tblEvtDur E
                              INNER JOIN dbo.tblMachine M ON E.MacID = M.MacID
                     WHERE    E.DayID
                              BETWEEN '20180401' AND '20180430'
                              AND M.MacNm LIKE 'COR%'
                     GROUP BY E.EventName ) ED ON AE.EventName = ED.EventName;

Open in new window

Mostly. I still need it to show BLK as 0.

Caveat: This can change the semantics. It can mean the event happened, but did not take time, instead of it did not happen.

If you really need it it like this, use ISNULL(ED.DurationInSec, 0) instead of the simple ED.DurationInSec in the outer SELECT:

WITH AllEvents AS 
    ( 
        SELECT   E.EventName
        FROM     dbo.tblEvtDur E
        GROUP BY E.EventName
    ) ,
    EventDurations AS 
    ( 
        SELECT   E.EventName ,
                 SUM(DATEDIFF(SECOND, E.StartDT, E.EndDT)) AS DurationInSec
        FROM     dbo.tblEvtDur E
                 INNER JOIN dbo.tblMachine M ON E.MacID = M.MacID
        WHERE    E.DayID
                 BETWEEN '20180401' AND '20180430'
                 AND M.MacNm LIKE 'COR%'
        GROUP BY E.EventName
    )
SELECT AE.EventName ,
       ISNULL(ED.DurationInSec, 0) AS DurationInSec
FROM   AllEvents AE
       LEFT JOIN EventDurations ED ON AE.EventName = ED.EventName;

Open in new window

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Hans J.HauAuthor Commented:
Thanks ste5an, the code works well. But there is a question I have. when I set the DayID as today's date:

between '20180608' and '20180608'

Open in new window


Which is also when the time captured is ever changing, I noticed the EventName will be arranged out of order. This seems to last for several seconds before reverting back to the normal sequence.
0
ste5anSenior DeveloperCommented:
Which is also when the time captured is ever changing [..]
What time?

This is a problem of improper object naming.The column is named DayID, this I assumed it is either a DATE or an INT. Thus:

So what data type has it?
What data should be stored in it (requirements)?
What is the domain of this data?
0
Hans J.HauAuthor Commented:
It's an integer string. But don't worry after adding another

order by EventName 

Open in new window

at the end of the code, the sequence is much more consistent now.

Overall code I have:

WITH AllEvents AS (SELECT E.EventName FROM dbo.tblEvtDur E GROUP BY E.EventName), 
EventDurations AS (SELECT E.EventName, SUM(DATEDIFF(SECOND, E.StartDT, E.EndDT)) 
AS DIFF FROM dbo.tblEvtDur E INNER JOIN dbo.tblMachine M ON E.MacID = M.MacID 
WHERE E.DayID BETWEEN '20180401' and '20180431' AND M.MacNm 
LIKE 'COR%' GROUP BY E.EventName) SELECT AE.EventName, 
ISNULL(ED.DIFF, 0) AS DIFF FROM AllEvents AE LEFT JOIN 
EventDurations ED ON AE.EventName = ED.EventName order by EventName

Open in new window

0
ste5anSenior DeveloperCommented:
hmm, what does "It's an integer string." mean?

The data type is either INT or [N]VARCHAR. Or do you mean, that is [N]VARCHAR, but it contains only digits as content? In this case consider using INT, cause it's faster and requires lesser storage space.
0
Hans J.HauAuthor Commented:
Its INT.
0
ste5anSenior DeveloperCommented:
Remove the single quotes in this case:

WITH AllEvents
AS ( SELECT   E.EventName
     FROM     dbo.tblEvtDur E
     GROUP BY E.EventName ) ,
     EventDurations
AS ( SELECT   E.EventName ,
              SUM(DATEDIFF(SECOND, E.StartDT, E.EndDT)) AS DIFF
     FROM     dbo.tblEvtDur E
              INNER JOIN dbo.tblMachine M ON E.MacID = M.MacID
     WHERE    E.DayID
              BETWEEN 20180401 AND 20180431
              AND M.MacNm LIKE 'COR%'
     GROUP BY E.EventName )
SELECT   AE.EventName ,
         ISNULL(ED.DIFF, 0) AS DIFF
FROM     AllEvents AE
         LEFT JOIN EventDurations ED ON AE.EventName = ED.EventName
ORDER BY EventName;

Open in new window


This avoids an implicit cast.
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
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
SQL

From novice to tech pro — start learning today.