Query question

[georgesOne]

Good afternoon,

I have two related tables

Table Tools
ID Tool Keytool
1 A True
2 B True
3 C True
4 D False
5 E False
6 F True

Table Failure
FailID ToolID FailureDate Cost
1 1 3/21/2006 $5
2 1 3/27/2006 $3
3 1 5/13/2006 $3
4 2 3/22/2006 $5
5 2 6/20/2006 $3
6 3 6/12/2006 $1
7 5 3/24/2006 $5

For a graph, I want the sum of all StartDate (in March) 3/xx/xxxx records where Keytool= True (i.e. FailID #1 and #2 are matches), all records for keytools without existing Startdate (i.e. ToolID #6) as 0, and also records for keytools with a Startdate other than 3/xx/xxxx as ToolID #3 as 0, but only, if they have no Startdate 3/xx/xxxx, to give following graph data:

xVal yVal
A $8 (sum of two 3/xx/xxxx, the 5/xx/xxxx is excluded)
B $5 (one 3/xx/xxxx, the 6/xx/xxxx is excluded)
C $0 (no 3/xx/xxxx)
F $0 (no StartDate)

D and E are not included because they are no Keytools.
Each Keytool should be included.

How can I do that?

 

[PHV]

What have you tried so far and where in your SQL code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[georgesOne]

Hi PHV,

actually I have a solution (at least for one case), but it involves 4 queries:

Query 24:
SELECT DISTINCT ztbl_Tools1.Tool AS xVal, 0 AS yVal
FROM ztbl_Tools1 LEFT JOIN tbl_DownTime1 ON ztbl_Tools1.ToolID = tbl_DownTime1.Tool
WHERE ztbl_Tools1.KeyTool=True AND Month([StartDate])<>bss_fMonth() OR ztbl_Tools1.KeyTool=True AND Month([StartDate]) Is Null;

to get Keytools outside the date range with yVal = 0

Query 25:
SELECT ztbl_Tools1.Tool AS xVal, Count(tbl_DownTime1.StartDate) AS yVal
FROM ztbl_Tools1 INNER JOIN tbl_DownTime1 ON ztbl_Tools1.ToolID = tbl_DownTime1.Tool
WHERE ztbl_Tools1.KeyTool=True And month(tbl_DownTime1.StartDate)=bss_fMonth()
GROUP BY ztbl_Tools1.Tool;

to get number of startdates within the date range with the yVal

Query 26:
SELECT Query24.xVal As xVal, Query24.yVal As yVal From Query24
UNION Select Query25.xVal as xVal, Query25.yVal As yVal From Query25;

to join these data

Query 27:
SELECT Query26.xVal AS xVal, sum(Query26.yVal) AS yVal
FROM Query26
GROUP BY xVal;

to add them together.

But I think there must be a better way... I have about 50 graphs and I do not want to write 200 (or more) queries.

Thanks for your interest and help,

Georges

 

[georgesOne]

I should add:
this is for the number of failures, but I guess I can calculate the resulting costs, etc. in a similar way.
I feel that there is an option with '(NOT) IN' or '(NOT)EXISTS', but I can't figure it out.

Regards, Georges

 

[PHV]

Typed, untested:

SELECT T.Tool AS xVal, Count(D.StartDate) AS yVal
FROM ztbl_Tools1 AS T LEFT JOIN tbl_DownTime1 AS D ON T.ToolID = D.Tool
WHERE T.KeyTool=True AND Nz(Month(D.StartDate),0)=bss_fMonth()
GROUP BY T.Tool

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[georgesOne]

Thanks PHV,
that is exactly the result I get from my Query25.
For the graph, I would need to add all tools without any match and with matches not in that interval.
Any idea?
Regards, Georges

 

[PHV]

OOps, sorry for the typo:
WHERE T.KeyTool=True AND Nz(Month(D.StartDate),bss_fMonth())=bss_fMonth()

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[georgesOne]

Hi PH,
its getting better, but still not there...
However, already I learned something about more efficient query code. Thanks.
Your recent query yields(e.g. for month March)

1. the tools which failed in March and the number of failures
2. the tools which never failed with number of failures = 0

but I am still missing those tools which failed in a different month (and not in March) also with number of failures = 0.

Would appreciate, if you could help me with that!

Regards, Georges

 

[georgesOne]

Hi PHV,

with the following one-step Query26 I am at the level of the previous three step Query26:

Query 26
SELECT DISTINCT T.Tool AS xVal, 0 AS yVal
FROM ztbl_Tools1 AS T LEFT JOIN tbl_DownTime1 AS D ON T.ToolID = D.Tool
WHERE (((T.KeyTool)=True) AND ((Month([StartDate]))<>bss_fMonth())) OR (((T.KeyTool)=True) AND ((Month([StartDate])) Is Null))
UNION SELECT T.Tool AS xVal, Count(D.StartDate) AS yVal
FROM ztbl_Tools1 AS T LEFT JOIN tbl_DownTime1 AS D ON T.ToolID = D.Tool
WHERE (((T.KeyTool)=True) AND ((Nz(Month([D].[StartDate]),bss_fMonth()))=bss_fMonth()))
GROUP BY T.Tool;

Now, is there a way to combine this with Query 27:

Query27:
SELECT Query26.xVal AS xVal, sum(Query26.yVal) AS yVal
FROM Query26
GROUP BY xVal;

Then, I would be where I want to be...

Regards, Georges