Tricky Transact SQL

Question

Tricky Transact SQL

am-244616

SSCarpal Tunnel

Points: 4936
More actions
June 27, 2008 at 11:31 am

#193920

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 NumPaid
_____________________________________
1c10500
1c111000
EmpPercentTable
RecordEmpIDPriorityAlloc PercentBeg Balance
________________________________________________________________________
11001150250
11002150350
1100321001500
Output
EmpAlloc
RecordEmpIDReceipt NumAlloc Amt
_________________________________________________
11001c10250
11002c10250
11003c100
11001c110
11002c11100
11003c11900
Is it possible to generate the output using TSQL.
Any suggestions and input would help.
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Yes, it can be done.

If you post some sample data in a way in which I don't have to key it in myself, I would be happy to help write the query:

http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 2

Michael has the right idea for you helping us to help you.

In the meantime, lookup "Cross Join" in Books Online...;)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

am-244616 SSCarpal Tunnel Points: 4936 More actions · Answer 3

The attachment has DDL and DML statements for the example mentioned.

Thanks

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 4

SELECT ep.Record,ep.EmpID,r.ReceiptNum,r.Paid,ep.Beg_Balance

FROM EmpPercentTable ep

CROSS JOIN ReceiptsTable r

ORDER BY r.ReceiptNum,ep.EmpID

I'm a bit tired and today's coffee has finally worn off... what formula are you using for the Alloc Amt in your output?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

am-244616 SSCarpal Tunnel Points: 4936 More actions · Answer 5

Thanks for your response

Alloc Amt is calculated based on Priority and Alloc Percent from EMPPERCENTTABLE. For EMPID - 1001 and 1002 where priority is 1 so whatever gets paid will go to these 2 employees based on Alloc Percent from the same table. So in the same example when 500 is paid since Alloc Percent is 50% for EMPID 1001 and 1002 within the same priority they get 250 dollars each and since 1003 EMPID priority is less than the 1001 and 1002 it will not get any share until 1001 and 1002 get paid off first due to higher priority.

Note: Greater the priority value less is the priority.

Thanks

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 6

am (6/28/2008)
Thanks for your response
Alloc Amt is calculated based on Priority and Alloc Percent from EMPPERCENTTABLE. For EMPID - 1001 and 1002 where priority is 1 so whatever gets paid will go to these 2 employees based on Alloc Percent from the same table. So in the same example when 500 is paid since Alloc Percent is 50% for EMPID 1001 and 1002 within the same priority they get 250 dollars each and since 1003 EMPID priority is less than the 1001 and 1002 it will not get any share until 1001 and 1002 get paid off first due to higher priority.
Note: Greater the priority value less is the priority.
Thanks

I understand that bit of logic for the Q10 Receipt... I don't understand it for the Q11 Receipt. Please explain your logic for that output. You list the expected output as follows:

[font="Courier New"]RECORD EMPID RECEIPTNUM ALLOC_AMT

----------- ----------- ---------- --------------

1 1001 C10 250.00

1 1002 C10 250.00

1 1003 C10 .00

1 1001 C11 .00

1 1002 C11 100.00

1 1003 C11 900.00[/font]

I'm obviously missing something in your rules because I would think the output should be...

[font="Courier New"]RECORD EMPID RECEIPTNUM ALLOC_AMT

----------- ----------- ---------- --------------

1 1001 C10 250.00

1 1002 C10 250.00

1 1003 C10 .00

1 1001 C11 500.00

1 1002 C11 500.00

1 1003 C11 .00[/font]

In fact, according to the rules you stated, it looks like employee 1003 will never get paid because employees 1001 and 1002 will always do a 50/50 split on everything...

... there's a rule missing somewhere 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Carl Federl One Orange Chip Points: 25384 More actions · Answer 7

This appears to be a variation on First-In First-Out allocation such as for allocating Payments to Invoices or for "best fit"

Someone posted a SQL solution this month but I cannot find it.

Anyone recall the poster ?

SQL = Scarcely Qualifies as a Language

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 8

Carl Federl (6/28/2008)
This appears to be a variation on First-In First-Out allocation such as for allocating Payments to Invoices or for "best fit"
Someone posted a SQL solution this month but I cannot find it.
Anyone recall the poster ?

Uh huh... which would give the results I said instead of what was posted. I need the OP to provide the missing link on this one... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

am-244616 SSCarpal Tunnel Points: 4936 More actions · Answer 9

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

Thanks

sgmunson SSC Guru Points: 110640 More actions · Answer 10

This solution is imperfect in that it doesn't identify a zero allocation, but it was fairly quickly developed, and perhaps someone with more skills can make it better:

DECLARE @RECEIPTSTABLE TABLE (

RECORD INT NULL,

RECEIPTNUM VARCHAR(10) NULL,

PAID NUMERIC(12, 2) NULL

)

DECLARE @EMPPERCENTTABLE TABLE (

RECORD INT NULL,

EMPID INT NULL,

PRIORITY INT NULL,

ALLOC_PERCENT NUMERIC (12, 2) NULL,

BEG_BALANCE NUMERIC (12, 2) NULL

)

DECLARE @EMPALLOCOUTPUT TABLE (

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)

*/

DECLARE@RECORD INT, @RECEIPTNUM VARCHAR(10), @PAID NUMERIC(12,2), @REMAINING_AMT NUMERIC(12,2)

DECLARE @AMOUNT_TO_PAY NUMERIC(12,2), @EMPID INT, @PCT INT, @REC INT, @Balance NUMERIC(12,2)

DECLARE @BRK_CTR INT, @test-2 NUMERIC(12,2)

DECLARE ALLOCATION_PROCESS CURSOR FORWARD_ONLY

FOR (SELECT RECORD, RECEIPTNUM, PAID FROM @RECEIPTSTABLE)

OPEN ALLOCATION_PROCESS

FETCH NEXT FROM ALLOCATION_PROCESS

INTO @RECORD, @RECEIPTNUM, @PAID

SET @BRK_CTR = 0

WHILE @@FETCH_STATUS = 0

BEGIN

SET @REMAINING_AMT = @PAID

THE_START:

SELECT @REC = (

SELECT TOP 1 RECORD

FROM @EMPPERCENTTABLE

WHERE BEG_BALANCE <> 0

ORDER BY PRIORITY, EMPID)

SELECT @EMPID = (

SELECT TOP 1 EMPID

FROM @EMPPERCENTTABLE

WHERE BEG_BALANCE <> 0

ORDER BY PRIORITY, EMPID)

SELECT @PCT = (

SELECT TOP 1 ALLOC_PERCENT

FROM @EMPPERCENTTABLE

WHERE BEG_BALANCE <> 0

ORDER BY PRIORITY, EMPID)

SELECT @Balance = (

SELECT TOP 1 BEG_BALANCE

FROM @EMPPERCENTTABLE

WHERE BEG_BALANCE <> 0

ORDER BY PRIORITY, EMPID)

SET @test-2 = (@PAID * (@PCT / 100.))

SELECT @AMOUNT_TO_PAY = (CASE WHEN @test-2 > @Balance THEN @Balance

WHEN @test-2 > @REMAINING_AMT THEN @REMAINING_AMT

ELSE @test-2 END)

UPDATE @EMPPERCENTTABLE

SET BEG_BALANCE = BEG_BALANCE - @AMOUNT_TO_PAY

FROM @EMPPERCENTTABLE

WHERE EMPID = @EMPID

INSERT INTO @EMPALLOCOUTPUT VALUES (@REC, @EMPID, @RECEIPTNUM, @AMOUNT_TO_PAY)

SET @REMAINING_AMT = @REMAINING_AMT - @AMOUNT_TO_PAY

SET @BRK_CTR = @BRK_CTR + 1

IF @REMAINING_AMT > 0 GOTO THE_START

FETCH NEXT FROM ALLOCATION_PROCESS

INTO @RECORD, @RECEIPTNUM, @PAID

END

SELECT *

FROM @EMPALLOCOUTPUT

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)