different results in these two sql queries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

different results in these two sql queries

Author

Topic

mana
Posting Yak Master

102 Posts

Posted - 2015-01-27 : 10:01:54

Hello

i had the following query:

SELECT a.MASCHINENNR AS Auftrag, MAX(c.SERIENNUMMER) AS Seriennummer, MAX(c.FAMILIE) AS Benennung, MAX(c.BESTIMMUNGSLAND) AS Land, MAX(c.[BV-05])
AS Datum_Offline, MAX(c.OKTOSHIP) AS OKtoShip, MAX(d.LPLATZ) AS Letzter_Platz, SUM(ISNULL(e.AVG_RepZeit, 0)) AS AVG_RepZeit_Offen, COUNT(b.FEHLERCODE)
AS AnzFehler_Offen, MAX(g.AVG_RepZeit_Total) AS AVG_RepZeit_Total, MAX(f.A) AS AnzFehler_Total, a.MASCHINENNR, a.MASCHINENNR AS Expr1
FROM AQIs.PRUEFUNGEN AS a INNER JOIN
AQIs.PRUEFFEHLER AS b ON a.PRUEFNR = b.PRUEFNR INNER JOIN
AQIs.STATISTIK_STAMMDATEN AS c ON a.MASCHINENNR = c.AUFTRAGSNUMMER INNER JOIN
AQIs.STAMMDATEN AS d ON a.MASCHINENNR = d.MASCHINENNR LEFT OUTER JOIN
(SELECT FEHLERCODE, MIN(ISNULL(REPARATURZEITMINUTEN, 0)) AS MIN_RepZeit, AVG(ISNULL(REPARATURZEITMINUTEN, 0)) AS AVG_RepZeit,
MAX(ISNULL(REPARATURZEITMINUTEN, 0)) AS MAX_RepZeit
FROM AQIs.PRUEFFEHLER
WHERE (DATUMEINTRAG >= DATEADD(M, - 3, GETDATE()))
GROUP BY FEHLERCODE) AS e ON b.FEHLERCODE = e.FEHLERCODE LEFT OUTER JOIN
AQIs.FEHLERCOUNT AS f ON a.MASCHINENNR = f.MASCHINENNR LEFT OUTER JOIN
(SELECT a.MASCHINENNR AS Auftrag, SUM(ISNULL(e_1.AVG_RepZeit, 0)) AS AVG_RepZeit_Total
FROM AQIs.PRUEFUNGEN AS a INNER JOIN
AQIs.PRUEFFEHLER AS b ON a.PRUEFNR = b.PRUEFNR INNER JOIN
AQIs.STATISTIK_STAMMDATEN AS c ON a.MASCHINENNR = c.AUFTRAGSNUMMER INNER JOIN
AQIs.STAMMDATEN AS d ON a.MASCHINENNR = d.MASCHINENNR LEFT OUTER JOIN
(SELECT FEHLERCODE, MIN(ISNULL(REPARATURZEITMINUTEN, 0)) AS MIN_RepZeit, AVG(ISNULL(REPARATURZEITMINUTEN, 0)) AS AVG_RepZeit,
MAX(ISNULL(REPARATURZEITMINUTEN, 0)) AS MAX_RepZeit
FROM AQIs.PRUEFFEHLER AS PRUEFFEHLER_1
WHERE (DATUMEINTRAG >= DATEADD(M, - 3, GETDATE()))
GROUP BY FEHLERCODE) AS e_1 ON b.FEHLERCODE = e_1.FEHLERCODE
WHERE (c.[BV-05] IS NOT NULL) AND (d.LETZTERMASCHINENSTATUS <> 9)
GROUP BY a.MASCHINENNR) AS g ON a.MASCHINENNR = g.Auftrag LEFT OUTER JOIN
(SELECT MASCHINENNR, BDEINDEX, BDEINHALT
FROM AQIs.BDEDATEN
WHERE (BDEINDEX = 9)) AS h ON a.MASCHINENNR = h.MASCHINENNR
WHERE (b.DATUMAUSTRAG IS NULL) AND (c.[BV-05] IS NOT NULL) AND (d.LETZTERMASCHINENSTATUS <> 9) AND (h.BDEINHALT <> 'X' OR
h.BDEINHALT IS NULL)
GROUP BY a.MASCHINENNR
HAVING (MAX(c.BESTIMMUNGSLAND) <> 'ENTI FIAT') AND (MAX(c.BESTIMMUNGSLAND) <> 'COMPANY SERVICE') AND (a.MASCHINENNR <> '559461') AND
(a.MASCHINENNR <> '560303') AND (a.MASCHINENNR <> '561787')

and i want to change it to have the quality gate and kostestellen fields as well, but when i changed it according below the value of avg_repzeit_offen is different and i don't know why. can you help me please? the new one query is as below:

SELECT i.Quality_Gate, j.BEZEICHNER AS Kostenstelle_Ausführung, a.MASCHINENNR AS Auftrag, MAX(c.SERIENNUMMER) AS Seriennummer, MAX(c.FAMILIE) AS Benennung, MAX(c.BESTIMMUNGSLAND) AS Land, MAX(c.[BV-05])
AS Datum_Offline, MAX(c.OKTOSHIP) AS OKtoShip, MAX(d.LPLATZ) AS Letzter_Platz, SUM(ISNULL(e.AVG_RepZeit, 0)) AS AVG_RepZeit_Offen, COUNT(b.FEHLERCODE)
AS AnzFehler_Offen, MAX(g.AVG_RepZeit_Total) AS AVG_RepZeit_Total, MAX(f.A) AS AnzFehler_Total
FROM AQIs.AQIs.PRUEFUNGEN AS a INNER JOIN
AQIs.AQIs.PRUEFFEHLER AS b
ON a.PRUEFNR = b.PRUEFNR INNER JOIN
AQIs.AQIs.STATISTIK_STAMMDATEN AS c
ON a.MASCHINENNR = c.AUFTRAGSNUMMER INNER JOIN
AQIs.AQIs.STAMMDATEN AS d
ON a.MASCHINENNR = d.MASCHINENNR
LEFT JOIN AQIs.dbo.AQIS_Reporting_Matrix i
ON b.FEHLERCODE = i.Fehler_Code
INNER JOIN AQIs.AQIs.KOSTENSTELLE j
ON b.KST_VERURSACHER_ID = j.KOSTENSTELLE_ID
LEFT OUTER JOIN
(SELECT FEHLERCODE, MIN(ISNULL(REPARATURZEITMINUTEN, 0)) AS MIN_RepZeit, AVG(ISNULL(REPARATURZEITMINUTEN, 0)) AS AVG_RepZeit,
MAX(ISNULL(REPARATURZEITMINUTEN, 0)) AS MAX_RepZeit
FROM AQIs.AQIs.PRUEFFEHLER
WHERE (DATUMEINTRAG >= DATEADD(M, - 3, GETDATE()))
GROUP BY FEHLERCODE) AS e ON b.FEHLERCODE = e.FEHLERCODE
LEFT OUTER JOIN
(SELECT
c.Quality_Gate, d.BEZEICHNER AS Kostenstelle_Verursacher,Pruefungen.MASCHINENNR,
COUNT(Fehler.FEHLERCODE) AS A
FROM
AQIs.AQIs.PRUEFUNGEN AS Pruefungen
INNER JOIN AQIs.AQIs.PRUEFFEHLER AS Fehler
ON Pruefungen.PRUEFNR = Fehler.PRUEFNR
LEFT JOIN AQIs.dbo.AQIS_Reporting_Matrix c
ON Fehler.FEHLERCODE = c.Fehler_Code
INNER JOIN AQIs.AQIs.KOSTENSTELLE d
ON Fehler.KST_VERURSACHER_ID = d.KOSTENSTELLE_ID

GROUP BY
c.Quality_Gate, d.BEZEICHNER, Pruefungen.MASCHINENNR) AS f
ON i.Quality_Gate = f.Quality_Gate
AND j.BEZEICHNER = f.Kostenstelle_Verursacher
AND a.MASCHINENNR = f.MASCHINENNR
LEFT OUTER JOIN
((SELECT e.Quality_Gate, f.BEZEICHNER AS Kostenstelle_Ausführung, a.MASCHINENNR AS Auftrag, SUM(ISNULL(e_1.AVG_RepZeit, 0)) AS AVG_RepZeit_Total
FROM AQIs.AQIs.PRUEFUNGEN AS a INNER JOIN
AQIs.AQIs.PRUEFFEHLER AS b ON a.PRUEFNR = b.PRUEFNR INNER JOIN
AQIs.AQIs.STATISTIK_STAMMDATEN AS c ON a.MASCHINENNR = c.AUFTRAGSNUMMER
INNER JOIN AQIs.AQIs.KOSTENSTELLE f
ON b.KST_VERURSACHER_ID = f.KOSTENSTELLE_ID
INNER JOIN
AQIs.AQIs.STAMMDATEN AS d ON a.MASCHINENNR = d.MASCHINENNR LEFT OUTER JOIN
(SELECT FEHLERCODE, MIN(ISNULL(REPARATURZEITMINUTEN, 0)) AS MIN_RepZeit, AVG(ISNULL(REPARATURZEITMINUTEN, 0)) AS AVG_RepZeit,
MAX(ISNULL(REPARATURZEITMINUTEN, 0)) AS MAX_RepZeit
FROM AQIs.AQIs.PRUEFFEHLER AS PRUEFFEHLER_1
WHERE (DATUMEINTRAG >= DATEADD(M, - 3, GETDATE()))
GROUP BY FEHLERCODE) AS e_1 ON b.FEHLERCODE = e_1.FEHLERCODE
LEFT JOIN AQIs.dbo.AQIS_Reporting_Matrix e
ON b.FEHLERCODE = e.Fehler_Code
WHERE (c.[BV-05] IS NOT NULL) AND (d.LETZTERMASCHINENSTATUS <> 9)
GROUP BY e.Quality_Gate, f.BEZEICHNER, a.MASCHINENNR)) AS g ON i.Quality_Gate = g.Quality_Gate AND j.BEZEICHNER = g.Kostenstelle_Ausführung AND a.MASCHINENNR = g.Auftrag LEFT OUTER JOIN
(SELECT MASCHINENNR, BDEINDEX, BDEINHALT
FROM AQIs.AQIs.BDEDATEN
WHERE (BDEINDEX = 9)) AS h ON a.MASCHINENNR = h.MASCHINENNR
WHERE (b.DATUMAUSTRAG IS NULL) AND (c.[BV-05] IS NOT NULL) AND (d.LETZTERMASCHINENSTATUS <> 9) AND (h.BDEINHALT <> 'X' OR
h.BDEINHALT IS NULL)
GROUP BY i.Quality_Gate,j.BEZEICHNER, a.MASCHINENNR
HAVING (MAX(c.BESTIMMUNGSLAND) <> 'ENTI FIAT') AND (MAX(c.BESTIMMUNGSLAND) <> 'COMPANY SERVICE') AND (a.MASCHINENNR <> '559461') AND
(a.MASCHINENNR <> '560303') AND (a.MASCHINENNR <> '561787')

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-27 : 10:19:28

The two queries are really very different. The second(new) one has 2 new JOINS in the main query and that's not even counting the subqueries! What I do to debug this kind of thing:

1. Starting with the original query, change ONE thing. e.g. add ONE new join.
2. compare the original results with the new results, looking for a match between the original columns selected and those in the new query.
3. If there are any differences in values or numbers of rows returned, investigate why before proceeding.
4. If you find discrepancies, zero in on a small subset of the data (with additional WHERE predicates as needed) until you get matching results.
5. Repeat as necessary (back to step 2).
6. When the results from the ONE new join match your expectations, add the second new JOIN and repeat the entire process.

Since you know your data (and there appears to be a lot of it!) only you can perform this process.

mana
Posting Yak Master

102 Posts

Posted - 2015-01-28 : 02:17:54

hello

it is too complicated for me these two queries now. i am also new to sql server. I just wanted to add quality gate column and kostenstellen to the first query and for each auftrag there are different quality gates. can you help me pelase?

quote:
Originally posted by gbritton

The two queries are really very different. The second(new) one has 2 new JOINS in the main query and that's not even counting the subqueries! What I do to debug this kind of thing:

1. Starting with the original query, change ONE thing. e.g. add ONE new join.
2. compare the original results with the new results, looking for a match between the original columns selected and those in the new query.
3. If there are any differences in values or numbers of rows returned, investigate why before proceeding.
4. If you find discrepancies, zero in on a small subset of the data (with additional WHERE predicates as needed) until you get matching results.
5. Repeat as necessary (back to step 2).
6. When the results from the ONE new join match your expectations, add the second new JOIN and repeat the entire process.

Since you know your data (and there appears to be a lot of it!) only you can perform this process.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-28 : 07:08:33

I cannot help further without knowing your data. Have you follow the steps outlined in my previous post? Do that before coming back here with another question.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources