I have this table structure and I want a query that needs to be returning opposite side columns values.
CREATE TABLE TransactionDetail ( ID NUMERIC NOT NULL PRIMARY KEY, TransactionCode bigint, COATitle NVARCHAR(50), DrAmount NUMERIC, CrAmount NUMERIC ); INSERT INTO TransactionDetail VALUES (1, 1, 'Overtime', '2500', NULL); INSERT INTO TransactionDetail VALUES (2, 1, 'Internship', NULL, '1500'); INSERT INTO TransactionDetail VALUES (3, 1, 'Medical', NULL, '1000'); INSERT INTO TransactionDetail VALUES (4, 2, 'Internship', '1150', NULL); INSERT INTO TransactionDetail VALUES (5, 2, 'Overtime', NULL, '1150'); INSERT INTO TransactionDetail VALUES (6, 3, 'Overtime', '600', NULL); INSERT INTO TransactionDetail VALUES (7, 3, 'Refreshment', '400', NULL); INSERT INTO TransactionDetail VALUES (8, 3, 'Car Loan', '200', NULL); INSERT INTO TransactionDetail VALUES (9, 3, 'Office Expenses', NULL, '1200');
If I pass parameter with value Overtime
then it should return following rows
SELECT COATitle, DrAmount, CrAmount FROM TransactionDetail WHERE COATitle <> Overtime Internship NULL 1500 Medical NULL 1000 Internship 1150 NULL Office Expenses NULL 1200
The logic is against each transaction if the selected Account is on Debit
side it should print the Credit
side accounts and if the selected Account is on Credit
side it should print Debit
side accounts against that specific TransactionCode
Advertisement
Answer
The following code gives the desired result. It does that by checking to see whether the provided parameter is a debit or credit (or doesn’t exist) in the current transaction, then only displays the reverse as specified.
declare @Parameter nvarchar(50) = 'Overtime' declare @Trans TABLE ( ID NUMERIC NOT NULL, TransactionCode bigint, COATitle NVARCHAR(50), DrAmount NUMERIC, CrAmount NUMERIC ); INSERT INTO @Trans VALUES (1, 1, 'Overtime', '2500', NULL); INSERT INTO @Trans VALUES (2, 1, 'Internship', NULL, '1500'); INSERT INTO @Trans VALUES (3, 1, 'Medical', NULL, '1000'); INSERT INTO @Trans VALUES (4, 2, 'Internship', '1150', NULL); INSERT INTO @Trans VALUES (5, 2, 'Overtime', NULL, '1150'); INSERT INTO @Trans VALUES (6, 3, 'Overtime', '600', NULL); INSERT INTO @Trans VALUES (7, 3, 'Refreshment', '400', NULL); INSERT INTO @Trans VALUES (8, 3, 'Car Loan', '200', NULL); INSERT INTO @Trans VALUES (9, 3, 'Office Expenses', NULL, '1200'); select TransactionCode, COATitle, DrAmount, CrAmount from ( SELECT TransactionCode, COATitle, DrAmount, CrAmount , case when exists (select 1 from @Trans T1 where T1.TransactionCode = T.TransactionCode and T1.COATitle = @Parameter and DrAmount is not null) then 1 when exists (select 1 from @Trans T1 where T1.TransactionCode = T.TransactionCode and T1.COATitle = @Parameter and CrAmount is not null) then -1 else 0 end TransSign FROM @Trans T WHERE COATitle <> @Parameter ) X where (TransSign = -1 and DrAmount is not null) or (TransSign = 1 and CrAmount is not null)