Hi all,

I am looking for an elegant way to join / or union these queries so I can get a final query result where all my results are on one line


eg.

SpecId | InstIID | Passed | ReAnalyzed | sent | TAT
 abc         1              2               10       minutesTAT + minutesTAT + minutesTAT ---- from all 3 queries


Select SpecId,'Passed' as aState,InstIID,round(((max(AcquiredDT) - min(AcquiredDT)) / (1000 * 60))) as minutesTAT
FROM Specs
WHERE SpecId IN (SELECT SpecId FROM Specs where state IN (3))
Group By SpecID
order by InstIID,SpecID;


Select SpecId,'ReAnalyzed' as aState,InstIID,round(((max(AcquiredDT) - min(AcquiredDT)) / (1000 * 60))) as minutesTAT
FROM Specs
WHERE SpecId IN (SELECT SpecId FROM Specs where state IN (9))
Group By SpecID
order by InstIID,SpecID;


Select SpecId,'Sent' as aState,InstIID,round(((max(AcquiredDT) - min(AcquiredDT)) / (1000 * 60))) as minutesTAT
FROM Specs
WHERE SpecId IN (SELECT SpecId FROM Specs where state IN (11))
Group By SpecID
order by InstIID,SpecID;



Thanks for the help

Silven

Silven,

<< I am looking for an elegant way to join / or union these queries so I can
get a final query result where all my results are on one line >>

Use this:

SELECT InstID, SpecID,
SUM(IF(State=3 THEN round(((max(AcquiredDT) - min(AcquiredDT)) / (1000 *
60))) ELSE 0)) as Passed,
SUM(IF(State=9 THEN round(((max(AcquiredDT) - min(AcquiredDT)) / (1000 *
60))) ELSE 0)) as ReAnalyzed,
SUM(IF(State=11 THEN round(((max(AcquiredDT) - min(AcquiredDT)) / (1000 *
60))) ELSE 0)) as Sent,
round(((max(AcquiredDT) - min(AcquiredDT)) / (1000 * 60))) AS TAT
FROM Specs
WHERE State IN (3,9,11)
GROUP By InstID, SpecID

No need for the ORDER BY - the GROUP BY will order the results accordingly.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Silven,

<< I am looking for an elegant way to join / or union these queries so I can
get a final query result where all my results are on one line >>

Use this:

SELECT InstID, SpecID,
SUM(IF(State=3 THEN round(((max(AcquiredDT) - min(AcquiredDT)) / (1000 *
60))) ELSE 0)) as Passed,
SUM(IF(State=9 THEN round(((max(AcquiredDT) - min(AcquiredDT)) / (1000 *
60))) ELSE 0)) as ReAnalyzed,
SUM(IF(State=11 THEN round(((max(AcquiredDT) - min(AcquiredDT)) / (1000 *
60))) ELSE 0)) as Sent,
round(((max(AcquiredDT) - min(AcquiredDT)) / (1000 * 60))) AS TAT
FROM Specs
WHERE State IN (3,9,11)
GROUP By InstID, SpecID

No need for the ORDER BY - the GROUP BY will order the results accordingly.


Hi Tim, Thanks for the response, however, the query gives me an error: invalid nesting of aggregate functions.  I am using 4.25 build 3.


Thanks,
Silven

Silven,

<< Hi Tim, Thanks for the response, however, the query gives me an error:
invalid nesting of aggregate functions.  I am using 4.25 build 3. >>

In that case, you're on the wrong newsgroup - this is the ElevateDB
newsgroup.   As for the query, use this:

SELECT InstID, SpecID,
IF(State=3 THEN round(((max(AcquiredDT) - min(AcquiredDT)) / (1000 * 60)))
ELSE 0) as Passed,
IF(State=9 THEN round(((max(AcquiredDT) - min(AcquiredDT)) / (1000 * 60)))
ELSE 0) as ReAnalyzed,
IF(State=11 THEN round(((max(AcquiredDT) - min(AcquiredDT)) / (1000 * 60)))
ELSE 0) as Sent,
round(((max(AcquiredDT) - min(AcquiredDT)) / (1000 * 60))) AS TAT
FROM Specs
WHERE State IN (3,9,11)
GROUP By InstID, SpecID

I shouldn't have included the SUM() calls in there.

--
Tim Young
Elevate Software
www.elevatesoft.com

Sorry for the newsgroup mix up

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Silven,

<< Hi Tim, Thanks for the response, however, the query gives me an error:
invalid nesting of aggregate functions.  I am using 4.25 build 3. >>

In that case, you're on the wrong newsgroup - this is the ElevateDB
newsgroup.   As for the query, use this:

SELECT InstID, SpecID,
IF(State=3 THEN round(((max(AcquiredDT) - min(AcquiredDT)) / (1000 * 60)))
ELSE 0) as Passed,
IF(State=9 THEN round(((max(AcquiredDT) - min(AcquiredDT)) / (1000 * 60)))
ELSE 0) as ReAnalyzed,
IF(State=11 THEN round(((max(AcquiredDT) - min(AcquiredDT)) / (1000 * 60)))
ELSE 0) as Sent,
round(((max(AcquiredDT) - min(AcquiredDT)) / (1000 * 60))) AS TAT
FROM Specs
WHERE State IN (3,9,11)
GROUP By InstID, SpecID

I shouldn't have included the SUM() calls in there.

--
Tim Young
Elevate Software
www.elevatesoft.com

Now I get the error Invalide Use on Non-Aggregate column in expression... query does not like the if statement.

silven <silven@canada.com> wrote:

Sorry for the newsgroup mix up

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Silven,

<< Hi Tim, Thanks for the response, however, the query gives me an error:
invalid nesting of aggregate functions.  I am using 4.25 build 3. >>

In that case, you're on the wrong newsgroup - this is the ElevateDB
newsgroup.   As for the query, use this:

SELECT InstID, SpecID,
IF(State=3 THEN round(((max(AcquiredDT) - min(AcquiredDT)) / (1000 * 60)))
ELSE 0) as Passed,
IF(State=9 THEN round(((max(AcquiredDT) - min(AcquiredDT)) / (1000 * 60)))
ELSE 0) as ReAnalyzed,
IF(State=11 THEN round(((max(AcquiredDT) - min(AcquiredDT)) / (1000 * 60)))
ELSE 0) as Sent,
round(((max(AcquiredDT) - min(AcquiredDT)) / (1000 * 60))) AS TAT
FROM Specs
WHERE State IN (3,9,11)
GROUP By InstID, SpecID

I shouldn't have included the SUM() calls in there.

--
Tim Young
Elevate Software
www.elevatesoft.com

Silven,

<< Now I get the error Invalide Use on Non-Aggregate column in expression...
query does not like the if statement. >>

Crap, sorry.  You can't use a non-aggregate column in an expression along
with aggregate expressions because it is impossible to calculate the proper
non-aggregate column value.  What you want to do is move the IF() condition
inside of the MAX() and MIN() calls, using whatever value you need for the
State columns that don't match.   That should fix the issue.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim based on our discussions I changed my Query requirements: please note I am still posting in the wrong newsgroup since we continued our discussion here

SPECID            SpecidVersion    State       AcquiredDT

NB15127534      97                        4           09-07-2007 00:48:00
NB15127534      98                        9           09-07-2007 00:54:00
NB15127534      99                        11         09-07-2007 00:49:00

I would like to calculate Max(state) AcquiredDT - Min(state) AcquiredDT is this possible with DBISams in one query


Thanks for all your help.

Silven




"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Silven,

<< Now I get the error Invalide Use on Non-Aggregate column in expression...
query does not like the if statement. >>

Crap, sorry.  You can't use a non-aggregate column in an expression along
with aggregate expressions because it is impossible to calculate the proper
non-aggregate column value.  What you want to do is move the IF() condition
inside of the MAX() and MIN() calls, using whatever value you need for the
State columns that don't match.   That should fix the issue.

--
Tim Young
Elevate Software
www.elevatesoft.com

Silven,

<< I would like to calculate Max(state) AcquiredDT - Min(state) AcquiredDT
is this possible with DBISams in one query >>

I need a little more information than you're providing here. Are you trying
to find the associated AcquiredDT column value for a given MAX or MIN State
column value ?  Could you possibly give me an example of the output that you
need ?  I'm assuming that what you're showing in your message is the input,
not the output.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim, Thanks for all the help...

from the example of input data below:  I would like to calculate the difference (Turn Around Time) between the AcquiredDT for a Spec
Turn Around Time = Spec's max(state) AcquiredDT - Spec's min(state) AcquiredDT
in the example code entries below I need to see something like:

SPECID            TAT
NB15127534     1
NB15127535     6  


INPUT table:Specs

SPECID            SpecidVersion    State       AcquiredDT
NB15127534      97                        4           09-07-2007 00:48:00
NB15127534      98                        9           09-07-2007 00:54:00
NB15127534      99                        11         09-07-2007 00:49:00
NB15127535      97                        4           09-07-2007 00:49:00
NB15127535      98                        9           09-07-2007 00:55:00


Thanks for all your help you have been more than kind



"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Silven,

<< I would like to calculate Max(state) AcquiredDT - Min(state) AcquiredDT
is this possible with DBISams in one query >>

I need a little more information than you're providing here. Are you trying
to find the associated AcquiredDT column value for a given MAX or MIN State
column value ?  Could you possibly give me an example of the output that you
need ?  I'm assuming that what you're showing in your message is the input,
not the output.

--
Tim Young
Elevate Software
www.elevatesoft.com