while-is it doable? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

while-is it doable?

Author

Topic

jmj
Starting Member

7 Posts

Posted - 2002-07-18 : 14:41:45

I have a table that I need to insert a row into a table for each new person and each quarter. For example if it's Quarter3 I need to add to sales $0 for that person for quarter 1 and $0 for quarter 2. Can I do this in a while loop? I'm not sure how to make it do an insert for each quarter until it gets to quarter 3.
Here is a mutilated view of what I would like to happen- the current quarter is decided dynamically -- the table gets inserts and the quarter set to the value of x. Is there any way to make this idea functional?
USE DummyDB
GO
X=0
X=x+1
WHILE X < (((ASTU_AllSchools.ENTERDATE)>= tbl_QuarterDate.BegQuarter-21 And (ASTU_AllSchools.ENTERDATE)<=
tbl_QuarterDate.EndQuarter) AND ((GetDate()) Between tbl_QuarterDate.BegQuarter And
tbl_QuarterDate.EndQuarter)) ROM ASTU_AllSchools, tbl_QuarterDate
INSERT INTO SALES
SalePersonID, SALES, Quarter
@SalesPersonID, 0, @X

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-07-18 : 15:10:00

1. Are the ASTU_Allschools and the tbl_Quarterdate tables expected to have only one row? You cannot loop X against multiple values unless you use a double loop and store the table values in a cursor and movenext.
2. Are you trying to loop X against a range? That won't go either. You must initialize X to the lower range and run it to the upper range.
3. Your syntax is incorrect and therefore difficult (impossible?) to understand.Is this:

quote:
(((ASTU_AllSchools.ENTERDATE)>= tbl_QuarterDate.BegQuarter-21 And (ASTU_AllSchools.ENTERDATE)<=
tbl_QuarterDate.EndQuarter) AND ((GetDate()) Between tbl_QuarterDate.BegQuarter And
tbl_QuarterDate.EndQuarter)) ROM ASTU_AllSchools, tbl_QuarterDate

supposed to be a query? It has no SELECT clause, seems to have only a WHERE clause without the word WHERE. And if it is a query, what is X being compared against and why?
Otherwise, basically, your question is quite simple.

Sarah Berger MCSD

jmj
Starting Member

7 Posts

Posted - 2002-07-18 : 15:35:20

1. ASTU_AllSchools has enties for each person and tbl_QuarterDate has dates for each quarter so it also has many rows.
I need to use the quarter dates to get the current quarter and then use that date range to get the new people(that's the confusing line from my while clause- I pulled it out of another query- I thought it would be easier to demonstrate what I need).
A. So basically what I need to do is pull all the new people enrolled in the current quarter.
B. I need an insert query to run on a 3rd table that will insert a row of information for all quarters < current quarter.
(So that needs to loop and "count"- 1-4) so with my example if it's 3rd quarter I need 2 inserts to happen
SalesPersonID Sales Quarter
12 $0 1
12 $0 2
Does that make sense?

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-07-18 : 20:46:11

Sounds like you need a double loop with a cursor.
DECLARE cs CURSOR FORWARD ONLY FOR select statement FOR READ ONLY 'Fill in your select of two columns
DECLARE @Person Int, @Quarter Tinyint,@X tinyint 'declare variables to hold the values
OPEN cs 'open the cursor
FETCH NEXT FROM cs INTO @Person,@Quarter 'fetch first row
SET @X = 1
WHILE @@FetchStatus = 0 'check for no errors
BEGIN
WHILE @X < @Quarter
BEGIN
INSERT INTO SALES (SalePersonID, SALES, Quarter) VALUES(@Person, 0, @X)
SET @X = @X + 1
END
FETCH NEXT FROM cs INTO @Person, @Quarter
END

CLOSE cs
DEALLOCATE cs

I hope this does it, and that it has no syntax errors.

Sarah Berger MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources