October 10, 2016 at 3:40 pm
I have two tables one is temporary and another is main dbo employee
In the main table I have added a column called fullamount. Basically this fullamount column is coming from different table dbo.Transfer( I want to split this full amount for all the participants for that transfer)
Expecting - I want to see the data in the full amount column under dbo.employee based on taking from dbo.Transfer
I am planning to implement the logic like this. Correct me if I am wrong plz.
select fullamount from dbo.Transfer t1 nerjoin dbo.Employee E2 where t1.fullamount = E2.fullamount and I have to keep some conditions here (employee number is not null and participant belong to USA.)
By doing this can i get this data split into dbo.employee table?
October 10, 2016 at 3:51 pm
Please provide sample data and expected results as outlined in the first link in my signature. Also, do you want to divide it evenly, or is there some weight factor that needs to be taken into account?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 10, 2016 at 3:52 pm
mcfarlandparkway (10/10/2016)
I have two tables one is temporary and another is main dbo employeeIn the main table I have added a column called fullamount. Basically this fullamount column is coming from different table dbo.Transfer( I want to split this full amount for all the participants for that transfer)
Expecting - I want to see the data in the full amount column under dbo.employee based on taking from dbo.Transfer
I am planning to implement the logic like this. Correct me if I am wrong plz.
select fullamount from dbo.Transfer t1 nerjoin dbo.Employee E2 where t1.fullamount = E2.fullamount and I have to keep some conditions here (employee number is not null and participant belong to USA.)
By doing this can i get this data split into dbo.employee table?
I don't see anything in your pseudo-code that does the required count nor any form of division to achieve the required split.
I'll also suggest that if you want a better answer than something like my rhetorical answer, please see the first link under "Helpful Links" in my signature line below.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 10, 2016 at 4:07 pm
mcfarlandparkway (10/10/2016)
I have two tables one is temporary and another is main dbo employeeIn the main table I have added a column called fullamount. Basically this fullamount column is coming from different table dbo.Transfer( I want to split this full amount for all the participants for that transfer)
Expecting - I want to see the data in the full amount column under dbo.employee based on taking from dbo.Transfer
I am planning to implement the logic like this. Correct me if I am wrong plz.
select fullamount from dbo.Transfer t1 nerjoin dbo.Employee E2 where t1.fullamount = E2.fullamount and I have to keep some conditions here (employee number is not null and participant belong to USA.)
By doing this can i get this data split into dbo.employee table?
real details, like the table definitions would have let me give a rock solid answer. Instead i have to guess on relationships and whether columns exist.
well, you don't want to join on an amount, you want to join on keys;
how do you determine that say, three people are related to a transfer? you need something like a count of participants to divide the full amount by, and to create or update records for the participants to now reflect the new amounts.
I've seen bonus distribution examples like this, where x amount of moneyi s distributed to everyone who meets a specific criteria; is that what you are trying to do?
does everyone receive a split equally?(ie Transfer.FullAmount / (NULLIF(TotalParticipants,0)
you need to get the EmployeeID's that are related to the specific transfer; i'm using a sloppy cross join, but it should be a real join between tables.
CREATE TABLE tempdb.dbo.Employee(EmployeeID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, EmployeeName VARCHAR(30) ,EmployeeNumber AS 'E' + CONVERT(VARCHAR,EmployeeID),participant VARCHAR(10) DEFAULT 'USA')
INSERT INTO tempdb.dbo.Employee(EmployeeName)
SELECT 'Lowell' UNION ALL SELECT 'mcfarlandparkway ' UNION ALL SELECT 'Phil Parkin'
INSERT INTO tempdb.dbo.Employee(EmployeeName,participant)
SELECT 'Sergio','Venezuela'
CREATE TABLE tempdb.dbo.Transfer(TransferID int IDENTITY(1,1) NOT NULL PRIMARY KEY,FullAmount money)
INSERT INTO tempdb.dbo.Transfer(FullAmount)
SELECT 129.55
CREATE TABLE tempdb.dbo.AllDistributions(FullAmount MONEY,SplitWithMembers INT, DistributedAmount MONEY ,EmployeeId INT,EmployeeName VARCHAR(30))
--an example
;WITH QualifiedParticipants
AS
(
SELECT * FROM tempdb.dbo.Employee WHERE EmployeeNumber IS NOT NULL AND participant IN ('USA')
),
TheCount
AS
(
SELECT COUNT(*) AS TotalParticipants FROM QualifiedParticipants
)
--INSERT INTO tempdb.dbo.AllDistributions([FullAmount],[SplitWithMembers],[DistributedAmount],[EmployeeId])
SELECT
T1.FullAmount,
NULLIF(C1.TotalParticipants,0),
T1.FullAmount / NULLIF(C1.TotalParticipants,0),
Q1.[EmployeeID],
Q1.EmployeeName
FROM tempdb.dbo.Transfer T1
CROSS JOIN QualifiedParticipants Q1
CROSS JOIN TheCount C1
results:
/*
129.55343.18331Lowell
129.55343.18332mcfarlandparkway
129.55343.18333Phil Parkin
*/
Lowell
October 10, 2016 at 8:05 pm
Basically we are loading SSIS package where the Employee information is coming from source(Different database)to staging (Temporary table) called EmpTemp
Note - Full amount column is not comming from source.
In the package after data is loaded from source to temp table there is a consolidated part where it using a stored procedure (we take data from temp and check first in main table weather record exists or not if there is no record gohead and insert into main table)
During processing I am trying to split this column from Transfer table ( and assign it to main table dbo.Employee (Where i just added this column here in this table as it is not coming from source.)
I am concerned about splitting process how to do it and how to join with Transfer table ( to pick up full amount column)
Here is some information on the tables
dbo.Employee
ID PK BIGINT NOT NULL
GI nvarchar(8),null
Participant GI nvarchar(8),nOT NULL
full amount (money,null)
Region varchar(5),null -- holds data as USA
dbo.Transfer
TransferId PK BIGINT NOT NULL,
fullamount (NUmeric(26,3),null)
The logic i am trying to implement in the stored procedure is This has to pick up by country after that take the amount from Transfer and divide by number of internal employees.
Amount has to split equally
difference between Internal and external is
GI Participant GI
87654321 67537433
87654321 00000000
Internal emp will have Participant GI field populated with ---(bla bla). External emp have this field populated with zeroes.
Anyone that is emp has Participant GI field populated.
Here is the stored procedure -
DECLARE
@GI nvarchar(8),
@Participant GI nvarchar(8),
@Region varchar(5)
@NoOfRecsInserted INT,
@fullamount money,
@Error INT
SET @NoOfRecsInserted = 0
DECLARE empCursor CURSOR STATIC FOR
SELECT
GI,
ParticipantGI,
Region,
fullamount
FROM EmpTemp
--BLa Bla (Where condition to filter records)
OPEN empCursor
FETCH NEXT FROM empCursor INTO
@GI,
@Participant GI,
@Region,
@fullamount
WHILE @@FETCH_STATUS = 0
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.EMployee
(
GI,
ParticipantGI,
Region,
fullamount
)
VALUES
(
@GI,
@Participant GI,
@Region,
@fullamount
)
SET @Error = @@Error
IF @Error <> 0
BEGIN
SET NOCOUNT ON
SELECT 'GI' + CAST(@GI AS VARCHAR(50))
--Insert into Error (Log errors)
END
IF @Error = 0
BEGIN
SET @NoOfRecsInserted = @NoOfRecsInserted + 1
END
FETCH NEXT FROM empCursor INTO
@GI,
@Participant GI,
@Region,
@fullamount
END
CLOSE empCursor
DEALLOCATE empCursor
October 13, 2016 at 3:07 pm
>> I have two tables one is temporary and another is main dbo.employee <<
please, please, please read just one book on basic data modeling. A table models a set; a set is a collection and less you truly have only one employee. This name is totally wrong under ISO 11179 rules and common sense! I think you might have meant "Personnel"
>> In the main [sic] table I have added a column called fullamount. Basically this fullamount column is coming from different table dbo.Transfer( I want to split this full amount for all the participants for that transfer) <<
The rudeness of a total lack of DDL discourages us from even wanting to help you. Why is a column that shows a relationship between your personnel and something else (you never told us what) in the wrong table?
>> Being incredibly rude, or so stupid yes you were wrong 0
Expecting - I want to see the data in the full amount column under dbo.employee [sic: personel] based on taking from dbo.Transfer [this is verb!]
>> I am planning to implement the logic like this. Correct me if I am wrong plz. <<
Yes, you are wrong. Very very wrong. Please post real DDL and not a vague narrative. Please explain what you are trying to do.
I know the first few weeks of learning a language are very hard, but please read the forum rules about posting. I am sure by the time you been writing SQL or at least reading about it for one or two months you will do better.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply