| Author |
 Topic  |
|
|
ljw121
Starting Member
3 Posts |
 Posted - 08/24/2009 : 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
India
22460 Posts |
Posted - 08/24/2009 : 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 |
Edited by - madhivanan on 08/24/2009 07:18:29 |
 |
|
|
ljw121
Starting Member
3 Posts |
Posted - 08/24/2009 : 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
India
22460 Posts |
Posted - 08/24/2009 : 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 |
|
|
| |
Topic |
|
|
|
INSERT Loop
