• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

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
Asked:
venkataramanaiahsr
  • 2
1 Solution
 
AntonyDNCommented:
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

0
 
AntonyDNCommented:
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

Open in new window

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now