Subquery Help 1

[southbean]

Hello All,

I need a query that selects a dataset (this will be used as a subquery) which only pulls records if one of the fields sums to 100.

Employees work on several projects. Each allots a percentage of their time accordingly - adding up to 100% of their time.

I need to pull only those EmpID’s where the ProjectCode is 200 or 300, the AcctCode is 101 and the ProjectPct sums to 1.00.
In the example below, the query would need to return only those records for EmpID 23.

Example A:
EmpID,	EmpType,	ProjectCode,	AcctCode,	ProjectPct
22	    Reg	     200	         101	      0.30
22	    Reg	     500	         101	      0.30
22	    Reg	     200	         101	      0.40

Example B:
EmpID,	EmpType,	ProjectCode,	AcctCode,	ProjectPct
23	    Reg	     200	         101	      0.33
23	    Reg	     200	         101	      0.33
23	    Reg	     200	         101	      0.34

Example C:
EmpID,	EmpType,	ProjectCode,	AcctCode,	ProjectPct
24	    PT	      300	         101	      0.80
24	    PT	      300	         101	      0.05
24	    PT	      300	         100	      0.15

Example D:
EmpID,	EmpType,	ProjectCode,	AcctCode,	ProjectPct
25	    Reg	     200	         101	      0.30
25	    Reg	     200	         101	      0.30
25	    Reg	     200	         101	      0.20

Any/All help would be GREATLY appreciated!!!

- tm

 

[AlexCuse]

something like this ought to do it (not tested):

[COLOR=blue]select[/color] *
[COLOR=blue]from[/color] leTable
[COLOR=blue]inner[/color] [COLOR=blue]join[/color]
(
[COLOR=blue]select[/color] EmpID
[COLOR=blue]from[/color] leTable
[COLOR=blue]where[/color] AcctID = 101
     and ProjectCode in (200, 300)
[COLOR=blue]group[/color] [COLOR=blue]by[/color] EmpID
[COLOR=blue]having[/color] sum(ProjectPct) = 1.00
) sQuery [COLOR=blue]on[/color] leTable.EmpID = sQuery.EmpId

Hope it helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[southbean]

Alex,

Sorry for not replying sooner. I got pulled onto another project.

Anyway, this was great. Worked perfectly!

Thanks a lot,

- tm