Max Date

How do i find out the max date of a particular month in a table.

I have a records in the table like this

DATE
--
01 Jan 2004
02 Jan 2004
05 Jan 2004
07 Jan 2004
04 Feb 2004
06 Feb 2004
19 Feb 2004
04 Mar 2004
28 Mar 2004
03 Apr 2004
05 Apr 2004

My output should be something like this
OUTPUT
---
07 Jan 2004
06 Feb 2004
28 Mar 2004
05 Apr 2004

I want a SQL statement that can do this for me

Thanks
ShankarHehe, just answered you on SQLServerCentral :)

select Max(datepart(dd,datecolumn)) as Day,datepart(mm,datecolumn) as month, datepart(yy,datecolumn) as Year
from table
group by datepart(mm,datecolumn),datepart(yy,datecolumn)

HTH|||thanks for your help,

i got a kind off tricky requirement here.

the query that i write has to return me 6 records always.

The first record that will be displayed will be the initial record that is created. The last record in these 6 records will be the latest record that is created. so now my question is i want to retrieve the remaining 4 records.

my conditions are like this.

i want to display the maximum date of a particular month in the 4 records. now if this condition does not give me 4 records, then i need to
pick up the current month's record also and make sure the count is equal to 4. What if i have only one record in the current month.
then i need to go to current - 1 month and pick up that record.

I need to keep doing this until i get the 4 records that i want.

Let me give u the example for this.

Lets take the example that i have given previously.

DATE
--
01 Jan 2004
02 Jan 2004
05 Jan 2004
07 Jan 2004
04 Feb 2004
06 Feb 2004
19 Feb 2004
04 Mar 2004
28 Mar 2004
03 Apr 2004
05 Apr 2004

My output should be something like this

01 Jan 2004 --> First Record that is created
07 Jan 2004 --> Max of Jan
06 Feb 2004 --> Max of Feb
28 Mar 2004 --> Max of March
03 Apr 2004 --> I choose this record becoz the count for max months
is not equal to 4 and hence to make it 4 , i add this
05 Apr 2004 --> The most recent record

Hope this helps in understanding the requirement

Thanks
Shankar

Matrix region formulas

Hi,
I would like a Matrix report with
Jan, Feb, Mar, Total on columns
where total sums up all the month values in that row.
And something like
2003, 2004, Difference2003-2004 on columns
AddColumn does't seem to do the trick
Thanks!Sorry, the first one I see I can do with the "Subtotal" option but what
about "average for the subtotal
"BoruRR" wrote:
> Hi,
> I would like a Matrix report with
> Jan, Feb, Mar, Total on columns
> where total sums up all the month values in that row.
> And something like
> 2003, 2004, Difference2003-2004 on columns
> AddColumn does't seem to do the trick
> Thanks!

Matrix Percentage Column ( Column 2 vs Column 1)

Need help with calculating percentage column in a matrix.

2005 2006

Jan 10 15

Feb 20 25

Need to add column showing variance :

Var Var

Jan 10 15 50%

Feb 20 25 25%

Any help would be appreciated....

Hi. Did you manage to get the result.I also have the similar case. If you have solved can you plz help me out.

Matrix Calculation - % Growth

I have a report that list customer's 2004 and '05 Sales by month:
Jan Feb Mar ...
Acct 10 2004 $$$ $$$ $$$ ...
2005 $$
% Growth
Acct 11 2004 $$$ $$$ $$$ ...
2005 $$
% Growth
The report currently list all of our accounts in a Matrix report. How can I
calculate the "% Growth"?
The Subtotal function obviouly isn't what I need. Any help is much
appreciated!
DavidDJONES wrote:
> I have a report that list customer's 2004 and '05 Sales by month:
> Jan Feb Mar ...
> Acct 10 2004 $$$ $$$ $$$ ...
> 2005 $$
> % Growth
> Acct 11 2004 $$$ $$$ $$$ ...
> 2005 $$
> % Growth
> The report currently list all of our accounts in a Matrix report. How
> can I calculate the "% Growth"?
> The Subtotal function obviouly isn't what I need. Any help is much
> appreciated!
hmm *scratching head**untestet*
=(100-(Sum(Fields!Jan 2004.Value)/(Sum(Fields!Jan 2005.Value)/100)))
should be the growth in percent
i.e. 100-(1555)/(1888/100) ~ 17,64%
regards
frank|||I was planning to place this formula in a group footer, but just realized
that I can't add a group footer in a Matrix style report. Is that correct?
Could I use a similar formula in a tabular report?
Thanks
"Frank Matthiesen" wrote:
> DJONES wrote:
> > I have a report that list customer's 2004 and '05 Sales by month:
> >
> > Jan Feb Mar ...
> > Acct 10 2004 $$$ $$$ $$$ ...
> > 2005 $$
> > % Growth
> >
> > Acct 11 2004 $$$ $$$ $$$ ...
> > 2005 $$
> > % Growth
> >
> > The report currently list all of our accounts in a Matrix report. How
> > can I calculate the "% Growth"?
> >
> > The Subtotal function obviouly isn't what I need. Any help is much
> > appreciated!
>
> hmm *scratching head**untestet*
> =(100-(Sum(Fields!Jan 2004.Value)/(Sum(Fields!Jan 2005.Value)/100)))
> should be the growth in percent
> i.e. 100-(1555)/(1888/100) ~ 17,64%
>
> regards
> frank
>
>

Matrix and Averages

My Matrix has the following structure
Product Jan Feb Mar Total
--
Prroduct1 2 4 1 6
How would I add an "average column"
Product Jan Feb Mar Total Avg
--
Prroduct1 2 4 1 6 2Okay, I solved my % Total problem.
In the data region, I split the cell into two, one is the original data, the
other is the % of the left cell to the scope. Then the Total and %Total
appear side by side if you do subtotal to the group. After that, hide the
%Total column using inscope function if it is in the scope.
But I am very interested in knowing if it is possible to do average though.
"Frank RS" wrote:
> I have the similar situation-- to add a %Total after the Total filed.
> It looks impossible because Total column is pretty much the last column in a
> matrix.
> Microsoft gurus, please help on this problem.
> At least let us know if it is doablt.
> Thanks in advance!
> Frank
> "SAcanuck" wrote:
> > My Matrix has the following structure
> > Product Jan Feb Mar Total
> > --
> > Prroduct1 2 4 1 6
> >
> > How would I add an "average column"
> >
> > Product Jan Feb Mar Total Avg
> > --
> > Prroduct1 2 4 1 6 2
> >|||I guess I got the answer to this question too.
Just modify the expression to change the sum function into average function.
I will post if I got time to play with it.
"Frank RS" wrote:
> Okay, I solved my % Total problem.
> In the data region, I split the cell into two, one is the original data, the
> other is the % of the left cell to the scope. Then the Total and %Total
> appear side by side if you do subtotal to the group. After that, hide the
> %Total column using inscope function if it is in the scope.
> But I am very interested in knowing if it is possible to do average though.
> "Frank RS" wrote:
> > I have the similar situation-- to add a %Total after the Total filed.
> > It looks impossible because Total column is pretty much the last column in a
> > matrix.
> > Microsoft gurus, please help on this problem.
> > At least let us know if it is doablt.
> > Thanks in advance!
> > Frank
> >
> > "SAcanuck" wrote:
> >
> > > My Matrix has the following structure
> > > Product Jan Feb Mar Total
> > > --
> > > Prroduct1 2 4 1 6
> > >
> > > How would I add an "average column"
> > >
> > > Product Jan Feb Mar Total Avg
> > > --
> > > Prroduct1 2 4 1 6 2
> > >