ASP.NET, SQL Server, WPF, C#, VB.NET

Hi, 
 
I have a complex SQL Query that I am having some trouble with.  I have 
 
2 tables TestTable and Actual Table.  I need to manipulate data from 
 
each table for 2 different time periods.  Using aliases for the tables, 
 
I am able to get the correct values I need for the two time periods. 
 
However, I don't want to show all the results.  The average values are 
 
typically between -50 and 50.  I want only those whose absolute value 
 
is greater than 5 to be displayed.  Is there a way to hide the rest? 
 
Also, is there a way to sort them by the absolute value? 
 
SELECT Avg(T1.Param1-E1.Param1)-Avg(T2.Param1-E2.Param1) AS DiffP1, 
 
Avg(T1.Param2-E1.Param2)-Avg(T2.Param2-E2.Param2) AS 
 
DiffP2, 
 
Avg(T1.Param3-E1.Param3)-Avg(T2.Param3-E2.Param3) AS 
 
DiffP3 
 
Avg(T1.Param4-E1.Param4)-Avg(T2.Param4-E2.Param4) AS 
 
DiffP4 
 
Avg(T1.Param5-E1.Param5)-Avg(T2.Param5-E2.Param5) AS 
 
DiffP5 
 
Avg(T1.Param6-E1.Param6)-Avg(T2.Param6-E2.Param6) AS 
 
DiffP6 
 
Avg(T1.Param7-E1.Param7)-Avg(T2.Param7-E2.Param7) AS 
 
DiffP7 
 
Avg(T1.Param8-E1.Param8)-Avg(T2.Param8-E2.Param8) AS 
 
DiffP8 
 
Avg(T1.Param9-E1.Param9)-Avg(T2.Param9-E2.Param9) AS 
 
DiffP9 
 
Avg(T1.Param10-E1.Param10)-Avg(T2.Param10-E2.Param10) AS 
 
DiffP10 
 
FROM TestTable AS T1 INNER JOIN ActualTable AS E1 ON 
 
T1.DataTime=E1.DataTime, TestTable AS T2 INNER JOIN ActualTable AS E2 
 
ON T2.DataTime=E2.DataTime 
 
WHERE (((T1.DataTime) Between [Start Date] And [End Date]) AND 
 
((T2.DataCaptureTime) Between [Start Date 2] And [End Date 2])); 
 
Right now my results appear as: 
 
DiffP1   DiffP2    DiffP3   DiffP4   DiffP5   DiffP6   DiffP7   DiffP8 
 
DiffP9   DiffP10 
 
-10         12         3         -6         -7         27       -14 
 
-4         -8         9 
 
I would like the following: 
 
DiffP6      DiffP7       DiffP2  DiffP1   DiffP10       DiffP9 
 
DiffP5     DiffP4 
 
27          -14            12      -10        9                -8 
 
-7           -6 
 
And columns with DiffP3, DiffP8 are eliminated. 
 
Any help would be greatly appreciated!! 
 
Jen

Add HAVING clauses to the query for the field where you want to filter the 
 
results as you indicate. Structure of HAVING clause is same as WHERE clause, 
 
but it goes after the GROUP BY clause (which comes after the WHERE clause). 
 
You don't appear to have a GROUP BY clause, so put it after the WHERE 
 
clause. 
 
If you use the query grid, you do this by putting the criterion in the WHERE 
 
box under the column that contains the AVG field of choice. 
 
-- 
 
Ken Snell 
 
<MS ACCESS MVP> 
 
"Jen" <leonard522@aol.com> wrote in message 
 
news:1152237267.164390.95870@s13g2000cwa.googlegroups.com...

I don't think so easily.... 
 
you are going to have to go "thin" 
 
(then maybe convert back if you want) 
 
save following (say as "qryunThin") 
 
(change "qJen" to name of your shown query) 
 
SELECT "DiffP1" As f, DiffP1 As v, Abs(DiffP1) As a FROM qJen 
 
UNION ALL 
 
SELECT "DiffP2", DiffP2, Abs(DiffP2) FROM qJen 
 
UNION ALL 
 
SELECT "DiffP3", DiffP3, Abs(DiffP3) FROM qJen 
 
UNION ALL 
 
SELECT "DiffP4", DiffP4, Abs(DiffP4) FROM qJen 
 
UNION ALL 
 
SELECT "DiffP5", DiffP5, Abs(DiffP5) FROM qJen 
 
UNION ALL 
 
SELECT "DiffP6", DiffP6, Abs(DiffP6) FROM qJen 
 
UNION ALL 
 
SELECT "DiffP7", DiffP7, Abs(DiffP7) FROM qJen 
 
UNION ALL 
 
SELECT "DiffP8", DiffP8, Abs(DiffP8) FROM qJen 
 
UNION ALL 
 
SELECT "DiffP9", DiffP9, Abs(DiffP9) FROM qJen 
 
UNION ALL 
 
SELECT "DiffP10", DiffP10, Abs(DiffP10) FROM qJen; 
 
then following query (say "qryThin") 
 
will then filter and sort "thinly" 
 
SELECT 
 
q.f, 
 
q.v 
 
FROM qryunThin As q 
 
WHERE 
 
q.a >5 
 
ORDER BY 
 
q.a DESC; 
 
which, using your data, would give: 
 
f            v 
 
DiffP6  27 
 
DiffP7 -14 
 
DiffP2  12 
 
DiffP1 -10 
 
DiffP10  9 
 
DiffP9   -8 
 
DiffP5   -7 
 
DiffP4   -6 
 
you could jump through some hoops 
 
to make this result wide again, but 
 
if you use a crosstab and pivot off 
 
of f, you will lose the "sort" 
 
TRANSFORM First(qryThin.v) AS FirstOfv 
 
SELECT Null AS <> 
 
FROM qryThin 
 
GROUP BY Null 
 
PIVOT qryThin.f; 
 
would give: 
 
<> DiffP1 DiffP2 DiffP4 DiffP5 DiffP6 DiffP7 DiffP9 DiffP10 
 
-10       12        -6       -7       27      -14     -8        9 
 
hoops w/o desired result.... 
 
I cannot immediately think of some simple, sneaky 
 
trick to get back the sort...sorry 
 
"Jen" wrote: