Question

I need to declare 12 decimal variables, corresponding to each month s year, with a cursor I sum values to this variables, then later I Update some sales information.

I don t know if sql server has this syntax

 Declare MonthsSale(1 to 12) as decimal(18,2)

This code works Ok. !

CREATE PROCEDURE [dbo].[proc_test]
AS
BEGIN

--SET NOCOUNT ON;

DECLARE @monthsales TABLE ( monthnr int,    amount decimal(18,2)    )


-- PUT YOUR OWN CODE HERE


-- THIS IS TEST CODE
-- 1 REPRESENTS JANUARY, ...
INSERT @monthsales (monthnr, amount) VALUES (1, 100)
INSERT @monthsales (monthnr, amount) VALUES (1, 100)

INSERT @monthsales (monthnr, amount) VALUES (2, 200)
INSERT @monthsales (monthnr, amount) VALUES (3, 300)
INSERT @monthsales (monthnr, amount) VALUES (4, 400)
INSERT @monthsales (monthnr, amount) VALUES (5, 500)
INSERT @monthsales (monthnr, amount) VALUES (6, 600)
INSERT @monthsales (monthnr, amount) VALUES (7, 700)
INSERT @monthsales (monthnr, amount) VALUES (8, 800)
INSERT @monthsales (monthnr, amount) VALUES (9, 900)
INSERT @monthsales (monthnr, amount) VALUES (10, 1000)
INSERT @monthsales (monthnr, amount) VALUES (11, 1100)
INSERT @monthsales (monthnr, amount) VALUES (12, 1200)


SELECT monthnr, SUM(amount) AS SUM_MONTH_1 FROM @monthsales WHERE monthnr = 1 GROUP BY monthnr
SELECT monthnr, SUM(amount) AS SUM_MONTH_2 FROM @monthsales WHERE monthnr = 2 GROUP BY monthnr
SELECT monthnr, SUM(amount) AS SUM_MONTH_3 FROM @monthsales WHERE monthnr = 3 GROUP BY monthnr
SELECT monthnr, SUM(amount) AS SUM_MONTH_4 FROM @monthsales WHERE monthnr = 4 GROUP BY monthnr
SELECT monthnr, SUM(amount) AS SUM_MONTH_5 FROM @monthsales WHERE monthnr = 5 GROUP BY monthnr
SELECT monthnr, SUM(amount) AS SUM_MONTH_6 FROM @monthsales WHERE monthnr = 6 GROUP BY monthnr
SELECT monthnr, SUM(amount) AS SUM_MONTH_7 FROM @monthsales WHERE monthnr = 7 GROUP BY monthnr
SELECT monthnr, SUM(amount) AS SUM_MONTH_8 FROM @monthsales WHERE monthnr = 8 GROUP BY monthnr
SELECT monthnr, SUM(amount) AS SUM_MONTH_9 FROM @monthsales WHERE monthnr = 9 GROUP BY monthnr
SELECT monthnr, SUM(amount) AS SUM_MONTH_10 FROM @monthsales WHERE monthnr = 10 GROUP BY monthnr
SELECT monthnr, SUM(amount) AS SUM_MONTH_11 FROM @monthsales WHERE monthnr = 11 GROUP BY monthnr
SELECT monthnr, SUM(amount) AS SUM_MONTH_12 FROM @monthsales WHERE monthnr = 12 GROUP BY monthnr

-- END TEST CODE
END

Answer 1

You could declare a table variable (Declaring a variable of type table):

declare @MonthsSale table(monthnr int)
insert into @MonthsSale (monthnr) values (1)
insert into @MonthsSale (monthnr) values (2)
....

You can add extra columns as you like:

declare @MonthsSale table(monthnr int, totalsales tinyint)

You can update the table variable like any other table:

update m
set m.TotalSales = sum(s.SalesValue)
from @MonthsSale m
left join Sales s on month(s.SalesDt) = m.MonthNr

Answer 2

Is there a reason why you aren t using a table variable and the aggregate SUM operator, instead of a cursor? SQL excels at set-oriented operations. 99.87% of the time that you find yourself using a cursor, there s a set-oriented alternative that s more efficient:

declare @MonthsSale table
(
MonthNumber int,
MonthName varchar(9),
MonthSale decimal(18,2)
)

insert into @MonthsSale
select
    1,  January , 100.00
union select    
    2,  February , 200.00
union select    
    3,  March , 300.00
union select    
    4,  April , 400.00
union select    
    5,  May , 500.00
union select    
    6,  June , 600.00
union select    
    7,  July , 700.00
union select    
    8,  August , 800.00
union select    
    9,  September , 900.00
union select    
    10,  October , 1000.00
union select    
    11,  November , 1100.00
union select    
    12,  December , 1200.00

select * from @MonthsSale   
select SUM(MonthSale) as [TotalSales] from @MonthsSale

Answer 3

T-SQL doesn t support arrays that I m aware of.

What s your table structure? You could probably design a query that does this instead:

select
month,
sum(sales)
from sales_table
group by month
order by month

Answer 4

Great question and great idea, but in SQL you ll need to do this:

For data type datetime, something like this-

declare @BeginDate    datetime =  1/1/2016 ,
        @EndDate      datetime =  12/1/2016 
create table #months (dates datetime)
declare @var datetime = @BeginDate
   while @var < dateadd(MONTH, +1, @EndDate)
   Begin
          insert into #months Values(@var)
          set @var = Dateadd(MONTH, +1, @var)
   end

If all you really want is numbers, do this-

create table #numbas (digit int)
declare @var int = 1        --your starting digit
    while @var <= 12        --your ending digit
    begin
        insert into #numbas Values(@var)
        set @var = @var +1
    end

Answer 5

You can declare an array using the VALUES keyword. Your example can be expressed succintly in the following form:

SELECT * FROM (VALUES 
    (1,100), 
    (2,200), 
    (3,300), 
    (4,400), 
    (5,500), 
    (6,600), 
    (7,700), 
    (8,800), 
    (9,900), 
    (10,1000), 
    (11,1100), 
    (12,1200)
) MonthSale (monthnr, Amount)

Feel free to INSERT it into a table or place a WHERE or GROUP condition for your purposes.

友情链接