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 Tables with DrillDown in one report

Hi NG,
i try to put 3 tables on a report who filled up by three different named
datasets (but with same structure).
All datasets are filled from the a AS-cube with the same MDX-Statement. The
difference between the datasets are the different parameters.
And here is my problem:
I start to develop the report with the designer...integrate the first
MDX-Statement and create a drillDown-Table. Everything works fine. After
that i put another table on the reportpage, bind the second dataset on it,
insert grouping to the table and so on. But the drillDown wont
work....there is no show-and-hide toggle (the +-sign)
Also...i could not copy the first table and put it on the same report...if
i do it, i get compile-errors.
Anybody here with an example which includes tow tables with drilldown who
based on the same dataset?
regards
FrankFrank Matthiesen wrote:
forget the question...i was to blind. I fixed it.
frank

Multiple Table names in result data set.

I have the below query. Basically it takes from a table the names of other tables. This seems to work but the result data set has tables named as Table, Table1, etc. How can I change the names of the tables in the result set?

Thank you.

Kevin

BEGIN

SET NOCOUNT ON;

DECLARE @.TableName VARCHAR(50)

DECLARE ReasonCategory_Cursor CURSOR FOR

SELECT CATEGORY

FROM CaseNoteReasonCategories

SELECT CATEGORY

FROM CaseNoteReasonCategories

OPEN ReasonCategory_Cursor;

FETCH NEXT FROM ReasonCategory_Cursor INTO @.TableName;

WHILE @.@.FETCH_STATUS = 0

BEGIN

EXEC ('SELECT * FROM ' + @.TableName + ' AS ' + @.TableName);

FETCH NEXT FROM ReasonCategory_Cursor INTO @.TableName;

END;

CLOSE ReasonCategory_Cursor;

DEALLOCATE ReasonCategory_Cursor;

END

Your query returns the names of the tables as stored in the CATEGORY column of the CaseNoteReasonCategories table.

If you want different table names, you might try changing the values in the Category column.

|||

The problem is that the name(s) of the tables do not correspond to the names in the category column. It seems that the names of the tables is fixed at Table, Table1, Table2, etc. This seems to be the case no matter what the names of the tables are in the CATEGORY column.

I have started to look some more into this and this may be an aritfact of ADO.NET. But I am not sure how to override what it is doing so I get the names of the tables corresponding to the names in the CATEGORY column.

|||

I don't believe this is an issue with ADO.NET. Your query is pulling the actual values out of the Category column of the CaseNoteReasonCategories. I suspect that there is some 'test' data in the table and that is what you are seeing.

Your query does not have a WHERE clause, so it will be gathering all rows from CaseNoteReasonCategories, and I suspect Table1, Table2, etc., are the first rows in the table.

Also, in the line below, the + ' AS ' + @.TableName has no value.

EXEC ('SELECT * FROM ' + @.TableName + ' AS ' + @.TableName);

|||

Arnie Rowland wrote:

I don't believe this is an issue with ADO.NET. Your query is pulling the actual values out of the Category column of the CaseNoteReasonCategories. I suspect that there is some 'test' data in the table and that is what you are seeing.

Your query does not have a WHERE clause, so it will be gathering all rows from CaseNoteReasonCategories, and I suspect Table1, Table2, etc., are the first rows in the table.

Also, in the line below, the + ' AS ' + @.TableName has no value.

EXEC ('SELECT * FROM ' + @.TableName + ' AS ' + @.TableName);

The values in the Category column are the names of the tables. These values are NOT Table1, Table2, etc.

I don't have a WHERE clause because I want all of the rows from CaseNoteReasonCategories. Table1, Table2 are not the first rows in this table.

Adding the 'AS' to the end of the query seems to have not effect. I have also tried

EXEC ('SELECT * FROM ' + @.TableName + ' ' + @.TableName);

|||

It is quite a mystery where those table names are being 'automagically' created...

My point was that assigning an alias for the table (with or without using 'AS') has no value since you are not using the alias in your code. It is wasted effort -albeit of little consequence.

multiple sessions when using ADO

Hi
I have an application that uses an ADOConnection named CONNECTION1 and an AD
ODataset.The dataset fetches a record for editing it.This dataset has no Con
nectionString and its connection is the CONNECTION1.After opening the datase
t and editing record (befor
posting it) I execute a Stord Procedure to update another record in another
table with CONNECTION1.EXECUTE.
After opening the dataset I see a row in Management -> Current Activity -> P
rocess Info in EnterPrise Manager.And After CONNECTION.EXECUTE statement I s
ee 2 rows in Enterprise Manager.
I do all data modification by one ADOConnection in Delphi5 but SQL SERVER 20
00 do these modification by separate SPIDs (sessions).
How can I prevent this?
Dose ADOConnection or ADODataset have any property to prevent that? Or shoul
d I do anything in SQL SERVER?
ThanksYou may not be able to prevent it. ADO and ODBC, for that matter, will
spawn connections as they see fit. In most cases it does this because the
first SPID is in the middle of some process that has not completed, so the
second SPID is necesary to perform any other task. Normally this does not
cause any problems.
Rand
This posting is provided "as is" with no warranties and confers no rights.

multiple sessions when using ADO

Hi
I have an application that uses an ADOConnection named CONNECTION1 and an ADODataset.The dataset fetches a record for editing it.This dataset has no ConnectionString and its connection is the CONNECTION1.After opening the dataset and editing record (befor
posting it) I execute a Stord Procedure to update another record in another table with CONNECTION1.EXECUTE.
After opening the dataset I see a row in Management -> Current Activity -> Process Info in EnterPrise Manager.And After CONNECTION.EXECUTE statement I see 2 rows in Enterprise Manager.
I do all data modification by one ADOConnection in Delphi5 but SQL SERVER 2000 do these modification by separate SPIDs (sessions).
How can I prevent this?
Dose ADOConnection or ADODataset have any property to prevent that? Or should I do anything in SQL SERVER?
Thanks
You may not be able to prevent it. ADO and ODBC, for that matter, will
spawn connections as they see fit. In most cases it does this because the
first SPID is in the middle of some process that has not completed, so the
second SPID is necesary to perform any other task. Normally this does not
cause any problems.
Rand
This posting is provided "as is" with no warranties and confers no rights.