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 with fixed column values (months 1-12)

I have a matrix that shows sales per year (rows) and month (columns).
If the data being used contains no records for a particular month for any
year (e.g. November), that column (i.e. column 11) is completely missing
from the matrix.
Can the matrix be configured to always show a given set of columns, even if
there is no underlying data?
ThanksHi Laurence,
If you don't have any data rows for a certain month, then it will not show
up in the matrix grouping. To ensure that certain groups/data values are
always present, you will need an outer join in your dataset query e.g. with
a simple table that just has 12 rows with one column and values from 1 to
12.
Details on how to use outer joins are available here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_09_0zqr.asp
http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_09_1h6b.asp
HTH,
Robert
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:O8YpoRDuEHA.1296@.TK2MSFTNGP10.phx.gbl...
> I have a matrix that shows sales per year (rows) and month (columns).
> If the data being used contains no records for a particular month for any
> year (e.g. November), that column (i.e. column 11) is completely missing
> from the matrix.
> Can the matrix be configured to always show a given set of columns, even
if
> there is no underlying data?
> Thanks
>|||OK thanks, I know how to do that.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:uWTPFZyuEHA.3228@.TK2MSFTNGP12.phx.gbl...
> Hi Laurence,
> If you don't have any data rows for a certain month, then it will not show
> up in the matrix grouping. To ensure that certain groups/data values are
> always present, you will need an outer join in your dataset query e.g.
> with
> a simple table that just has 12 rows with one column and values from 1 to
> 12.
> Details on how to use outer joins are available here:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_09_0zqr.asp
> http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_09_1h6b.asp
> HTH,
> Robert
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:O8YpoRDuEHA.1296@.TK2MSFTNGP10.phx.gbl...
>> I have a matrix that shows sales per year (rows) and month (columns).
>> If the data being used contains no records for a particular month for any
>> year (e.g. November), that column (i.e. column 11) is completely missing
>> from the matrix.
>> Can the matrix be configured to always show a given set of columns, even
> if
>> there is no underlying data?
>> Thanks
>>
>

Matrix Question

In a matrix with multiple groups on an axis, which has sub totals, how do
you determine in an expression where a particular data cell is?
For example:
----
| -2003 | +2004 |
Total |
----
| Q1 | Q2 | Q3 | Q4 | |
|
----
Item 1 | x1 | x2 | x3 | x4 | x5 |
x6 |
----
Item 2 | x7 | x8 | x9 | x10 | x11 |
x12 |
----
x1 = Item 1 for 2003-Q1
x2 = Item 1 for 2003-Q2
x3 = Item 1 for 2003-Q3
x4 = Item 1 for 2003-Q4
x5 = Item 1 for 2004
x6 = Item 1 for (2003 + 2004)
x7 = Item 2 for 2003-Q1
x8 = Item 2 for 2003-Q2
x9 = Item 2 for 2003-Q3
x10 = Item 2 for 2003-Q4
x11 = Item 2 for 2004
x12 = Item 2 for (2003 + 2004)
For instance:
At cell position x4, how do I know that I am in group 2 (Quarters), at cell
position x5, how do I know that I am in group 1 (years), and at cell
position x6, how do I know that I am in group 1 (years) sub total'Did you look at the InScope function? It will allow you to distinguish
between cells in subtotals and cells in the groupings. More information on
InScope is available at:
http://msdn.microsoft.com/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul Allan" <paul_allan@.maxqtech.com> wrote in message
news:O%23pHpENnEHA.3172@.TK2MSFTNGP10.phx.gbl...
> In a matrix with multiple groups on an axis, which has sub totals, how do
> you determine in an expression where a particular data cell is?
> For example:
> ----
> | -2003 | +2004
|
> Total |
> ----
> | Q1 | Q2 | Q3 | Q4 | |
> |
> ----
> Item 1 | x1 | x2 | x3 | x4 | x5 |
> x6 |
> ----
> Item 2 | x7 | x8 | x9 | x10 | x11 |
> x12 |
> ----
> x1 = Item 1 for 2003-Q1
> x2 = Item 1 for 2003-Q2
> x3 = Item 1 for 2003-Q3
> x4 = Item 1 for 2003-Q4
> x5 = Item 1 for 2004
> x6 = Item 1 for (2003 + 2004)
> x7 = Item 2 for 2003-Q1
> x8 = Item 2 for 2003-Q2
> x9 = Item 2 for 2003-Q3
> x10 = Item 2 for 2003-Q4
> x11 = Item 2 for 2004
> x12 = Item 2 for (2003 + 2004)
> For instance:
> At cell position x4, how do I know that I am in group 2 (Quarters), at
cell
> position x5, how do I know that I am in group 1 (years), and at cell
> position x6, how do I know that I am in group 1 (years) sub total'
>

Matching on from a list

HI,

say I have a list from an sql statement (results list)
this list contains 10 items

In another table, in one particular column - there is a match for one of these items from the initial list.

SO... this may be the list
_____________________
itemnumber
1
2
3
4
5
6
7
8
9
10
----------

in the other table there is a match...
but just for one item on that list.
____________________
othertablefield
11
13
14
3 <-- match
99
78
---------

How do I find that match with my sql statement?SELECT [Othertablefield] FROM Table2 WHERE [Othertablefield] NOT IN (SELECT [itemnumber] FROM Table1)

HTH