Help on a select query

HI all,

SELECT @.PmtId = PmtId
FROM EmpStatus WHERE
PmtStatusDt = @.PmtStatusDt AND (PmtStatusCd= 'T' OR PmtStatusCd='X' )

SELECT PmtID, PmtBalanceCd, SUM (EmpPmtBalanceAmt)
FROM Emp_Pmt_Balance WHERE PmtId = @.PmtId
GROUP BY PmtBalanceCd

But my problem here is this that:
My PmtID can be multiple records. example of the tables is as below

EmpStatus
-----

PmtId PmtStatusDt PmtStatusCd

1 12/11/03 T
2 12/11/03 X
3 12/11/03 T

Emp_Pmt_Balance
------

PmtId PmtBalanceCd EmpPmtBalanceAmt
1 560 12.00
1 562 13.00
2 560 24.00
2 562 28.00
3 560 34.00 3 562 28.00

The expected result:

PmtBalanceCd EmpPmtBalanceAmt
560 70.00
562 69 .00

Any help to get this tuff working is appreciated.If you want your results to literally be this:

PmtBalanceCd EmpPmtBalanceAmt
560 70.00
562 69 .00

Then just drop the PmtID from your query and you will get the desired result:

SELECT PmtBalanceCd, SUM (EmpPmtBalanceAmt)
FROM Emp_Pmt_Balance WHERE PmtId = @.PmtId
GROUP BY PmtBalanceCd

Terri|||I tried that. It just displays the results for PmtId =3

PmtBalanceCd EmpPmtBalanceAmt
560 34.00
562 28.00

Now I am trying the Coalesce function. But it gives me this error
"Syntax error converting the varchar value '2, 3' to a column of data type int."

DECLARE @.PmtId AS Int

DECLARE @.PmtIdList varchar(100)

SELECT @.PmtIdList = COALESCE(@.PmtIdList + ', ', '') +
CAST(PmtId AS varchar(5))
FROM FCTrans.dbo.Emp_Pmt_Status
WHERE CAST(FLOOR(CAST(PmtStatusDt AS float)) AS datetime) = @.PmtStatusDt AND (PmtStatusCd= 'T' OR PmtStatusCd='X' )

SELECT PmtBalanceCd, SUM (EmpPmtBalanceAmt)
FROM FCTrans.dbo.Emp_Pmt_Balance WHERE PmtId = Cast(COALESCE(@.PmtIdList,CAST(PmtId As varchar(5))) As Int) And PmtBalancePeriodCd ='P' AND (PmtBalanceCd='560' OR PmtBalanceCd='565' OR PmtBalanceCd='570' OR PmtBalanceCd='575' OR PmtBalanceCd='580')
GROUP BY PmtBalanceCd
GO|||OK, I think I understand better what you are looking for. You should be able to do it in one statement, like this:

SELECT
 Balance.PmtBalanceCd,
 SUM (Balance.EmpPmtBalanceAmt)
FROM
 Emp_Pmt_Balance AS Balance
INNER JOIN
 Emp_Pmt_Status AS Status
 ON
 Status.PmtID = Balance.PmtID AND
 Status.PmtStatusDt = @.PmtStatusDt AND
 Status.PmtStatusCd IN ('T','X' ) AND
 Balance.PmtBalancePeriodCd ='P' AND
 Balance.PmtBalanceCd IN ('560','565','570','575','580')
GROUP BY
 Balance.PmtBalanceCd

I don't have your tables set up, so I haven't tested it. But it should be close.

Terri