Issues PLEASE HELP

i am running the following and i keep getting process errors " can not read next data row for data set SERVER. transaction process id 838 was deadlocked on lock comminication buffer resources with another process and has been chosen as the deadlock victum rerun transactions."

SELECT transactions.practice_id, practice.practice_name, CONVERT(smalldatetime, transactions.closing_date) AS Date, transactions.type,

ISNULL(SUM(trans_detail.paid_amt), 0) AS Pmts, ISNULL(SUM(trans_detail.adj_amt), 0) AS Adjs, 0 AS NSF, transactions.tran_code_id, 0 AS Debits,

0 AS Credits, 0 AS Capitation, 0 AS UnAppl, trans_detail.system_charge_code,''

FROM provider_mstr RIGHT OUTER JOIN

charges ON provider_mstr.provider_id = charges.rendering_id LEFT OUTER JOIN

location_mstr ON charges.location_id = location_mstr.location_id RIGHT OUTER JOIN

transactions INNER JOIN

trans_detail ON transactions.trans_id = trans_detail.trans_id INNER JOIN

practice ON transactions.practice_id = practice.practice_id ON charges.charge_id = trans_detail.charge_id

where (CONVERT(smalldatetime, transactions.closing_date) BETWEEN @.begin_date AND @.end_date) and (transactions.practice_id = @.practice_id) and (convert(char(36), charges.rendering_id) like @.provider_id) and (convert(char(36), charges.location_id) like @.location_id)

GROUP BY transactions.practice_id, CONVERT(smalldatetime, transactions.closing_date), transactions.type, practice.practice_name, transactions.tran_code_id,

trans_detail.system_charge_code

HAVING (transactions.type = 'C' OR transactions.type = 'R')

AND transactions.tran_code_id NOT IN ('14759070-3370-4E4D-B752-01AE84A350EF', '23783F6F-8661-4785-922C-F55C154349F9', '36037DC4-4799-4E84-9AAB-78BE4F271CCD', '5F1665EE-08DC-47FF-8BC4-6F4BA6CC84C0',

'39137DD5-796F-4824-860B-EBD00D66CBB0') AND (trans_detail.system_charge_code is null)

UNION

SELECT tr.practice_id, pr.practice_name, CONVERT(smalldatetime, tr.closing_date) AS Date, tr.type, 0 AS Pmts, 0 AS Adjs, ISNULL(SUM(td.adj_amt), 0) AS NSF,

tr.tran_code_id, 0, 0, 0, 0, '',''

FROM provider_mstr pm1 RIGHT OUTER JOIN

charges c1 ON pm1.provider_id = c1.rendering_id LEFT OUTER JOIN

location_mstr lm1 ON c1.location_id = lm1.location_id RIGHT OUTER JOIN

transactions tr INNER JOIN

trans_detail td ON tr.trans_id = td.trans_id INNER JOIN

practice pr ON tr.practice_id = pr.practice_id ON c1.charge_id = td.charge_id

where (CONVERT(smalldatetime, tr.closing_date) BETWEEN @.begin_date AND @.end_date) AND (convert(char(36), c1.rendering_id) like @.provider_id) and (convert(char(36), c1.location_id) like @.location_id)

GROUP BY tr.practice_id, CONVERT(smalldatetime, tr.closing_date), tr.type, pr.practice_name, tr.tran_code_id

HAVING (tr.practice_id = @.practice_id) AND (tr.type = 'A') AND

(tr.tran_code_id = 'CEC50D49-3317-404D-A082-36610DAE6F0F')

UNION

SELECT tn.practice_id, prs.practice_name, CONVERT(smalldatetime, tn.closing_date) AS Date, tn.type, 0 AS Pmts, 0 AS Adjs, 0 AS NSF, tn.tran_code_id,

isnull(SUM(tds.paid_amt), 0) AS Debits, 0, 0, 0, '',''

FROM provider_mstr pm2 RIGHT OUTER JOIN

charges c2 ON pm2.provider_id = c2.rendering_id LEFT OUTER JOIN

location_mstr lm2 ON c2.location_id = lm2.location_id RIGHT OUTER JOIN

transactions tn INNER JOIN

trans_detail tds ON tn.trans_id = tds.trans_id INNER JOIN

practice prs ON tn.practice_id = prs.practice_id ON c2.charge_id = tds.charge_id

where (CONVERT(smalldatetime, tn.closing_date) BETWEEN @.begin_date AND @.end_date) AND (convert(char(36), c2.rendering_id) like @.provider_id) and (convert(char(36), c2.location_id) like @.location_id)

GROUP BY tn.practice_id, CONVERT(smalldatetime, tn.closing_date), tn.type, prs.practice_name, tn.tran_code_id

HAVING (tn.practice_id = @.practice_id) AND (tn.type = 'C') AND

(tn.tran_code_id = '14759070-3370-4E4D-B752-01AE84A350EF' OR

tn.tran_code_id = '23783F6F-8661-4785-922C-F55C154349F9' OR tn.tran_code_id='B6BA0D7D-BAC6-4653-9050-C5C5E8029DCB') AND isnull(SUM(tds.paid_amt), 0) > 0

