asked on

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

AntonyDN

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

Open in new window

ASKER CERTIFIED SOLUTION

AntonyDN

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial