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 value on y axis in chart?

Friends, I would like to put employee name and type of leave on the y-axis and on the x-axis the date, so that users can select the date start and end and employee/employees and see the chart or leaves.

But how to do that?

My fields are: Name, LeaveDescription and Date.

The data with me is for each employee, for each date, leave codes are there.

You would drag and drop name and leave description to the category fields (y-axis) and date to data fields (x-axis).|||

Dear ??€?§Q?,

It gives me this error:

More than one data set, data region, or grouping in the report has the name 'chart1_CategoryGroup1'. Data set, data region, and grouping names must be unique within a report.

Please help me.

Thanks,

|||Are you trying to drag and drop the same field twice to the same axis? Find where chart1_CategoryGroup1 is being added multiple times to a dataset, data region or grouping...|||

Dear ??€?§Q?,

Yeah that worked. Thanks.

But the thing is that, It gives me description on x axis and not on y.

Multiple value on y axis in chart?

Friends, I would like to put employee name and type of leave on the y-axis and on the x-axis the date, so that users can select the date start and end and employee/employees and see the chart or leaves.

But how to do that?

My fields are: Name, LeaveDescription and Date.

The data with me is for each employee, for each date, leave codes are there.

You would drag and drop name and leave description to the category fields (y-axis) and date to data fields (x-axis).|||

Dear ??€?§Q?,

It gives me this error:

More than one data set, data region, or grouping in the report has the name 'chart1_CategoryGroup1'. Data set, data region, and grouping names must be unique within a report.

Please help me.

Thanks,

|||Are you trying to drag and drop the same field twice to the same axis? Find where chart1_CategoryGroup1 is being added multiple times to a dataset, data region or grouping...|||

Dear ??€?§Q?,

Yeah that worked. Thanks.

But the thing is that, It gives me description on x axis and not on y.

Multiple value on y axis in chart?

Friends, I would like to put employee name and type of leave on the y-axis and on the x-axis the date, so that users can select the date start and end and employee/employees and see the chart or leaves.

But how to do that?

My fields are: Name, LeaveDescription and Date.

The data with me is for each employee, for each date, leave codes are there.

You would drag and drop name and leave description to the category fields (y-axis) and date to data fields (x-axis).|||

Dear ??€?§Q?,

It gives me this error:

More than one data set, data region, or grouping in the report has the name 'chart1_CategoryGroup1'. Data set, data region, and grouping names must be unique within a report.

Please help me.

Thanks,

|||Are you trying to drag and drop the same field twice to the same axis? Find where chart1_CategoryGroup1 is being added multiple times to a dataset, data region or grouping...|||

Dear ??€?§Q?,

Yeah that worked. Thanks.

But the thing is that, It gives me description on x axis and not on y.

Multiple Time Dimensions in a cube

I have a fact table that includes multiple date fields OrderEntryDateId, WantDateId, ShippedDateId, InvoicedDateId.

Can I join time to more than one field or do I need to create 4 different cubes?

Thanks,

Chris

Hello. Provided that you have the same keys in the time dimensions table as for each date in your fact table, you can use this single time dimension for all time keys in the fact table.

You design the relations(primary key and foreign key) in the data source view, that you create before the cube.

When you build your cube i BI-Dev Studio, with the wizard, it will take care of creating different cube time dimensions for each fact table date field.

So one time dimension table will work in SSAS2005.

HTH

Thomas Ivarsson

|||

I am using SSAS2000. Does this mean I need to upgrade? I don't see a way to do this in 2000.

|||

Hello. In SSAS2000 you will have to build views in order to clone your original time dimension. Each time view will have to be joined to the fact table.

Upgrade or not? This depends. Some day you will have to.

Role playing dimensions(that i have decribed to you) is not the most important feature in SSA2005.

Regards

Thomas Ivarsson

|||

Are you saying to make a view in SQL Server, or is this something that is done in SSAS 2000.

Thanks

|||

Hello again.

With SSAS2000 you will have to build the view in SQL Server.

Regards

Thomas Ivarsson

Multiple Time Dimensions in a cube

I have a fact table that includes multiple date fields OrderEntryDateId, WantDateId, ShippedDateId, InvoicedDateId.

Can I join time to more than one field or do I need to create 4 different cubes?

Thanks,

Chris

Hello. Provided that you have the same keys in the time dimensions table as for each date in your fact table, you can use this single time dimension for all time keys in the fact table.

You design the relations(primary key and foreign key) in the data source view, that you create before the cube.

When you build your cube i BI-Dev Studio, with the wizard, it will take care of creating different cube time dimensions for each fact table date field.

So one time dimension table will work in SSAS2005.

HTH

Thomas Ivarsson

|||

I am using SSAS2000. Does this mean I need to upgrade? I don't see a way to do this in 2000.

|||

Hello. In SSAS2000 you will have to build views in order to clone your original time dimension. Each time view will have to be joined to the fact table.

Upgrade or not? This depends. Some day you will have to.

Role playing dimensions(that i have decribed to you) is not the most important feature in SSA2005.

Regards

Thomas Ivarsson

|||

Are you saying to make a view in SQL Server, or is this something that is done in SSAS 2000.

Thanks

|||

Hello again.

With SSAS2000 you will have to build the view in SQL Server.

Regards

Thomas Ivarsson

Multiple table query

I just inhertied a database. In it are multiple tables that keep track of a
users profile. In each table is a modified_date field. That date only
relates to that table and not the entire profile. There was never a central
modified_date for the user.
I need to be able to check the users profile to see when the last time they
made an update. I am looking for a way to query all the tables
modified_date and get the most recent one date.While you're busy preparing the DDL and some sample data, here's a wild gues
s:
select max(combined_set.modified_date) as last_modified_date
from (
select modified_date
,user_id
from table_1
union
select modified_date
,user_id
from table_1
union
select modified_date
,user_id
from table_1
-- ...add more tables here...
) combined_set
where (user_id = @.user_id)
ML|||If you don't like the way the question is presented then feel free not to
answer it. Adding wise ass comments doesn't help anyone.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:B395E91D-77AB-4739-8712-E7A8AAF15508@.microsoft.com...
> While you're busy preparing the DDL and some sample data, here's a wild
> guess:
> select max(combined_set.modified_date) as last_modified_date
> from (
> select modified_date
> ,user_id
> from table_1
> union
> select modified_date
> ,user_id
> from table_1
> union
> select modified_date
> ,user_id
> from table_1
> -- ...add more tables here...
> ) combined_set
> where (user_id = @.user_id)
>
> ML|||On Wed, 7 Sep 2005 15:07:23 -0400, Brian wrote:

>If you don't like the way the question is presented then feel free not to
>answer it. Adding wise ass comments doesn't help anyone.
Hi Brian,
If you don't like the fact that the professionals in this group prefer
to help as good as possible, and that this can only be done if the
specifications are very clear, feel free to take your questions
elsewhere.
I've seen lots of threads that start with a vague question, then the
first answer isn't correct because the question wasn't exact enough, etc
etc. That can go on for days. What a waste of time for everyone
involved!
ML really deserves better than your brush-off. Instead of just asking
for better specifications, he ALSO posted a query that (to me, at least)
looks like it might do the job for you. Did you already try it?
If ML's suggestions is not working for you, then please post better
specs: table structure (as CREATE TABLE statements), sample data (as
INSERT statements) and required results. See www.aspfaq.com/5006 for
more details, and some useful hints and links.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo -
Believe me I appreciate every answer I have ever gotten from this newsgroup.
But I see little snide comments like that all the time (in a variety of
newsgroups) and it isn't really necessary. People can simple say, as I have
seen, please provide 'this' information so we can get a better idea of what
you are really looking for.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:86iuh11vpom3uilukkv3kpuncjkdbue989@.
4ax.com...
> On Wed, 7 Sep 2005 15:07:23 -0400, Brian wrote:
>
> Hi Brian,
> If you don't like the fact that the professionals in this group prefer
> to help as good as possible, and that this can only be done if the
> specifications are very clear, feel free to take your questions
> elsewhere.
> I've seen lots of threads that start with a vague question, then the
> first answer isn't correct because the question wasn't exact enough, etc
> etc. That can go on for days. What a waste of time for everyone
> involved!
> ML really deserves better than your brush-off. Instead of just asking
> for better specifications, he ALSO posted a query that (to me, at least)
> looks like it might do the job for you. Did you already try it?
> If ML's suggestions is not working for you, then please post better
> specs: table structure (as CREATE TABLE statements), sample data (as
> INSERT statements) and required results. See www.aspfaq.com/5006 for
> more details, and some useful hints and links.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Multiple subtotals in a Matrix

I would like to set up a report which would look simular to

Mon Tues Wed Thurs Fri Total Month to date total

salesguy1 10 5 11 10 9 45 120

SalesGuy2 9 1 15 0 0 25 89

I have created a matrix which shows the data upto total.

Is there a way to include the Month to date total after total?

Thanks

Any Ideas Anyone?|||Any Ideas? Anyone?

Multiple subtotals in a Matrix

I would like to set up a report which would look simular to

Mon Tues Wed Thurs Fri Total Month to date total

salesguy1 10 5 11 10 9 45 120

SalesGuy2 9 1 15 0 0 25 89

I have created a matrix which shows the data upto total.

Is there a way to include the Month to date total after total?

Thanks

Any Ideas Anyone?|||Any Ideas? Anyone?

Multiple Sorting

Hello All!
My report has columns Date, Year, Model, Cost, Color
I have users that will require the abililty to sort by multiple criteria.
Is it possible to configure a primary AND secondary AND maybe a THIRD sort
in Reporting Services?
For example: Sort the above columns as: Date, Model and Cost at the same
time?
This sorting criteri will change based on the user and the info sought. So
another user may want to
sort on Cost and Year, while another person wants Color,Cost, Year etc..
Currently, as far as I know, Reporting Services limits one to one column
sort at a time in a report.
Thanks in advance !
ScottOn Nov 16, 9:53 am, iamscott <iamsc...@.discussions.microsoft.com>
wrote:
> Currently, as far as I know, Reporting Services limits one to one column
> sort at a time in a report.
By default, all of your objects will be populated in the order of the
data in the DataSet. You can define this with a ORDER BY clause in
your SQL.
But, you can create Sort rules by object in your Report. Go to your
Table Properties, then the Sort tab, and you can add multiple sorting
rules to your table. Each rule is applied in sequence, with an
Ascending or Descending. Note that the sorting value is actually an
Expression, which means you can make it dynamic with a little code.
For your problem, what I would do is create a Parameter called
SortRules, make it a Integer, then define Available values as:
"Date, Model, Cost", 1
"Cost, Year", 2
"Color, Cost, Year", 3
etc
Then, in your table, add three Sort rules, each Ascending
Rule 1:
= IIF( Parameters!SortRules.Value = 1, Fields!Date.Value,
IIF( Parameters!SortRules.Value = 2, Fields!Cost.Value,
IIF( Parameters!SortRules.Value = 3, Fields!Color.Value,
Nothing ) ) )
Rule 2:
= IIF( Parameters!SortRules.Value = 1, Fields!Model.Value,
IIF( Parameters!SortRules.Value = 2, Fields!Year.Value,
IIF( Parameters!SortRules.Value = 3, Fields!Cost.Value,
Nothing ) ) )
Rule 3:
= IIF( Parameters!SortRules.Value = 1, Fields!Cost.Value,
IIF( Parameters!SortRules.Value = 2, Nothing,
IIF( Parameters!SortRules.Value = 3, Fields!Year.Value,
Nothing ) ) )
Now, when the user views the report, they will get a Parameter Prompt
at the top to select a sort method, then after choosing one and
hitting the View Report, the table will sort based on that method.
-- Scott|||Thanks so much for your idea! have a great weekend!
Scott
"Orne" wrote:
> On Nov 16, 9:53 am, iamscott <iamsc...@.discussions.microsoft.com>
> wrote:
> > Currently, as far as I know, Reporting Services limits one to one column
> > sort at a time in a report.
> By default, all of your objects will be populated in the order of the
> data in the DataSet. You can define this with a ORDER BY clause in
> your SQL.
> But, you can create Sort rules by object in your Report. Go to your
> Table Properties, then the Sort tab, and you can add multiple sorting
> rules to your table. Each rule is applied in sequence, with an
> Ascending or Descending. Note that the sorting value is actually an
> Expression, which means you can make it dynamic with a little code.
> For your problem, what I would do is create a Parameter called
> SortRules, make it a Integer, then define Available values as:
> "Date, Model, Cost", 1
> "Cost, Year", 2
> "Color, Cost, Year", 3
> etc
> Then, in your table, add three Sort rules, each Ascending
> Rule 1:
> = IIF( Parameters!SortRules.Value = 1, Fields!Date.Value,
> IIF( Parameters!SortRules.Value = 2, Fields!Cost.Value,
> IIF( Parameters!SortRules.Value = 3, Fields!Color.Value,
> Nothing ) ) )
> Rule 2:
> = IIF( Parameters!SortRules.Value = 1, Fields!Model.Value,
> IIF( Parameters!SortRules.Value = 2, Fields!Year.Value,
> IIF( Parameters!SortRules.Value = 3, Fields!Cost.Value,
> Nothing ) ) )
> Rule 3:
> = IIF( Parameters!SortRules.Value = 1, Fields!Cost.Value,
> IIF( Parameters!SortRules.Value = 2, Nothing,
> IIF( Parameters!SortRules.Value = 3, Fields!Year.Value,
> Nothing ) ) )
> Now, when the user views the report, they will get a Parameter Prompt
> at the top to select a sort method, then after choosing one and
> hitting the View Report, the table will sort based on that method.
> -- Scott
>

Multiple set result from a stored procedure as the data source

My stored procedure generates two results:
1) declares a variable executes a view to obtain date driven data which has
a begin and end
2) selects the data associated with date driven info
When RS executes the stored procedure I only get the first set results (the
date information).
Can RS handle multiple results from a stored procedure? If so is there any
information to read.
I can change my stored procedure with a couple joins but I am curious how RS
works (I'm a newbee).
SincerelyRS only works with the first resultset.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
news:CDADB1FA-43D3-40A0-BD94-CFD98F9FAFCE@.microsoft.com...
> My stored procedure generates two results:
> 1) declares a variable executes a view to obtain date driven data which
has
> a begin and end
> 2) selects the data associated with date driven info
> When RS executes the stored procedure I only get the first set results
(the
> date information).
> Can RS handle multiple results from a stored procedure? If so is there any
> information to read.
> I can change my stored procedure with a couple joins but I am curious how
RS
> works (I'm a newbee).
> Sincerely|||Sorry...RS doesn't support multiple result sets.
Adrian M.
"HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
news:CDADB1FA-43D3-40A0-BD94-CFD98F9FAFCE@.microsoft.com...
> My stored procedure generates two results:
> 1) declares a variable executes a view to obtain date driven data which
> has
> a begin and end
> 2) selects the data associated with date driven info
> When RS executes the stored procedure I only get the first set results
> (the
> date information).
> Can RS handle multiple results from a stored procedure? If so is there any
> information to read.
> I can change my stored procedure with a couple joins but I am curious how
> RS
> works (I'm a newbee).
> Sincerely|||Thanks for the info
"Adrian M." wrote:
> Sorry...RS doesn't support multiple result sets.
> Adrian M.
> "HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
> news:CDADB1FA-43D3-40A0-BD94-CFD98F9FAFCE@.microsoft.com...
> > My stored procedure generates two results:
> > 1) declares a variable executes a view to obtain date driven data which
> > has
> > a begin and end
> > 2) selects the data associated with date driven info
> >
> > When RS executes the stored procedure I only get the first set results
> > (the
> > date information).
> >
> > Can RS handle multiple results from a stored procedure? If so is there any
> > information to read.
> >
> > I can change my stored procedure with a couple joins but I am curious how
> > RS
> > works (I'm a newbee).
> >
> > Sincerely
>
>

Multiple rows of data into 1 row results?

Is there a way to get multiple rows of data to show in 1 row of results?
I have a Data Table (ID, Name, Date, etc), Facility Table (ID, Name) and FacilityKey Table (Data ID and Facility ID). The Data table has a 1 to many relationship with the FacilityKey table.

I want to return the Data ID and each Facility Name associated with that Data ID to display in a DataGrid (preferably each Facility Name in the same cell on the grid too), but am stumped on how to get teh query to work (or where to look in the SQL documentation for something like this).

Thanks!What you are trying to do is typically called a pivot table or cross tab query.

Take a look at this post:view post 350666. Someone has posted a suggestion to use a table control to accomplish this. See if anything there helps you. If not, post again :-)

Terri|||I think I may have to do it outside of SQL. I don't want to do any sort of aggregation of the data - just display it in 1 row instead of multiple and I don't see anywhere how to do the SQL cross tab without using an aggregate function of some sort.|||Well, you could write a UDF that would take the DataID as a parameter and would return a comma-delimited list of all of the facilities for that DataID. Like this:

CREATE FUNCTION getFacilities
(@.DataID int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @.facilities VARCHAR(8000)
SET @.facilities = ''
SELECT
@.facilities = @.facilities + ', ' + FacilityTable.FacilityName
FROM
DataTable
INNER JOIN
FacilityKeyTable ON DataTable.DataID = FacilityKeyTable.KeyDataID
INNER JOIN
FacilityTable ON FacilityKeyTable.KeyFacilityID = FacilityTable.FacilityID
WHERE
DataTable.DataID = @.DataID
IF @.facilities <> ''
BEGIN
SET @.facilities = SUBSTRING(@.facilities,3,LEN(@.facilities)-2)
END
RETURN (@.facilities)
END

You would use that function like this:

SELECT DataID, dbo.getFacilities(DataID) FROM DataTable

Terri|||Thank you! That is exactly what I was looking for!|||If efficiency is a priority for you then this function is a poor solution since under the hood it means you are doing N queries for each row returned where N is the number of facilities. A cross-tab query will be more efficient.