February 1, 2008 at 9:49 am
Let me start by apologizing for not following the proper rules for posting, I'm not able to post the code.
That said, what I have are three tables, one for earnings, one for taxes, and the other for deductions used to create a report in which all three are listed on the same line, might look something like this:
Earnings: Taxes: Deductions:
100.50 82.39 2.50
But the data has to be taken from three seperate tables. What I am doing now is grabbing all the people for the table, and assigning them 20 rows (should never be more than 20 lines), basically the same row duplicated with a row number column of 1-20. The three previously mentioned tables are then assigned row numbers 1-n (never more than 20) and then combined in a join statement. Lastly all rows that contain nulls for all three columns are deleted. It's a rather convoluded query and I have it working, but I want to see if there is a better way to do this. (Hopefully faster too...)
February 1, 2008 at 9:55 am
Y, sorry dude... you said it best in your post that you can't post the code. That means you realize that there's really no way for us to help you w/o more specifics.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant
February 1, 2008 at 10:09 am
There is a way to help, but I realize it's not as easy as just fixing my code. I suppose I could post the 865 lines of code that are doing this, but you probably wouldn't take the time to read through it. On the other hand I suppose I could just make something that looked like what I am doing... One sec
-- employee data with seqno (table that contains one column with 20 rows 1-20)
insert into #temptable
select name, address, id, seqno from employeedata,sequenceno
isnert into #earnings
select id,earningcode,curamt, row_number() Row_number() over (PARTITION BY earnings.ID ORDER BY earnings.curamt DESC) AS seqno
from earnings where employeedata.id = earnings.id
insert into #taxes
select id,taxcode,curamt, row_number() Row_number() over (PARTITION BY taxes.ID ORDER BY taxes.curamt DESC) AS seqno
from taxes where employeedata.id = taxes.id
insert into #deductions
select id,deductioncode,curamt, row_number() Row_number() over (PARTITION BY deductions.ID ORDER BY deductions.curamt DESC) AS seqno
from deductions where employeedata.id = earnings.id
-- I realize that the code below is wrong, but you get the idea
insert into #finaltable
select * from #temptable inner join (earnings inner join taxes inner join deductions) on id = id
where earningcode is not null and taxcode is not null and deductioncode is not null
I was hoping that someone may have a theory to doing this, just trying to learn from some others that have more knowledge on this topic. Thank you for your reply.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply