Out of 3 same/ similar rows, remove two and keep one
-
Hello everyone,
I would like to ask someone for an advice. I am not even a 100% sure it is doable, but it might be.
I have a table that returns the same or almost the same rows of information often times 3 times.
Basically there are 3 types of Transactions: 1. GL Posted - means it is paid. 2. GL Accrual Reversed. 3. GL Accrual.
I need to create some kind of logic that if for one [Voucher Num] there is [GL Posted] for [G/L Transaction Type Desc] we want just to keep this row and remove two rows that have GL Accrual Reversed and GL Accrual rows.
Does someone know if this can be done in SQL?
SELECT
[Freight Bill ID],
[Voucher Num],
[G/L Transaction Type Desc],
[Freight Bill Num],
*
FROM final_ouput
-
By 'remove', do you mean you want these rows deleted from the database?
Or simply a query which removes them from the rows which are selected?
Please provide your sample data in a consumable form (CREATE TABLE / INSERT) if you would like a coded solution.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
DELETE fo
FROM final_output fo
WHERE EXISTS(SELECT 1
FROM final_output fo2
WHERE fo2.[Voucher Num] = fo.[Voucher Num]
AND fo2.[G/L Transaction Type Desc] = 'GL Posted')
AND fo2.[G/L Transaction Type Desc] IN('GL Accrual Reversed', 'GL Accrual') -
@Phil Parkin, simply a query which removes them from the rows that are selected.
I don't have any CREATE statements. I am just selecting rows from CTE. But the way how data is there, we have multiple rows for the same Voucher and I need only those that are 'GL Posted'. But in case there are some 'Accrual Reversed' and 'Accruals' I want to keep them (if they didn't move to GL Posted).
SELECT
[Freight Bill ID],
[Voucher Num],
[G/L Transaction Type Desc],
[Freight Bill Num],
*
FROM final_ouput
-
@jonathan-2 thank you, but I don't think I should delete records from the table, I think I should just not select them in a query, is that possible?
-
Try this:
Replace * with your required columns
SELECT *
FROM final_output fo
WHERE fo.[G/L Transaction Type Desc] = 'GL Posted'
and fo.[Freight Bill Number] is not nullYou can add more conditions in the WHERE to SELECT the row that you need. An alternate is use ROW_NUMBER() function to assign row number 1 (one) to the row that you need and then eliminate the others with rownumber > 1.
=======================================================================
-
Emperor100 thank you, but that's not what I am trying to achieve. I am not trying to remove NULLs. I very often time have the same row twice but one is GL Posted and one Accruals or GL Accrual Reversed (for example) and if it happens I want to keep GL Posted only. If there is no GL Posted, I want keep Accruals. It has nothing to do with NULLs. It may be NULL of course and it may not be.
-
SELECT *
FROM final_output fo
WHERE [G/L Transaction Type Desc] = 'GL Posted'
OR ([G/L Transaction Type Desc] <> 'GL Posted'
AND NOT EXISTS(SELECT 1
FROM final_output fo2
WHERE fo2.[Voucher Num] = fo.[Voucher Num]
AND fo2.[G/L Transaction Type Desc] = 'GL Posted')
)
; -
It should check Voucher Num. If it has GL Posted then take 'GL Posted'. If it doesn't have 'GL Posted' take 'GL Accrual Reversed' and 'GL Acrual'
I was thinking smth like this, but there is a mistake somewhere it returns the whole dataset and doesn't filter anything.
SELECT *
FROM final_output_2 fo
WHERE EXISTS(SELECT 1
FROM final_output_2 fo2
WHERE fo2.[Voucher Num] = fo.[Voucher Num]
AND fo2.[G/L Transaction Type Desc] = 'GL Posted'
OR fo2.[G/L Transaction Type Desc] IN ('GL Accrual Reversed', 'GL Accrual')) -
It should check Voucher Num. If it has GL Posted then take 'GL Posted'. If it doesn't have 'GL Posted' take 'GL Accrual Reversed' and 'GL Acrual'
I was thinking smth like this, but there is a mistake somewhere it returns the whole dataset and doesn't filter anything.
SELECT *
FROM final_output_2 fo
WHERE EXISTS(SELECT 1
FROM final_output_2 fo2
WHERE fo2.[Voucher Num] = fo.[Voucher Num]
AND fo2.[G/L Transaction Type Desc] = 'GL Posted'
OR fo2.[G/L Transaction Type Desc] IN ('GL Accrual Reversed', 'GL Accrual')) -
It should check Voucher Num. If it has GL Posted then take 'GL Posted'. If it doesn't have 'GL Posted' take 'GL Accrual Reversed' and 'GL Acrual'
I was thinking smth like this, but there is a mistake somewhere it returns the whole dataset and doesn't filter anything.
SELECT *
FROM final_output_2 fo
WHERE EXISTS(SELECT 1
FROM final_output_2 fo2
WHERE fo2.[Voucher Num] = fo.[Voucher Num]
AND fo2.[G/L Transaction Type Desc] = 'GL Posted'
OR fo2.[G/L Transaction Type Desc] IN ('GL Accrual Reversed', 'GL Accrual')) -
The simplest would be to add a where clause - but I am guessing that you want all voucher's with the 'latest' transaction type available for that voucher.
If that is the case - then lookup ROW_NUMBER(). Set the partition based on the voucher number - and order by transaction type descending, then select where your row number = 1.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Jeffrey not exactly. Query should check Voucher Num. If it has GL Posted then take 'GL Posted'. If it doesn't have 'GL Posted' take 'GL Accrual Reversed' and 'GL Accrual'. It is not based on latest transaction date.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply