Fearghal Fearghal - 3 months ago 8
SQL Question

Parsing a string in SQL with If statement

I have a table with a string in some columns values that tells me if I should delete the row....however this string needs some parsing to understand whether to delete or not.

What is the string: it tells me the recurrence of meetings eg everyday starting 21st march for 10 meetings.

My table is a single column called

recurrence
:

Recurrence
-------------------------------
daily;1;21/03/2015;times;10
daily;1;01/02/2016;times;8
monthly;1;01/01/2016;times;2
weekly;1;21/01/2016;times;4


What to do: if the meetings are finished then remove the row.

The string is of the following format

<frequency tag>;<frequency number>;<start date>;times;<no of times>


For example

daily;1;21/03/2016;times;10


everyday starting 21st march, for 10 times

Does anybody know how I would calculate if the string indicates all meetings are in past? I want a select statement that tells me if the recurrence values are in past - true or false

Answer

I added one string ('weekly;1;21/05/2016;times;4') that definitely must not be deleted to show some output. At first try to add to temp table `@table1' all data from your table and check if all is deleted well.

DECLARE @table1 TABLE (
    Recurrence nvarchar(max)
)
DECLARE @xml xml

INSERT INTO @table1 VALUES
('daily;1;21/03/2016;times;10'),
('daily;1;21/03/2015;times;10'),
('daily;1;01/02/2016;times;8'),
('monthly;1;01/01/2016;times;2'),
('weekly;1;21/01/2016;times;4'),
('weekly;1;21/05/2016;times;4')


SELECT @xml= (
SELECT CAST('<s><r>' + REPLACE(Recurrence,';','</r><r>') + '</r><r>'+ Recurrence+'</r></s>' as xml)
FROM @table1
FOR XML PATH ('')
)

;WITH cte as (
SELECT  t.v.value('r[1]','nvarchar(10)') as how,
        t.v.value('r[2]','nvarchar(10)') as every,
        CONVERT(date,t.v.value('r[3]','nvarchar(10)'),103) as since,
        t.v.value('r[4]','nvarchar(10)') as what,
        t.v.value('r[5]','int') as howmany,
        t.v.value('r[6]','nvarchar(max)') as Recurrence
FROM @xml.nodes('/s') as t(v)
)

DELETE t
FROM @table1 t
LEFT JOIN cte c ON c.Recurrence=t.Recurrence
WHERE 
     CASE WHEN how = 'daily' THEN DATEADD(day,howmany,since)
            WHEN how = 'weekly' THEN DATEADD(week,howmany,since)
            WHEN how = 'monthly' THEN DATEADD(month,howmany,since)
            ELSE NULL END < GETDATE()

SELECT * FROM @table1

Output:

Recurrence
-----------------------------
weekly;1;21/05/2016;times;4

(1 row(s) affected)