SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 INSERT Loop
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ljw121
Starting Member

3 Posts

Posted - 08/24/2009 :  07:06:37  Show Profile  Reply with Quote
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

India
22772 Posts

Posted - 08/24/2009 :  07:15:42  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Edited by - madhivanan on 08/24/2009 07:18:29
Go to Top of Page

ljw121
Starting Member

3 Posts

Posted - 08/24/2009 :  08:21:14  Show Profile  Reply with Quote
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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 08/24/2009 :  08:34:22  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Why do you need a seperate table? You can just use SELECT statement whenever needed

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000