Tricky Transact SQL - 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)

Tricky Transact SQL

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2008-06-27 : 13:31:07

Guys,

I have ReceiptsTable and EmpPercentTable from these two tables I am trying to generate the output into EmpAlloc

Basically what ever the receipt is paid for a particular record the employee share should be divided based on Priority and Allocation percent from EmpPercentTable.

For example

ReceiptsTable

Record Receipt Num Paid
_____________________________________
1 c10 500
1 c11 1000

EmpPercentTable

Record EmpID Priority Alloc Percent Beg Balance
________________________________________________________________________
1 1001 1 50 250
1 1002 1 50 350
1 1003 2 100 1500

Output

EmpAlloc

Record EmpID Receipt Num Alloc Amt
_________________________________________________
1 1001 c10 250
1 1002 c10 250
1 1003 c10 0
1 1001 c11 0
1 1002 c11 100
1 1003 c11 900

Is it possible to generate the output using TSQL.
Any suggestions and input would help.

Thanks

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-06-27 : 15:17:16

Huh?? Please explain better.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-28 : 02:45:31

Not clearly about sample data & o/p given. Can you elaborate how you think you'll get the Alloc Amt for each receipt?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-28 : 06:05:12

I have seen Peter's solution to a similar problem before sometime last year. Think it is also available in the script library. Check there for script by Peso.

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-28 : 06:48:26

quote:
Originally posted by khtan

I have seen Peter's solution to a similar problem before sometime last year. Think it is also available in the script library. Check there for script by Peso.

KH
[spoiler]Time is always against us[/spoiler]

Were you able to make out what OP asked for? I really couldnt make much from his explanation

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-28 : 10:24:27

quote:
Originally posted by visakh16

quote:
Originally posted by khtan

I have seen Peter's solution to a similar problem before sometime last year. Think it is also available in the script library. Check there for script by Peso.

KH
[spoiler]Time is always against us[/spoiler]

Were you able to make out what OP asked for? I really couldnt make much from his explanation

Yes. He is looking for some solution for automatic receipt matching. Can be easily done with loop or cursor will not be easy with set based.

Receipt c10 of 500 paid for
EmpID 1001 : 250
EmpID 1002 : 250
total : 500 = Receipt Amt
so EmpID 1003 : 0

After receipt c10, EmpID 1001 balance is 0 (fully paid), EmpID 1002 balance of 100 (350 - 250), EmpID 1003 still at balance of 1500

Receipt c11 of 1000 will paid for
EmpID 1001 : 0
EmpID 1002 : 100
EmpID 1003 : 900
total : 1000

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-28 : 13:52:20

quote:
Originally posted by khtan

quote:
Originally posted by visakh16

quote:
Originally posted by khtan

I have seen Peter's solution to a similar problem before sometime last year. Think it is also available in the script library. Check there for script by Peso.

KH
[spoiler]Time is always against us[/spoiler]

Were you able to make out what OP asked for? I really couldnt make much from his explanation

Yes. He is looking for some solution for automatic receipt matching. Can be easily done with loop or cursor will not be easy with set based.

Receipt c10 of 500 paid for
EmpID 1001 : 250
EmpID 1002 : 250
total : 500 = Receipt Amt
so EmpID 1003 : 0

After receipt c10, EmpID 1001 balance is 0 (fully paid), EmpID 1002 balance of 100 (350 - 250), EmpID 1003 still at balance of 1500

Receipt c11 of 1000 will paid for
EmpID 1001 : 0
EmpID 1002 : 100
EmpID 1003 : 900
total : 1000

KH
[spoiler]Time is always against us[/spoiler]

Ok...Now it makes sense

thanks for explanation Khtan.

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2008-07-02 : 15:24:18

My bad that I was not clear enough, the receipts pay off the EMPID until their balance is 0. In this example

1001 beg_balance is 250
1002 beg_balance is 350
1003 beg_balance is 1500

when first receipt - C10 (500) is paid taking priority and alloc percent into consideration 1001 and 1002 get the share first. After C10 receipt is applied beg_balance is now updated to

1001 allocated is 250
1002 allocated is 250
1003 allocated is 0

when first receipt - C11 (1000) is paid taking priority and alloc percent into consideration since 1001 is paid off the receipt C11 doesnt get applied and 1002 gets the first share of C11 due to highest priority and will be paid of first (100). After that remaining amount (900) from C11 receipt is applied to 1003 since 1001 and 1002 is paid off.

1001 allocated is 0
1002 allocated is 100
1003 allocated is 900

Below is the script for the sample data

DROP TABLE RECEIPTSTABLE
DROP TABLE EMPPERCENTTABLE
DROP TABLE EMPALLOCOUTPUT

CREATE TABLE RECEIPTSTABLE (
RECORD INT NULL,
RECEIPTNUM VARCHAR(10) NULL,
PAID NUMERIC(12, 2) NULL
)

CREATE TABLE EMPPERCENTTABLE (
RECORD INT NULL,
EMPID INT NULL,
PRIORITY INT NULL,
ALLOC_PERCENT NUMERIC (12, 2) NULL,
BEG_BALANCE NUMERIC (12, 2) NULL
)

CREATE TABLE EMPALLOCOUTPUT (
RECORD INT NULL,
EMPID INT NULL,
RECEIPTNUM VARCHAR(10) NULL,
ALLOC_AMT NUMERIC (12, 2) NULL
)

INSERT INTO RECEIPTSTABLE VALUES (1, 'C10', 500)
INSERT INTO RECEIPTSTABLE VALUES (1, 'C11', 1000)

INSERT INTO EMPPERCENTTABLE VALUES (1, 1001, 1, 50, 250)
INSERT INTO EMPPERCENTTABLE VALUES (1, 1002, 1, 50, 350)
INSERT INTO EMPPERCENTTABLE VALUES (1, 1003, 2, 100, 1500)

INSERT INTO EMPALLOCOUTPUT VALUES (1, 1001, 'C10', 250)
INSERT INTO EMPALLOCOUTPUT VALUES (1, 1002, 'C10', 250)
INSERT INTO EMPALLOCOUTPUT VALUES (1, 1003, 'C10', 0)
INSERT INTO EMPALLOCOUTPUT VALUES (1, 1001, 'C11', 0)
INSERT INTO EMPALLOCOUTPUT VALUES (1, 1002, 'C11', 100)
INSERT INTO EMPALLOCOUTPUT VALUES (1, 1003, 'C11', 900)

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources