Advertisement
Ranish666

hospital journal counter

Dec 8th, 2021
1,216
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 3.89 KB | None | 0 0
  1.  
  2. ALTER Procedure [dbo].[AccountDetailsgetByBillNo]    
  3. @fromBillNo nvarchar(max),      
  4. @toBillNo nvarchar(max) ,  
  5. @userId int = null    
  6. AS      
  7. declare @patientLedger decimal(18,5)      
  8. declare @frombillId int      
  9. declare @toBillID int      
  10. set @frombillId= (isnull((select billingMasterID from tblbillingmaster Where strBillNo=@fromBillNo),0))      
  11. set @toBillID= (isnull((select billingMasterID from tblbillingmaster Where strBillNo=@toBillNo),0))      
  12.    
  13. set @patientLedger = (select
  14. sum(BillDiscount) + sum(DiscountAmt) + sum(ReceiveAmt) - sum(TaxAmt) - sum(total + DiscountAmt -taxAmt)    
  15. from tblbillingmaster WHERE AcledgerID=22 and billingMasterID between @frombillId and @toBillID  
  16. and UserID = case when @userId is null or @userId = 0 then UserID else @userId end )      
  17.    
  18. ; with tempCash as    
  19.  (select accountGroupId from tbl_AccountGroup where accountGroupId = 27    
  20.  union    
  21.  select accountGroupId from tbl_AccountGroup where groupUnder = 27)    
  22.  , tempBank as (select accountGroupId from tbl_AccountGroup where accountGroupId = 28    
  23.  union    
  24.  select accountGroupId from tbl_AccountGroup where groupUnder = 28)    
  25.    
  26. select  'sales' as heading,16 as ledgerid,    
  27. --sum(total + DiscountAmt) as amount,    
  28. sum(total + DiscountAmt -taxAmt) as amount,    
  29. 'Cr' as DrCR      
  30. from tblbillingmaster Where billingMasterID between @frombillId and @toBillID    
  31. and UserID = case when @userId is null or @userId = 0 then UserID else @userId end  
  32.    
  33. Union ALL      
  34.    
  35. select 'cash' as heading,1 as ledgerid, isnull( sum(receiveAmt) ,0) as amount, 'Dr' as DrCR      
  36. from tblbillingmaster bm    
  37. inner join tempCash c on c.accountGroupId in (select accountGroupId from tbl_AccountLedger where ledgerId =   ReceiveLedgerID)    
  38. Where billingMasterID between @frombillId and @toBillID    
  39. and UserID = case when @userId is null or @userId = 0 then UserID else @userId end  
  40.    
  41. Union ALL      
  42.    
  43. select 'Discount' as heading, 8 as ledgerid,      
  44. sum(discountAmt+BillDiscount )      
  45. as amount, 'Dr' as DrCR      
  46. from tblbillingmaster Where billingMasterID between @frombillId and @toBillID  
  47. and UserID = case when @userId is null or @userId = 0 then UserID else @userId end  
  48.  
  49. Union ALL      
  50. select 'tax' as heading, 36 as ledgerid, sum(taxAmt) as tax, 'Cr' as DrCR from tblbillingmaster Where billingMasterID between @frombillId and @toBillID  
  51. and UserID = case when @userId is null or @userId = 0 then UserID else @userId end    
  52.    
  53. Union ALL      
  54.    
  55. select 'patientLedger' as heading,22 as ledgerid, ABS(@patientLedger)  as amount, case when @patientLedger> 0 then 'Cr' when @patientLedger<0 then'Dr' else 'Dr' end as DrCR    
  56.    
  57. Union ALL      
  58.    
  59. select ac.ledgerName as heading,    
  60. (select AccRefId from tbl_AccountLedger where ledgerId = ReceiveLedgerID) as ledgerid,      
  61. --(sum(total + case when billdispercent = 100 or BillingType = 'Credit' then 0 else      
  62. --discountAmt+BillDiscount      
  63. --end-taxAmt)+sum(taxAmt))-(sum(receiveAmt)+sum(taxAmt))    
  64. --(sum(total) - sum(receiveAmt))    
  65. sum(receiveAmt)    
  66. as amount,      
  67. --case when (select accountGroupId from tbl_AccountLedger where ledgerId = ReceiveLedgerID) = 28 then 'Dr'    
  68. --else case when @patientLedger> 0 then 'Cr' when @patientLedger<0 then'Dr' else 'Dr' end    
  69. --end    
  70. 'Dr'    
  71. as DrCR        
  72. --case when @patientLedger> 0 then 'Cr' when @patientLedger<0 then'Dr' else 'Dr' end as DrCR        
  73.    
  74. from tblbillingmaster tm      
  75. inner join tbl_accountledger ac on tm.ReceiveLedgerID = ac.ledgerid      
  76. inner join tempBank bank on bank.accountGroupId in (select accountGroupId from tbl_AccountLedger where ledgerId =   ReceiveLedgerID)    
  77. WHERE  tm.billingMasterID between @frombillId and @toBillID      
  78. and tm.UserID = case when @userId is null or @userId = 0 then tm.UserID else @userId end  
  79. group by tm.ReceiveLedgerID, ac.ledgerName
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement