Max

Hi ,
I have a list of data as show below ,
Data1 Update Date
-- --
1 01-01-2004
2 01-01-2004
3 01-01-2004
1 01-01-2005
2 01-01-2005
3 01-01-2005
How can I build a query to select the "latest" update date data from this
list ?
Travis Tan
On Mon, 7 Nov 2005 08:31:14 -0800, Travis wrote:

>Hi ,
> I have a list of data as show below ,
> Data1 Update Date
> -- --
> 1 01-01-2004
> 2 01-01-2004
> 3 01-01-2004
> 1 01-01-2005
> 2 01-01-2005
> 3 01-01-2005
> How can I build a query to select the "latest" update date data from this
>list ?
Hi Travis,
Your question is not reallly clear. Providing expected output would have
been helpful. (And posting CREATE TABLE and INSERT statements even more;
check www.aspfaq.com/5006).
Anywway, here are two possible answers for two possible interpretations
of your question:
SELECT MAX("Update Date")
FROM YourTable
SELECT Data1, MAX("Update Date")
FROM YourTable
GROUP BY Data1
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
sql

Matrix Totals

Below is part of a matrix report. Sooo close, but I have two problems I have not been able to solve. Please help as a I have several similar reports to do.

1. Row totals. I have been able to get a row total by adding a row group (i.e., $849.7 in the first row). However it does not display a group total (i.e., the sum of $849.7 and $371.3 for Economic Development) for that column.

I have not been able to add a static column outside of the dynamic groupings. I thought this might be a resolution by displaying the sum of the Estimated Costs in a static column and hoping it would show the group totals the same as with the dynamic column totals. Is there a way to do this?

2. Sorting. The report needs to display the groups based on the descending total values. I have specified the following sorts on the groups: sum(Fields!Estimated_Cost),"matrix1_Proj_Typ_Group" descending (this is the first column) and sum(Fields!Estimated_Cost),"matrix1_Proj_Typ" descending (this is the second column). Neither sort appears to be work.

City

County

Federal

Joint

Estimated Cost

% of Total

Estimated Cost

% of Total

Estimated Cost

% of Total

Estimated Cost

% of Total

Economic Development

Business District Development

$849.7

$816.0

66.83%

$27.2

2.23%

$0.0

0.0%

$6.5

0.53%

Industrial Sites & Parks

$371.3

$131.5

10.77%

$190.4

15.59%

$0.0

0.0%

$36.0

2.95%

Total

$947.6

77.61%

$217.6

17.82%

$0.0

0.0%

$42.5

3.48%

Education

K-12 New School Construction

$1,594.7

$223.0

10.05%

$1,363.5

61.43%

$0.0

0.0%

$0.0

0.0%

Non K-12 Education

$37.8

$3.1

0.14%

$33.2

1.5%

$0.0

0.0%

$0.0

0.0%

School System-wide Need

$587.1

$167.2

7.53%

$419.2

18.89%

$0.0

0.0%

$0.0

0.0%

Total

$393.2

17.72%

$1,816.0

81.82%

$0.0

0.0%

$0.0

0.0%

General Government

Other Facilities

$21.3

$17.5

4.3%

$3.9

0.95%

$0.0

0.0%

$0.0

0.0%

Property Acquisition

$8.8

$6.8

1.68%

$2.0

0.49%

$0.0

0.0%

$0.0

0.0%

Public Buildings

$375.5

$294.2

72.54%

$72.0

17.74%

$3.0

0.74%

$6.2

1.52%

Total

$318.5

78.52%

$77.8

19.19%

$3.0

0.74%

$6.2

1.52%

1. Row totals: finally ran across a reference to "subtotals" and it solved this issue perfectly.

2. Can anyone help me with the sorting issue? Is this a bug or am I specifying this incorrectly?

|||

Regarding sorting:

What exactly are you trying to sort? You have a matrix - so if you are trying to sort descending based on the totals for "Economic Development", it will impact the overall column group sort order (which is shared for all row groups!)

You cannot have every row group instance ("Economic Development", etc.) sort the column group differently. They share the same column groups. If just the cell data is re-sorted, the final matrix data would be correct for some row/column intersections but incorrect for other intersections.

-- Robert

Matrix Control pushes out Graphs on Report

I have a report with multiple graphs below each other (some bar, some line, some pie)
To the left of each graph, I have a few text boxes and tables tables displaying information about each graph to the right. (fits on A4 portait page)
For lack of being able to display a pic:
Table Graph
_______ ________________________________
| | | |
| | | |
|______ | |_______________________________|
_______ ________________________________
| | | |
| | | |
|______ | |_______________________________|
Right at the bottom of the report, just below the last table/graph combination, I have a simple matrix control.
In the preview pane, all is well, no problem. When I deploy the report to the report server, the matrix control pushes all graphs out for the entire length of the matrix.
Table Blank space Graph
_______ ________________________________
| | | |
| | | |
|______ | |_______________________________|
_______ ________________________________
| | | |
| | | |
|______ | |_______________________________|
_______________________
| |
|______________________|
Matrix /\
The only way I get the report to display correctly is when I specify that the matrix must start on a new page. Unfortunately, the customer wants all on one page.
Any ideas?As items grow vertically, they push items below them.
As they grow horizontally, they push items beside them on the page.
An easy way to prevent this is to make sure your graphs aren't considered to
be to the right of the matrix by grouping the table and graph together in a
rectangle:
--
| -- -- |
| |Table| |Graph| |
| -- -- |
--
--
|Matrix|
--
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Michelle" <Michelle@.discussions.microsoft.com> wrote in message
news:363724F0-D9EE-4BD3-9769-032E79430C6F@.microsoft.com...
> I have a report with multiple graphs below each other (some bar, some
line, some pie)
> To the left of each graph, I have a few text boxes and tables tables
displaying information about each graph to the right. (fits on A4 portait
page)
> For lack of being able to display a pic:
> Table Graph
> _______ ________________________________
> | | | |
> | | | |
> |______ | |_______________________________|
> _______ ________________________________
> | | | |
> | | | |
> |______ | |_______________________________|
>
> Right at the bottom of the report, just below the last table/graph
combination, I have a simple matrix control.
> In the preview pane, all is well, no problem. When I deploy the report to
the report server, the matrix control pushes all graphs out for the entire
length of the matrix.
> Table Blank space Graph
> _______ ________________________________
> | | |
|
> | | |
|
> |______ | |_______________________________|
> _______ ________________________________
> | | |
|
> | | |
|
> |______ | |_______________________________|
> _______________________
> | |
> |______________________|
> Matrix /\
> The only way I get the report to display correctly is when I specify that
the matrix must start on a new page. Unfortunately, the customer wants all
on one page.
> Any ideas?

math or text

in the below sql why is year(classdate) " + " a " + " MONTH(classdate) a math command giving me 2006 - 12 = 167

and not "2006/12" as text? please help me

cmdGetCat = New SqlDataAdapter("SELECT DISTINCT year(classdate) " + " a " + " MONTH(classdate) AS monthcode FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL) ", conNorthwind)

Hi,

i think the code that you provided has certain syntax problem, can you verify the codes?

anyway the sql statement should be something like this:

"SELECT DISTINCT year(classdate) + ' a ' + MONTH(classdate) AS monthcode FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL) "

|||

sorry its this.... i dont get 2006/12 ...i get 126 which is 2006-12=126 hahaha ..i need "yyyy/mm" why god why!!!

"SELECT DISTINCT year(classdate) + ' -/' + MONTH(classdate) AS monthcode FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL) "

|||

Hi,

You can use this sql statement to giv you yyyy/MM in varchar format:
SELECT CAST(YEAR(classdate) AS VARCHAR(4)) + '/' + CAST(MONTH(classdate) AS VARCHAR(2)) AS 'monthcode'
FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL)

you can also choose to use the CONVERT function to return a certain format of the date and use the data in your programm

Hope this helps

|||

Here is a Convert Sample for you:

select convert(varchar(7),classdate,111) AS 'monthcode' FROM
...........