Chart problem: count of dates 1

[danidgf]

Hello,

I have a table (in fact a view) with Jobs for Equipment. Every Equipment can have several Jobs. The fields of concern are:
- EquipmentID
- StartDate

In a Numeric Axis Chart I want for the x-axis the different years in StartDate: so the field is placed in "On change of" and Order: per Year

For the y-axis I want to count the maximum of StartDate for every EquipmentID. This means that I want to count per year the last Job every Equipment was repaired.

I tried this with a calculation field:
Maximum ({vJobSce.StartDate},{vJobSce.EquipmentID} )

and also with a SQL expression (and linked to the Job table through EquipmentID):
(
Select EquipmentID, Max(StartDate)
from vJobSce
group by EquipmentID
)

But I get always the count for every StartDate in the table per year?

Than you for your valuable help.

 

[lbass]

I think your SQL expression would have failed (unless you were really creating a command?). Anyway, try setting up your SQL expression like this:

(
Select Max(`StartDate`)
from vJobSce A
where A.`EquipmentID` = vJobSce.`EquipmentID`
)

This assumes that the start date and equipment ID field are found in the vJobSce table. The table vJobSce must also be used in your main report. Also note that the punctuation will be specific to your datasource.

-LB

 

[danidgf]

Thanks lbass for your quick reply, but ...

When I use this SQL Expression in a Command (Table), I get the error: "The column prefix 'vJobSce' does not match with a table name or alias name used in the query."

For my Chart I need the two dates: StartDate and Max(StartDate)

When I try another self join syntax:

SELECT MAX(T1.StartDate) AS Expr1
FROM vJobSce T1 LEFT OUTER JOIN
vJobSce T2 ON T1.EquipmentID = T2.EquipmentID
GROUP BY T1.EquipmentID

I get the correct maxima of the dates but I can not get the StartDates as well (for my x-axis)

Thanks for your reply

 

[lbass]

What I showed you was a SQL expression (field explorer->SQL expression->new), NOT a command. For a command, try the following:

Select vJobSce.`Start Date`,
(
Select Max(`StartDate`)
from vJobSce A
where A.`EquipmentID` = vJobSce.`EquipmentID`
) as MaxDate
From vJobSce

The syntax and punctuation would be specific to your datasource though.

-LB

 

[danidgf]

You are right lbass, I thought you were talking about a SQL in a command (table)! My mistake.

Your command works, thanks for that but this is not a good solution for me, due to my question I guess.

The solution however is more simple. I made the following Command:

Select EquipmentID, max(StartDate)
from vJobSce
Group by EquipmentID

In my chart I take On change of: "Command.Expr1001 - A" and Order: per Year
For Show value(s): "Count Command.Expr1001"

Thank you very much lbass, you made my day!