Loop - 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

General SQL Server Forums

New to SQL Server Programming

Loop

Author

Topic

tree309
Starting Member

4 Posts

Posted - 2012-02-23 : 00:09:22

I have to create a SQL statement based upon an employee table with deposit information. Each employee can have several records for their deposits. As an example, $50 can go to one account, then 10% can go to another account, 40% could go to another account, and then 50% could go to another account. For percentages it would show the remaining amount not distributed--not the percentage of the person's total amount. So, for the 10% line the remaining percentage is 90% because (40+50)/100. For the 40% line the remaining percentage(100-10) is 44.4% because 40/90. For the 50% line the remaining percentage (90-40) is 100% because 50/50. I have no idea why they want it this way. I don't know how to loop through records. Here is where I started, but I really don't know how to do this. Any guidance would be appreciated.

--in-memory employee table to hold distinct employee_id
DECLARE @PCTSUM int
DECLARE @DDACTNUM char(17)
DECLARE @DDTRANUM char(9)
DECLARE @DDTRANS char(3)
DECLARE @EMPLOYID char(15)
DECLARE @pr_ind_code char(1)
DECLARE @PCT INT
DECLARE @DLR numeric (19,5)
DECLARE @i int
DECLARE @numrows int
DECLARE @employee_id char(15)
DECLARE @DDEE TABLE (
idx smallint Primary Key IDENTITY(1,1)
, employid char(15)
)
--Build a temp table for processing
Insert @DDEE
select employid
from (
Select employid as EMPLOYID, MAX(ddactnum)as DDACTNUM, MAX(ddtranum) as DDTRANUM, MAX(ddtrans)as DDTRANS,
MAX(ddpct) as DDPCT, MAX(ddamtdlr)as DDAMTDLR, COUNT(index1) as Index1
from DD00200
group by employid) as dd
where INDEX1 > 1

-- enumerate the table
SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM @DDEE)
IF @numrows > 0
WHILE (@i <= (SELECT MAX(idx) FROM @DDEE))
BEGIN

-- get the next employee primary key
SET @employee_id = (SELECT employid FROM @DDEE WHERE idx = @i)

-- do something with this employee
if exists (select * from DD00200 where DD00200.INDEX1 = 1 and DD00200.DDPCT =0)
select @EMPLOYID=employid, @DDACTNUM=ddactnum, @DDTRANUM=ddtranum, @DDTRANS=ddtrans
,@pr_ind_code='F'--Fixed Amount
,@DLR= DDAMTDLR
from DD00200
where DD00200.EMPLOYID = @employee_id
order by index1
else
if exists (select * from DD00200 where index1 =1 and dd00200.ddpct > 0)
select @EMPLOYID=employid, @DDACTNUM=ddactnum, @DDTRANUM=ddtranum, @DDTRANS=ddtrans,
@pr_ind_code='P',--Percentage
@PCT=DDPCT,
@PCTSUM = DDPCT + @PCTSUM
from DD00200
where DD00200.EMPLOYID = @employee_id
order by index1

else
select @EMPLOYID=employid, @DDACTNUM=ddactnum, @DDTRANUM=ddtranum, @DDTRANS=ddtrans,
@pr_ind_code='P',
@DLR=0
from DD00200
where DD00200.EMPLOYID = @employee_id
order by index1

-- increment counter for next employee
SET @i = @i + 1
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 09:06:42

sorry your issue is not clear. please post data in below format and explain

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-23 : 09:11:26

++

What would be really helpful would just be the table definitions for the tables involved. Some sample data, and the required result. Then we'll be able to help.

You won't need a look to do this.

Charlie.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

tree309
Starting Member

4 Posts

Posted - 2012-02-23 : 10:10:28

The table has:

EMPLOYID INDEX1 INACTIVE DEDNMTHD DEDUCTON DDPRE DDPRECNT DDPCT DDAMTDLR DDACTNUM DDTRANUM DDTRANS DEX_ROW_ID
ACKE0001 1 0 0 -none- 0 0 100 0.00000 12345 123456780 22 1
BARB0001 1 0 0 -none- 0 0 0 150.00000 4567 123456780 32 8
BARB0001 2 0 0 -none- 0 0 50 0.00000 67890 123456780 22 9
BARB0001 3 0 0 -none- 0 0 25 0.00000 6783924 123456780 22 10
BARB0001 4 0 0 -none- 0 0 25 0.00000 SFR 123456780 22 11
BARR0001 1 0 0 -none- 1 3 100 0.00000 9876 123456780 23 4
BONI0001 1 0 0 -none- 0 0 100 0.00000 3456789 123456780 22 5

So for BARB0001 we have 4 direct deposit records. The first record (index1) is a fixed amount (150) . I would show that information. The second record is a percent (50). For that record I need to show the remaining amount would be (25 + 25)/100 or 50. For the next record, the remaining amount would be (25)/50 or 50. And the last one would be 25/25 or 100.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-23 : 10:18:26

I still don't know what you mean. Here's what you gave us in a usable form -- in future if you present data can you do it like this so that we don't waste time.....



DECLARE @sample TABLE (
	EMPLOYID CHAR(8)
	, INDEX1 INT
	, INACTIVE BIT
	, DEDNMTHD BIT
	, DEDUCTON CHAR(1)
	, DDPRE BIT
	, DDPRECNT INT
	, DDPCT FLOAT
	, DDAMTDLR FLOAT
	, DDACTNUM INT
	, DDTRANUM INT
	, DDTRANS INT
	, DEX_ROW_ID INT
	)

INSERT @sample
VALUES
('ACKE0001', 1,	0,	0, NULL, 0,	0,	100,	0.00000,	12345,	 123456780,	22, 1)
, ('BARB0001', 1,	0,	0, NULL, 0,	0,	0,		150.00000,	4567,	 123456780,	32, 8)
, ('BARB0001', 2,	0,	0, NULL, 0,	0,	50,		0.00000,	67890,	 123456780,	22, 9)
, ('BARB0001', 3,	0,	0, NULL, 0,	0,	25,		0.00000,	6783924, 123456780,	22, 10)
, ('BARB0001', 4,	0,	0, NULL, 0,	0,	25,		0.00000,	NULL,	 123456780,	22, 11)
, ('BARR0001', 1,	0,	0, NULL, 1,	3,	100,	0.00000,	9876,	 123456780,	23, 4)
, ('BONI0001', 1,	0,	0, NULL, 0,	0,	100,	0.00000,	3456789, 123456780,	22, 5)

SELECT * FROM @sample
WHERE
	[EMPLOYID] = 'BARB0001'
ORDER BY
	[INDEX1]

I don't understand the rules presented here:

quote:

So for BARB0001 we have 4 direct deposit records. The first record (index1) is a fixed amount (150) . I would show that information. The second record is a percent (50). For that record I need to show the remaining amount would be (25 + 25)/100 or 50. For the next record, the remaining amount would be (25)/50 or 50. And the last one would be 25/25 or 100.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 10:19:21

what determines whether its value or % etc? is it INDEX1 value? also will it be consistent always

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tree309
Starting Member

4 Posts

Posted - 2012-02-23 : 11:58:26

sorry, Charlie, I'm new to SQL Server Programming, as the post says.

visakh16: If there is a value in DDAMTDLR, then it's an amount, if there is a value in DDPCT, then it's a percent.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 14:46:43

so as per above data what should be output? can you post it in proper format?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tree309
Starting Member

4 Posts

Posted - 2012-02-23 : 22:14:25

Here are the results I would be expecting:

EMPLOYID DDACTNUM DDTRANUM pr_ind_code deposit_amount INDEX1
ACKE0001 12345 123456780 P 100 1
BARB0001 4567 123456780 F 150 1
BARB0001 67890 123456780 P 50 2
BARB0001 6783924 123456780 P 50 3
BARB0001 SFR 123456780 P 100 4
BARR0001 9876 123456780 P 100 1
BONI0001 3456789 123456780 P 100 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources