Computed columns

I have a table with fields called fname (First Name) and lname (Last
Name). I need the users email thai is compose from lname and fname:
LOWER(LEFT (fname,1) + lname)

Is there any difference between creatig this computed column ia a table
or in a view in SQL Server 2000?

I can do:

1. CREATE TABLE Users(
fname varchar(20),
lname varchar(20),
email as LOWER(LEFT (fname,1) + lname) )

Or

2. CREATE TABLE Users (
fname varchar(20),
lname varchar(20))

CREATE VIEW Vw_users (fname, Lname ,
email)
AS
SELECT fname, Lname ,
LOWER(LEFT (fname,1) + lname) )

Is one of them is better?

Paulo

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Paulo Andre Ortega Ribeiro" <paulo.andre.66@.terra.com.br> wrote in message
news:3fa28c1c$0$201$75868355@.news.frii.net...
> I have a table with fields called fname (First Name) and lname (Last
> Name). I need the users email thai is compose from lname and fname:
> LOWER(LEFT (fname,1) + lname)
> Is there any difference between creatig this computed column ia a table
> or in a view in SQL Server 2000?
> I can do:
> 1. CREATE TABLE Users(
> fname varchar(20),
> lname varchar(20),
> email as LOWER(LEFT (fname,1) + lname) )
> Or
> 2. CREATE TABLE Users (
> fname varchar(20),
> lname varchar(20))
> CREATE VIEW Vw_users (fname, Lname ,
> email)
> AS
> SELECT fname, Lname ,
> LOWER(LEFT (fname,1) + lname) )
>
> Is one of them is better?
> Paulo
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Adding a computed column means the value is computed on demand (unless you
index that column) when you query the table. The view is rather like storing
a query definition in the database, so the value is 'calculated' only when
you query the view (unless you index the view). In that sense, both
approaches are very similar.

However, the view is a separate object, which means you can have separate
security permissions on it. A view will also be more portable to other
database systems, if that is a consideration for you.

In any case, neither solution is a good one, assuming that this isn't a
theoretical question. You will quickly have duplicate email addresses, even
with just a few names. If you have John Smith, and then James Smith is
added, their email addresses would be the same, so the best all-round
solution is to use a normal table column for the email address.

Simon

composite index and column order

Hi,

I created a composite index (lastname, firstname). I know the following
queries will use this index:

WHERE lastname = ...
WHERE lastname = ... AND firstname = ...

Also this won't use the index:
WHERE firstname = ...

But how about: WHERE firstname = .. AND lastname = ...

And why?

Thanks a lot,

Baihao

--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORGBaihao Yuan wrote:
> Hi,
> I created a composite index (lastname, firstname). I know the following
> queries will use this index:
> WHERE lastname = ...
> WHERE lastname = ... AND firstname = ...
> Also this won't use the index:
> WHERE firstname = ...
> But how about: WHERE firstname = .. AND lastname = ...

It will use the index.

> And why?

Because, as far as the query optimizer is concerned, these two are
exactly the same:

WHERE lastname = ... AND firstname = ...
WHERE firstname = ... AND lastname = ...|||Thanks for your help, I really appreciate it.

Baihao

--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG|||Baihao Yuan wrote:
> Hi,
> I created a composite index (lastname, firstname). I know the following
> queries will use this index:
> WHERE lastname = ...
> WHERE lastname = ... AND firstname = ...
> Also this won't use the index:
> WHERE firstname = ...

Not necessarily. Consider the following query:

select lastname, firstname from some_table where firstname = ...

It will use the index, and, more to the point, it will not touch the
table at all - the index already has all the information the query
needs. It is called "index covering".|||Alexander Kuznetsov wrote:
> Baihao Yuan wrote:
> > Hi,
> > I created a composite index (lastname, firstname). I know the following
> > queries will use this index:
> > WHERE lastname = ...
> > WHERE lastname = ... AND firstname = ...
> > Also this won't use the index:
> > WHERE firstname = ...
> Not necessarily. Consider the following query:
> select lastname, firstname from some_table where firstname = ...
> It will use the index, and, more to the point, it will not touch the
> table at all - the index already has all the information the query
> needs. It is called "index covering".

No, it won't. If you had a list of people on a piece of paper, sorted
by last name and then by first name, explain how you would use that
list to find everyone with a first name of "Joe" without looking
through the entire list.|||I created a table called tblNames with nonClustered index defined on
lastname,firstname (composite index).

select * from tblnames where lastname = 'smith'--Performed an Index
Seek

select * from tblnames where lastname = 'smith' and firstname =
'john'--Performed an Index Seek

select * from tblnames where firstname = 'john'--Performed a Table Scan

select * from tblnames where firstname = 'john' and lastname =
'smith'--Performed an Index Seek

ZeldorBlat wrote:
> Alexander Kuznetsov wrote:
> > Baihao Yuan wrote:
> > > Hi,
> > > > I created a composite index (lastname, firstname). I know the following
> > > queries will use this index:
> > > > WHERE lastname = ...
> > > WHERE lastname = ... AND firstname = ...
> > > > Also this won't use the index:
> > > WHERE firstname = ...
> > > Not necessarily. Consider the following query:
> > select lastname, firstname from some_table where firstname = ...
> > It will use the index, and, more to the point, it will not touch the
> > table at all - the index already has all the information the query
> > needs. It is called "index covering".
> No, it won't. If you had a list of people on a piece of paper, sorted
> by last name and then by first name, explain how you would use that
> list to find everyone with a first name of "Joe" without looking
> through the entire list.|||> > Not necessarily. Consider the following query:
> > select lastname, firstname from some_table where firstname = ...
> > It will use the index, and, more to the point, it will not touch the
> > table at all - the index already has all the information the query
> > needs. It is called "index covering".
> No, it won't. If you had a list of people on a piece of paper, sorted
> by last name and then by first name, explain how you would use that
> list to find everyone with a first name of "Joe" without looking
> through the entire list.

Why don't you try it out in practice? You might be in for some
surprise. If the index is smaller than the table, and contains all the
necessary information, it is likely to be used instead of the table.
Google up "index covering".|||Alexander Kuznetsov wrote:
> > > Not necessarily. Consider the following query:
> > > > select lastname, firstname from some_table where firstname = ...
> > > > It will use the index, and, more to the point, it will not touch the
> > > table at all - the index already has all the information the query
> > > needs. It is called "index covering".
> > No, it won't. If you had a list of people on a piece of paper, sorted
> > by last name and then by first name, explain how you would use that
> > list to find everyone with a first name of "Joe" without looking
> > through the entire list.
> Why don't you try it out in practice? You might be in for some
> surprise. If the index is smaller than the table, and contains all the
> necessary information, it is likely to be used instead of the table.
> Google up "index covering".

I did try it in practice -- as did the OP who posted his results in
this thread.|||>
> select * from tblnames where firstname = 'john'--Performed a Table Scan

If you only select 2 columns, firstname, lastname

select columns, firstname from tblnames where firstname = 'john'

and the table has a lot of other columns, the index is likely to be
used even if firstname is not the first column in the index. The reason
is simple: the index contains all the information necessary to satisfy
the query and it is smaller than the table.|||> I did try it in practice -- as did the OP who posted his results in
> this thread.

the OP tried for

select * from ...

while I was speaking aobut

select lastName, firstname from ...

Big difference.|||correction:

If you only select 2 columns, firstname, lastname

select firstname, lastname from tblnames where firstname = 'john'

> and the table has a lot of other columns, the index is likely to be
> used even if firstname is not the first column in the index. The reason
> is simple: the index contains all the information necessary to satisfy
> the query and it is smaller than the table.|||ZeldorBlat (zeldorblat@.gmail.com) writes:
> No, it won't. If you had a list of people on a piece of paper, sorted
> by last name and then by first name, explain how you would use that
> list to find everyone with a first name of "Joe" without looking
> through the entire list.

Say further that with each list there is a page number to references
where the persons appear in the book.

If all you want to know is the name of the persons, you can scan
the index, you don't have to read the whole book.

It's important to keep in mind that an index can be used in two
ways: Seek (look up data through the index tree) and Scan (read
the entire index from left to right): While the latter is far more
expensive, it can still be useful at times.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
> ZeldorBlat (zeldorblat@.gmail.com) writes:
> > No, it won't. If you had a list of people on a piece of paper, sorted
> > by last name and then by first name, explain how you would use that
> > list to find everyone with a first name of "Joe" without looking
> > through the entire list.
> Say further that with each list there is a page number to references
> where the persons appear in the book.
> If all you want to know is the name of the persons, you can scan
> the index, you don't have to read the whole book.
> It's important to keep in mind that an index can be used in two
> ways: Seek (look up data through the index tree) and Scan (read
> the entire index from left to right): While the latter is far more
> expensive, it can still be useful at times.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Point taken. Thanks, Erland.|||I'd like to repeat the suggestion to do your own experimenting. The
technology evolves quite fast, so anything you might have read in any
book / article / whatever esle may be already obsolete. The optimizer
is way smarter now than it used to be 5 or 10 years ago. In this
particular case you did not need to read anything, you could just take
any table of, say, 100K rows, with, say, 20 columns, create an index on
it

create index i1 on t1(col1, col2)

and see the execution plan for the query

select col1, col2 from t1 where col2 =...

That's all it takes, it's that simple.

Good luck!

Complex query. Can anyone solve this one

Hi
A little complex one
What i really want is that my table contains duplicate
firstname,lastname and dob.
what I need to do is check the two fields datecreated and
datelastupdated and retain the latest record based on
date of these two columns and update the other records
with OLD.
so the result I want is retain row 1 as datelastupdated
is latest for John Li as it is and update the 2 John Li
as John (OLD) Li
and also retain record 5 as datecreated is the latest so
retain this one and update the other one as OLD.
I have about 700 records to do and some are 2 duplicates
and some 3.
How can i write a query/cursor to solve this one.
DDL and DML are posted
CREATE TABLE Employees (
[EmployeeId] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [varchar] (45) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastName] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Dob] [datetime] NOT NULL ,
[DateCreated] [datetime] NOT NULL ,
[DateLastUpdated] [datetime] NULL ,
[EmployerId] [int] NOT NULL
) ON [PRIMARY]
GO
insert into employees
values('John','Li','1971-02-09 00:00:00','2004-12-06
12:20:48.577','2005-02-01 17:21:33.047',111)
insert into employees
values('John','Li','1971-02-09 00:00:00','2004-03-26
12:20:48.577','2004-02-01 17:21:33.047',111)
insert into employees
values('John','Li','1971-02-09 00:00:00','2004-12-10
12:20:48.577','2003-02-01 17:21:33.047',111)
insert into employees
values('Tom','Lan','1979-05-07 00:00:00','2005-03-22
14:00:15.207','2004-04-21 13:20:20.273',111)
insert into employees
values('Tom','Lan','1979-05-07 00:00:00','2004-10-22
14:00:15.207','2004-09-25 13:20:20.273',111)
--list the duplicates
select firstname,lastname,dob,count(*) as count
from employees e
group by firstname,lastname,dob
having count(*)>1
order by count(*) desc
--list the table
select * from employeesHi
Adding a status column...
CREATE TABLE Employees (
[EmployeeId] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [varchar] (45) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastName] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Dob] [datetime] NOT NULL ,
[DateCreated] [datetime] NOT NULL ,
[DateLastUpdated] [datetime] NULL ,
[EmployerId] [int] NOT NULL,
[Status] CHAR(3) NULL
) ON [PRIMARY]
GO
insert into employees( [FirstName],
[LastName],
[Dob],
[DateCreated],
[DateLastUpdated],
[EmployerId] )
values('John','Li','19710209 00:00:00','20041206 12:20:48.577','20050201
17:21:33.047',111)
insert into employees( [FirstName],
[LastName],
[Dob],
[DateCreated],
[DateLastUpdated],
[EmployerId] )
values('John','Li','19710209 00:00:00','20040326 12:20:48.577','20040201
17:21:33.047',111)
insert into employees( [FirstName],
[LastName],
[Dob],
[DateCreated],
[DateLastUpdated],
[EmployerId] )
values('John','Li','19710209 00:00:00','20041210 12:20:48.577','20030201
17:21:33.047',111)
insert into employees( [FirstName],
[LastName],
[Dob],
[DateCreated],
[DateLastUpdated],
[EmployerId] )
values('Tom','Lan','19790507 00:00:00','20050322 14:00:15.207','20040421
13:20:20.273',111)
insert into employees( [FirstName],
[LastName],
[Dob],
[DateCreated],
[DateLastUpdated],
[EmployerId] )
values('Tom','Lan','19790507 00:00:00','20041022 14:00:15.207','20040925
13:20:20.273',111)
--list the duplicates
select firstname,lastname,dob,count(*) as count
from employees e
group by firstname,lastname,dob
having count(*)>1
order by count(*) desc
--list the table
UPDATE e
SET status = 'Old'
FROM employees e
WHERE EXISTS ( SELECT 1 FROM Employees p where p.firstname = e.firstname and
p.lastname = e.lastname and p.dob = e.dob and
e.[DateLastUpdated] < p.[DateLastUpdated] )
select * from employees
This is ignoring datecreated as it seems to be obsolete once it has been on
older record has been updated!
John
"Apok" wrote:

> Hi
> A little complex one
> What i really want is that my table contains duplicate
> firstname,lastname and dob.
> what I need to do is check the two fields datecreated and
> datelastupdated and retain the latest record based on
> date of these two columns and update the other records
> with OLD.
> so the result I want is retain row 1 as datelastupdated
> is latest for John Li as it is and update the 2 John Li
> as John (OLD) Li
> and also retain record 5 as datecreated is the latest so
> retain this one and update the other one as OLD.
> I have about 700 records to do and some are 2 duplicates
> and some 3.
> How can i write a query/cursor to solve this one.
> DDL and DML are posted
> CREATE TABLE Employees (
> [EmployeeId] [int] IDENTITY (1, 1) NOT NULL ,
> [FirstName] [varchar] (45) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [LastName] [varchar] (30) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Dob] [datetime] NOT NULL ,
> [DateCreated] [datetime] NOT NULL ,
> [DateLastUpdated] [datetime] NULL ,
> [EmployerId] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> insert into employees
> values('John','Li','1971-02-09 00:00:00','2004-12-06
> 12:20:48.577','2005-02-01 17:21:33.047',111)
> insert into employees
> values('John','Li','1971-02-09 00:00:00','2004-03-26
> 12:20:48.577','2004-02-01 17:21:33.047',111)
> insert into employees
> values('John','Li','1971-02-09 00:00:00','2004-12-10
> 12:20:48.577','2003-02-01 17:21:33.047',111)
>
> insert into employees
> values('Tom','Lan','1979-05-07 00:00:00','2005-03-22
> 14:00:15.207','2004-04-21 13:20:20.273',111)
>
> insert into employees
> values('Tom','Lan','1979-05-07 00:00:00','2004-10-22
> 14:00:15.207','2004-09-25 13:20:20.273',111)
> --list the duplicates
> select firstname,lastname,dob,count(*) as count
> from employees e
> group by firstname,lastname,dob
> having count(*)>1
> order by count(*) desc
> --list the table
> select * from employees
>|||No But there is record where datecreated is the latest
one
your query does not cover that
I need to compare both the dates and find the latest one
leave that one and update the rest

>--Original Message--
>Hi
>Adding a status column...
>CREATE TABLE Employees (
> [EmployeeId] [int] IDENTITY (1, 1) NOT NULL ,
> [FirstName] [varchar] (45) COLLATE
>SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [LastName] [varchar] (30) COLLATE
>SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Dob] [datetime] NOT NULL ,
> [DateCreated] [datetime] NOT NULL ,
> [DateLastUpdated] [datetime] NULL ,
> [EmployerId] [int] NOT NULL,
> [Status] CHAR(3) NULL
> ) ON [PRIMARY]
>GO
>
>insert into employees( [FirstName],
>[LastName],
>[Dob],
>[DateCreated],
>[DateLastUpdated],
>[EmployerId] )
>values('John','Li','19710209 00:00:00','20041206
12:20:48.577','20050201
>17:21:33.047',111)
>insert into employees( [FirstName],
>[LastName],
>[Dob],
>[DateCreated],
>[DateLastUpdated],
>[EmployerId] )
>values('John','Li','19710209 00:00:00','20040326
12:20:48.577','20040201
>17:21:33.047',111)
>insert into employees( [FirstName],
>[LastName],
>[Dob],
>[DateCreated],
>[DateLastUpdated],
>[EmployerId] )
>values('John','Li','19710209 00:00:00','20041210
12:20:48.577','20030201
>17:21:33.047',111)
>
>insert into employees( [FirstName],
>[LastName],
>[Dob],
>[DateCreated],
>[DateLastUpdated],
>[EmployerId] )
>values('Tom','Lan','19790507 00:00:00','20050322
14:00:15.207','20040421
>13:20:20.273',111)
>
>insert into employees( [FirstName],
>[LastName],
>[Dob],
>[DateCreated],
>[DateLastUpdated],
>[EmployerId] )
>values('Tom','Lan','19790507 00:00:00','20041022
14:00:15.207','20040925
>13:20:20.273',111)
>--list the duplicates
>select firstname,lastname,dob,count(*) as count
>from employees e
>group by firstname,lastname,dob
>having count(*)>1
>order by count(*) desc
>--list the table
>UPDATE e
>SET status = 'Old'
>FROM employees e
>WHERE EXISTS ( SELECT 1 FROM Employees p where
p.firstname = e.firstname and
>p.lastname = e.lastname and p.dob = e.dob and
>e.[DateLastUpdated] < p.[DateLastUpdated] )
>select * from employees
>This is ignoring datecreated as it seems to be obsolete
once it has been on
>older record has been updated!
>John
>"Apok" wrote:
>
and
datelastupdated
Li
so
duplicates
>.
>|||Apok,
It's not clear what you mean by "latest record based on
date of these two columns." But here's a try:
update employees set
Status = 'Old'
where exists (
select * from employees as Ecopy
where Ecopy.FirstName = employees.FirstName
and Ecopy.LastName = employees.LastName
and (
(Ecopy.DateCreated > employees.DateCreated
and Ecopy.DateCreated > employees.DateLastUpdated)
or
(Ecopy.DateLastUpdated > employees.DateCreated
and Ecopy.DateLastUpdated > employees.DateLastUpdated)
)
)
Steve Kass
Drew University
Apok wrote:

>Hi
>A little complex one
>What i really want is that my table contains duplicate
>firstname,lastname and dob.
>what I need to do is check the two fields datecreated and
>datelastupdated and retain the latest record based on
>date of these two columns and update the other records
>with OLD.
>so the result I want is retain row 1 as datelastupdated
>is latest for John Li as it is and update the 2 John Li
>as John (OLD) Li
>and also retain record 5 as datecreated is the latest so
>retain this one and update the other one as OLD.
>I have about 700 records to do and some are 2 duplicates
>and some 3.
>How can i write a query/cursor to solve this one.
>DDL and DML are posted
>CREATE TABLE Employees (
> [EmployeeId] [int] IDENTITY (1, 1) NOT NULL ,
> [FirstName] [varchar] (45) COLLATE
>SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [LastName] [varchar] (30) COLLATE
>SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Dob] [datetime] NOT NULL ,
> [DateCreated] [datetime] NOT NULL ,
> [DateLastUpdated] [datetime] NULL ,
> [EmployerId] [int] NOT NULL
> ) ON [PRIMARY]
>GO
>
>insert into employees
>values('John','Li','1971-02-09 00:00:00','2004-12-06
>12:20:48.577','2005-02-01 17:21:33.047',111)
>insert into employees
>values('John','Li','1971-02-09 00:00:00','2004-03-26
>12:20:48.577','2004-02-01 17:21:33.047',111)
>insert into employees
>values('John','Li','1971-02-09 00:00:00','2004-12-10
>12:20:48.577','2003-02-01 17:21:33.047',111)
>
>insert into employees
>values('Tom','Lan','1979-05-07 00:00:00','2005-03-22
>14:00:15.207','2004-04-21 13:20:20.273',111)
>
>insert into employees
>values('Tom','Lan','1979-05-07 00:00:00','2004-10-22
>14:00:15.207','2004-09-25 13:20:20.273',111)
>--list the duplicates
>select firstname,lastname,dob,count(*) as count
>from employees e
>group by firstname,lastname,dob
>having count(*)>1
>order by count(*) desc
>--list the table
>select * from employees
>
>

Complex Query question. Using Sum and CAST operation.

Hi

Can onayone tell me what is wrong with the below code. Thank you,

SELECT P.FirstName, P.LastName, P.PortfolioID, P.PortfolioDescription, SC.Description, SC.SecurityID, SC.SecurityTypeID, SUM(T.Quantity) AS TQuantity,
CASE SC.SecurityTypeID WHEN 11 THEN CAST(SUM(T .Quantity)) WHEN 2 THEN CAST(SUM(T .Quantity *
(SELECT Price
FROM dbo.SecurityPrices AS SP WITH (Nolock)
WHERE T .SymbolID = SP.SecurityID AND PriceDate = '1/24/2007'))) / 100 ELSE CAST(SUM(T .Quantity *
(SELECT Price
FROM dbo.SecurityPrices AS SP WITH (Nolock)
WHERE T .SymbolID = SP.SecurityID AND PriceDate = '1/24/2007'))) END AS Amount, P.AccountNumber, P.AccountTypeDescription,
SC.CodeDescription, SC.CodeDescription2, SC.Symbol
FROM dbo.Transactions AS T WITH (Nolock) INNER JOIN
dbo.Portfolios AS P WITH (Nolock) ON P.PortfolioID = T.PortfolioID INNER JOIN
dbo.SFGSecuritiesAndCodes AS SC WITH (Nolock) ON T.SymbolID = SC.SecurityID INNER JOIN
dbo.Groups AS G WITH (Nolock) ON P.PortfolioID = G.PortfolioID
WHERE (T.StatusTypeID = 100) AND (G.OwningPortfolioID = 270) AND (T.TradeDate <= '1/24/2007')
GROUP BY T.SymbolID, P.FirstName, P.LastName, P.PortfolioID, SC.Description, SC.SecurityID, SC.SecurityTypeID, P.PortfolioDescription, P.AccountNumber,
P.AccountTypeDescription, SC.CodeDescription, SC.CodeDescription2, SC.Symbol

The correct syntax for CAST is CAST( <expr> as <datatype>). So fix your query and try again. Also, it is good to post the actual error message that you are getting so that we don't have to guess what might be wrong.|||

The following query will work for you..

Note: You Cannot perform an aggregate function on an expression containing an aggregate or a subquery; So you have to use INNER JOIN Instead of Subquery. It is good practice to use join rather than the subquery.

SELECT
P.FirstName,
P.LastName,
P.PortfolioID,
P.PortfolioDescription,
SC.Description,
SC.SecurityID,
SC.SecurityTypeID,
SUM(T.Quantity) AS TQuantity,
CASE SC.SecurityTypeID
WHEN 11 THEN
CAST(SUM(T .Quantity) As Int)
WHEN 2 THEN
CAST(SUM(T .Quantity * SP.Price) / 100 as INT)
ELSE
CAST(SUM(T .Quantity * SP.Price) as INT)
END AS Amount,
P.AccountNumber,
P.AccountTypeDescription,
SC.CodeDescription,
SC.CodeDescription2,
SC.Symbol
FROM
dbo.Transactions AS T WITH (Nolock)
INNER JOIN dbo.Portfolios AS P WITH (Nolock) ON P.PortfolioID = T.PortfolioID
INNER JOIN dbo.SFGSecuritiesAndCodes AS SC WITH (Nolock) ON T.SymbolID = SC.SecurityID
INNER JOIN dbo.Groups AS G WITH (Nolock) ON P.PortfolioID = G.PortfolioID
INNER JOIN dbo.SecurityPrices As SP WITH (Nolock) ON T.SymbolID = SP.SecurityID AND SP.PriceDate = '1/24/2007'
WHERE
(T.StatusTypeID = 100)
AND (G.OwningPortfolioID = 270)
AND (T.TradeDate <= '1/24/2007')
GROUP BY
T.SymbolID,
P.FirstName,
P.LastName,
P.PortfolioID,
SC.Description,
SC.SecurityID,
SC.SecurityTypeID,
P.PortfolioDescription,
P.AccountNumber,
P.AccountTypeDescription,
SC.CodeDescription,
SC.CodeDescription2,
SC.Symbol