Brain Asplodes! Please help with Aggregate query!!

[Solo4357]

Ok, I got two tables. On is job detail table. In the Job detail table there is an amount field JCDT_AMT, a Job Number Field JCDT_JOB_NUM, a job control number field JCDT_JOB_CTRL_CODE, and a Job Type Field JCDT_JOB_TYPE.

In the other table is the project managers and their job numbers. PROJMAN and Job_CODE.

I need sum up job amounts where job_type = c and where job_type = b. It has to join JOB_NUM in either JCDT_JOB_CODE or JCDT_JOB_CTRL_CODE. If it's JCDT_Job_code, the JOB_CTRL_CODE = 'ALL' but sometimes it only matches with JOB_CTRL_CODE.

Oh and I want to be able to break it up by a selected POST_DATE (also in the job detail table.)

I don't want the job numbers, I just want one line that will do it. Here's what I got:

SELECT  UETD_PROJECTPER.PROJECTMAN, SUM(decode(JCDETAIL.JCDT_TYPE_CODE, 'B', JCDETAIL.JCDT_AMT, 0)) AS BILLED, SUM(decode(JCDETAIL.JCDT_TYPE_CODE, 'C', JCDETAIL.JCDT_AMT, 0)) AS C_SUM, JCDETAIL.JCDT_JOB_CODE

FROM         UETD_PROJECTPER, JCDETAIL

WHERE     (UETD_PROJECTPER.JOB_CODE = JCDETAIL.JCDT_JOB_CTRL_CODE OR
                      UETD_PROJECTPER.JOB_CODE = JCDETAIL.JCDT_JOB_CODE) AND (JCDETAIL.JCDT_POST_DATE < TO_DATE('1/1/2007', 'MM/DD/YYYY')) AND 
                      (JCDETAIL.JCDT_POST_DATE >= TO_DATE('1/1/2006', 'MM/DD/YYYY'))
GROUP BY  UETD_PROJECTPER.PROJECTMAN 
ORDER BY UETD_PROJECTPER.PROJECTMAN

The problem is that the numbers are wrong. Upon further checks, I find that when a record is selected where JCDT_JOB_CODE = UETD_JOB_CODE, that record has JCDT_JOB_CTRL_CODE = 'ALL'. So the the sum for that job number is correct. But when I need to link it with the JOB_CTRL_NUM, then the sum comes up quadruple what it should be.

I've played with case statements, but they don't seem to work after the where clause. Union also doesn't seem to work with grouped-queries. I've even used DISTINCT but got the same results. Umm.. HALP!

Thanks!

 

[carp]

It would help us help you if you would post a subset of your data as a test set for us to use; maybe one or two master records and a dozen child records, along with the results you are getting. This would help us pin down what is going on with your query.

 

[Solo4357]

Billed C_SUM UETD.Job_code UETD.ProjMan Jcdetail.job_code jcdetail.Job_ctrl_code
400 236.25 8000120174 BRIGPETE 800012 8000120174
842.50 191.78 8000150014 BRIGPETE 800015 8000150014
0 5460.90 8000150034 BRIGPETE 800015 8000150034
1015 213.08 8000150035 BRIGPETE 800015 8000150035
1527.50 698.02 8000150037 BRIGPETE 800015 8000150037
5640.05 3128.60 8000150038 BRIGPETE 800015 8000150038
1182.50 238.03 8000150040 BRIGPETE 800015 8000150040
2101.50 716.30 8000150054 BRIGPETE 800015 8000150054
1095 522.73 8000150057 BRIGPETE 800015 8000150057
467.50 0 8000150060 BRIGPETE 800015 8000150060
632.50 0 8000150063 BRIGPETE 800015 8000150063
1245 617.21 8000150071 BRIGPETE 800015 8000150071
1495 1508.51 8000150072 BRIGPETE 800015 8000150072
1667.50 1395.93 8000150073 BRIGPETE 800015 8000150073
1619 513.78 8000150079 BRIGPETE 800015 8000150079
996.50 0 8000150080 BRIGPETE 800015 8000150080
5689.15 2782.93 8000150081 BRIGPETE 800015 8000150081
255 65 8000150087 BRIGPETE 800015 8000150087
412.50 0 8000150088 BRIGPETE 800015 8000150088
922.50 46.94 8000150090 BRIGPETE 800015 8000150090

The first Job_code is what's in the projectper table and the last two are in the JCDETAIL Table. For any where the JOB_CODE = JCDT_JOB_CODE the JCDT_JOB_CTRL_CODE = 'ALL'.. otherwise the Job_ctrlcode is the first 5 or six digits of the job code. The problem comes in when the UETD_PROJECTPER table doesn't contain all the job numbers. Then I have to revert to when it the Job_ctrl_code. for instance, in this example BRIGPETE has 8000150090 in the UETD table so that matches with the detail table. However-- due to bad data entry possibly -- 8000150091 is not. So then for the jcdetail row I'd have to revert to the job_ctrl_code which would be 800015 for that record. However I can't seem to get the statement right without getting a lot of duplication. I don't always want the ctrl_code otherwise it adds it up again and again. Does that make sense?

 

[SimonSellick]

The problem seems to have changed between your posts - do you mean that you want to join on different columns depending on their content? Could you post raw data for the relevant tables showing the issues you've described?

 

[Solo4357]

Ok, after numerous tries and re-tries on my end, let me start over and simplify now that I pinpointed my problem. Here's some example data:

Table UETD_PROJECTPER

ProjectMAN JOB_CODE
------------------------------
FLEMTRAV 200
FLEMTRAV 201
FLEMTRAV 200100
FLEMTRAV 201100
FLEMTRAV 202100

Table JCDETAIL

Job_CODE JOB_CTRL_CODE
-------------------------------
200 ALL
200100 200
2001002 200
201 ALL
201001 201
201100 201
202 ALL
202100 202


Ok forget the earilier stuff, here's the issue. I need to join the JCDETAIL table with UETD so I can get all jobs in JCDETAIL for a project manager. (I didn't set up this database, I'm just a slave to its whims.) Due to possibly bad data entry, not all the jobs for a particular PM are in the UETD Table. Take Job Number 200. I would need 200, 200100, and 2001002. Now since the UETD table matches with JOB_NUM only on 200 and 200100, I would miss 2001002. But it does match up with JOB_CTRL_CODE so that would grab it. Unfortunately I sometimes end up with duplication, since 200100 would match up with 200 (CTRL_CODE) and 200100 (JOB_CODE). Even worse, since he has 202100, then he should have 202 but someone might've not entered it.

Now I oversimplified it a bit, there would actually be several dozen entries in JCDETAIL for a single JOB_NUM that would be grouped by JOB_NUM to get cost and billing totals. But I think those will work out themselves if I can get the records properly. That help? THanks in advance!