Advertisement
touhid_xml

Dr Cr and vCr vDr Combine Report Query

May 27th, 2019
450
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 1.92 KB | None | 0 0
  1. SELECT  [id]
  2.       ,CASE WHEN [type] = 1 THEN 'Debit' ELSE 'Credit' END as [TrType]
  3.       ,[date]
  4.       ,[ref]
  5.       ,[refId]
  6.       ,[particular]
  7.       ,[debit]
  8.       ,[debit] as [Dr]
  9.       ,NULL as [vDr]
  10.       ,[credit]
  11.       ,[credit] as [Cr]
  12.       ,NULL as [vCr]
  13.       ,[balance]
  14.       ,[balance] as [RealBalance]
  15.       ,NULL as [vBalance]
  16.       ,NULL as [TotalPayment]
  17.       ,(SELECT [transactionCat].[catName] FROM transactionCat WHERE [transactionCat].[catId] = [trCat] ) AS [TrCat]
  18.       ,[remarks]
  19.       ,(SELECT [clients].clientName FROM clients WHERE clientId = [client]) AS[Client]
  20.           , [client] as [ClientId]
  21.       ,(SELECT [users].[userName] FROM [users] WHERE users.userId = [addedBy]) AS[Creator]
  22.       ,[modifiedBy]
  23.       ,[timeAdded]
  24.       ,[timeModified]
  25.           ,NULL as [Virtual]
  26.           , NULL as [PaymentDate]
  27.           , NULL as [interestRate]
  28.       ,[status]
  29.  
  30.   FROM [raipur_auto].[dbo].[debitCredit]
  31.   UNION
  32.   select   [id]
  33.       ,CASE WHEN [type] = 1 THEN 'Debit' ELSE 'Credit' END as [TrType]
  34.       ,[date]
  35.       ,[ref]
  36.       ,[refId]
  37.       ,[particular]
  38.       ,[debit]
  39.       ,NULL as [Dr]
  40.       ,[debit] as [vDr]
  41.       ,[credit]
  42.       ,NULL as [Cr]
  43.       ,[credit] as [vCr]
  44.       ,[balance]
  45.       ,NULL as [RealBalance]
  46.       ,[balance] as [vBalance]
  47.  
  48.       ,   ( SELECT
  49.       SUM(Transcations.TrAmount)
  50.     from
  51.       Transcations
  52.     WHERE
  53.       RefId = id
  54.   ) AS [TotalPayment]
  55.       ,(SELECT [transactionCat].[catName] FROM transactionCat WHERE [transactionCat].[catId] = [trCat] ) AS [TrCat]
  56.       ,[remarks]
  57.       ,(SELECT [clients].clientName FROM clients WHERE clientId = [client]) AS[Client]
  58.       , [client] as [ClientId]
  59.       ,(SELECT [users].[userName] FROM [users] WHERE users.userId = [addedBy]) AS[Creator]
  60.       ,[modifiedBy]
  61.       ,[timeAdded]
  62.       ,[timeModified]
  63.       ,[vDc] as [Virtual]
  64.       ,[lastPaymentDate] as [PaymentDate],
  65.       [interestRate] as [interestRate]
  66.       ,[status] from VirtualDebitCredit
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement