T Sql looping.... program - 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 2005 Forums

Transact-SQL (2005)

T Sql looping.... program

Author

Topic

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2010-02-08 : 15:22:29

Hi All,
I am trying to solve this problem and could not so far. i need suggestions please...

The table looks as below. For each ActNo there could be more than 1 line with same or differnt code and Grp. The program sould go like this for each ActNo pick the highest Grp value and to this add next highest Grp*0.5 and if any more records are there then to this combined value add next Grp*0.25 till allthe records for that ActNo are done.finally calculating the total Grp value.

again very briefly for the max Grp it is the 100% second highest Grp 50% and rest all 25%. In this below example for ActNo FN0004287283 the combined end result should be 3300+1100+306.25+306.25 =5012.5

Please any suggestions is greatly appreciated.

Thanks,

ActNo Rev Code Units Loc Fac Grp
FN0004287283 750 45385 1 NEN NW 3300
FN0004287283 750 45380 1 NEN NW 2200
FN0004287283 750 45380 1 NEN NW 1225
FN0004287283 750 45380 1 NEN NW 1225
FN0004295063 750 45385 1 NEN NW 5500
FN0004295063 750 45380 1 NEN NW 4400
FN0004300081 750 45380 1 NEN NW 2200
FN0004315292 750 45380 1 NEN NW 2200
FN0004315649 750 45385 1 NEN NW 3300
FN0004315649 750 45380 1 NEN NW 2200
FN0004315653 750 45385 1 NEN NW 1225

srouse
Starting Member

7 Posts

Posted - 2010-02-08 : 18:52:01

I have used logic similar to this in the past. It's not elegant, but it is functional. I use three temp tables to process the data and #fn1 holds the sample data. I have no idea how this would scale.

#result holds the final answer
#report holds the sum for each account and is truncated between accounts
#sorter is used to sort the values and is truncated between accounts.

NOTE: I assume all accounts will have values > 0

/*

drop table #fn1
drop table #sorter
drop table #result
drop table #report
go
*/

-- sample data
create table #fn1(
ActNo varchar(50),
Rev int,
Code int,
Units int,
Loc varchar(10),
Fac varchar(10),
Grp int
)

-- final results
create table #result(
tid int identity,
actno varchar(50),
grp_sum decimal(6,2) default 0
)

-- holds actno sums, truncated between accounts
create table #report(
actno varchar(50),
grp_sum decimal(6,2)
)

-- used to sort, truncated between accounts
create table #sorter(
tid int identity,
actno varchar(50),
grp int,
grp_value decimal(6,2) null
)

-- test data
insert into #fn1(ActNo, Rev, Code, Units, Loc, Fac, Grp) values('FN0004287283', 750, 45385, 1 ,'NEN', 'NW', 3300)
insert into #fn1(ActNo, Rev, Code, Units, Loc, Fac, Grp) values('FN0004287283', 750, 45380, 1 ,'NEN', 'NW', 2200)
insert into #fn1(ActNo, Rev, Code, Units, Loc, Fac, Grp) values('FN0004287283', 750, 45380, 1 ,'NEN', 'NW', 1225)
insert into #fn1(ActNo, Rev, Code, Units, Loc, Fac, Grp) values('FN0004287283' ,750, 45380, 1 ,'NEN', 'NW', 1225)
insert into #fn1(ActNo, Rev, Code, Units, Loc, Fac, Grp) values('FN0004295063', 750, 45385, 1 ,'NEN', 'NW', 5500)
insert into #fn1(ActNo, Rev, Code, Units, Loc, Fac, Grp) values('FN0004295063', 750, 45380, 1 ,'NEN', 'NW', 4400)
insert into #fn1(ActNo, Rev, Code, Units, Loc, Fac, Grp) values('FN0004300081', 750, 45380, 1 ,'NEN', 'NW', 2200)
insert into #fn1(ActNo, Rev, Code, Units, Loc, Fac, Grp) values('FN0004315292', 750, 45380, 1 ,'NEN', 'NW', 2200)
insert into #fn1(ActNo, Rev, Code, Units, Loc, Fac, Grp) values('FN0004315649', 750, 45385, 1 ,'NEN', 'NW', 3300)
insert into #fn1(ActNo, Rev, Code, Units, Loc, Fac, Grp) values('FN0004315649', 750, 45380, 1 ,'NEN', 'NW', 2200)
insert into #fn1(ActNo, Rev, Code, Units, Loc, Fac, Grp) values('FN0004315653', 750, 45385, 1 ,'NEN', 'NW', 1225)

-- populate result table
insert into #result(actno)
select distinct actno
from #fn1
order by actno

-- loop until no accounts have a sum of 0
while (select count(*) from #result where grp_sum = 0) > 0
begin

insert into #sorter(actno, grp, grp_value)
select actno, grp, grp
from #fn1
where #fn1.actno = (select min(actno) from #result where grp_sum = 0)
order by 2 desc

update #sorter
set grp_value = grp_value * 0.5
where tid = 2

update #sorter
set grp_value = grp_value * 0.25
where tid > 2

--populate #report with reported values for this account
insert into #report
select actno, sum(grp_value) from #sorter group by actno

update #result
set grp_sum = #report.grp_sum
from #report
where #result.actno =#report.actno

-- truncate worker tables and loop
truncate table #sorter
truncate table #report

end

select * from #result

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-02-08 : 19:29:21

sreenu9f,

What are you using as the primary key on this table?

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources