Limit SQL query results using an aggregate formula 1

[czab]

I'm fairly new to SQL, so I'm not sure if my problem is something fairly simple or it's just not possible to do with SQL.

I have a query that gives me the information I want, but I need to limit the results based on an aggregate formula (I think that's what I want to do)

This is my present query:

SELECT
detail.TCONO as COMPANY,
detail.TJBNO as JOB_NUM,
detail.TCSTY as COST_TYPE,
Sum(detail.TAMAT) AS AMOUNT,
Sum(detail.TRGHR) AS REG_HOURS,
Sum(detail.TOVHR) AS OVT_HOURS,
Sum(detail.TOTHR) AS OTH_HOURS,
summary.DESCR AS DESCRIPTION

FROM detail INNER JOIN summary ON (detail.TJBNO = summary.GJBNO) AND (detail.TDVNO = summary.GDVNO) AND (detail.TCONO = summary.GCONO)

WHERE EXISTS (SELECT * FROM summary WHERE summary.GDTLU >= 20050101 AND detail.TJBNO = summary.GJBNO AND summary.GCONO = 1 AND summary.GDVNO = 0)

AND (detail.TCONO = 1 AND detail.TDVNO = 0)

GROUP BY detail.TCONO, detail.TJBNO, detail.TCSTY, summary.DESCR


Like I said, the present SQL works fine, but I need tweak it a little more.

I'm trying to limit my report to pull data ONLY for jobs that have a loss.
The Amount field is always a positive number.
The Cost Type determines whether the Amount is Income (CostType = "I") or Expense (CostType <> "I").

I know what I want to do, but I don't know how to write it in SQL.

Simply put, in English, I want to exclude jobs where:
(sum(Amount) where Cost Type = "I") < (sum(Amount) where Cost Type <> "I")

I don't know if this can even be translated into SQL or I'm trying to do something impossible.

This is what I came up with:

SELECT
detail.TCONO as COMPANY,
detail.TJBNO as JOB_NUM,
detail.TCSTY as COST_TYPE,
Sum(detail.TAMAT) AS AMOUNT,
Sum(detail.TRGHR) AS REG_HOURS,
Sum(detail.TOVHR) AS OVT_HOURS,
Sum(detail.TOTHR) AS OTH_HOURS,
summary.DESCR AS DESCRIPTION

FROM detail INNER JOIN summary ON (detail.TJBNO = summary.GJBNO) AND (detail.TDVNO = summary.GDVNO) AND (detail.TCONO = summary.GCONO)

WHERE EXISTS (SELECT * FROM summary WHERE summary.GDTLU >= 20050101 AND detail.TJBNO = summary.GJBNO AND summary.GCONO = 1 AND summary.GDVNO = 0 AND (Sum(detail.TAMAT) WHERE detail.TCSTY = 'I') < (Sum(detail.TAMAT) WHERE detail.TCSTY <> 'I'))

AND (detail.TCONO = 1 AND detail.TDVNO = 0)

GROUP BY detail.TCONO, detail.TJBNO, detail.TCSTY, summary.DESCR

When I parse the query, I get the following error message:

Connection or SQL sentence error: (DA00005)
[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0199 - Keyword WHERE not expected. Valid tokens: < > = <> <= !< !> != >= ¬< ¬> ¬= IN IS NOT LIKE BETWEEN.-199

Like I said, I'm new to SQL and don't have a clue if this is possible with SQL or not. Apparently it definitely won't work the way I tried to do it.

Is there a way to do it?

 

[LKBrwnDBA]

Try using the 'HAVING' statement:

SELECT ...etc...
WHERE EXISTS (SELECT * FROM summary WHERE summary.GDTLU  >= 20050101 AND  detail.TJBNO = summary.GJBNO AND summary.GCONO = 1 AND  summary.GDVNO = 0)
HAVING (Sum(CASE WHEN detail.TCSTY = 'I' THEN detail.TAMAT ELSE 0 END) 
< (Sum(CASE WHEN detail.TCSTY <> 'I' THEN detail.TAMAT ELSE 0 END))

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[rac2]

Something which SQL does not do is compare one row to another. In this case the value of Sum(detail.TAMAT) on a line with detail.TCSTY = 'I' needs to be compared to another line with detail.TCSTY <> 'I'. The trick is to write a query in such a way that these values appear in the same row.

That is accomplished through JOINing subqueries.

A query with just the income values -

SELECT
   detail.TCONO as COMPANY, 
   detail.TJBNO as JOB_NUM, 
   Sum(detail.TAMAT) AS INCOME
FROM detail
WHERE detail.TCSTY = 'I'
GROUP BY detail.TCONO, detail.TJBNO, detail.TCSTY

This yields rows with income for all companies and jobs.

The corresponding query for cost-

SELECT
   detail.TCONO as COMPANY, 
   detail.TJBNO as JOB_NUM, 
   Sum(detail.TAMAT) AS COST
FROM detail
WHERE detail.TCSTY <> 'I'
GROUP BY detail.TCONO, detail.TJBNO

And another for hours, regardless of cost and income-

SELECT
   detail.TCONO as COMPANY, 
   detail.TJBNO as JOB_NUM, 
   Sum(detail.TRGHR) AS REG_HOURS,
   Sum(detail.TOVHR) AS OVT_HOURS, 
   Sum(detail.TOTHR) AS OTH_HOURS
FROM detail
GROUP BY detail.TCONO, detail.TJBNO

These can be combined to show the Income, Cost, and Hours for every company and job.

SELECT
   a.COMPANY, 
   a.JOB_NUM, 
   a.INCOME,
   b.COST,
   c.REG_HOURS,
   c.OVT_HOURS, 
   c.OTH_HOURS
FROM (
      SELECT
            detail.TCONO as COMPANY, 
            detail.TJBNO as JOB_NUM, 
            Sum(detail.TAMAT) AS INCOME
            FROM detail
            WHERE detail.TCSTY = 'I'
            GROUP BY detail.TCONO, detail.TJBNO,
                     detail.TCSTY
      ) a

JOIN (
       SELECT
             detail.TCONO as COMPANY, 
             detail.TJBNO as JOB_NUM, 
             Sum(detail.TAMAT) AS COST
             FROM detail
             WHERE detail.TCSTY <> 'I'
             GROUP BY detail.TCONO, detail.TJBNO,
                      detail.TCSTY
     ) b ON b.COMPANY = a.COMPANY
        AND b.JOB_NUM = a.JOB_NUM

JOIN (
      SELECT
             detail.TCONO as COMPANY, 
             detail.TJBNO as JOB_NUM, 
             Sum(detail.TRGHR) AS REG_HOURS,
             Sum(detail.TOVHR) AS OVT_HOURS, 
             Sum(detail.TOTHR) AS OTH_HOURS
             FROM detail
             GROUP BY detail.TCONO, detail.TJBNO,
                      detail.TCSTY
     ) c ON c.COMPANY = a.COMPANY
        AND c.JOB_NUM = a.JOB_NUM

WHERE a.INCOME < b.COST

This yields one row for each company and job with the cost and income in the same row. Thus the two values can be compared and used as a condition in a WHERE clause as shown.


The further conditions represented in the EXISTS clause can also be achieved through JOINing the Summary table.

SELECT
   a.COMPANY, 
   a.JOB_NUM, 
   a.INCOME,
   b.COST,
   c.REG_HOURS,
   c.OVT_HOURS, 
   c.OTH_HOURS,
   d.DESCR AS DESCRIPTION
FROM (
      SELECT
            detail.TCONO as COMPANY, 
            detail.TJBNO as JOB_NUM, 
            Sum(detail.TAMAT) AS INCOME
            FROM detail
            WHERE detail.TCSTY = 'I'
            GROUP BY detail.TCONO, detail.TJBNO,
                     detail.TCSTY
      ) a

JOIN (
       SELECT
             detail.TCONO as COMPANY, 
             detail.TJBNO as JOB_NUM, 
             Sum(detail.TAMAT) AS COST
             FROM detail
             WHERE detail.TCSTY <> 'I'
             GROUP BY detail.TCONO, detail.TJBNO,
                      detail.TCSTY
     ) b ON b.COMPANY = a.COMPANY
        AND b.JOB_NUM = a.JOB_NUM

JOIN (
      SELECT
             detail.TCONO as COMPANY, 
             detail.TJBNO as JOB_NUM, 
             Sum(detail.TRGHR) AS REG_HOURS,
             Sum(detail.TOVHR) AS OVT_HOURS, 
             Sum(detail.TOTHR) AS OTH_HOURS
             FROM detail
             GROUP BY detail.TCONO, detail.TJBNO,
                      detail.TCSTY
     ) c ON c.COMPANY = a.COMPANY
        AND c.JOB_NUM = a.JOB_NUM

JOIN Summary d ON d.GCONO = a.COMPANY
              AND d.GJBNO = a.JOB_NUM
              AND d.GDVNO = 0
              AND d.GDTLU  >= 20050101

This JOIN also allows us to pick up the description from the Summary table.

The GDVNO (gol darned vendor number?) is limited to the value 0, therefore there is no need to use it in the JOIN condition. Right?

One other note, these queries require that every company job has details for both income and cost. If that is not true, then use LEFT JOIN instead for the subqueries a, b, and c. You can test this by running the query both ways, with JOIN and with LEFT JOIN. Probably best to use the LEFT JOIN, just to cover the early phase when no income has been recorded in the detail table.

 

[PHV]

Perhaps something like this ?
SELECT
detail.TCONO as COMPANY,
detail.TJBNO as JOB_NUM,
detail.TCSTY as COST_TYPE,
Sum(detail.TAMAT) AS AMOUNT,
Sum(detail.TRGHR) AS REG_HOURS,
Sum(detail.TOVHR) AS OVT_HOURS,
Sum(detail.TOTHR) AS OTH_HOURS,
summary.DESCR AS DESCRIPTION,
Sum(CASE WHEN detail.TCSTY = 'I' THEN detail.TAMAT ELSE -detail.TAMAT END) AS LOSS
FROM detail INNER JOIN summary ON (detail.TJBNO = summary.GJBNO) AND (detail.TDVNO = summary.GDVNO) AND (detail.TCONO = summary.GCONO)
WHERE EXISTS (SELECT * FROM summary WHERE summary.GDTLU >= 20050101 AND detail.TJBNO = summary.GJBNO AND summary.GCONO = 1 AND summary.GDVNO = 0)
AND (detail.TCONO = 1 AND detail.TDVNO = 0)
GROUP BY detail.TCONO, detail.TJBNO, detail.TCSTY, summary.DESCR
HAVING Sum(CASE WHEN detail.TCSTY = 'I' THEN detail.TAMAT ELSE -detail.TAMAT END) < 0

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[czab]

Thanks for you help LKBrwnDBA.

I've just tried fooling around with HAVING, but can't get it to do what I want.

If I add the HAVING statement in with the WHERE EXISTS (SELECT)... clause, I get the following error message:

[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0122 - Column GSTAT or expression in SELECT list not valid.-122

If I add the HAVING statement after the GROUP by clause it drops all the income lines and includes all the expenses.



rac2, I think you're on the right track. Give me some time to chew on your code. I'll let you know how it works. GDVNO is the division number. I'm actually using variables in my real report. I just wanted to simplify things for posting in the forum. It does need to be joined. There are definitely jobs that have NO income. The purpose of my query is to find out which jobs are underbilled or have never been billed.

 

[LKBrwnDBA]

Ora maybe try this:

SELECT
   detail.TCONO as COMPANY, 
   detail.TJBNO as JOB_NUM, 
   detail.TCSTY as COST_TYPE, 
   Sum(detail.TAMAT) AS AMOUNT, 
   Sum(detail.TRGHR) AS REG_HOURS,
   Sum(detail.TOVHR) AS OVT_HOURS, 
   Sum(detail.TOTHR) AS OTH_HOURS, 
   summary.DESCR AS DESCRIPTION
FROM detail INNER JOIN summary 
               ON (detail.TJBNO = summary.GJBNO) 
              AND (detail.TDVNO = summary.GDVNO) 
              AND (detail.TCONO = summary.GCONO)

JOIN (
	SELECT
	   detail.TCONO as COMPANY, 
	   detail.TJBNO as JOB_NUM, 
	   Sum(CASE WHEN detail.TCSTY = 'I' THEN detail.TAMAT ELSE -detail.TAMAT END) AS AMOUNT
	FROM detail
	WHERE EXISTS (SELECT * FROM summary 
		       WHERE summary.GDTLU  >= 20050101
			 AND detail.TJBNO = summary.GJBNO
			 AND  summary.GCONO = 1
			 AND  summary.GDVNO = 0)  
	  AND (detail.TCONO = 1 AND detail.TDVNO = 0)
	GROUP BY detail.TCONO, detail.TJBNO, detail.TCSTY, summary.DESCR
	HAVING Sum(CASE WHEN detail.TCSTY = 'I' THEN detail.TAMAT ELSE -detail.TAMAT END) < 0 
     ) c ON c.COMPANY = detail.COMPANY
        AND c.JOB_NUM = detail.JOB_NUM
GROUP BY detail.TCONO, detail.TJBNO, detail.TCSTY, summary.DESCR

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[czab]

Maybe I'm way out of my league. I swear, yesterday I was feeling smart. Today I'm feeling stupid.

rac2, I've been working with your code, but the joins make it all so complicated.

My purpose in using the GDTLU field in the summary file is to limit the results to jobs that have had activity during the present year (> 20050101). I know I need to do some more work on this, but for now, the numbers are coming back way off and I'm getting jobs that shouldn't be there based on the GDTLU criteria selection.

Just to clarify, I have the query set up to run for one company and one division at a time. This is entered at runtime into a variable field.

My query presently returns data similar to this:

Job#  CsTy   Amount  Descr    RegHrs  OvtHrs OthHrs
123    I    700.00   Job 1     0.00   0.00    0.00
123    B     50.00   Job 1     0.00   0.00    0.00
123    L    400.00   Job 1     5.00   1.00    0.00
123    M    350.00   Job 1     0.00   0.00    0.00

I need the Cost Type breakdown so I can show that for Job #123, Income = $700, Total Cost = $800, Net Income/Loss =(100), Material = $350, Labor = $400, Burden = $50, Total Hours = 6.

I can get this information with the code I have now. However, if the income line was 800 instead of 700 I wouldn't want Job 123 included in my results.

 

[PHV]

Just to know, have you tried my suggestion stamped 20 Dec 05 14:33 ?

 

[czab]

PHV, I ran out of time at work. I'm posting from home. I'll definitely give your approach a try tomorrow. It looks promising.

Thanks.

 

[czab]

PHV, when I tried your code, it still didn't limit the report to ONLY the jobs with a loss. It also didn't include any of the income on the report. It worked more or less like the first post by LKBrwnDBA.

LKBrwnDBA, when I tried your modified suggestion, the report gave me EXACTLY what I needed and nothing more.

Thanks for your help guys. I learned a lot from this little exercise. I managed to tweak my query even better than I imagined I could. The CASE and HAVING function/clause make everything work so much better.

Thanks again!!!