Query help...(Unsolved) - 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

SQL Server Development (2000)

Query help...(Unsolved)

Author

Topic

nb
Starting Member

9 Posts

Posted - 2003-01-28 : 02:23:35

Herez some sample data

Salesman Date Amount
A 12/12/2002 5000
B 1/01/2003 10000
C 1/11/2001 8000

supposing if i chose a value of 10000. my result should be

C 1/11/2001 8000
B 12/12/2002 2000

the salesman with the earliest date is preferred first.

Herez the script to work.

create table testsp(spname varchar(10),
spdate datetime,
amount decimal(9,2))

insert into testsp values('A','12/12/2002',5000)
insert into testsp values('B','1/01/2003',10000)
insert into testsp values('C','1/11/2001',8000)

Can anyone help on this?

Edited by - nb on 01/28/2003 08:49:31

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2003-01-28 : 03:28:23

I bet there's a lot of "fellaz" who could help, but they don't have time to do your HOMEWORK!!!

Greetingzzz!

nb
Starting Member

9 Posts

Posted - 2003-01-28 : 08:26:00

huh? what made you think its a homework .

gone r my school days to ask homework questions. am working in a real time environment. and i thought this site would be of some help to my problem. i have posted my actual question http://sqlteam.com/Forums/topic.asp?TOPIC_ID=23316 here. but i thought bcoz of the length of the Q pple might be shying away from it. so, i broke it into a sample problem and provided DDL and DML operations.

i still hope folks here would be of some help to me. without actually jumping into unnecessary conclutions!!.

Cheers mate!

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-28 : 09:08:59

select spname, spdate, amount
from testsp
where amount < 10000
order by spdate asc

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-28 : 09:28:44

You need a running total; it sounds like to you need to match up those amounts up to a ceiling. In your example it was 10,000. I made some minor alterations to use table variables, but take a look:

Declare @Ceiling decimal(9,2);

declare @testsp table (spname varchar(10),
spdate datetime,
amount decimal(9,2))

set nocount on
insert into @testsp values('A','12/12/2002',5000)
insert into @testsp values('B','1/01/2003',10000)
insert into @testsp values('C','1/11/2001',8000)
set nocount off

set @Ceiling = 10000;

SELECT SpName, SpDate, Amount, RunningTotal,
CASE WHEN RunningTotal < @Ceiling THEN Amount
WHEN @Ceiling - (RunningTotal - Amount) > 0 THEN @Ceiling - (RunningTotal - Amount)
ELSE 0 END as Result
FROM
(
SELECT Spname, spDate, Amount,
(SELECT Sum(Amount) FROM @testsp T2 WHERE T2.spDate <= T.spDate) as RunningTotal
FROM
@testsp T
) A
ORDER BY spDate ASC

- Jeff

Edited by - jsmith8858 on 01/28/2003 09:59:53

nb
Starting Member

9 Posts

Posted - 2003-01-28 : 09:29:45

Nopes, that wouldnt work valter . coz the query would give me
results

C 1/11/2001 8000
B 12/12/2002 10000

what i want is

C 1/11/2001 8000
B 12/12/2002 2000 (though B's amount contain's 1000. but i have already got 8000 from C, so remaining 2000 i take it from B).

hope that makes it more clear.

Thanx though.

nb
Starting Member

9 Posts

Posted - 2003-01-28 : 10:09:34

Thanx Jeff,it works like a charm.

btw, i was reading this article http://sqlteam.com/item.asp?ItemID=3856 and i think this will help me too.

Jeff, would you mind reading my original Q. ultmiately am trying to solve this one http://sqlteam.com/Forums/topic.asp?TOPIC_ID=23316 .

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-28 : 16:40:21

I thought you had a typ0 how's one supposed know you want a running total and how you want to combine the totals??

How would you know to subtract b and c's totals and not include a.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-28 : 16:45:32

it took me a while to figure out what he needed to ... plus his example was wrong, note that the date of A is 12/12/2002 and not B.

When he wrote:

quote:

the salesman with the earliest date is preferred first.

that was the clue for the order by which to apply the 10,000 to the others.

- Jeff

nb
Starting Member

9 Posts

Posted - 2003-01-29 : 00:03:38

my bad, Jeff. still am very much thankful to u and valter to have taken the time to answer it.

quote:

it took me a while to figure out what he needed to ... plus his example was wrong, note that the date of A is 12/12/2002 and not B.

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/2003 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/2003 2000
B 25/12/2002 8000
B 1/01/2003 7000

Edited by - nb on 01/29/2003 00:05:10

Edited by - nb on 01/30/2003 00:20:10

nb
Starting Member

9 Posts

Posted - 2003-01-29 : 08:01:20

Herez the complete DDL and DML operations to work with.

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/Nov/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)

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-30 : 00:42:26

select a.salesman, a.date, case when a.tot + a.amount <= l.limits then a.amount else l.limits - a.tot end, l.limits
from
(select salesman, date, amount, tot = (select coalesce(sum(amount), 0) from #Sales s2 where s1.salesman = s2.salesman and s2.date < s1.date)
from #Sales s1
) as a, #Limits l
where a.Salesman = l.Salesman
and a.tot <= l.limits
order by a.Salesman, a.date

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nb
Starting Member

9 Posts

Posted - 2003-01-30 : 00:49:07

You Rock nr

it works and works well.

Can you explain me how this query works.

Thanx

Edited by - nb on 01/30/2003 00:51:52

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-30 : 01:02:18

tot = (select coalesce(sum(amount), 0) from #Sales s2 where s1.salesman = s2.salesman and s2.date < s1.date)
gets the sum of all the previous sales for the salesman.

and a.tot <= l.limits
only gets the rows up to the limit

a.tot + a.amount is the total amount including the current sale so

case
when a.tot + a.amount <= l.limits ------ total is less than limit so
then a.amount --- include all amount
else ----------- otherwies
l.limits - a.tot end -------- just include amount to make up limit.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources