Delete Query ?

I have following Invoice Nos.
LS-123
LS-123R
LS-123A
LS-124
LS-124R
LS-125
LS-126
1. I need to delete all invoice nos. which have been issued again as 'R' at
the end of Invoice No., for example: LS-123
2. I need to delete all invoice nos. which have 'R' at the end of Invoice
No., for example: LS-123R (Reversed)
3. Any Invoice which has not been issued again as 'R' should not be deleted,
for example: LS-125 and LS-126 (Actual/Original) .
4. Any Invoice which contains 'R' and not contains 'A', so the Original and
Revised, both Invoices should be deleted, For example: LS-124 was issued and
then reversed with LS-124R, then no LS-124A was issued, so both the invoices
should be deleted.
The data after deleting above Invoices should be:
LS-123A
LS-125
LS-126
Please note:
'R' at the end of Invoice No. means 'Reversed'
'A' at the end of Invoice No. means 'Actual'
Best Regards,
LuqmanThe delete statements below should perform the necessary deletes:
CREATE TABLE dbo.Invoices
(
InvoiceNo VARCHAR(10)
)
INSERT INTO dbo.Invoices SELECT 'LS-123'
INSERT INTO dbo.Invoices SELECT 'LS-123R'
INSERT INTO dbo.Invoices SELECT 'LS-123A'
INSERT INTO dbo.Invoices SELECT 'LS-124'
INSERT INTO dbo.Invoices SELECT 'LS-124R'
INSERT INTO dbo.Invoices SELECT 'LS-125'
INSERT INTO dbo.Invoices SELECT 'LS-126'
-- Remove the original inovice for any re-issued invoices
DELETE T2
FROM dbo.Invoices T1 JOIN dbo.Invoices T2
ON SUBSTRING(T1.invoiceno, 1, LEN(T1.invoiceno) - 1) = T2.invoiceno
WHERE RIGHT(T1.invoiceno, 1) = 'R'
-- Remove the re-issued invoices
DELETE
FROM dbo.Invoices
WHERE RIGHT(invoiceno, 1) = 'R'
SELECT InvoiceNo
FROM dbo.Invoices
Returns:
InvoiceNo
--
LS-123A
LS-125
LS-126
(3 row(s) affected)
- Peter Ward
WARDY IT Solutions
"Luqman" wrote:

> I have following Invoice Nos.
> LS-123
> LS-123R
> LS-123A
> LS-124
> LS-124R
> LS-125
> LS-126
> 1. I need to delete all invoice nos. which have been issued again as 'R' a
t
> the end of Invoice No., for example: LS-123
> 2. I need to delete all invoice nos. which have 'R' at the end of Invoice
> No., for example: LS-123R (Reversed)
> 3. Any Invoice which has not been issued again as 'R' should not be delete
d,
> for example: LS-125 and LS-126 (Actual/Original) .
> 4. Any Invoice which contains 'R' and not contains 'A', so the Original an
d
> Revised, both Invoices should be deleted, For example: LS-124 was issued a
nd
> then reversed with LS-124R, then no LS-124A was issued, so both the invoic
es
> should be deleted.
> The data after deleting above Invoices should be:
> LS-123A
> LS-125
> LS-126
> Please note:
> 'R' at the end of Invoice No. means 'Reversed'
> 'A' at the end of Invoice No. means 'Actual'
>
> Best Regards,
> Luqman
>
>
>
>