Incorrect syntax near the keyword 'or'. in criteria for a rolling range-CodePudding

I am showing two nested select statements that I have in a larger select statement. There are actually 10 of these that sum the sales/issued/usage for products with the criteria being matching warehouse and Item and then the date value range for this year, last , two years ago. There is no datetime column, and the columns for determining the date are a [Year] and [Month] column. I have to get sums from the date criteria mentioned.

All of the statements I use for static hears and for YTD work fine. But when I use the 'or' syntax to say "get everything from 2 years ago whose month is >= to my current month" that works fine but when I add the or to include everything from this year as well whose month is <= the current month" I get the OR error. Also the AS for some reason is now causing the shame syntactical error but with the keyword AS (Incorrect syntax near the keyword 'as') Could the underscore in the alias name cause the:

Incorrect syntax near the keyword 'as'

error?

(SELECT SUM(sold)   sum(Issued)AS Expr1
     FROM dbo.M_NewtrnYM 
        WHERE (WH = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.Warehouse) AND (ITEM = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.ItemNumber) AND (YEAR = YEAR(CURRENT_TIMESTAMP) - 1) 
            and (CAST([Month] as Integer) >=  Month(CURRENT_TIMESTAMP))) or (YEAR = YEAR(CURRENT_TIMESTAMP)) and (CAST([Month] as Integer) <=  Month(CURRENT_TIMESTAMP))) AS Twelvemth_usage, 

(SELECT SUM(sold)   sum(Issued)AS Expr1
     FROM dbo.M_NewtrnYM 
        WHERE (WH = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.Warehouse) AND (ITEM = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.ItemNumber) AND (YEAR = year(dateadd(m, -6, getdate() - datepart(d, getdate())   1))) 
            and (CAST([Month] as Integer) >=   month(dateadd(m, -6, getdate() - datepart(d, getdate())   1)) or (YEAR = YEAR(CURRENT_TIMESTAMP)) and (CAST([Month] as Integer) <=  Month(CURRENT_TIMESTAMP))) AS sixmth_usage,

CodePudding user response：

You are missing one closing bracket at the end:

(

    SELECT SUM(sold)   sum(Issued)AS Expr1
    FROM dbo.M_NewtrnYM 
    WHERE 
        (WH = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.Warehouse) 
        AND (ITEM = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.ItemNumber) 
        AND (YEAR = year(dateadd(m, -6, getdate() - datepart(d, getdate())   1))) 
        and (CAST([Month] as Integer) >=   month(dateadd(m, -6, getdate() - datepart(d, getdate())   1)) or (YEAR = YEAR(CURRENT_TIMESTAMP)) and (CAST([Month] as Integer) <=  Month(CURRENT_TIMESTAMP)))
)

Also, you can remove some of the brackets on the AND condition and looking at your last condition, are you sure this is what you want?

(

    SELECT SUM(sold)   sum(Issued)AS Expr1
    FROM dbo.M_NewtrnYM 
    WHERE 
        WH = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.Warehouse
        AND ITEM = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.ItemNumber
        AND YEAR = year(dateadd(m, -6, getdate() - datepart(d, getdate())   1))
        and 
        (
            CAST([Month] as Integer) >=   month(dateadd(m, -6, getdate() - datepart(d, getdate())   1)) 
            or 
            YEAR = YEAR(CURRENT_TIMESTAMP) 
            and 
            CAST([Month] as Integer) <=  Month(CURRENT_TIMESTAMP)
        )
)

You have X or Y and Z. If X is true, Z will no be ignored.

CodePudding user response：

(SELECT SUM(sold) AS Expr1
 FROM dbo.M_NewtrnYM 
    WHERE (WH = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.Warehouse) 
        AND (ITEM = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.ItemNumber) 
        AND (Year = YEAR(CURRENT_TIMESTAMP) - 1) 
         AND (CAST(Month AS Integer) >= Month(CURRENT_TIMESTAMP)) 
 OR (WH = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.Warehouse) 
      AND (ITEM = dbo.ac_Inventory_WH_Class_MP_Itnbr_Dtl.ItemNumber)  
       and(Year = YEAR(CURRENT_TIMESTAMP)) 
          AND (CAST(Month AS Integer) <= Month(CURRENT_TIMESTAMP))) 
            AS RollingTwelvemth_sale