SQL Server incorrect syntax near '-' on subtraction

Question

I have two MS SQL Server queries that, when run separately, produce the correct results, but when I try to subtract the results from one from the other, I receive the error:

Incorrect syntax near '-'.

Any thoughts? I've counted my parentheses over and over, but I could be totally missing something.

The complete query is:

GO
declare @oldDate DATETIME = '3/31/2014';
declare @newDate DATETIME = '5/1/2014';

(SELECT COALESCE(SUM([Revenue]), 0) FROM [authmanager2].[dbo].[cboyd_monthly_finance_funnel]
WHERE [Opp_ID] IN (
    -- Select everything in the new date
    SELECT [Opp_ID] FROM [authmanager2].[dbo].[cboyd_monthly_finance_funnel]
    WHERE [Stage] = '6. Upside' 
    AND ([Status] = 'Won - Not Booked' OR [Status] = 'Open') AND [Report_Date] = @newDate

    Except
    -- everything in the old date
    (SELECT [Opp_ID] FROM [authmanager2].[dbo].[cboyd_monthly_finance_funnel]
    WHERE [Stage] = '6. Upside' 
    AND ([Status] = 'Won - Not Booked' OR [Status] = 'Open') AND [Report_Date] = @oldDate
    Union
    -- everything that was a new item
    SELECT [Opp_ID] FROM [authmanager2].[dbo].[cboyd_monthly_finance_funnel]
        WHERE [Opp_ID] NOT IN (SELECT [Opp_ID] FROM [authmanager2].[dbo].[cboyd_monthly_finance_funnel]
        WHERE [Report_Date] = @oldDate)
    AND [Stage] = '6. Upside' 
    AND ([Status] = 'Won - Not Booked' OR [Status] = 'Open') AND [Report_Date] = @newDate
    Union
    -- everything that was added to upside (stage change)
    SELECT [t].[Opp_ID]
      FROM [authmanager2].[dbo].[cboyd_monthly_finance_funnel][t]
      Join [authmanager2].[dbo].[cboyd_monthly_finance_funnel][s] On [t].[Opp_ID] = [s].[Opp_ID]
      WHERE [t].[Report_Date] = @newDate And [t].[Stage] = '6. Upside'
      And ([t].[Status] = 'Open' Or [t].[Status] = 'Won - Not Booked')
      And ([s].[Stage] = '7. Forecast' And [s].[Report_Date] = @oldDate)
    )
) AND [Report_Date] = @newDate)
-
(SELECT COALESCE(SUM([Revenue]), 0) FROM [authmanager2].[dbo].[cboyd_monthly_finance_funnel]
WHERE [Opp_ID] IN (
    -- Select everything in the old date
    SELECT [Opp_ID] FROM [authmanager2].[dbo].[cboyd_monthly_finance_funnel]
    WHERE [Stage] = '6. Upside' 
    AND ([Status] = 'Won - Not Booked' OR [Status] = 'Open') AND [Report_Date] = @oldDate

    Except
    -- everything that was taken from upside (stage change)
    (-- Select everything in the new date
    SELECT [Opp_ID] FROM [authmanager2].[dbo].[cboyd_monthly_finance_funnel]
        WHERE [Stage] = '6. Upside' 
        AND ([Status] = 'Won - Not Booked' OR [Status] = 'Open') AND [Report_Date] = @newDate
    Union
    SELECT [t].[Opp_ID]
      FROM [authmanager2].[dbo].[cboyd_monthly_finance_funnel][t]
      Join [authmanager2].[dbo].[cboyd_monthly_finance_funnel][s] On [t].[Opp_ID] = [s].[Opp_ID]
      WHERE [t].[Report_Date] = @newDate And [t].[Stage] = '7. Forecast'
      And ([t].[Status] = 'Open' Or [t].[Status] = 'Won - Not Booked')
      And ([s].[Stage] = '6. Upside' And [s].[Report_Date] = @oldDate)
    Union
    -- everything that was booked
    SELECT [s].[Opp_ID]
        FROM [authmanager2].[dbo].[cboyd_monthly_finance_funnel][t]
        Join [authmanager2].[dbo].[cboyd_monthly_finance_funnel][s] On [t].[Opp_ID] = [s].[Opp_ID]
        WHERE [s].[Stage] = '6. Upside' AND [t].[Report_Date] = @newDate And [t].[Status] = 'Closed - Won'
        And [s].[Report_Date] = @oldDate And [s].[Status] != 'Closed - Won'
    Union
    -- everything that went to proposed
    SELECT [t].[Opp_ID]
      FROM [authmanager2].[dbo].[cboyd_monthly_finance_funnel][t]
      Join [authmanager2].[dbo].[cboyd_monthly_finance_funnel][s] On [t].[Opp_ID] = [s].[Opp_ID]
      WHERE [t].[Report_Date] = @newDate And [t].[Stage] = '5. Proposed'
      And ([t].[Status] = 'Open' Or [t].[Status] = 'Won - Not Booked')
      And ([s].[Stage] = '6. Upside' And [s].[Report_Date] = @oldDate)
      And ([s].[Status] = 'Open' Or [s].[Status] = 'Won - Not Booked')
    Union
    -- everything that was lost
    SELECT [s].[Opp_ID]
        FROM [authmanager2].[dbo].[cboyd_monthly_finance_funnel][t]
        Join [authmanager2].[dbo].[cboyd_monthly_finance_funnel][s] On [t].[Opp_ID] = [s].[Opp_ID]
        WHERE [s].[Stage] = '6. Upside' AND [t].[Report_Date] = @newDate And ([t].[Status] = 'Closed - Lost' Or [t].[Status] = 'Inactive')
        And [s].[Report_Date] = @oldDate And ([s].[Status] != 'Closed - Lost' And [s].[Status] != 'Inactive')
    )
) AND [Report_Date] = @oldDate)

You cannot use `-` between two queries. Either put a `select` in front to subtract two scalars or use `except` for set "subtraction". — Gordon Linoff, May 09 '14 at 22:39
Is the query auto-generated? All those square bracket are useless — Serpiton, May 09 '14 at 22:48
@GordonLinoff was right. I was being an idiot because I had dragged this out of my main procedure and was rewriting it. I wrapped the whole thing like so and it works (not enough rep to answer my own question):`SELECT DISTINCT (query1) - (query2) FROM [authmanager2].[dbo].[cboyd_monthly_finance_funnel];` — jackbanditdude, May 09 '14 at 22:50

score 0 · Answer 1 · answered May 09 '14 at 22:42

0

Where do you want the result to go?

Into a variable or displayed in console?

Try

SELECT (QUERY1) - (QUERY2)

for the console and

DECLARE @myvar INTEGER
SELECT @myvar = (QUERY1) - (QUERY2)

for a variable.

answered May 09 '14 at 22:42

Biff MaGriff

8,102
9
61
98

score 0 · Answer 2 · edited May 23 '17 at 12:05

0

i think the problem is when you use "Won - Not Booked".. i suggest using escape characters and see how it goes. check Escape a string in SQL Server so that it is safe to use in LIKE expression and Using Like

edited May 23 '17 at 12:05

Community

1
1

answered May 09 '14 at 22:46

nathandrake

427
1
4
19

Every occurrence of that string (that I see) is surrounded by single quotes and hence is a string literal. – Gordon Linoff May 09 '14 at 22:51

SQL Server incorrect syntax near '-' on subtraction

2 Answers2