Hi everyone. Hope you are well. I need a help for sql query please I have this Q

Question

Hi everyone.

Hope you are well. I need a help for sql query please I have this Qurey which is going to show the data between the range that exist in data base. But I am enter the range between 1 to 500 which I suppose to see all data between 1 to 500 but I am getting data over the 500 also, is any body can help for this issue.

Best regards

my SQL Qurey:

SELECT

CAST(r.[Balance Due Adj] as decimal(37,2)) AS 'Balance Due'

FROM

AgentRangeList a

LEFT JOIN RPT_Calculated r on (r.DebtorNumber = a.DebtorNumber and a.ClientCode = r.ClientCode)

LEFT JOIN ExcludedStatuses es on (es.Status = a.Status)

LEFT JOIN ExcludedCategories ec on (ec.Category = a.Category)

LEFT JOIN ExcludedClientDebtors ecd on (ecd.ClientCode = a.ClientCode AND ecd.DebtorNumber = a.DebtorNumber)

LEFT JOIN ExcludedInsuranceCompanies ei on (ei.InsuranceCompanyName = a.InsuranceCompanyName)

LEFT JOIN _Contracts co on (r.ContractId = co.ContractId)

LEFT JOIN RPT_Calculated_Without_Contract rp on (r.DebtorNumber = rp.DebtorNumber AND r.ClientCode = rp.ClientCode)

JOIN CombineClientList c on (r.ClientCode= c.ClientCode)

JOIN _FinancialClasses f on (f.FinancialClassId = c.FinancialClassId)

WHERE(r.[Balance Due Adj] BETWEEN '1' AND '500')

OUTPUT:

Balance Due

11603.05

102.50

2803.40

1906.14

2047.00

34307.22

228.06

10005.51

Explanation / Answer

Answer:

SELECT

CAST(r.[Balance Due Adj] as decimal(37,2)) AS 'Balance Due'

FROM

AgentRangeList a

LEFT JOIN RPT_Calculated r on (r.DebtorNumber = a.DebtorNumber and a.ClientCode = r.ClientCode)

LEFT JOIN ExcludedStatuses es on (es.Status = a.Status)

LEFT JOIN ExcludedCategories ec on (ec.Category = a.Category)

LEFT JOIN ExcludedClientDebtors ecd on (ecd.ClientCode = a.ClientCode AND ecd.DebtorNumber = a.DebtorNumber)

LEFT JOIN ExcludedInsuranceCompanies ei on (ei.InsuranceCompanyName = a.InsuranceCompanyName)

LEFT JOIN _Contracts co on (r.ContractId = co.ContractId)

LEFT JOIN RPT_Calculated_Without_Contract rp on (r.DebtorNumber = rp.DebtorNumber AND r.ClientCode = rp.ClientCode)

JOIN CombineClientList c on (r.ClientCode= c.ClientCode)

JOIN _FinancialClasses f on (f.FinancialClassId = c.FinancialClassId)

WHERE r.[Balance Due Adj] >= '1' AND r.[Balance Due Adj] <='500' /*Here i have splitted the between clause to two different conditions which will give the values between 1 and 500 */
Group by r.[Balance Due Adj];

Sample output:

102.50

228.06

Let me know incase if you have any doubts

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.