January 28, 2003 at 11:45 pm
Salesman Date Amount
A 12/12/2002 5000
B 1/01/2003 10000
C 1/11/2001 8000
A 1/1/2003 3000
A 5/1/2002 11000
B 25/12/2002 8000
i have a table with the salesman and its value limits.
Limits table
Salesman Limits
A 10000
B 15000
the output am looking for is.
Salesman Date Amount
A 12/12/2002 5000
A 1/1/2003 3000
A 5/1/2002 2000
B 25/12/2002 8000
B 1/01/2003 10000
January 29, 2003 at 2:39 am
This should do the trick.
SELECT s.SalesMan, s.Date, l.Limits - s.Total
FROM
(SELECT s1.SalesMan, s1.Date,
(SELECT sum(Amount) FROM Sales s2
WHERE s2.SalesMan = s1.Salesman
AND s2.Date <= s1.Date) Total
FROM Sales s1) s
INNER JOIN
Limits l
ON l.SalesMan = s.SalesMan
January 29, 2003 at 6:11 am
Thanx Npeters, but am note getting my expected results. its giving me some inconsitent results.
to work with . herez ddl and dml operations.
create table Sales(salesman varchar(10),
date datetime,
amount decimal(9,2))
insert into Sales values('A','12/12/2002',5000)
insert into SAles values('B','1/01/2003',10000)
insert into Sales values('C','1/11/2001',8000)
insert into Sales values('A','1/jan/2003',3000)
insert into Sales values('A','5/Jan/2002',11000)
insert into Sales values('B','25/Dec/2002',8000)
create table Limits(salesman varchar(10),
Limits decimal(9,2))
insert into Limits values('A',10000)
insert into Limits values('B',15000)
January 29, 2003 at 8:14 am
With this data:
Salesman Date Amount
A 12/12/2002 5000
B 1/01/2003 10000
C 1/11/2001 8000
A 1/1/2003 3000
A 5/1/2002 11000
B 25/12/2002 8000
Limits table
Salesman Limits
A 10000
B 15000
How are you getting these results, specifically the result with the ** next to it
Salesman Date Amount
A 12/12/2002 5000
A 1/1/2003 3000
A 5/1/2002 2000 **
B 25/12/2002 8000
B 1/01/2003 10000
And what does the Limits table have to do with the data in the Salesman table? Is it that any amount over the limit should only return the amount over the limit (ie. A for 5/1/2002 is 11000, but A's limit is 10000, so should it return 1000)?
-SQLBill
January 29, 2003 at 9:57 pm
oops!,my bad, am sorry.
Salesman Date Amount
A 5/1/2002 10000 **(Running total Equals 10000 which is limits table for A)
B 25/12/2002 8000
B 1/01/2003 7000**(Running Total equals 15000 which is in Limits table for B)
Actually using Limits table. from the salesman table using FIFO Records have to be displayed at one point when a records amout becomes excess to the limits amount(i mean running total amount) then the remaining amount should be displayed.
hope this makes it more clear.
Edited by - nb on 01/29/2003 10:31:32 PM
January 30, 2003 at 11:14 am
Will this do the trick
select s.salesman,s.date,
(case when (s.amount + isnull((select sum(amount) from Sales where salesman = s.salesman and date < s.date),0)) > l.Limits
then (l.Limits - isnull((select sum(amount) from Sales where salesman = s.salesman and date < s.date),0))
else s.amount
end) as 'amount'
from Sales s
inner join Limits l on l.salesman = s.salesman
order by s.salesman,s.date
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply