Max in select clause

I have the following code......
SELECT
a..acct_nbr
,p.pric_uval_amt AS 'Unit Price'
,MAX(pp.pric_asof_dt) AS 'Unit_Price_Date'
FROM
account_t a
LEFT OUTER JOIN price_t p
ON a.ser_id = p.ser_id
GROUP BY
a.acct_nbr ,p.pric_uval_amt
Its purpose is to return an account number from one table, and from the
corresponding price table the latest unit price date and unit price.
Basically the price table
keeps a list of all products associated with an account. Each account can
have multiple products. The price table keeps regularly updated prices for
these products and whatever the date that theprice was updated. What I want
to do is to be able to get the latest (max) unit price date and the
corresponding unit price value.
The code above gives me the latest unit price date (if I take out the
,p.pric_uval_amt AS 'Unit Price' line). If I leave that line in I also get
the prices at all previous dates.
What I tend to get is this......
acct_nbr unit price
unit_price_date
----
0001 90
05/05/2005
0001 98
05/06/2005
0001 91
05/07/2005
0002 43
05/05/2005
0002 45
05/06/2005
When what I want is this.........
acct_nbr unit price
unit_price_date
----
0001 91
05/07/2005
0002 45
05/06/2005
Any idea's ?Hi
Is this the query that you are looking for:
SELECT account_t.acct_nbr, account_t.[unit price], derived.Unit_Price_Date
FROM account_t
INNER JOIN (
SELECT a.acct_nbr,MAX(pp.pric_asof_dt) AS 'Unit_Price_Date'
FROM account_t a
LEFT OUTER JOIN price_t p ON a.ser_id = p.ser_id
GROUP BY a.acct_nbr
) derived
ON derived.Unit_Price_Date = account_t.Unit_Price_Date AND
derived.acct_nbr = account_t.acct_nbr
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"quiglepops" wrote:

> I have the following code......
>
> SELECT
> a..acct_nbr
> ,p.pric_uval_amt AS 'Unit Price'
> ,MAX(pp.pric_asof_dt) AS 'Unit_Price_Date'
> FROM
> account_t a
> LEFT OUTER JOIN price_t p
> ON a.ser_id = p.ser_id
> GROUP BY
> a.acct_nbr ,p.pric_uval_amt
> Its purpose is to return an account number from one table, and from the
> corresponding price table the latest unit price date and unit price.
> Basically the price table
> keeps a list of all products associated with an account. Each account can
> have multiple products. The price table keeps regularly updated prices for
> these products and whatever the date that theprice was updated. What I wan
t
> to do is to be able to get the latest (max) unit price date and the
> corresponding unit price value.
> The code above gives me the latest unit price date (if I take out the
> ,p.pric_uval_amt AS 'Unit Price' line). If I leave that line in I also get
> the prices at all previous dates.
> What I tend to get is this......
> acct_nbr unit price
> unit_price_date
> ----
> 0001 90
> 05/05/2005
> 0001 98
> 05/06/2005
> 0001 91
> 05/07/2005
> 0002 43
> 05/05/2005
> 0002 45
> 05/06/2005
>
> When what I want is this.........
> acct_nbr unit price
> unit_price_date
> ----
> 0001 91
> 05/07/2005
> 0002 45
> 05/06/2005
>
> Any idea's ?
>
>|||Try,
Try,
SELECT
a..acct_nbr
,p.pric_uval_amt AS 'Unit Price'
,p.pric_asof_dt AS 'Unit_Price_Date'
FROM
account_t as a
LEFT OUTER JOIN
price_t as p
ON a.ser_id = p.ser_id
where
p.pric_asof_dt = (select max(p1.pric_asof_dt) from price_t as p1 where
p1.ser_id = a.ser_id)
AMB
"quiglepops" wrote:

> I have the following code......
>
> SELECT
> a..acct_nbr
> ,p.pric_uval_amt AS 'Unit Price'
> ,MAX(pp.pric_asof_dt) AS 'Unit_Price_Date'
> FROM
> account_t a
> LEFT OUTER JOIN price_t p
> ON a.ser_id = p.ser_id
> GROUP BY
> a.acct_nbr ,p.pric_uval_amt
> Its purpose is to return an account number from one table, and from the
> corresponding price table the latest unit price date and unit price.
> Basically the price table
> keeps a list of all products associated with an account. Each account can
> have multiple products. The price table keeps regularly updated prices for
> these products and whatever the date that theprice was updated. What I wan
t
> to do is to be able to get the latest (max) unit price date and the
> corresponding unit price value.
> The code above gives me the latest unit price date (if I take out the
> ,p.pric_uval_amt AS 'Unit Price' line). If I leave that line in I also get
> the prices at all previous dates.
> What I tend to get is this......
> acct_nbr unit price
> unit_price_date
> ----
> 0001 90
> 05/05/2005
> 0001 98
> 05/06/2005
> 0001 91
> 05/07/2005
> 0002 43
> 05/05/2005
> 0002 45
> 05/06/2005
>
> When what I want is this.........
> acct_nbr unit price
> unit_price_date
> ----
> 0001 91
> 05/07/2005
> 0002 45
> 05/06/2005
>
> Any idea's ?
>
>|||Thanks everyone for helping.
Alejandro, used your solution worked well. Thanks a lot.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:B65F1A34-7B16-46BB-B371-6143E989ACC5@.microsoft.com...
> Try,
> Try,
> SELECT
> a..acct_nbr
> ,p.pric_uval_amt AS 'Unit Price'
> ,p.pric_asof_dt AS 'Unit_Price_Date'
> FROM
> account_t as a
> LEFT OUTER JOIN
> price_t as p
> ON a.ser_id = p.ser_id
> where
> p.pric_asof_dt = (select max(p1.pric_asof_dt) from price_t as p1 where
> p1.ser_id = a.ser_id)
>
> AMB
>
> "quiglepops" wrote:
>
can
for
want
get

Matrix subtotal row question

I have created a matrix that looks like the following example:
APRIL MAY JUNE
PRODUCT X 10 20 30
PRODUCT Y 20 30 40
where the data is profit per unit sold (=PROFIT/# of UNITS)
I need to add a row that is the average profit for each month. I know
how to get the subtotal row to show up, but this justs adds the rows
(which is meaningless for me). Even the simple average (e.g. (10 +
20)/2) won't do - I need a weighted average per month (e.g. ALL PROFIT
for APRIL/ALL UNITS sold for APRIL). A data example is:
for April I sold 10 units of X for a $100 profit and I sold 20 units
of Y for a $400 profit
As in the table above, the profit per unit is (100/10) $10 for X and
(400/20) $20 for Y
But the average profit I want is not ($10 + $20) / 2 ($15). It is $500
profit / 30 units = ~$17
So two questions:
How do I add a average row to the matrix?
How do I make this a weighted average?
Sorry if my explanation is confusing or the answer is basic... new to
this and can't find an answer anywhere!It sounds like your current expression is something like this:
=Sum(Fields!ProfitPerUnit.Value)
What you really need is something more like this:
=Sum(Fields!TotalProfit.Value)/Sum(Fields!Units.Value)
If you are only returning ProfitPerUnit and Units in your query, you could
do this instead:
=Sum(Fields!ProfitPerUnit.Value*Fields!Units.Value)/Sum(Fields!Units.Value)
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"coldfact" <bryan@.coldfact.com> wrote in message
news:249185cd.0407151323.79162246@.posting.google.com...
> I have created a matrix that looks like the following example:
> APRIL MAY JUNE
> PRODUCT X 10 20 30
> PRODUCT Y 20 30 40
> where the data is profit per unit sold (=PROFIT/# of UNITS)
> I need to add a row that is the average profit for each month. I know
> how to get the subtotal row to show up, but this justs adds the rows
> (which is meaningless for me). Even the simple average (e.g. (10 +
> 20)/2) won't do - I need a weighted average per month (e.g. ALL PROFIT
> for APRIL/ALL UNITS sold for APRIL). A data example is:
> for April I sold 10 units of X for a $100 profit and I sold 20 units
> of Y for a $400 profit
> As in the table above, the profit per unit is (100/10) $10 for X and
> (400/20) $20 for Y
> But the average profit I want is not ($10 + $20) / 2 ($15). It is $500
> profit / 30 units = ~$17
> So two questions:
> How do I add a average row to the matrix?
> How do I make this a weighted average?
> Sorry if my explanation is confusing or the answer is basic... new to
> this and can't find an answer anywhere!|||Very nice - works now - thanks for your help!
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message news:<uONOoHsaEHA.1656@.TK2MSFTNGP09.phx.gbl>...
> It sounds like your current expression is something like this:
> =Sum(Fields!ProfitPerUnit.Value)
> What you really need is something more like this:
> =Sum(Fields!TotalProfit.Value)/Sum(Fields!Units.Value)
> If you are only returning ProfitPerUnit and Units in your query, you could
> do this instead:
> =Sum(Fields!ProfitPerUnit.Value*Fields!Units.Value)/Sum(Fields!Units.Value)
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "coldfact" <bryan@.coldfact.com> wrote in message
> news:249185cd.0407151323.79162246@.posting.google.com...
> > I have created a matrix that looks like the following example:
> >
> > APRIL MAY JUNE
> > PRODUCT X 10 20 30
> > PRODUCT Y 20 30 40
> >
> > where the data is profit per unit sold (=PROFIT/# of UNITS)
> > I need to add a row that is the average profit for each month. I know
> > how to get the subtotal row to show up, but this justs adds the rows
> > (which is meaningless for me). Even the simple average (e.g. (10 +
> > 20)/2) won't do - I need a weighted average per month (e.g. ALL PROFIT
> > for APRIL/ALL UNITS sold for APRIL). A data example is:
> > for April I sold 10 units of X for a $100 profit and I sold 20 units
> > of Y for a $400 profit
> > As in the table above, the profit per unit is (100/10) $10 for X and
> > (400/20) $20 for Y
> > But the average profit I want is not ($10 + $20) / 2 ($15). It is $500
> > profit / 30 units = ~$17
> >
> > So two questions:
> > How do I add a average row to the matrix?
> > How do I make this a weighted average?
> >
> > Sorry if my explanation is confusing or the answer is basic... new to
> > this and can't find an answer anywhere!|||Very nice - works now - thanks for your help!
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message news:<uONOoHsaEHA.1656@.TK2MSFTNGP09.phx.gbl>...
> It sounds like your current expression is something like this:
> =Sum(Fields!ProfitPerUnit.Value)
> What you really need is something more like this:
> =Sum(Fields!TotalProfit.Value)/Sum(Fields!Units.Value)
> If you are only returning ProfitPerUnit and Units in your query, you could
> do this instead:
> =Sum(Fields!ProfitPerUnit.Value*Fields!Units.Value)/Sum(Fields!Units.Value)
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "coldfact" <bryan@.coldfact.com> wrote in message
> news:249185cd.0407151323.79162246@.posting.google.com...
> > I have created a matrix that looks like the following example:
> >
> > APRIL MAY JUNE
> > PRODUCT X 10 20 30
> > PRODUCT Y 20 30 40
> >
> > where the data is profit per unit sold (=PROFIT/# of UNITS)
> > I need to add a row that is the average profit for each month. I know
> > how to get the subtotal row to show up, but this justs adds the rows
> > (which is meaningless for me). Even the simple average (e.g. (10 +
> > 20)/2) won't do - I need a weighted average per month (e.g. ALL PROFIT
> > for APRIL/ALL UNITS sold for APRIL). A data example is:
> > for April I sold 10 units of X for a $100 profit and I sold 20 units
> > of Y for a $400 profit
> > As in the table above, the profit per unit is (100/10) $10 for X and
> > (400/20) $20 for Y
> > But the average profit I want is not ($10 + $20) / 2 ($15). It is $500
> > profit / 30 units = ~$17
> >
> > So two questions:
> > How do I add a average row to the matrix?
> > How do I make this a weighted average?
> >
> > Sorry if my explanation is confusing or the answer is basic... new to
> > this and can't find an answer anywhere!