Ranish666

Untitled

Mar 7th, 2021
472
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Alter proc [dbo].[PatientDiagnosis_Get]  
  2. @fromdate datetime, @todate datetime,  
  3. @patientID nvarchar(max),  
  4. @Code nvarchar(max),  
  5. @DiagnosisID int,  
  6. @pagenumber int, @pagesize int,  
  7. @export int    
  8. as  
  9.  
  10.  
  11. create table #tab (patientID nvarchar(max), visitID nvarchar(max), Diagnosis int,  
  12.      diagnosistype int, catagory int, IcdName nvarchar(max), Code nvarchar(max), patientName nvarchar(max),  
  13.      dtmDOB datetime, AgeSex nvarchar(max), intmobile nvarchar(max), FullAddress nvarchar(max))  
  14. if (@DiagnosisID = '' or @DiagnosisID = null) and (@Code = '' or @Code = null)  
  15.  begin  
  16.  insert into #tab  
  17.   select pd.patientID, pd.visitID, pd.Diagnosis, pd.diagnosistype, pd.catagory,  
  18.   case when pd.IcdName != '' or pd.IcdName != null then  
  19.   pd.IcdName  
  20.   else    
  21.    case when pd.Code != null or pd.Code != ''    
  22.     then    
  23.     isnuLL((select diagnosisName from tblICDdiagnosis where Code = pd.Code), 'Code -'+ pd.Code)    
  24.     else    
  25.      ld.diagnosisName    
  26.     end    
  27.   end IcdName,  
  28.   ISNULL(pd.Code, '') Code , concat(pt.srtfname,' ', pt.strlname) patientName,  
  29.   convert(date,dtmDOB) dtmDOB,                  
  30.   concat( floor(DATEDIFF(Day, dtmDOB, getdate())/365.25),'y/',strGender) AgeSex,  
  31.   isnull(intmobile, '') intmobile,  
  32.   concat( (select Municipality from tblMunicipality where mID = pt.[VDC/Municipality] and districtid = pt.strDistrict) ,'-',strAddress,', ',              
  33.    (select DistrictName from tblDistrict where ID = pt.strDistrict)                  
  34.    ) as FullAddress  
  35.   from tblPatientDiagnosis pd  
  36.   left join tblpaitentinfo pt with(nolock) on pd.patientId = pt.patientID  
  37.    and pt.patientID = case when @patientID = '' or @patientID = null then pt.patientID else @patientID end  
  38.   left join tblLocalDiagnosis ld with(nolock) on ld.diagnosisID = pd.Diagnosis  
  39.   where  
  40.   convert(date, pd.Entrydate) between convert(date, @fromdate) and convert(date, @todate)  
  41.  end  
  42. else if (@DiagnosisID = '' or @DiagnosisID = null)  
  43.  begin  
  44.  insert into #tab  
  45.   select pd.patientID, pd.visitID, pd.Diagnosis, pd.diagnosistype, pd.catagory,  
  46.   case when pd.IcdName != '' or pd.IcdName != null then  
  47.   pd.IcdName  
  48.   else    
  49.     isnuLL((select diagnosisName from tblICDdiagnosis where Code = pd.Code), 'Code -'+ pd.Code)    
  50.   end IcdName,  
  51.   ISNULL(pd.Code, '') Code , concat(pt.srtfname,' ', pt.strlname) patientName,  
  52.   convert(date,dtmDOB) dtmDOB,                  
  53.   concat( floor(DATEDIFF(Day, dtmDOB, getdate())/365.25),'y/',strGender) AgeSex,  
  54.   isnull(intmobile, '') intmobile,  
  55.   concat( (select Municipality from tblMunicipality where mID = pt.[VDC/Municipality] and districtid = pt.strDistrict) ,'-',strAddress,', ',              
  56.    (select DistrictName from tblDistrict where ID = pt.strDistrict)                  
  57.    ) as FullAddress  
  58.   from tblPatientDiagnosis pd  
  59.   left join tblpaitentinfo pt with(nolock) on pd.patientId = pt.patientID  
  60.    and pt.patientID = case when @patientID = '' or @patientID = null then pt.patientID else @patientID end  
  61.   where  
  62.   --pd.Diagnosis = 0 and  
  63.   convert(date, pd.Entrydate) between convert(date, @fromdate) and convert(date, @todate)  
  64.   and pd.code = case when @Code = '' or @Code = null then pd.code else @Code end  
  65.  end  
  66. else if (@Code = '' or @Code = null)  
  67.  begin  
  68.  insert into #tab  
  69.   select pd.patientID, pd.visitID, pd.Diagnosis, pd.diagnosistype, pd.catagory,  
  70.   case when pd.IcdName != '' or pd.IcdName != null then  
  71.   pd.IcdName  
  72.   else    
  73.    case when pd.Code != null or pd.Code != ''    
  74.     then    
  75.     isnuLL((select diagnosisName from tblICDdiagnosis where Code = pd.Code), 'Code -'+ pd.Code)    
  76.     else    
  77.      ld.diagnosisName    
  78.     end    
  79.   end IcdName,  
  80.   ISNULL(pd.Code, '') Code , concat(pt.srtfname,' ', pt.strlname) patientName,  
  81.   convert(date,dtmDOB) dtmDOB,                  
  82.   concat( floor(DATEDIFF(Day, dtmDOB, getdate())/365.25),'y/',strGender) AgeSex,  
  83.   isnull(intmobile, '') intmobile,  
  84.   concat( (select Municipality from tblMunicipality where mID = pt.[VDC/Municipality] and districtid = pt.strDistrict) ,'-',strAddress,', ',              
  85.    (select DistrictName from tblDistrict where ID = pt.strDistrict)                  
  86.    ) as FullAddress  
  87.   from tblPatientDiagnosis pd  
  88.   left join tblpaitentinfo pt with(nolock) on pd.patientId = pt.patientID  
  89.    and pt.patientID = case when @patientID = '' or @patientID = null then pt.patientID else @patientID end  
  90.   left join tblLocalDiagnosis ld with(nolock) on ld.diagnosisID = pd.Diagnosis  
  91.   where  
  92.   pd.Diagnosis <> 0 and  
  93.   convert(date, pd.Entrydate) between convert(date, @fromdate) and convert(date, @todate)  
  94.   and  
  95.   pd.Diagnosis = case when @DiagnosisID = '' or @DiagnosisID = null then pd.Diagnosis else @DiagnosisID end  
  96.  end  
  97. if @export = 0        
  98.  Begin          
  99.   declare @off int                                                  
  100.   set @off = (@pagenumber - 1) * @pagesize    
  101.  
  102.   select * from #tab  
  103.   order by patientID        
  104.   offset @off rows          
  105.   fetch first @pagesize rows only  
  106.  end  
  107. else  
  108.  begin  
  109.   select * from #tab  
  110.  end  
  111.  
  112. drop table #tab;  
RAW Paste Data

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×