Ranish666

changes in sql 2021-05-26

May 26th, 2021
1,124
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. go
  2. Alter proc PatientPassInsert  
  3. @pid nvarchar(100),  
  4. @eid nvarchar(100),  
  5. @printNo int,  
  6. @printBy int
  7.  
  8. as  
  9. begin  
  10.   insert into tblPatientPass (PatientId,EncounterId, NumOfPrints,PrintBy,PrintDate)  
  11.    values (@pid,@eid,@printNo,@printBy,GETDATE())  
  12. end
  13. go
  14. Alter PROC [dbo].[DepositGetAllPatient_Pagination]                      
  15. @patientid nvarchar(max),                
  16. @fromdate date,              
  17. @todate date,        
  18. @duetype nvarchar(max),    
  19. @pagenumber int, @pagesize int    
  20. as    
  21.    
  22. DECLARE @firstRecord int = (@pagenumber - 1) * @pagesize + 1;          
  23. DECLARE @lastRecord int = @firstRecord + @pagesize - 1;      
  24.      
  25. create table #temp (      
  26.  mobile nvarchar(max),      
  27.  PatientID nvarchar(max),      
  28.  [Name] nvarchar(max),      
  29.  PatientWard nvarchar(max),      
  30.  BedNo nvarchar(max),      
  31.  strStatus nvarchar(max),      
  32.  Receivable decimal(18,2),      
  33.  Received decimal(18,2),      
  34.  Deposit decimal(18,2),      
  35.  DepositReturn decimal(18,2),      
  36.  DueType nvarchar(max)      
  37. );      
  38.    
  39. IF @duetype='Receivable'    
  40.   begin    
  41.    insert into #temp    
  42.    select temp.mobile,temp.PatientID,temp.[Name],temp.PatientWard,temp.BedNo,temp.strStatus,    
  43.    sum(temp.Receivable)as  'Receivable',sum(temp.Received)as  'Received',sum(temp.Deposit)as  'Deposit',sum(temp.DepositReturn)as  'DepositReturn',    
  44.    temp.DueType    
  45.    from (    
  46.    select isnull(tp.intmobile,'N/A') as 'mobile',TP.patientID as 'PatientID',Ltrim(Rtrim(TP.srtFname)) +' '+TP.strLname as 'Name',                  
  47.    '0' as 'PatientWard',  
  48.  '0' as 'BedNo',  
  49.  '0'  as 'strStatus',  
  50.  --(select dbo.getLatestDepartment((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))) as 'PatientWard',                          
  51.  --isnull((select dbo.getLatestbED((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))),'N/A') as 'BedNo',  
  52.  --isnull((select dbo.getpatientCurrentStatus((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))),'')as 'strStatus',            
  53.    case when tm.BillingType='Credit' then isnull(tm.Total-tm.ReceiveAmt,0) else 0 end   as 'Receivable',    
  54.    0 as  'Received',    
  55.    0  as 'Deposit',    
  56.    0  as 'DepositReturn',    
  57.    'Counter' as 'DueType'    
  58.    from tblBillingMaster tm                          
  59.    INNER JOIN tblEncounter te on te.strencounterid=tm.strEncounterID    
  60.    INNER JOIN tblpaitentinfo TP on TP.patientID=te.strPatientID                            
  61.    where --TM.AcLedgerID=22 and                      
  62.    Tp.patientID=(case when  @patientid='' then Tp.patientID else @patientid end)              
  63.    and convert(date,Tm.EntryTime)  between convert(date,@fromdate) and convert(date,@todate)  
  64.    and (tm.receiveAmt-(tm.Total-tm.billdiscount))<>0  
  65.    ) as temp    
  66.    group by temp.mobile,temp.PatientID,temp.[Name],temp.PatientWard,temp.BedNo,temp.strStatus,temp.DueType  
  67.      
  68.    union all  
  69.  
  70.    select temp1.mobile,temp1.PatientID,temp1.[Name],temp1.PatientWard,temp1.BedNo,temp1.strStatus,    
  71.    sum(temp1.Receivable)as  'Receivable',sum(temp1.Received)as  'Received',sum(temp1.Deposit)as  'Deposit',sum(temp1.DepositReturn)as  'DepositReturn',    
  72.    temp1.DueType    
  73.    from (    
  74.    select isnull(tp.intmobile,'N/A') as 'mobile',TP.patientID as 'PatientID',Ltrim(Rtrim(TP.srtFname)) +' '+TP.strLname as 'Name',                  
  75.    '0' as 'PatientWard',  
  76.  '0' as 'BedNo',  
  77.  '0'  as 'strStatus',  
  78.  --(select dbo.getLatestDepartment((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))) as 'PatientWard',                          
  79.  --isnull((select dbo.getLatestbED((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))),'N/A') as 'BedNo',  
  80.  --isnull((select dbo.getpatientCurrentStatus((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))),'')as 'strStatus',          
  81.    case when tm.PharmacyType='Credit' then isnull(tm.Total-tm.ReceiveAmt,0) else 0 end   as 'Receivable',    
  82.    0 as  'Received',    
  83.    0  as 'Deposit',    
  84.    0  as 'DepositReturn',    
  85.    'Counter' as 'DueType'    
  86.    from tblPharmacySalesMaster tm                          
  87.    INNER JOIN tblEncounter te on te.strencounterid=tm.strEncounterID    
  88.   INNER JOIN tblpaitentinfo TP on TP.patientID=te.strPatientID                            
  89.    where --TM.AcLedgerID=22 and                      
  90.    Tp.patientID=(case when  @patientid='' then Tp.patientID else @patientid end)              
  91.    and convert(date,Tm.EntryTime)  between convert(date,@fromdate) and convert(date,@todate)  
  92.    and (tm.receiveAmt-(tm.Total-tm.PharmacyDiscount))<>0  
  93.    ) as temp1    
  94.    group by temp1.mobile,temp1.PatientID,temp1.[Name],temp1.PatientWard,temp1.BedNo,temp1.strStatus,temp1.DueType  
  95.   END    
  96.    
  97.     ELSE IF @duetype='Received'                      
  98.     BEGIN    
  99.      insert into #temp    
  100.    select temp.mobile,temp.PatientID,temp.[Name],temp.PatientWard,temp.BedNo,temp.strStatus,    
  101.    sum(temp.Receivable)as  'Receivable',sum(temp.Received)as  'Received',sum(temp.Deposit)as  'Deposit',sum(temp.DepositReturn)as  'DepositReturn',    
  102.    temp.DueType    
  103.    from (    
  104.    select isnull(tp.intmobile,'N/A')  mobile, TP.patientID PatientID, Ltrim(Rtrim(TP.srtFname)) +' '+TP.strLname  
  105.    as 'Name',                  
  106.    '0' as 'PatientWard',  
  107.  '0' as 'BedNo',  
  108.  '0'  as 'strStatus',  
  109.  --(select dbo.getLatestDepartment((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))) as 'PatientWard',                          
  110.  --isnull((select dbo.getLatestbED((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))),'N/A') as 'BedNo',  
  111.  --isnull((select dbo.getpatientCurrentStatus((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))),'')as 'strStatus',            
  112.    0  as 'Receivable',    
  113.    case when tm.[type]='Due' then isnull(tm.receiveAmt,0) else  0 end as  'Received',    
  114.    0  as 'Deposit',    
  115.    0  as 'DepositReturn',    
  116.    'Counter' as 'DueType'    
  117.    from tblBillingMaster tm                          
  118.    INNER JOIN tblEncounter te on te.strencounterid=tm.strEncounterID    
  119.    INNER JOIN tblpaitentinfo TP on TP.patientID=te.strPatientID                            
  120.   where --TM.AcLedgerID=22 and                      
  121.    Tp.patientID=(case when  @patientid='' then Tp.patientID else @patientid end)              
  122.    and convert(date,Tm.EntryTime)  between convert(date,@fromdate) and convert(date,@todate)  
  123.    and (tm.receiveAmt-(tm.Total-tm.billdiscount))<>0  
  124.    ) as temp    
  125.    group by temp.mobile,temp.PatientID,temp.[Name],temp.PatientWard,temp.BedNo,temp.strStatus,temp.DueType  
  126.      
  127.    union all  
  128.  
  129.   select temp1.mobile,temp1.PatientID,temp1.[Name],temp1.PatientWard,temp1.BedNo,temp1.strStatus,    
  130.    sum(temp1.Receivable)as  'Receivable',sum(temp1.Received)as  'Received',sum(temp1.Deposit)as  'Deposit',sum(temp1.DepositReturn)as  'DepositReturn',    
  131.    temp1.DueType    
  132.    from (    
  133.    select isnull(tp.intmobile,'N/A') as 'mobile',TP.patientID as 'PatientID',Ltrim(Rtrim(TP.srtFname)) +' '+TP.strLname as 'Name',                  
  134.    '0' as 'PatientWard',  
  135.  '0' as 'BedNo',  
  136.  '0'  as 'strStatus',  
  137.  --(select dbo.getLatestDepartment((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))) as 'PatientWard',                          
  138.  --isnull((select dbo.getLatestbED((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))),'N/A') as 'BedNo',  
  139.  --isnull((select dbo.getpatientCurrentStatus((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))),'')as 'strStatus',          
  140.    0  as 'Receivable',    
  141.    case when tm.[type]='Due' then isnull(tm.receiveAmt,0) else  0 end as  'Received',    
  142.    0  as 'Deposit',    
  143.    0  as 'DepositReturn',    
  144.    'Counter' as 'DueType'    
  145.    from tblPharmacySalesMaster tm                          
  146.    INNER JOIN tblEncounter te on te.strencounterid=tm.strEncounterID    
  147.    INNER JOIN tblpaitentinfo TP on TP.patientID=te.strPatientID                            
  148.    where --TM.AcLedgerID=22 and                      
  149.    Tp.patientID=(case when  @patientid='' then Tp.patientID else @patientid end)              
  150.    and convert(date,Tm.EntryTime)  between convert(date,@fromdate) and convert(date,@todate)  
  151.    and (tm.receiveAmt-(tm.Total-tm.PharmacyDiscount))<>0  
  152.    ) as temp1    
  153.    group by temp1.mobile,temp1.PatientID,temp1.[Name],temp1.PatientWard,temp1.BedNo,temp1.strStatus,temp1.DueType  
  154.  
  155.     END    
  156.    
  157.     ELSE IF @duetype='Deposit'                      
  158.        BEGIN      
  159.   insert into #temp    
  160.    select temp.mobile,temp.PatientID,temp.[Name],temp.PatientWard,temp.BedNo,temp.strStatus,    
  161.    sum(temp.Receivable)as  'Receivable',sum(temp.Received)as  'Received',sum(temp.Deposit)as  'Deposit',sum(temp.DepositReturn)as  'DepositReturn',    
  162.    temp.DueType    
  163.    from (    
  164.    select isnull(tp.intmobile,'N/A') as 'mobile',TP.patientID as 'PatientID',Ltrim(Rtrim(TP.srtFname)) +' '+TP.strLname as 'Name',                  
  165.    '0' as 'PatientWard',  
  166.  '0' as 'BedNo',  
  167.  '0'  as 'strStatus',  
  168.  --(select dbo.getLatestDepartment((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))) as 'PatientWard',                          
  169.  --isnull((select dbo.getLatestbED((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))),'N/A') as 'BedNo',  
  170.  --isnull((select dbo.getpatientCurrentStatus((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))),'')as 'strStatus',            
  171.    0  as 'Receivable',    
  172.    0 as  'Received',    
  173.    case when tm.[type]='Deposit' then isnull(tm.receiveAmt,0) else 0 end  as 'Deposit',    
  174.    0  as 'DepositReturn',    
  175.    'Counter' as 'DueType'    
  176.    from tblBillingMaster tm                          
  177.    INNER JOIN tblEncounter te on te.strencounterid=tm.strEncounterID    
  178.    INNER JOIN tblpaitentinfo TP on TP.patientID=te.strPatientID                            
  179.    where --TM.AcLedgerID=22 and                      
  180.    Tp.patientID=(case when  @patientid='' then Tp.patientID else @patientid end)              
  181.    and convert(date,Tm.EntryTime)  between convert(date,@fromdate) and convert(date,@todate)  
  182.    and (tm.receiveAmt-(tm.Total-tm.billdiscount))<>0  
  183.    ) as temp    
  184.    group by temp.mobile,temp.PatientID,temp.[Name],temp.PatientWard,temp.BedNo,temp.strStatus,temp.DueType    
  185.  
  186.    union all  
  187.  
  188.    select temp1.mobile,temp1.PatientID,temp1.[Name],temp1.PatientWard,temp1.BedNo,temp1.strStatus,    
  189.    sum(temp1.Receivable)as  'Receivable',sum(temp1.Received)as  'Received',sum(temp1.Deposit)as  'Deposit',sum(temp1.DepositReturn)as  'DepositReturn',    
  190.    temp1.DueType    
  191.    from (    
  192.    select isnull(tp.intmobile,'N/A') as 'mobile',TP.patientID as 'PatientID',Ltrim(Rtrim(TP.srtFname)) +' '+TP.strLname as 'Name',                  
  193.    '0' as 'PatientWard',  
  194.  '0' as 'BedNo',  
  195.  '0'  as 'strStatus',  
  196.  --(select dbo.getLatestDepartment((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))) as 'PatientWard',                          
  197.  --isnull((select dbo.getLatestbED((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))),'N/A') as 'BedNo',  
  198.  --isnull((select dbo.getpatientCurrentStatus((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))),'')as 'strStatus',          
  199.    0  as 'Receivable',    
  200.    0 as  'Received',    
  201.    case when tm.[type]='Deposit' then isnull(tm.receiveAmt,0) else 0 end  as 'Deposit',    
  202.    0  as 'DepositReturn',    
  203.    'Counter' as 'DueType'    
  204.    from tblPharmacySalesMaster tm                          
  205.    INNER JOIN tblEncounter te on te.strencounterid=tm.strEncounterID    
  206.    INNER JOIN tblpaitentinfo TP on TP.patientID=te.strPatientID                            
  207.    where --TM.AcLedgerID=22 and                      
  208.    Tp.patientID=(case when  @patientid='' then Tp.patientID else @patientid end)              
  209.    and convert(date,Tm.EntryTime)  between convert(date,@fromdate) and convert(date,@todate)  
  210.    and (tm.receiveAmt-(tm.Total-tm.PharmacyDiscount))<>0  
  211.    ) as temp1    
  212.    group by temp1.mobile,temp1.PatientID,temp1.[Name],temp1.PatientWard,temp1.BedNo,temp1.strStatus,temp1.DueType  
  213.  END    
  214.    
  215.    ELSE IF @duetype='Deposit Return'                      
  216.        BEGIN    
  217.     insert into #temp    
  218.   select temp.mobile,temp.PatientID,temp.[Name],temp.PatientWard,temp.BedNo,temp.strStatus,    
  219.   sum(temp.Receivable)as  'Receivable',sum(temp.Received)as  'Received',sum(temp.Deposit)as  'Deposit',sum(temp.DepositReturn)as  'DepositReturn',    
  220.   temp.DueType    
  221.   from (    
  222.   select isnull(tp.intmobile,'N/A') as 'mobile',TP.patientID as 'PatientID',Ltrim(Rtrim(TP.srtFname)) +' '+TP.strLname as 'Name',                  
  223.   '0' as 'PatientWard',  
  224.  '0' as 'BedNo',  
  225.  '0'  as 'strStatus',  
  226.  --(select dbo.getLatestDepartment((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))) as 'PatientWard',                          
  227.  --isnull((select dbo.getLatestbED((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))),'N/A') as 'BedNo',  
  228.  --isnull((select dbo.getpatientCurrentStatus((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))),'')as 'strStatus',            
  229.   0  as 'Receivable',    
  230.   0 as  'Received',    
  231.   0 as 'Deposit',    
  232.   case when tm.[type]='Return' then isnull(abs(tm.receiveAmt),0) else 0 end  as 'DepositReturn',    
  233.   'Counter' as 'DueType'    
  234.   from tblBillingMaster tm                          
  235.   INNER JOIN tblEncounter te on te.strencounterid=tm.strEncounterID    
  236.   INNER JOIN tblpaitentinfo TP on TP.patientID=te.strPatientID                            
  237.   where --TM.AcLedgerID=22 and                      
  238.   Tp.patientID=(case when  @patientid='' then Tp.patientID else @patientid end)              
  239.   and convert(date,Tm.EntryTime)  between convert(date,@fromdate) and convert(date,@todate)  
  240.   and (tm.receiveAmt-(tm.Total-tm.billdiscount))<>0  
  241.   ) as temp    
  242.   group by temp.mobile,temp.PatientID,temp.[Name],temp.PatientWard,temp.BedNo,temp.strStatus,temp.DueType    
  243.  
  244.   union all  
  245.  
  246.   select temp1.mobile,temp1.PatientID,temp1.[Name],temp1.PatientWard,temp1.BedNo,temp1.strStatus,    
  247.   sum(temp1.Receivable)as  'Receivable',sum(temp1.Received)as  'Received',sum(temp1.Deposit)as  'Deposit',sum(temp1.DepositReturn)as  'DepositReturn',    
  248.   temp1.DueType    
  249.   from (    
  250.   select isnull(tp.intmobile,'N/A') as 'mobile',TP.patientID as 'PatientID',Ltrim(Rtrim(TP.srtFname)) +' '+TP.strLname as 'Name',                  
  251.   '0' as 'PatientWard',  
  252.  '0' as 'BedNo',  
  253.  '0'  as 'strStatus',  
  254.  --(select dbo.getLatestDepartment((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))) as 'PatientWard',                          
  255.  --isnull((select dbo.getLatestbED((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))),'N/A') as 'BedNo',  
  256.  --isnull((select dbo.getpatientCurrentStatus((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))),'')as 'strStatus',          
  257.   0  as 'Receivable',    
  258.   0 as  'Received',    
  259.   0 as 'Deposit',    
  260.   case when tm.[type]='Return' then isnull(abs(tm.receiveAmt),0) else 0 end  as 'DepositReturn',    
  261.   'Counter' as 'DueType'    
  262.   from tblPharmacySalesMaster tm                          
  263.   INNER JOIN tblEncounter te on te.strencounterid=tm.strEncounterID    
  264.   INNER JOIN tblpaitentinfo TP on TP.patientID=te.strPatientID                            
  265.   where --TM.AcLedgerID=22 and                      
  266.   Tp.patientID=(case when  @patientid='' then Tp.patientID else @patientid end)              
  267.   and convert(date,Tm.EntryTime)  between convert(date,@fromdate) and convert(date,@todate)    
  268.   and (tm.receiveAmt-(tm.Total-tm.PharmacyDiscount))<>0  
  269.   ) as temp1    
  270.   group by temp1.mobile,temp1.PatientID,temp1.[Name],temp1.PatientWard,temp1.BedNo,temp1.strStatus,temp1.DueType  
  271.    END    
  272.    
  273. ELSE IF @duetype='All'                      
  274.   BEGIN    
  275.   insert into #temp    
  276.   select temp.mobile,temp.PatientID,temp.[Name],temp.PatientWard,temp.BedNo,temp.strStatus,    
  277.   sum(temp.Receivable)as  'Receivable',sum(temp.Received)as  'Received',sum(temp.Deposit)as  'Deposit',sum(temp.DepositReturn)as  'DepositReturn',    
  278.   temp.DueType    
  279.   from (    
  280.   select isnull(tp.intmobile,'N/A') as 'mobile',TP.patientID as 'PatientID',Ltrim(Rtrim(TP.srtFname)) +' '+TP.strLname as 'Name',                  
  281.   '0' as 'PatientWard',  
  282.  '0' as 'BedNo',  
  283.  '0'  as 'strStatus',  
  284.  --(select dbo.getLatestDepartment((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))) as 'PatientWard',                          
  285.  --isnull((select dbo.getLatestbED((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))),'N/A') as 'BedNo',  
  286.  --isnull((select dbo.getpatientCurrentStatus((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))),'')as 'strStatus',            
  287.   case when tm.BillingType='Credit' then isnull(tm.Total-tm.ReceiveAmt,0) else 0 end   as 'Receivable',    
  288.   case when tm.[type]='Due' then isnull(tm.receiveAmt,0) else  0 end as  'Received',    
  289.   case when tm.[type]='Deposit' then isnull(tm.receiveAmt,0) else 0 end  as 'Deposit',    
  290.   case when tm.[type]='Return' then isnull(abs(tm.receiveAmt),0) else 0 end  as 'DepositReturn',    
  291.   'Counter' as 'DueType'  from tblBillingMaster tm                          
  292.   INNER JOIN tblEncounter te on te.strencounterid=tm.strEncounterID    
  293.   INNER JOIN tblpaitentinfo TP on TP.patientID=te.strPatientID                            
  294.   where --TM.AcLedgerID=22 and                      
  295.   Tp.patientID=(case when  @patientid='' then Tp.patientID else @patientid end)              
  296.   and convert(date,Tm.EntryTime)  between convert(date,@fromdate) and convert(date,@todate)  
  297.   and (tm.receiveAmt-(tm.Total-tm.billdiscount))<>0  
  298.   ) as temp    
  299.   group by temp.mobile,temp.PatientID,temp.[Name],temp.PatientWard,temp.BedNo,temp.strStatus,temp.DueType  
  300.  
  301.   union all  
  302.  
  303.   select temp1.mobile,temp1.PatientID,temp1.[Name],temp1.PatientWard,temp1.BedNo,temp1.strStatus,    
  304.   sum(temp1.Receivable)as  'Receivable',sum(temp1.Received)as  'Received',sum(temp1.Deposit)as  'Deposit',sum(temp1.DepositReturn)as  'DepositReturn',    
  305.   temp1.DueType    
  306.   from (    
  307.   select isnull(tp.intmobile,'N/A') as 'mobile',TP.patientID as 'PatientID',Ltrim(Rtrim(TP.srtFname)) +' '+TP.strLname as 'Name',                  
  308.   '0' as 'PatientWard',  
  309.  '0' as 'BedNo',  
  310.  '0'  as 'strStatus',  
  311.  --(select dbo.getLatestDepartment((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))) as 'PatientWard',                          
  312.  --isnull((select dbo.getLatestbED((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))),'N/A') as 'BedNo',  
  313.  --isnull((select dbo.getpatientCurrentStatus((select top 1 strEncounterID from tblEncounter where strPatientID=tp.patientID order by ID desc))),'')as 'strStatus',            
  314.   case when tm.[type]='Credit' then isnull(tm.Total-tm.ReceiveAmt,0) else 0 end   as 'Receivable',    
  315.   case when tm.[type]='Due' then isnull(tm.receiveAmt,0) else  0 end as  'Received',    
  316.   case when tm.[type]='Deposit' then isnull(tm.receiveAmt,0) else 0 end  as 'Deposit',    
  317.   case when tm.[type]='Return' then isnull(abs(tm.receiveAmt),0) else 0 end  as 'DepositReturn',    
  318.   'Counter' as 'DueType'  from tblPharmacySalesMaster tm                          
  319.   INNER JOIN tblEncounter te on te.strencounterid=tm.strEncounterID    
  320.   INNER JOIN tblpaitentinfo TP on TP.patientID=te.strPatientID                            
  321.   where --TM.AcLedgerID=22 and                      
  322.   Tp.patientID=(case when  @patientid='' then Tp.patientID else @patientid end)              
  323. and convert(date,Tm.EntryTime)  between convert(date,@fromdate) and convert(date,@todate)    
  324.   and (tm.receiveAmt-(tm.Total-tm.PharmacyDiscount))<>0  
  325.   ) as temp1    
  326.   group by temp1.mobile,temp1.PatientID,temp1.[Name],temp1.PatientWard,temp1.BedNo,temp1.strStatus,temp1.DueType  
  327.  
  328.   END    
  329.    
  330. ;with totalTem as(    
  331.  select * from #temp    
  332.  )          
  333. select @pagesize as pageRows , count (*) as totalRows, @firstRecord as firstRecord,          
  334. case when count(*) > @pagesize then @lastRecord          
  335. else @firstRecord + count(*) - 1 end          
  336. as lastRecord, Ceiling(CONVERT(decimal,count(*))/@pagesize)          
  337. as TotalPages, @pagenumber as Currentpage from totalTem  
  338. go
  339.  
RAW Paste Data