UNION

SELECT trs.practice_id, ps.practice_name, CONVERT(smalldatetime, trs.closing_date) AS Date, trs.type, 0 AS Pmts, 0 AS Adjs, 0 AS NSF, trs.tran_code_id, 0,

isnull(SUM(ts .paid_amt), 0) AS Credits, 0, 0, '',''

FROM provider_mstr pm3 RIGHT OUTER JOIN

charges c3 ON pm3.provider_id = c3.rendering_id LEFT OUTER JOIN

location_mstr lm3 ON c3.location_id = lm3.location_id RIGHT OUTER JOIN

transactions trs INNER JOIN

trans_detail ts ON trs.trans_id = ts .trans_id INNER JOIN

practice ps ON trs.practice_id = ps.practice_id ON c3.charge_id = ts .charge_id

where (CONVERT(smalldatetime, trs.closing_date) BETWEEN @.begin_date AND @.end_date) AND (convert(char(36), c3.rendering_id) like @.provider_id) and (convert(char(36), c3.location_id) like @.location_id)

GROUP BY trs.practice_id, CONVERT(smalldatetime, trs.closing_date), trs.type, ps.practice_name, trs.tran_code_id

HAVING (trs.practice_id = @.practice_id) AND (trs.type = 'C') AND

(trs.tran_code_id = '14759070-3370-4E4D-B752-01AE84A350EF' OR

trs.tran_code_id = '23783F6F-8661-4785-922C-F55C154349F9' OR trs.tran_code_id='B6BA0D7D-BAC6-4653-9050-C5C5E8029DCB') AND isnull(SUM(ts .paid_amt), 0) < 0

UNION

SELECT tran1.practice_id, ps1.practice_name, CONVERT(smalldatetime, tran1.closing_date) AS Date, tran1.type, 0 AS Pmts, 0 AS Adjs, 0 AS NSF,

tran1.tran_code_id, 0, 0, isnull(SUM(ts1.paid_amt), 0), 0, '',''

FROM provider_mstr pm4 RIGHT OUTER JOIN

charges c4 ON pm4.provider_id = c4.rendering_id LEFT OUTER JOIN

location_mstr lm4 ON c4.location_id = lm4.location_id RIGHT OUTER JOIN

transactions tran1 INNER JOIN

trans_detail ts1 ON tran1.trans_id = ts1.trans_id INNER JOIN

practice ps1 ON tran1.practice_id = ps1.practice_id ON c4.charge_id = ts1.charge_id

where (CONVERT(smalldatetime, tran1.closing_date) BETWEEN @.begin_date AND @.end_date) AND (convert(char(36), c4.rendering_id) like @.provider_id) and (convert(char(36), c4.location_id) like @.location_id)

GROUP BY tran1.practice_id, CONVERT(smalldatetime, tran1.closing_date), tran1.type, ps1.practice_name, tran1.tran_code_id

HAVING (tran1.practice_id = @.practice_id) AND (tran1.type = 'C')

AND (tran1.tran_code_id IN ('36037DC4-4799-4E84-9AAB-78BE4F271CCD', '5F1665EE-08DC-47FF-8BC4-6F4BA6CC84C0', '39137DD5-796F-4824-860B-EBD00D66CBB0'))

UNION

SELECT tran2.practice_id, ps2.practice_name, CONVERT(smalldatetime, tran2.closing_date) AS Date, tran2.type, 0 AS Pmts, 0 AS Adjs, 0 AS NSF,

tran2.tran_code_id, 0, 0, 0, isnull(SUM(ts2.paid_amt), 0), ts2.system_charge_code,''

FROM provider_mstr pm5 RIGHT OUTER JOIN

charges c5 ON pm5.provider_id = c5.rendering_id LEFT OUTER JOIN

location_mstr lm5 ON c5.location_id = lm5.location_id RIGHT OUTER JOIN

transactions tran2 INNER JOIN

trans_detail ts2 ON tran2.trans_id = ts2.trans_id INNER JOIN

practice ps2 ON tran2.practice_id = ps2.practice_id ON c5.charge_id = ts2.charge_id

where (CONVERT(smalldatetime, tran2.closing_date) BETWEEN @.begin_date AND @.end_date) AND (isnull(convert(char(36), c5.rendering_id),'') like @.provider_id) and (isnull(convert(char(36), c5.location_id),'') like @.location_id)

GROUP BY tran2.practice_id, CONVERT(smalldatetime, tran2.closing_date), tran2.type, ps2.practice_name, tran2.tran_code_id, ts2.system_charge_code

HAVING (tran2.practice_id = @.practice_id) AND (tran2.type = 'C')

AND ts2.system_charge_code is not null

ORDER BY CONVERT(smalldatetime, transactions.closing_date)

Try moving all your "HAVING" clauses to WHERE clauses.

HAVING is for calculated fields. Your fields appear to be static, except for the "isnull(SUM(ts .paid_amt), 0) < 0" which will need to be in the HAVING clause.

I would suspect it is retrieving all records, multiple times, to check the having clauses, when it doesn't need too.

|||Also, try posting this to the TSQL forum, you will probably have better luck.