INSERT Loop - 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)

INSERT Loop

Author

Topic

ljw121
Starting Member

3 Posts

Posted - 2009-08-24 : 07:06:37

Hi,

I'm trying to run a query on a table and update a second table based on the results.

For instance one table (salesinfo) contains all the information for Sales:

DateOfSale | SoldBy | SaleAmount
2009-08-23 | Greg | 23
2009-08-23 | Pete | 21
2009-08-23 | Pete | 25
2009-08-23 | Pete | 20
2009-08-24 | Greg | 24

And I want a second table that will summarise all this information once a week by executing a stored procedure.

An example of this week for the salessummary table would be:

WeekStartDate | Salesman | NumberOfSales
2009-08-23 | Pete | 3
2009-08-23 | Greg | 2

The code I've got works fine for one person but I want to loop the code and repeat for the other salesman for the second line on data.

This is what I have:

DECLARE @Startdate datetime
DECLARE @Enddate datetime
DECLARE @Salesman varchar(50)

SET @EndDate =
( (SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD])

)

SET @StartDate =
( DATEADD(d,-7,@enddate)

)

SET @Tracker = 'Greg'

SET @NumberOfSales =

( Select Count(*)
FROM salesinfo
Where (DateOfSale > @StartDate) AND
(DateOfSale < @EndDate) AND
(SoldBy = @Salesman)
)

INSERT INTO SalesSummary(
WeekStartDate,
Salesman,
NumberofSales
)
VALUES (
@StartDate,
@Salesman,
@NumberOfSales
)

Ideally I want everything from Set @NumberOfSales to the end to loop and repeat for a specified list different Salesman.

Is this possible?

Many Thanks,

Lewis

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-24 : 07:15:42

You dont need a loop, all you need is

select DateOfSale ,SoldBy ,count(SaleAmount) as NumberOfSales from salesinfo
where DateOfSale>DATEADD(day,datediff(day,0,getdate()),-7) and DateOfSale<DATEADD(day,datediff(day,0,getdate()),1)
group by DateOfSale ,SoldBy

Madhivanan

Failing to plan is Planning to fail

ljw121
Starting Member

3 Posts

Posted - 2009-08-24 : 08:21:14

Hi,

Thanks for your response. How would I work that into an INSERT to update the table salessummary with that information?

Thanks for your help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-24 : 08:34:22

Why do you need a seperate table? You can just use SELECT statement whenever needed

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources