Max Dates For Cost Query

Hi,

I am trying to identify the costs for products with the latest cost date. I am unable to run the query. Is there something that I can do?

SELECT PART,COST, DATE FROM DATA/COSTFILE AS T1 WHERE T1.DATE= (SELECT MAX(DATE) AS T2 FROM DATA/COSTFILE AS T2 WHERE T2.PART=T1.PART)

Thanks,

DavidOriginally posted by hidvegi
Hi,

I am trying to identify the costs for products with the latest cost date. I am unable to run the query. Is there something that I can do?

SELECT PART,COST, DATE FROM DATA/COSTFILE AS T1 WHERE T1.DATE= (SELECT MAX(DATE) AS T2 FROM DATA/COSTFILE AS T2 WHERE T2.PART=T1.PART)

Thanks,

David

SELECT
T1.PART,
T1.COST,
T1.DATE
FROM
[DATA/COSTFILE] AS T1
WHERE
T1.DATE= (
SELECT MAX(T2.DATE) AS MAXDATE
FROM DATA/COSTFILE AS T2
WHERE T2.PART=T1.PART)

FYI, you shouldn't use ANY special characters in your column names or table names, especially "/\-_.,&%@.+"=".

The only exception to this is "_" which can be used after for sp_name on stored procedures that must reside in master and run on every database.

Matrix Problems

Hi All,
I need to get the following desing in a matrix...
---
Revison Rev1 Rev2 Rev3 Rev4
---
Totals
---
Revenue $100 $200 $400 $500
Cost $10 $30 $40 $50
---
Toals is static row how to get that row in a matrrix'
Please help me out here.......
Thanks,You could put the Totals as a group header or footer, depending on what you
are trying to achieve.
"CCP" <CCP@.discussions.microsoft.com> wrote in message
news:C79192E6-2C58-4412-AA48-6C6BAB289D25@.microsoft.com...
> Hi All,
> I need to get the following desing in a matrix...
> ---
> Revison Rev1 Rev2 Rev3 Rev4
> ---
> Totals
> ---
> Revenue $100 $200 $400 $500
> Cost $10 $30 $40 $50
> ---
> Toals is static row how to get that row in a matrrix'
> Please help me out here.......
> Thanks,|||I have a static matrix column example at www.msbicentral.com
it is called Matrix.StaticColumns.RDL and it is under downloads, reporting
services, RDL
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CCP" <CCP@.discussions.microsoft.com> wrote in message
news:C79192E6-2C58-4412-AA48-6C6BAB289D25@.microsoft.com...
> Hi All,
> I need to get the following desing in a matrix...
> ---
> Revison Rev1 Rev2 Rev3 Rev4
> ---
> Totals
> ---
> Revenue $100 $200 $400 $500
> Cost $10 $30 $40 $50
> ---
> Toals is static row how to get that row in a matrrix'
> Please help me out here.......
> Thanks,

matrix issues

example rpt..

| Last year | this year |
Resolution | Resolved | Closed | Cost | Resolved | Closed | Cost |
| (qty) | (qty) | (total)| (qty) | (qty) | total) |
Res 1 | 1 | 0 | 500 | 5 | 2 | 350 |
Res 2 | 6 | 3 | 475 | 3 | 4 | 600 |
. . . |
Res 10 | 0 | 0 | 0 | 0 | 0 | 0 |
Res 11 | 3 | 4 | 640 | 4 | 2 | 450 |

Data info: a case can have a status of 'Open', 'Resolved', or 'Closed', I am only interested in counts of 'Resolved' and 'Closed', with a sum of the costs for each resolution type (1 - 11). And split by 'Last Year' and 'This Year'

I'm having difficulty setting up the groupings, plus I want all 11 rows to show up even if they did not happen to be in the recordset (like Res 10, above) with 0 totals. and I want the Resolutions to be listed in a specific order (not alphabetically like the example).

I've tried creating a new row group for each Resolution, and filtering buy that specific value, but after the second row is added I get an array out of bounds error...

any assistance would be great
SamWell, I've given up on trying to make this through a matrix. I re-wrote the stored procedure to calculate all of the needed fields, then used a standard table...

Thank you to anyone that took the time to read the question and try to come up with a solution..

SC|||I done it using Microstrategy , it's very easy.
if u want to use SQL Server , use anaysis services ( MDX)

matrix issues

example rpt..

| Last year | this year |
Resolution | Resolved | Closed | Cost | Resolved | Closed | Cost |
| (qty) | (qty) | (total)| (qty) | (qty) | total) |
Res 1 | 1 | 0 | 500 | 5 | 2 | 350 |
Res 2 | 6 | 3 | 475 | 3 | 4 | 600 |
. . . |
Res 10 | 0 | 0 | 0 | 0 | 0 | 0 |
Res 11 | 3 | 4 | 640 | 4 | 2 | 450 |

Data info: a case can have a status of 'Open', 'Resolved', or 'Closed', I am only interested in counts of 'Resolved' and 'Closed', with a sum of the costs for each resolution type (1 - 11). And split by 'Last Year' and 'This Year'

I'm having difficulty setting up the groupings, plus I want all 11 rows to show up even if they did not happen to be in the recordset (like Res 10, above) with 0 totals. and I want the Resolutions to be listed in a specific order (not alphabetically like the example).

I've tried creating a new row group for each Resolution, and filtering buy that specific value, but after the second row is added I get an array out of bounds error...

any assistance would be great
SamWell, I've given up on trying to make this through a matrix. I re-wrote the stored procedure to calculate all of the needed fields, then used a standard table...

Thank you to anyone that took the time to read the question and try to come up with a solution..

SC|||I done it using Microstrategy , it's very easy.
if u want to use SQL Server , use anaysis services ( MDX)

Matrix column drill down

I have a matrix with a row grouping of project and a column grouping of
project manager. I am summing the gross margin, labor cost, and gross
profit. Is there a way to only display the gross profit initially, but
supply a drill down on that field to display the sum of the other two fields
when the user clicks on the gross profit?This is what I have:
PM 1
PM2
Margin Labor Profit
Margin Labor Profit
+ Project 1
100 75 25
- Project 2 Foreman1 50 25 25
Foreman2 75 25 50
+ Project 3 10 15 -5
This is what I would like:
PM 1
PM2
Margin Labor Profit
Profit
+ Project 1
+ 25
- Project 2 Foreman1
50 25 25
Foreman2 75 25 50
+ Project 3 10 15 -5
"MikeS" wrote:
> I have a matrix with a row grouping of project and a column grouping of
> project manager. I am summing the gross margin, labor cost, and gross
> profit. Is there a way to only display the gross profit initially, but
> supply a drill down on that field to display the sum of the other two fields
> when the user clicks on the gross profit?|||The requirement is to see as much as possible on one page.
This is what I have:
PM 1
PM2 ...
Margin Labor Profit
Margin Labor Profit ...
+ Project 1
100 75 25 ...
- Project 2 Foreman1 50 25 25
Foreman2 75 25 50
+ Project 3 10 15 -5
This is what I would like:
PM 1
PM2 ...
Margin Labor Profit
Profit ...
+ Project 1
+ 25
- Project 2 Foreman1 - 25
50 25
Foreman2 - 50
75 25
+ Project 3 -5
Is it possible to do this? If so can someone please point me in the right
direction?
Thanks
"MikeS" wrote:
> I have a matrix with a row grouping of project and a column grouping of
> project manager. I am summing the gross margin, labor cost, and gross
> profit. Is there a way to only display the gross profit initially, but
> supply a drill down on that field to display the sum of the other two fields
> when the user clicks on the gross profit?