API tools faq
paste
Ranish666

changes in sql 2021-05-26

Ranish666
May 26th, 2021
1,603
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 21.09 KB | None | 0 0
raw download clone embed print report
  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.  
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!