I have this cursor and the 'between' where it gives error. I would like to select the years from 2005 to 2008

Navigation
0
DECLARE @Year int
DECLARE db_cursor CURSOR FOR
Select distinct Year(OrderDate) as Year
From Sales.SalesOrderHeader
Order by Year(OrderDate)

OPEN db_cursor FETCH NEXT FROM db_cursor INTO @Year
WHILE @@FETCH_STATUS = 0   
BEGIN

--INSERT INTO xxxxx ("Year", "Month", "Total", "RunningTotal")
SELECT a.Year, a.Month, Sum(a.TotalDue) as Total, Sum(a.RunningTotal) as RunningTotal
From (
    select Year = Year(convert(int,OrderDate,111)),
        case Month(convert(int,OrderDate,111))
            when 1 then 'Janeiro'
            when 2 then 'Fevereiro'
            when 3 then 'Março'
            when 4 then 'Abril'
            when 5 then 'Maio'
            when 6 then 'Junho'
            when 7 then 'Julho'
            when 8 then 'Agosto'
            when 9 then 'Setembro'
            when 10 then 'Outubro'
            when 11 then 'Novembro'
            when 12 then 'Dezembro'
        else 'unknown'
        end as "Month1",
        Month = Month(convert(int,OrderDate,111)),
        TotalDue = convert(money,TotalDue,1),
        RunningTotal = convert(money,
            (SELECT sum(convert(money, TotalDue,1))
             FROM   Sales.SalesOrderHeader as Header
             WHERE  SalesOrderID <= soh.SalesOrderID
                AND year(OrderDate) '2005' between '2008'
                ),
            1)
    FROM Sales.SalesOrderHeader soh
    WHERE year(OrderDate) '2005' between '2008'
) a
group by a.Year, a.Month
order by 1, 2;

FETCH NEXT FROM db_cursor INTO @Year
END

CLOSE db_cursor
DEALLOCATE db_cursor

--End Cursor
    
asked by anonymous 11.08.2017 / 18:47

3 answers

2

The syntax is wrong ...

It has to be 

WHERE year(OrderDate) between '2005' and '2008'
    
11.08.2017 / 18:49
2

You have two syntax errors that I could see, the first is the use of BETWEEN 

AND year(OrderDate) '2005' between '2008'

The correct syntax is 

WHERE col BETWEEN value1 AND value2

And your other error and compare the return of your YEAR a> with a varchar, the year returns an int then compare with an int.

The correct one would be 

WHERE year(OrderDate) between 2005 and 2008

Some things in your query are unnecessary as Month(convert(int,getdate(),111)) there is no need here to use convert when you can use Month to return the month see that the select below return the same result. 

select Month(convert(int,getdate(),111)), Month(getdate())

The same goes for Year.

I do not know the idea of using CURSOR ?? you did it and did not use it at all ... 

DECLARE @Year int
DECLARE db_cursor CURSOR FOR
Select distinct Year(OrderDate) as Year
From Sales.SalesOrderHeader
Order by Year(OrderDate)

OPEN db_cursor FETCH NEXT FROM db_cursor INTO @Year
WHILE @@FETCH_STATUS = 0

If your goal is to make an insert with the return data based on a range of YEARS (2005 - 2008) use lool for and avoid making a select unnecessarily. 

DECLARE @ano INT = 2005;

WHILE @ano < 2009
BEGIN
   {-- todo}
   SET @ano = @ano + 1;
END;

Use the variable you have incremented in your loop to select 

AND year(OrderDate) = @ano;

in place of your AND year(OrderDate) '2005' between '2008'

Finally, if you just insert it in that range even then just use between and remove from within your CURSOR it will only duplicate your data the way it is.

    
11.08.2017 / 19:42
1

I believe your error is syntax synonymous; uses between as below: 

WHERE year(OrderDate) between '2005' and '2008'

    
11.08.2017 / 18:50
How to add an attribute with JSTL under one condition? Create a way to not repeat my code [closed]