June 10, 2008 at 1:26 pm
Hi,
I have two tables called Actcodes and a another table called FundBalances...
The Act codes have the following format..
PlanId int,
ClientId int,
StartDate datetime,
EndDate datetime,
ClientActCode char(2)
CodeDescription char(15)
so based on what the user has selected i am getting the names of each codes
and they have the following Rows.
PlanId ClientId Startdate EndDate ClientActCode CodeDescription
111 1 1/1/2008 3/31/2008 01 Begininng balance
111 1 1/1/2008 3/31/2008 02 Contributions
111 1 1/1/2008 3/31/2008 03 something
111 1 1/1/2008 3/31/2008 04 sdkfjdkf
111 1 1/1/2008 3/31/2008 05 dfdfd
111 1 1/1/2008 3/31/2008 06 dfddfs
111 1 1/1/2008 3/31/2008 09 dfdf
111 1 1/1/2008 3/31/2008 15 dfdkfdlfk
and my fund balance i have the following rows..
PlanId int
Participantid int
StartDAte datetime,
end date datetime,
FundId int
Loans
Act1 char(2)
TotAct1 money
.
Act20 char(2)
TotAct20 money
and the data is as follows
PlanId ParticipantId StartDate EndDate fundId Loans Act1 TotAct1 Act2 totact2 ----- Act20 TotAct20
111 1212 1/1/2008 3/31/2008 15 NULL 01 15.15 02 15.48 20 12.4561
111 1212 1/1/2008 3/31/2008 45 0 01 45.12 02 453.123 20 54.00
so on and so so forth
tht Act1 matches the ClientActCode in the Act..
So is there a way i can get those column in my select which have actCodes present in the Act code table. for in... in my act code table for this plan i have 01,02,03,04,05,06, 09, 15 clientAct codes
so can i get the its respective Acts and Totacts column as Act1, Act, Act3, Act Act5, Act6 based on the ClientAct codes.
I want my final output to be
Select Act1, CodeDescrption1, Totact1, Act2, codeDescription2, TotAct3... Depending on the rows in return in this query
Select
cs.ClientActCode,
cs.CodeDescription,
NULL
From
ACtCodes cs
Where
cs.PlanId = @PlanId
and
cs.StartDate = @StartDate
And
cs.EndDate = @Enddate
Any help will be appreciated..
Thanks
Karen
June 10, 2008 at 1:57 pm
Select
cs.ClientActCode,
cs.CodeDescription
From
ActCodes cs
Where
cs.PlanId = @PlanId
and
cs.StartDate = @StartDate
And
cs.EndDate = @EnddateThis is the OutPut I get from the above query 01BEGINNING BALANCE
02CONTRIBS
03FORFEITURE
04LOAN TAKEN
05LOAN PRINCIPAL
06LOAN INTEREST
07FUND TRANSFERS
08WITHDRAWAL
09GAIN/LOSS
15CLOSING BALANCE I want the Rows in the fund balance tables of those Column That are found in the above query
The data in the Fund balances will look like this
PlanId PartId FundId Loans ACt1 TotAct1 Act2 TotAct2 3 3
5837599920LOAN012238.06 020.00030.00040.0005-222.09060.00070.00080.00090.00100.00110.00120.00130.00140.00152015.97 160.00170.00180.00NULLNULLNULLNULL
5837599921103NULL017635.57020.00030.00040.00050.00060.00070.00080.0009-324.76100.00110.00120.00130.00140.00157310.81 160.00170.00180.00NULLNULLNULLNULL
58375999211324NULL0116080.45020.00030.00040.00050.00060.00070.00080.0009-675.66100.00110.00120.00130.00140.001515404.79 160.00170.00180.00NULLNULLNULLNULL
58375999213728NULL0111166.37020.00030.00040.00050.00060.00070.00080.0009-743.19100.00110.00120.00130.00140.001510423.18 160.00170.00180.00NULLNULLNULLNULL
58375999213823NULL017548.66 020.00030.00040.00050.00060.00070.00080.0009-539.27100.00110.00120.00130.00140.00157009.39 160.00170.00180.00NULLNULLNULLNULL
58375999226343NULL012189.51 021065.59030.00040.0005222.090620.37070.00080.0009-68.85100.00110.00120.00130.00140.00153428.71 160.00170.00180.00NULLNULLNULLNULL
So my sample out put should be
Select PlanId, PartId, FundId, Loans, Act1,CodeDescription,Totact1,
Act2,CodeDescription,Totact2,
Act3,CodeDescription,Totact3,
Act4,CodeDescription,Totact4,
Act5,CodeDescription,Totact5,
Act6,CodeDescription,Totact6,
Act7,CodeDescription,Totact7,
Act8,CodeDescription,Totact8,
Act9,CodeDescription,Totact9,
Act15,CodeDescription,Totact15
June 10, 2008 at 2:07 pm
I'm getting a little lost in your question. What exactly is it that you want from all this data?
Also, would it be possible to provide table definitions (create statements) and some sample data in a format that can be inserted into the tables? Trying to decipher which columns go with which data in the layout it ended up in is a little rough.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 10, 2008 at 2:11 pm
Thanks for ur answer GSquared..
In my first post i hve the table definitions.. and in the second post i have given the data i get in my select query and also listed how the data will be in the fund balance table and finally how i want my data to appear..
June 16, 2008 at 8:34 am
What I very specifically asked for is create scripts and insert statements.
For example:
create table T1 (
ID int identity primary key,
Col1 varchar(25),
constraint CK_Col1 check (col1 != '' or col1 is null))
go
insert into dbo.T1 (Col1)
select 'Val1' union all
select 'Val2'
Yes, I can parse through your post and try to write my own table create scripts and insert statements. No, I don't have time to do that at this point. I was asking if you could do that, which would make answering your question much easier. I'm just asking you to help us help you.
If you don't have time to post something that can be copy-and-pasted into Management Studio and run, that's fine. It will delay getting an answer, but someone might still have time to build it themselves.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 17, 2008 at 5:19 am
Hi,
I dont think it would be possible to make this work using one single query.
I would suggest the following to make it work..
Write a stored proc and use ur query to fetch the index numbers. get them into a table variable.
loop throgh the table variable and get the column names from fund balances table, (since the column names seem to end with the indexes that are returned by the query )
note : make use of the following query to get all columns of fund balances table..
select name from syscolumns where id =
(select id from sysobjects where name = 'fundbalances')
so, may be u could rewrite it some thing like..
select name from syscolumns where id =
(select id from sysobjects where name = 'fundbalances') and name like '%15' ... based upon the values returned in ur first query...
get thes column names into a table variable as well. now u could use this table as a reference to build ur final query..
I know this would sound really confusing, and due to lack of time, i am not able to elaborate with an example tsql code..
please try the steps and let me know if u could succeed...
happy querying..
--AsN
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply