SP remains

[codrutza]

SQL Server 2000.

I have the following SPs:

SP1
CREATE procedure [dbo].[Z_Mg] @Year int as
DECLARE @DateStart datetime
DECLARE @DateEnd datetime
DECLARE @c_result INT

SET @DateStart = cast(@Year-1 as varchar(4))+'0101'
-- Set date start to be 01/01/year-1 00:00:00

SET @DateEnd = cast(@Year+1 as varchar(4))+'0101'
-- Set date end to be 01/01/year+1 00:00:00

IF @Year= YEAR(GETDATE())
-- Current year, must get last full month
BEGIN
SET @DateEnd = CAST(CONVERT(varchar(8),DateAdd(dd,-DAY(GetDate()), GetDate()), 112) as datetime) + 1
END

SELECT
JobA.Sales,
…

WHERE
…
AND (Jobs.Date >= @DateStart AND Jobs.Date<@DateEnd)
…

SP2
CREATE procedure [dbo].[Z_Mg_Sea] @Year int as
DECLARE @DateStart datetime
DECLARE @DateEnd datetime
DECLARE @c_result INT

SET @DateStart = cast(@Year-1 as varchar(4))+'0101'
-- Set date start to be 01/01/year-1 00:00:00

SET @DateEnd = cast(@Year+1 as varchar(4))+'0101'
-- Set date end to be 01/01/year+1 00:00:00

IF @Year= YEAR(GETDATE())
-- Current year, must get last full month
BEGIN
SET @DateEnd = CAST(CONVERT(varchar(8),DateAdd(dd,-DAY(GetDate()), GetDate()), 112) as datetime) + 1
END

SELECT
JobA.Sales,
….

WHERE
…..
AND Jobs.Via='Sea'
AND Jobs.CCCC<>'200'
AND (Jobs.Date >= @DateStart AND Jobs.Date<@DateEnd)

SP3
CREATE procedure [dbo].[Z_Mg_CCCC] @Year int as
DECLARE @DateStart datetime
DECLARE @DateEnd datetime
DECLARE @c_result INT

SET @DateStart = cast(@Year-1 as varchar(4))+'0101'
-- Set date start to be 01/01/year-1 00:00:00

SET @DateEnd = cast(@Year+1 as varchar(4))+'0101'
-- Set date end to be 01/01/year+1 00:00:00

IF @Year= YEAR(GETDATE())
-- Current year, must get last full month
BEGIN
SET @DateEnd = CAST(CONVERT(varchar(8),DateAdd(dd,-DAY(GetDate()), GetDate()), 112) as datetime) + 1
END


SELECT
JobA.Sales,
Jobs.CCC,
….
FROM
…

WHERE
…
AND Jobs.CCCC='200'
AND (Jobs.Date >= @DateStart AND Jobs.Date<@DateEnd)
I have to retrieve al the records from SP1 that are not in SP2 and SP3.

In other words, “mathematically”:
SP1-(SP2 U SP3)

A 5 sea
B 2 air
C 3 AAAA
E 8 OOO
D 7 CCCC

A 5 sea

D 7 CCCC

The others
B 2 air
C 3 AAAA
E 8 OOO


How do I write this in a SP?
Thanks