Multiples in a select statement

Is it possible to write a single select statement that would pull the following from a table named tblPayments.

I need the 'first payment date and amount', 'last payment date and amount', 'largest payment date and amount', and 'total payments' for each client by a date range.

tblPayments
ClientID
PaymentDate
PaymentAmount

Thanks for any suggestions.

create table p (ClientID int, PaymentDate datetime, PaymentAmount numeric(10,2))
insert into p
select 0,'1/1/2004',100 union all
select 0,'1/2/2004',80 union all
select 0,'1/3/2004',110 union all
select 1,'1/5/2004',87 union all
select 1,'1/12/2004',180 union all
select 1,'1/13/2004',10
select
d.clientID,
d.firstPmtDate,
p1.PaymentAmount,
d.lastPmtDate,
p2.PaymentAmount,
p3.PaymentDate maxPmtDate,
d.maxPmt,
d.ttlPmt
from
(
select
clientID,
min(paymentdate) firstPmtDate,
max(paymentDate) lastPmtDate,
max(PaymentAmount) maxPmt,
sum(PaymentAmount) ttlPmt
from p
--where PaymentDate between @.startDate and @.endDate
group by clientid
) d
join p p1 on p1.clientid = d.clientid and p1.paymentdate = d.firstPmtDate
join p p2 on p2.clientid = d.clientid and p2.paymentdate = d.lastPmtDate
join p p3 on p3.clientid = d.clientid and p3.PaymentAmount = d.maxPmt
--where p3.PaymentDate between @.startDate and @.endDate

|||Thank you, but this does not appear to return the amounts associated with each last and first payment dates. That is where I run into problems. I could be reading it bad?

Thanks again,|||Did you run the query in QA with the sample data?|||Ok, It worked in QA. I'm having some trouble understanding how you did this. How can I learn to understand this type of process?

Thanks for any suggestions,|||I'm having trouble converting this to my data. Is there anything specific I should know to do this?

Thanks again,|||What are you having trouble with specifically?|||Well, I get no errors but there is no data being returned.

Thanks,|||To demonstrate the query, I created a table called p, which represents your table tblPayments, it is an example. Then the table p is aliased as p1, p2,p3 where the joins are occuring to simplify the columns assignments. The alias d is used to declare the derived table which perform the aggregate functions to obtain max,min,etc...

If you want to use this query for you purposes you can change the table assignment of p in the from and join clauses to tblPayments.

Does that help to clarify?|||Yes, and that is what I did and I know there is data but none is being returned. This very puzzling.

Thanks again,|||I had not changed the table name in the joins and had listed my table AS p in the From clause. Now I get data, but I get many records for each client. I was hoping to end up with one row for each client. Any thoughts on this?

Thanks again,|||Can you post the revised query you are using..|||Here it is. Thanks,

select

d.Client_ID,

d.firstPmtDate,

p1.AmountPaid,

d.lastPmtDate,

p2.AmountPaid,

p3.PaymentDate maxPmtDate,

d.maxPmt,

d.ttlPmt

from

(

select

Client_ID,

min(paymentdate) firstPmtDate,

max(paymentDate) lastPmtDate,

max(AmountPaid) maxPmt,

sum(AmountPaid) ttlPmt

from tblPayments

--where PaymentDate between @.startDate and @.endDate

group by Client_ID

) d

join tblPayments p1 on p1.Client_ID = d.Client_ID and p1.paymentdate = d.firstPmtDate

join tblPayments p2 on p2.Client_ID = d.Client_ID and p2.paymentdate = d.lastPmtDate

join tblPayments p3 on p3.Client_ID = d.Client_ID and p3.AmountPaid = d.maxPmt

--where p3.PaymentDate between @.startDate and @.endDate|||It is somewhat difficult to fully understand the data behavior w/o seeing the data and variations that occur. If the rows are displaying duplicate data for each client you can modify the main select to select distinct which will limit dups from the results set.

Hope that helps.sql

Multiple view from a single select statement

Hi,
I want to write an SQL query which should return me 2 kinds of outputs depending on which condition is true. i.e.

the query should be something like

select someview if (condition1 = true)
else select someotherview if (condition2 = true)

where someview is a set of columns from one table only, and someotherview is a set of columns which is a superset of someview (& is generated from two tables, which have no common field)

Let me explain this further --
I want to select data from one table and a single field from other table, however if I the first table does not return any data, I still want data from the other table to be returned.
Can I write a SQL query to do this?

-- Amitdo a union query.

select * from table1
union
select 'ed' from table2;

if table 1 is return not rows table2 will still return data.

Does this answer your question?|||Originally posted by edwinjames
do a union query.

select * from table1
union
select 'ed' from table2;

if table 1 is return not rows table2 will still return data.

Does this answer your question?

I know, that I can use a union, however I want to support TimesTen & Oracle using thsame query...TT does not allow union while oracle does, can I write another query which emulates union?|||I am sorry I am not farmiliar with TT.

Does it support outer joins?|||Originally posted by edwinjames
I am sorry I am not farmiliar with TT.

Does it support outer joins?

Yes it dows, hence I am now using outer joins|||Glad to be of help

:)

Multiple values in one column

I'm trying to write a query which allows that multiple values from one
column are placed in one record.

ex:
table
NrLetters
1A
2A
2B
2C
3A
3B
3C
3D
3E
4A

The result I want to get from an select:
NrAll Letters
1A
2A, B, C
3A, B, C, D, E
4A

OlivierOlivier (olivier.lammens@.belgacom.be) writes:
> I'm trying to write a query which allows that multiple values from one
> column are placed in one record.
> ex:
> table
> Nr Letters
> 1 A
> 2 A
> 2 B
> 2 C
> 3 A
> 3 B
> 3 C
> 3 D
> 3 E
> 4 A
> The result I want to get from an select:
> Nr All Letters
> 1 A
> 2 A, B, C
> 3 A, B, C, D, E
> 4 A

You cannot do this in a single query in SQL 2000. And this is not a
thing you are supposed to do in a relational database, because repeating
values in a column is not supposed to occur.

You can achieve it by an iterative solution and aggregat into a temp
table. (Some people may suggest shortcuts for this, but they are not
reliable.) But in essence, this is really something that should be performed
on the client side.

On a side note, this is actually possible to do in SQL2005, currently in
beta, by using some XML extensions. But it still not really a relational
thing to do, so the client is still the best place for the work.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||olivier.lammens@.belgacom.be (Olivier) wrote in message news:<66db75c6.0412020207.4eb3a569@.posting.google.com>...
> I'm trying to write a query which allows that multiple values from one
> column are placed in one record.
> ex:
> table
> NrLetters
> 1A
> 2A
> 2B
> 2C
> 3A
> 3B
> 3C
> 3D
> 3E
> 4A
> The result I want to get from an select:
> NrAll Letters
> 1A
> 2A, B, C
> 3A, B, C, D, E
> 4A
> Olivier

Hey, man!
Try to have a look at http://www.aspfaq.com/show.asp?id=2529
You have to write a UDF to concatenate first.
HTH|||"Olivier" <olivier.lammens@.belgacom.be> wrote in message news:66db75c6.0412020207.4eb3a569@.posting.google.c om...
> I'm trying to write a query which allows that multiple values from one
> column are placed in one record.
> ex:
> table
> Nr Letters
> 1 A
> 2 A
> 2 B
> 2 C
> 3 A
> 3 B
> 3 C
> 3 D
> 3 E
> 4 A
> The result I want to get from an select:
> Nr All Letters
> 1 A
> 2 A, B, C
> 3 A, B, C, D, E
> 4 A
> Olivier

Just get a copy of iAnywhere's Adaptive Server Anywhere (ASA), which includes the List() aggregate function (and has had
it for about 8 years).

-Paul-|||The result you want isn't much use in a database - it's really a
formatted report for display or printing. For this reason it's probably
best done in your presentation tier rather than in SQL. If you have no
other option, however, you may be able to do something like this:

SELECT T.nr,
MAX(CASE WHEN seq=1 THEN letter END)+
MAX(CASE WHEN seq=2 THEN ','+letter ELSE '' END)+
MAX(CASE WHEN seq=3 THEN ','+letter ELSE '' END)+
MAX(CASE WHEN seq=4 THEN ','+letter ELSE '' END)+
MAX(CASE WHEN seq=5 THEN ','+letter ELSE '' END)+
MAX(CASE WHEN seq=6 THEN ','+letter ELSE '' END)
/* ... etc ... */
FROM
(SELECT T1.nr, T1.letter, COUNT(*) AS seq
FROM YourTable AS T1
JOIN YourTable AS T2
ON T1.nr = T2.nr
AND T1.letter >= T2.letter
GROUP BY T1.nr, T1.letter) AS T
GROUP BY T.nr

I'm assuming here that (nr,letter) is unique and non-NULL.
--
David Portas
SQL Server MVP
--

Multiple Sort Order Specification in EDB database

Hi,

I am using the EDB as a database in smart phone applications.I can able to mount the database , create the tables , write the records into the tables and read from the table with single sort order specification.But if i am using more than one sort order specification seek database is throwing an error message "The drive cannot locate a specific area or track on the disk".

I created one table named as Icon with more than one sort order specification.In this table i am giving pageid and iconid as sort order specifications. While saving the data into the database i am opening the database with pageid sort order and storing into the table.while reading from the database i am opening the database with iconid as sortorder.So here while seeking the database the above error is displaying.And i tried to read the data from the table by using pagid as sortorder speicification.It is working fine.I am applying the 3 sort order specifications for another table named as contentTable. but it is working fine with 3 sort order specifications.I had done the samething for icon table .But no use.Please help me regarding this.

Thanks in Advance,

Thanks & Regards,

Prasanna Kumar

Moving to Sql Server compact edition forum where it has got better chances of being answered.

-Thanks,

Mohit

Multiple Server query

I'm trying to write a query that will be used in a stored
proc to grab data from a table on a different server.
So I have SVR1 trying to get data from SVR2, so I'm trying
to use this query:
SELECT * FROM SVR2..TESTDB.TESTTBL
but I get the following error:
OLE DB provider 'SVR2' does not contain
table '"dbo"."master"'. The table either does not exist
or the current user does not have permissions on that
table.
OLE DB error trace [Non-interface error: OLE DB provider
does not contain the table: ProviderName='SVR2',
TableName='"dbo"."master"'].
Can anyone help?
Thanks,
Chris
Chris,
It seems more likely that you would want
select * from SVR2.TESTDB..TESTTBL
though I'm not quite sure why you get this particular error. What did
you use to add the server SVR2?
Steve Kass
Drew University
Chris W wrote:

>I'm trying to write a query that will be used in a stored
>proc to grab data from a table on a different server.
>So I have SVR1 trying to get data from SVR2, so I'm trying
>to use this query:
>SELECT * FROM SVR2..TESTDB.TESTTBL
>but I get the following error:
>OLE DB provider 'SVR2' does not contain
>table '"dbo"."master"'. The table either does not exist
>or the current user does not have permissions on that
>table.
>OLE DB error trace [Non-interface error: OLE DB provider
>does not contain the table: ProviderName='SVR2',
>TableName='"dbo"."master"'].
>
>Can anyone help?
>Thanks,
>Chris
>
>