December 16, 2004 at 11:15 am
Hi,
I want to display for each employID in Transaction_Table with Enroll_Ind=3, check if the EmployID is existing in DD00100. DD00100 is the master table, 1 employID has only 1 row, but in Transaction_Table, 1 employID can have more than 1 row.
Transaction_Table
EmployID Enroll_Ind EAcct_Num
--------- ---------- ---------
335908888 1 <null>
354698762 2 <null>
354698762 3 397002730398
618174551 3 397002730406
639128843 3 397002730414
123458462 0 <null>
DD00100 doesn't have employid '618174551' and '639128843'.
The result should be
CountID EmployID EAcct_Num
1 354698762 397002730398
0 618174551 397002730406
0 639128843 397002730414
The query I wrote:
SELECT COUNT(*) AS CountID, T.EmployID, T.EAcct_Num
FROM HFC.dbo.DD00100 D RIGHT OUTER JOIN
Epay_Transaction_Table T ON D.EMPLOYID = T.EmployID
WHERE (T.Enroll_Ind = 3)
GROUP BY T.EmployID, T.EAcct_Num
Returns:
CountID EmployID EAcct_Num
------- --------- -------------
1 354663418 397002730398
1 611349861 397002730406
1 632227263 397002730414
which is not correct.
How can I work this out?
Thanks.
December 16, 2004 at 11:32 am
By counting * you are counting all occurences. Count a field from the DD00100 table and that should fix your problem.
If the phone doesn't ring...It's me.
December 16, 2004 at 11:45 am
Hi, Probably this may help.
select t.employid, t.eacct_num, count(*) countid
from transaction_table t
where t.enroll_ind = 3
and exists (select 'x'
from dd00100 b
where b.employid = t.employid)
group by t.employid, t.eacct_num
December 16, 2004 at 12:14 pm
Hi, Sorry my earlier reply won't work.
December 16, 2004 at 12:29 pm
Note the problem is you are doing * for the count which will be 1 per row. Instead do
SELECT
Count(D.EMPLOYID) AS CountID,
T.EmployID,
T.EAcct_Num
FROM
HFC.dbo.DD00100 D
RIGHT JOIN
Epay_Transaction_Table T
ON
D.EMPLOYID = T.EmployID
WHERE
T.Enroll_Ind = 3
GROUP BY
T.EmployID,
T.EAcct_Num
Another way is to conditioanl decide if to count (1) the row as valid or not (0) and sum the numbers together.
SELECT
SUM(CASE WHEN D.EMPLOYID IS NULL THEN 0 ELSE 1 END) AS CountID,
T.EmployID,
T.EAcct_Num
FROM
HFC.dbo.DD00100 D
RIGHT JOIN
Epay_Transaction_Table T
ON
D.EMPLOYID = T.EmployID
WHERE
T.Enroll_Ind = 3
GROUP BY
T.EmployID,
T.EAcct_Num
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply