Whats The Best Way To Store Multiple IDs?

May 17, 2008

I'm working on an mini intranet site for a "University" type style. I have a table called "events" which stores information. One of my columns is "eventRSVP" which stores the employees ID's who click on the button, separated by a comma.

example:
Quote: 1751,2179,1371,2314,1778,702,1607,798,2097 Would it be better to create a new table called something like "event_jn" and it has 3 columns.

Quote: ---------------------------
| jnID | jnEVENT | jnEMP |
---------------------------
| 1 | 3 | 2097 |
---------------------------

I'm also redoing our whole intranet and would be doing a lot of these similar type id linking. So my other question is, the more tables I have, is it going to slow down the response time?

Appreciate any insight that can be given!

TIA!

View 3 Replies


ADVERTISEMENT

Whats The Best Way To Store Filename / File Path?

Jan 20, 2004

I'm guessing the best way / only way to store a filename is to store it as a varchar?

I'm using it as a reference to pictures wefor a gallery

View 2 Replies View Related

Where To Store Multiple Connectionstings For Multiple Users?

Oct 26, 2006



We have an application where users log in and set up a ConnectionString based on the login-info. As to now, we have used

Application.Contents("conn") = (strConnectionString)

in Global.asax.vb (Application_Start section) and then in the in the webforms we use

Dim con As New SqlClient.SqlConnection
con = New SqlClient.SqlConnection(Application("conn"))
con.Open()
......
......
con.close()

(Each user has their own sql database that they connect to using the same scripts.)

The question is: When many users are logged on at the same time, will the use of Application.Contents be a safe way to connect to each users own database or is there a possibility that users suddenly will try to establish a connection to a database that is not their own?

Scenario: User1 logs in at 10:00 and establish a connection to his own database. At 10:05 User2 logs on and establish a connection to his database since Global.asax.vb is run again. Will User1 now use User2's connection so that he is actually woking on User2's database?

Is there a better/safer way to do this connection in Global.asax.vb ?

Help much appreciated!!!!

View 8 Replies View Related

How To Store Multiple Language-tables?

Jun 11, 2008

Dear
Since my other threads concerning my question weren't clearly answered to me I'm starting again:
I want to store musclegroups,musclecategories and muscles in different languages, please have a look at the proposal below and tell me if I'm on the right way

View 4 Replies View Related

Multiple Updates In Store Procedure?

May 25, 2004

I am a total newbie to SQL Server, but have a good idea about how things operate..

I have a "job" that runs multiple (10) update queries for me in different tables.

Can I create a stored procedure to do these so that I can make one call to them?

If not, how can I call the job to start external to MS SQL?

View 1 Replies View Related

Store Multiple Values In A Single Value

Nov 6, 2005

was hoping someone couild provide some insight into a problem I'm trying to solve.

I have a table called SEARCHCRITERIA. It consists of a USERID column and a CRITERIA column. Users will be able to search for other users based on a set of criteria. There are 5 total criteria a user can choose. They can choose as few as none or all five. I'd like to store the criteria chosen as a single number in the SEARCHCRITERIA table. Then use a function to parse out the criteria. For example:

CRITERIAID CRITERIA CRITERIAVALUE
1 AGE 2
2 SEX 4
3 GRADE 8
4 LOCALE 16
5 REGION 32

A user performs a search based on AGE, SEX, and LOCALE. I would then store the value 22 (the sum of 2, 4, and 16) in the SEARCH table. I would then need a function to pull out the three individual values.

Has anyone done anything like this before?

If so, any help would be appreciated!

Thanks in advance!

View 1 Replies View Related

How To Store Multiple Records Feasibly

Aug 11, 2014

Am doing a simple application.Am using vb.net and sql server 2008.

Functionality:

For customers, I need to store the entered amount daily.
Eg as follows:

Name 01/01 02/01 03/01 04/01 ------ 17/04 18/04 (100 days from JAN to APRIL)

Raja 100 100 - 100 - 100
Priya 200 300 100 - - -
Giri 100 -

Daily it will come upto 300 records. So for 100 days 300*100 records.How to store the records feasibly in database?How to define table to store records for above example?

View 2 Replies View Related

How Do I Store Multiple Selected Listbox Items

Jan 12, 2008

Hi There
This is probably realy simple but since im still a newbie some help would be appreciated.  I have a listbox bound to an sqldatasource which has names  of people from my sql database employeeDetails table.  I simply want to allow someone to select to select multiple names and insert them into another field in my database. 
I have this bit workng, ie they can select multiple people on the list, it does insert, however only the first selected name, not the others.Can you please help me out
Kind Regads
Dan

View 3 Replies View Related

Create Multiple Store Procedures In 1 SQL Statement

Nov 17, 2006

Hi guys , may I know is that possible to create multiple store procedures in 1 time using 1 SQL statement? Thx for the assistance.

Best Regards,

Hans

View 5 Replies View Related

How To Store Multiple Result In A Variable On Sql Server Stored Procedure

Feb 29, 2008

 name               age            weightaaa                    23                50bbb                    23                60ccc                     22               70ddd                    24                20  eee                     22               30i need the output that calculate the sum of weight group by name input : age limit ex: 22 - 23 output  : age           total weight  23               11022                100  this output must stored in a sql declared variable for some other further process . 

View 7 Replies View Related

Using A Match Table To Store Multiple Columns For Parent Data

Mar 1, 2008

Sorry for the confusing subject. Here's what im doing:I have a table of products. Products have N categories andsubcategories. Right now its 4. But there could be more down theline so it needs to be extensible.So ive created a product table. Then a category table that has manycategories of products, of which a product can belong to N number ofthese categories. Finally a ProductCategory "match" table.This is pretty straigth forward. But im getting confused as to how towrite views/sprocs to pull out rows of products that list all theproducts categories as columns in a single query view.For example:lets say productId 1 is Cap'n Crunch cereal. It is in 3 categories:Cereal, Food for Kids, Crunchy food, and Boxed.So we have:Product----------------1 Capn CrunchCategories-----------------1 Cereal2 Food for Kids3 Crunchy food4 BoxedProductCategories------------------1 11 21 31 4How do I go about writing a query that returns a single result set fora view or data set (for use in a GridView control) where I would havethe following result:Product results---------------------------------ProductId ProductName Category 1 Category 2Category 3 Category N ...------------------------------------------------------------------------1 Capn Crunch Cereal Food for Kids Crunchy foodBoxedAm I just thinking about this all wrong? Sure seems like it.Cheers,Will

View 1 Replies View Related

Want 2 Store Multiple Room Numbers Of A Hotel In A Single Column Using SQL Server2k.

Jan 15, 2004

Hi!
Want 2 store Multiple room numbers of a hotel in a single column using SQL Server2k.

Can anybody tell me how to do it??

Thanx.

View 2 Replies View Related

Integration Services :: Store Multiple Config In Single Table Using SSIS Configurations?

Oct 20, 2015

I want to store multiple config files into single table I am unable to do it.

View 8 Replies View Related

Whats Better?

Jul 20, 2005

<usualDisclaimer>Please forgive me if this is in the wrong group, and if so,what is the right group.</usualDisclaimer>Let me start off by first saying im a newb. Ok, with that out of the way Iam trying really hard and boy have I learned a lot in the last little whilebut I have a question i just can't seem to find a good answer to.Lets say i have a table that simply stores how many times someone has loggedinto a webpage. Is it better to store each login as a new record then countthe records or is it better to simply have one record that updates the totalvalue by incrementing that one field. I have read all manner of articlesand some say one way is better vs. the other but what I can't find is why?If I knew why one way was better than another then I could make and educateddecision and choose the best way that is right for me. Is updating more orless expensive then inserting? Does it matter and is a relevant question?And before anyone comments on my use of all uppercase letters for my tablename I do this so that my table names stand out within the sql serverenterprise manager. In other words system tables are lowercase and mytables are uppercase. People always seem to give me crap for this but neverback it up with a good explanation so far as I can tell its personalpreference? Am I wrong?CREATE TABLE USER_METRICS(-- here i can select all the records and count them up.ID INT IDENTITY(1,1) PRIMARY KEY,Email VARCHAR(250));-- Or this?CREATE TABLE USER_METRICS(-- and of course here can i simply retrieve the value of Total_LoginsID INT IDENTITY(1,1) PRIMARY KEY,Email VARCHAR(250) NOT NULL,Total_Logins INT);Regards,Muhd

View 3 Replies View Related

Whats Wrong In This...

Sep 12, 2007

Hello DBA's 
i use this proc to insert & update the records in the databse
update works good untill i change the user id.  
userid can be changed where adminuserid is the IDENTITY coulmn in the table.ALTER PROCEDURE [dbo].[spinsertusers]
-- Add the parameters for the stored procedure here
@adminuserid varchar(36),
@userid varchar(15),
@fname varchar(25),
@mname varchar(25),
@lname varchar(25),
@password varchar(15),
@address1 varchar(255),
@address2 varchar(255),
@postcode varchar(15),
@cityidentity varchar(36),
@dob smalldatetime ,
@email varchar(50),
@crtduser varchar(36),
@crtdon datetime,
@isactive char(4),
@mode char(10),
@reccount INT output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Count INT
-- Insert statements for procedure here
IF (@mode='insert')
SET @Count=(SELECT COUNT(*) FROM adminusermaster WHERE userid=@userid)
IF @count=0
--Inserting the Records
BEGIN
INSERT INTO [school].[dbo].[AdminUserMaster]
([AdminUserIdentity]
,[FirstName]
,[MiddleName]
,[LastName]
,[UserID]
,[Password]
,[Address1]
,[Address2]
,[PostCode]
,[CityIdentity]
,[DOB]
,
,[CreatedBy]
,[CreatedOn]
,[IsActive])
VALUES
(
@adminuserid ,
@fname ,
@mname ,
@lname ,
@userid ,
@password,
@address1,
@address2,
@postcode,
@cityidentity ,
@dob,
@email,
@crtduser,
@crtdon ,
@isactive
)
SET @reccount=2
return @reccount
END

-- End of Query
IF (@mode='update')

SET @Count=(SELECT COUNT(*) FROM adminusermaster WHERE userid=@userid)
IF @Count=1
--Query for Update the Records
BEGIN
update AdminUserMaster
set
[FirstName]=@fname ,
[MiddleName] =@mname,
[LastName]=@lname,
[userid]=@userid ,
[Address1]=@address1,
[Address2]=@address2,
[PostCode]=@postcode,
[CityIdentity]=@cityidentity,
[DOB]=@dob,
=@email,
ModifiedBy=@crtduser ,
ModifiedOn=@crtdon ,
isactive='Y'
where
AdminUserIdentity= @adminuserid
set @reccount=3
return @reccount
END

-- End of Query
END





 
 

View 3 Replies View Related

DBO Or MDF Whats The Diffrence?

May 11, 2008

]First of all here is how i created my SQL database and established a connection in visual studio 05. I go into the server explorer and right click on the data connections node and and select "create new SQL database". A window then prompts me for a server name and data base name. Under server name I write "localhostSQLEXPRESS" and under data base name I put in whatever. This totally worked and the database was asigned the extension of .DBO
I can now insert, select and delete information with some simple ADO coding. The only problem is that using this method makes it seamingly impossible to FTP my database onto my host server.  I am aware that I can create a data base in the APP folder by simply right clicking it and selecting " new database".
This would simplify the deploying process but I cant seem to establish a connection this way. Ive also noticed that when I use this method that it saves it as a .MDF instead of a .DBO. What is the difference? Why does my .DBO work but not my .MDF in my app folder? Im so confused over this issue that I am problably not even asking the right question. Bottom line is that I have a small web application with a .dbo database that works wonderfully in my isolated visual studio environment and I need a solution to deploy it on the world wide web.  Please help with any information I would be very thankfull.

View 15 Replies View Related

Whats The Difference

Feb 2, 2004

Between having SQL and using SQL server enterprise manager with MSDE

View 1 Replies View Related

Whats Wrong With This Sp?

Aug 11, 2004

I am not an expert with sp. I am trying to pass the column name as a parameter but I get the following error "Must declare the variable '@strColumnName'."

ALTER PROCEDURE dbo.TempGetDataForDropDown
(
@strColumnNamenvarchar (50)
)
as

EXEC ('SELECT DISTINCT @strColumnName = ' + @strColumnName + ' FROM Customers')

View 1 Replies View Related

Whats Wrong In This

Oct 3, 2006

SELECT 'http=' replace (prot,'ww','+')',+'replace(city,'u','+')',+'proj_s tate'+'proj_zip5
INTO :ls_MAP FROM project4 WHERE proj_id=:gv_proj_id;

View 4 Replies View Related

Whats Too Many Records?

Jan 9, 2004

Hi all,
I've recently created a shopping cart system that uses SQL Server on the backend. The shopping cart will add a record to a table called CartEvents each time anything happens, like view item, update, delete etc. anyhow, the site has been up for about a week now and that particular table has about 4200 records already, I project tens of thousands of records in that table, so how many records is too many records? Whats your experience with tables that have hundreds of thousands of records? How many records can SQL server handle and still perform well?

Thanks

View 4 Replies View Related

What Is ALL,SOME|ANY ? Whats The Usage?

Jun 5, 2008

What is ALL,SOME|ANY ? Whats the usage?

Is they are comparison operators?
If not then what kind?

If SOME and ANY are same then where to use SOME and where to use ANY?

View 2 Replies View Related

Whats The Server Name

Jun 23, 2008

Im using windows xp sp2 and MS Sql server 2005 enterprise edition may b and im also havin sql server express
previously when i installed sql server express it took server name on its own and i used to run queries using AdventureWorks database..
Recently i formated my desktop and again installed both the versions im having but neither is taking server name.. and i dint noticed what was the server name before.. im a student home user i just hav to query on adventurework databases on the same computer i cant afford to buy any servers..
so can anyone tell me how can i configure sql so that i can query !

View 1 Replies View Related

Whats Wrong Here??

Nov 17, 2006

DECLARE @DBName varchar(100);
SET @DBName = 'TestTest';

CREATE DATABASE @DBName ON PRIMARY;


message is: wrong syntax near '@DBName'.

View 8 Replies View Related

Whats The Difference?

Nov 22, 2007

I've been writing a few sql queries over the past few days, and would like to know what the difference is between:

website is null

website like ''

Becasue if they mean different things i want to know how to look them both up at the same time. Obv im after data that is blank or has nothing written in it.

View 6 Replies View Related

Whats Wrong With This SQL

Mar 21, 2008

I am trying to run an sql query with a function in SQL SERVER MANAGEMENT STUDIO and am getting the following error.

Msg 207, Level 16, State 1, Line 9
Invalid column name 'UserID'.

