x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 264

# Sql Qry problem

I have  taken a specific example so that i can clearly explain the problem
In the qry mentioned below , it returns 3 rows.

Qry
Select hbdf.caseid,hbdf.DoctorCode,sum(hBdf.Charges)as visitcharges
From HISBillDoctorFees hBdf
inner join hisbill hb on hb.caseid = hbdf.caseid
where billdate between '20080501' and '20080531'
and billcancelled = 0  and billno <> 0 and branch = 2
and hbdf.caseid = 67161
Group by hbdf.caseid,doctorcode
O/p
caseid      DoctorCode      visitcharges
67161      1      0
67161      70      500
67161      164      0

For this caseid i have given a discount . to retrive the discount for this caseid
i have written the following qry.
Select A.caseid,A.doctorcode,A.Visitcharges,
dbo.fnBillDiscount(A.caseid,2)
From
(Select hbdf.caseid,hbdf.DoctorCode,sum(hBdf.Charges)as visitcharges
From HISBillDoctorFees hBdf
inner join hisbill hb on hb.caseid = hbdf.caseid
where billdate between '20080501' and '20080531'
and billcancelled = 0  and billno <> 0 and branch = 2
and hbdf.caseid = 67161
Group by hbdf.caseid,doctorcode
)A
order by A.caseid
where  dbo.fnBillDiscount(A.caseid,2) is function retruns a discount for a particualr caseid and branch.
when i run this qry, it returns the foll output

caseid      doctorcode      Visitcharges      discount
67161      1      0      50
67161      70      500      50
67161      164      0      50

Here though the actual disocunt is give for a caseid is rs 50 , it is repeating 3 times because
caseid is repeating 3 times.
My Qry is  in the below mentioned qry
Select A.caseid,A.doctorcode,A.Visitcharges,
dbo.fnBillDiscount(A.caseid,2)
From
(Select hbdf.caseid,hbdf.DoctorCode,sum(hBdf.Charges)as visitcharges
From HISBillDoctorFees hBdf
inner join hisbill hb on hb.caseid = hbdf.caseid
where billdate between '20080501' and '20080531'
and billcancelled = 0  and billno <> 0 and branch = 2
and hbdf.caseid = 67161
Group by hbdf.caseid,doctorcode
)A
order by A.caseid

is there any way i can retrieve the discount only once and not  3 times

Thanks in advance and i very much appreciate experts advice at the earliest
regards
Venkat

0
venkataramanaiahsr
• 2
1 Solution

Commented:
With the query as it is, I guess, "NO!"

It would help to see the SQL for the function, but my guess is that that is applied to the CaseID, but you are returning rows at the DoctorCode level - a different granularity.

If you removed the DoctorCode, you'd get one row, and - I guess - only one "50" .

This code should (I hope - I didn't set anything up to test it) show you what I mean.

``````Select A.caseid
--,A.doctorcode
,A.Visitcharges
dbo.fnBillDiscount(A.caseid,2)
From
(Select hbdf.caseid
--,hbdf.DoctorCode
,sum(hBdf.Charges)as visitcharges
From HISBillDoctorFees hBdf
inner join hisbill hb on hb.caseid = hbdf.caseid

where billdate between '20080501' and '20080531'
and billcancelled = 0  and billno <> 0 and branch = 2
and hbdf.caseid = 67161
Group by hbdf.caseid --,doctorcode
)A
order by A.caseid
``````
0

Commented:
This code will actually work! Apologies for the previous version ...
``````   Select hbdf.caseid
--,hbdf.DoctorCode
,  sum(hBdf.Charges)as visitcharges
,  dbo.fnBillDiscount(hbdf.caseid,2)
From HISBillDoctorFees hBdf
inner join hisbill hb on hb.caseid = hbdf.caseid
where billdate between '20080501' and '20080531'
and billcancelled = 0  and billno <> 0 and branch = 2
and hbdf.caseid = 67161
Group by hbdf.caseid --,doctorcode
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.