basic question

hi i have the following variables.
declare @.viStock int
set @.viStock = 1000
declare @.vdStartDate datetime
declare @.vdEndDate datetime
set @.vdStartDate = getdate()
set @.vdEndDate = dateadd(w, 10, getdate())
i want to insert into a temp ttable the following
amount date
100 24-10-2005
100 31-11-2005
100 31-11-2005
.. and so on for 10 ws.
where amount = stock / @.vdStartDate - @.vdEndDate
how do i do this so that the amount is equally divided?
thankshere is the schema.
--
-- drop table temp
-- create table temp
-- (
-- amt int,
-- date datetime
-- )
--
declare @.viStock int
set @.viStock = 1000
declare @.vdStartDate datetime
declare @.vdEndDate datetime
set @.vdStartDate = getdate()
set @.vdEndDate = dateadd(w, 10, getdate())
insert into temp
(amt,date)
(
select @.viStock / datediff([w], @.vdStartDate, @.vdEndDate),
dateadd([w], 1, @.vdEndDate)
--group by @.viStock/ datediff(w, @.vdStartDate, @.vdEndDate), dateadd(w,
1, @.vdEndDate))
)
select * From temp
delete from temp
i cant use a group by here says -Server: Msg 164, Level 15, State 1, Line 20
GROUP BY expressions must refer to column names that appear in the select
list.
how do i correct that?
thanks
"ichor" <ichor@.hotmail.com> wrote in message
news:eJJ2wST2FHA.2564@.TK2MSFTNGP10.phx.gbl...
> hi i have the following variables.
> declare @.viStock int
> set @.viStock = 1000
> declare @.vdStartDate datetime
> declare @.vdEndDate datetime
> set @.vdStartDate = getdate()
> set @.vdEndDate = dateadd(w, 10, getdate())
>
> i want to insert into a temp ttable the following
> amount date
> 100 24-10-2005
> 100 31-11-2005
> 100 31-11-2005
> .. and so on for 10 ws.
> where amount = stock / @.vdStartDate - @.vdEndDate
> how do i do this so that the amount is equally divided?
>
> thanks
>|||On Tue, 25 Oct 2005 17:21:15 +1000, ichor wrote:

>hi i have the following variables.
>declare @.viStock int
>set @.viStock = 1000
>declare @.vdStartDate datetime
>declare @.vdEndDate datetime
>set @.vdStartDate = getdate()
>set @.vdEndDate = dateadd(w, 10, getdate())
>
>i want to insert into a temp ttable the following
>amount date
>100 24-10-2005
>100 31-11-2005
>100 31-11-2005
>.. and so on for 10 ws.
>where amount = stock / @.vdStartDate - @.vdEndDate
>how do i do this so that the amount is equally divided?
Hi ichor,
First step is to create and fill a numbers table. This is a one-time
job, and you'll find that there are many uses for a umbers table. Check
out http://www.aspfaq.com/show.asp?id=2516 for details on how to make a
numbers table and some examples of how to use it.
Now to the actual query:
SET @.NumWs = DATEDIFF(w, @.vdStartDate, @.vdEndDate)
INSERT INTO temptable (amount, [date])
SELECT @.viStock / @.NumWs,
DATEADD(w, Number-1, @.vdStartDate)
FROM Numbers
WHERE Number BETWEEN 1 AND @.NumWs
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)