here is my Query
-----------------
USE vportal4vsearch
GO
CREATE FUNCTION dbo.ConcatUserGroups(@UserID int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @groupname Varchar(2000)
SELECT @groupname = COALESCE(u.group_name + ', ', '') + CONVERT(varchar(20), u.group_name)
FROMdbo.[user] A
JOIN dbo.UserGroup_combo UG
ON UG.ug_userID = a.USER_ID
JOIN dbo.usergroup U
ON UG.ug_groupID = u.group_ID
WHEREA.USER_ID = @UserID
ORDER BY U.GROUP_NAME

RETURN @groupname
END
GO
SELECT
user_ID,
user_Firstname,
user_Lastname,
user_Email,
Username = (Case When user_Username = ' ' or user_username is Null then 'None' else User_Username end),
user_creationdate,
Active = (Case When user_Active = 1 then 'Yes' else 'No' end),
dbo.ConcatUserGroups(UserID),
TheatreCode = (Case When User_TheaterCode = ' ' or User_TheaterCode is Null then 'N/A' else User_TheaterCode end),
JobTitle = (Case When user_JobTitle = ' ' or User_Jobtitle is Null then 'N/A' else User_Jobtitle end)
FROM [user]
GO
DROP FUNCTION dbo.ConcatUserGroups
GO

View 6 Replies View Related

Any One Know Whats Wrong With It

Jan 14, 2006

[CODE]

 

Private Sub pulllikehell()
        Dim cn As SqlCeConnection
        Dim rda As SqlCeRemoteDataAccess = Nothing
        Dim sqlEngine As SqlCeEngine

        Try

            ' Create database if it doesn't already exist
            If (Not File.Exists("My DocumentsORDER.sdf")) Then
                sqlEngine = New SqlCeEngine
                sqlEngine.LocalConnectionString = "Data Source=My DocumentsORDER.sdf;"

                sqlEngine.CreateDatabase()
                sqlEngine.Dispose()
            Else
                ' Open the connection to the database
                cn = New SqlCeConnection("Data Source=My Documents" & _
                                         "ORDER.sdf:")
                cn.Open()
                Dim cmd As SqlCeCommand = cn.CreateCommand()

                ' Drop the FieldMemos table
                cmd.CommandText = "DROP TABLE STAFF"
                cmd.ExecuteNonQuery()

                ' Close the connection

            End If

            ' Instantiate the RDA Object
            rda = New SqlCeRemoteDataAccess
            ' Connection String to the SQL Server.
            Dim remoteConnectString As String = "Provider=SQLOLEDB;" & _
                                                "Data Source=KANGALERT/SQLEXPRESS;" & _
                                                "Initial Catalog=ORDER;"

            rda.InternetLogin = ""
            rda.InternetPassword = ""
            rda.InternetUrl = "http://kangalert/server/sqlcesa30.dll"
            rda.LocalConnectionString = "Data Source=My Documents" & _
                                        "ORDER.sdf;"


            rda.Pull("STAFF", "Select * from STAFF", remoteConnectString, _
             RdaTrackOption.TrackingOnWithIndexes)
            MsgBox("ok")

        Catch sqlex As SqlCeException
            Dim sqlError As SqlCeError
            For Each sqlError In sqlex.Errors
                MessageBox.Show(sqlError.Message)
            Next
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            rda.Dispose()
        End Try

    End Sub

[/CODE]

computer server name is kangalert

and got 2 sqlservice run in my pc,  SQLEXPRESS  and MSSQLSERVER

DATABASE NAME IS ORDER with a TABLE call STAFF

when come to this statement

rda.LocalConnectionString = "Data Source=My DocumentsORDER.sdf"

ERROR MESSAGE: A request to send data to the computer running IIS has failed. For more information, see HRESULT.

 when come to this statement

rda.Pull("STAFF", "Select * from STAFF", remoteConnectString, _
             RdaTrackOption.TrackingOnWithIndexes)

ERROR MESSAGE :A required property is not specified. [ Property name = SubscriberConnectionString ]

HOPEFULLY ANYONE CAN HELP ME SOLVE IT. THANKS IN ADVANCE

View 3 Replies View Related

Whats Wrong With My SP

Aug 25, 2006

Whats wrong with my stored procedure i thought i am declaring the varible when i write this check it out can some one help me out thanks!!!!



CREATE PROCEDURE dbo.GetCompanyDEMO
[

@CompanyName nvarchar(40)
]

As

Select ContactName, ContactTitle, Address, City, Region, Phone
FROM Customers C
Where C.CompanyName = @CompanyName

View 3 Replies View Related

Some One Tell Me What Is Trigger And Whats The Use Of It? And When Do I Use It?

Mar 6, 2008

some one tell me what is trigger and whats the use of it? and when do i use it?

some one show me demo , example of trigger

thank u

maxs

View 10 Replies View Related

How To Search Multiple Table Which Table Name Is Store In Another Table And Join The Result Together?

Dec 1, 2006

I have one control table to store all related table name
 Table ID                   TableName
     1                           TableA
     2                           TableB
 
In Table A:
RecordID                Value
     1                         1
     2                         2
     3                         3
 
In Table B:
RecordID             Value
    1                         1
    2                         2
    3                         3
 How can I get the result by select the Table list first and then combine the data in table A and table B?
 
Thank you!

View 1 Replies View Related

Complex Query, Whats The Best Way?

May 15, 2007

I have a query that must return information for an invoice. It is not straight forward though. Some things need to be displayed from the if something else is happening...
For example...declare @id int
declare @chargeid int
set @id = 1
set @chargeid = 9

declare @pp bit, @pages int, @pagefee money, @pagecharge money
set @pp = (select perpage from tblmainfees where mainfeesid = @chargeid)
if (@pp = 1)
begin
set @pages = (select pages from tblcharges inner join tblrequest on requestid = fkrequestid where requestid = @id)
if (@pages > 0)
begin
set @pagefee = (select perpagefee from tblmainfees where mainfeesid = @chargeid)
set @pagecharge = (@pages * @pagefee)
end
end
...

select @pagecharge as PerPageCharge

 
Or should I do some complex query with only one select?

View 4 Replies View Related

Sql Data Source...whats Best

May 17, 2007

Im a bit confused as to what is best.
I have a web site built in c# web dev which utilises a data base.
What is best practise ...........
1.    To have one sql data source on one page and all other pages use that source.
2..   each page has its own source and connection string depending on its data requirements.
3.....  have no source and use insert statements with stored procedures because most of my site extracts data from visitors
rather than displaying data to them.
If I have this right.......the data source is a copy of the data base that is available for use to the visitor
or is it just part of the connection to the data base.
 My only gripe with the whole development process has been the discovery that I have learnt
four ways of doing the same thing.......which in its self becomes confusing..
However one thing has stood above all else ........this forum
richard 

View 2 Replies View Related

Whats Wrong With This Query

Nov 8, 2007

 1 SELECT
2 RowNumber,
3 'Source.Dbf, Plan.Dbf',
4 'Source Name is missing for Source Number "' + IsNull(RTrim(f.SOURCE_NUM),'Unknown') + '" in Plan.Dbf table.'
5 FROM
6 SourceDbf f
7 JOIN
8 (
9 SELECT DISTINCT
10 SOURCE_NUM,
11 (Select CASE s.SOURCE_NUMWhen 1 Then SRC1NAME
12 WHEN 2 Then SRC2NAME
13 WHEN 3 THEN SRC3NAME
14 WHEN 4 THEN SRC4NAME
15 WHEN 5 THEN SRC5NAME
16 WHEN 6 THEN SRC6NAME
17 WHEN 7 THEN SRC7NAME
18 WHEN 8 THEN SRC8NAME
19 WHEN 9 THEN SRC9NAME
20 WHEN 10 THEN SRC10NAME
21 WHEN 11 THEN SRC11NAME
22 WHEN 12 THEN SRC12NAME
23 WHEN 13 THEN SRC13NAME
24 WHEN 14 THEN SRC14NAME
25 WHEN 15 THEN SRC15NAME
26 END
27 FROM
28 PlanDBF p
29 Where
30 p.PLAN_NUM = s.PLAN_NUM
31 ) as SourceName
32 FROM
33 SourceDBF s ) c on f.PLAN_NUM = c.PLAN_NUM

 i am getting an error on Line 33 and this what the error says...
Msg 207, Level 16, State 1, Line 33Invalid column name 'PLAN_NUM'.
 
Any help will appreciated..
Regards
Karen

View 17 Replies View Related

Whats Wrong With This Statement

Nov 16, 2007

I am getting an error Msg 8155, Level 16, State 2, Procedure usp_ImportNewASC_ParticipantPlanFundBalances, Line 1No column was specified for column 49 of 'a'.I think its something to do with the line number  48 and 102 ALLOC_PER is char(9) so i want to to cast it as a int and insert into the prodction data. 1 UPDATE
2 Statements..ParticipantPlanFundBalances1
3 SET
4 Act1 = ACT_ID1,
5 TotAct1 = TOT_ACT1,
6 Act2 = ACT_ID2,
7 TotAct2 = TOT_ACT2,
8 Act3 = ACT_ID3,
9 TotAct3 = TOT_ACT3,
10 Act4 = ACT_ID4,
11 TotAct4 = TOT_ACT4,
12 Act5 = ACT_ID5,
13 TotAct5 = TOT_ACT5,
14 Act6 = ACT_ID6,
15 TotAct6 = TOT_ACT6,
16 Act7 = ACT_ID7,
17 TotAct7 = TOT_ACT7,
18 Act8 = ACT_ID8,
19 TotAct8 = TOT_ACT8,
20 Act9 = ACT_ID9,
21 TotAct9 = TOT_ACT9,
22 Act10 = ACT_ID10,
23 TotAct10 = TOT_ACT10,
24 Act11 = ACT_ID11,
25 TotAct11 = TOT_ACT11,
26 Act12 = ACT_ID12,
27 TotAct12 = TOT_ACT12,
28 Act13 = ACT_ID13,
29 TotAct13 = TOT_ACT13,
30 Act14 = ACT_ID14,
31 TotAct14 = TOT_ACT14,
32 Act15 = ACT_ID15,
33 TotAct15 = TOT_ACT15,
34 Act16 = ACT_ID16,
35 TotAct16 = TOT_ACT16,
36 Act17 = ACT_ID17,
37 TotAct17 = TOT_ACT17,
38 Act18 = ACT_ID18,
39 TotAct18 = TOT_ACT18,
40 Act19 = ACT_ID19,
41 TotAct19 = TOT_ACT19,
42 Act20 = ACT_ID20,
43 TotAct20 = TOT_ACT20,
44 OpeningUnits = UNIT_OP,
45 OPricePerUnit = PRICE_OP,
46 ClosingUnits = UNIT_CL,
47 CPricePerUnit = PRICE_CL,
48 AllocationPercent = i.ALLOC_PER1
49
50 FROM
51 Statements..ParticipantPlanFundBalances1 pfb
52 JOIN (
53 Select
54 cp.PlanId,
55 p.ParticipantId,
56 @PeriodId Period,
57 f.FundId,
58 a.ACT_ID1,
59 a.TOT_ACT1,
60 a.ACT_ID2,
61 a.TOT_ACT2,
62 a.ACT_ID3,
63 a.TOT_ACT3,
64 a.ACT_ID4,
65 a.TOT_ACT4,
66 a.ACT_ID5,
67 a.TOT_ACT5,
68 a.ACT_ID6,
69 a.TOT_ACT6,
70 a.ACT_ID7,
71 a.TOT_ACT7,
72 a.ACT_ID8,
73 a.TOT_ACT8,
74 a.ACT_ID9,
75 a.TOT_ACT9,
76 a.ACT_ID10,
77 a.TOT_ACT10,
78 a.ACT_ID11,
79 a.TOT_ACT11,
80 a.ACT_ID12,
81 a.TOT_ACT12,
82 a.ACT_ID13,
83 a.TOT_ACT13,
84 a.ACT_ID14,
85 a.TOT_ACT14,
86 a.ACT_ID15,
87 a.TOT_ACT15,
88 a.ACT_ID16,
89 a.TOT_ACT16,
90 a.ACT_ID17,
91 a.TOT_ACT17,
92 a.ACT_ID18,
93 a.TOT_ACT18,
94 a.ACT_ID19,
95 a.TOT_ACT19,
96 a.ACT_ID20,
97 a.TOT_ACT20,
98 a.UNIT_OP,
99 a.PRICE_OP,
100 a.UNIT_CL,
101 a.PRICE_CL,
102 Cast(i.ALLOC_PER1 as int)
103 FROM
104 ASDBF a
105 -- Derive the unique PlanId from the Statements ClientPlan table
106 INNER JOIN Statements..ClientPlan cp
107 ON a.PLAN_NUM = cp.ClientPlanId
108 AND
109 cp.ClientId = @ClientId
110 -- Derive the unique ParticipantId from the Statements Participant table
111 INNER JOIN Statements..Participant p
112 ON a.PART_ID = p.PartId
113 --Derive the unique FundID from the Statements Fund Table...
114 INNER JOIN Statements..Fund f
115 ONa.FUND_ID = f.Cusip
116 OR
117 a.FUND_ID = f.Ticker
118 OR
119 a.FUND_ID = f.ClientFundId
120 -- get the allocation percent from the INVSRC
121 LEFT Outer JOIN INVSRC i
122 ONa.FUND_ID = i.INV_ID
123 AND
124 a.PLAN_NUM = i.Plan_Number
125 AND
126 a.PART_ID = i.PART_ID
127 --INNER JOIN Statements..AscActCodes as
128 --ON
129
130 WHERE
131 a.Import = 1
132 )a
133 ON pfb.PlanId = a.PlanId
134 AND
135 pfb.ParticipantId = a.ParticipantId
136 AND
137 pfb.PeriodId = PeriodId
138 AND
139 pfb.FundId = a.FundId
140
 
Any help will be appreciated
Regards
Karen

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved