Max Date for Each Month

Here is the code I currently have, but I want to pull just the max day for each of the 12 months from the database. For example for the month 01 we may have
01
02
03
04
07
28

I would want to pull ONLY 01/28/2011
and so on for each month.
Here is the code I have already, but I'm stuck gathering ONLY the MAX date for month.


USE VDBA
SELECT [DateTime]
      ,RIGHT(Year(DateTime),2) AS YearPart
      ,RIGHT('0'+cast(Month(DateTime) AS VARCHAR(2)),2) AS MonthPart
      ,DAY(DateTime) as SampleDay
      ,RIGHT('0'+cast(Month(DateTime) AS VARCHAR(2)),2)+'/'+right(Year(DateTime),2) AS ChartDate
      ,[DataSpaceUsage]
      ,[IndexSpaceUsage]
      ,[SpaceAvailable]
      ,[Size]
      ,[SpaceAvailableInMb]
  FROM [VDBA].[dbo].[SQLDatabaseSize]
  WHERE DatabaseName = 'VDBA' and DateTime>'2011'
  ORDER BY DateTime
LVL 7
El ConquistadorSenior DBAAsked:
Who is Participating?
 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
What is the result of this query?

SELECT a.* FROM
[VDBA].[dbo].[SQLDatabaseSize] a
INNER JOIN
(
SELECT Max([DateTime]) [DateTime]
    ,RIGHT(Year(DateTime),2) AS YearPart
      ,RIGHT('0'+cast(Month(DateTime) AS VARCHAR(2)),2) AS MonthPart
FROM [VDBA].[dbo].[SQLDatabaseSize]
  WHERE DatabaseName = 'VDBA' and DateTime>'2011'
 GROUP BY RIGHT(Year(DateTime),2)
      ,RIGHT('0'+cast(Month(DateTime) AS VARCHAR(2)),2)
) B on a.[DateTime] = b.[DateTime]
0
 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
SELECT a.* FROM
[VDBA].[dbo].[SQLDatabaseSize] a
INNER JOIN
(
SELECT Max([DateTime]) [DateTime]
    ,RIGHT(Year(DateTime),2) AS YearPart
      ,RIGHT('0'+cast(Month(DateTime) AS VARCHAR(2)),2) AS MonthPart
FROM [VDBA].[dbo].[SQLDatabaseSize]
  WHERE DatabaseName = 'VDBA' and DateTime>'2011'
 GROUP BY RIGHT(Year(DateTime),2)
      ,RIGHT('0'+cast(Month(DateTime) AS VARCHAR(2)),2)
) B on a.[DateTime] = b.[DateTime]
 WHERE a.DatabaseName = 'VDBA'
0
 
Elias SaadéConnect With a Mentor Senior DBACommented:
PLEASE USE THE BELOW
 
USE VDBA
SELECT MAX([DateTime])
      ,RIGHT(Year(DateTime),2) AS YearPart
      ,RIGHT('0'+cast(Month(DateTime) AS VARCHAR(2)),2) AS MonthPart
      ,MAX(DAY(DateTime)) as SampleDay
      ,MAX(RIGHT('0'+cast(Month(DateTime) AS VARCHAR(2)),2)+'/'+right(Year(DateTime),2)) AS ChartDate
      ,MAX([DataSpaceUsage])
      ,MAX([IndexSpaceUsage])
      ,MAX([SpaceAvailable])
      ,MAX([Size])
      ,MAX([SpaceAvailableInMb])
  FROM [VDBA].[dbo].[SQLDatabaseSize]
  WHERE DatabaseName = 'VDBA' and DateTime>'2011'
  GROUP BY RIGHT(Year(DateTime),2))
      ,RIGHT('0'+cast(Month(DateTime) AS VARCHAR(2)),2))
  ORDER BY MAX([DateTime])
0
 
El ConquistadorSenior DBAAuthor Commented:
@EliasSaade
I don't understand why all the MAX syntax?
0
 
El ConquistadorSenior DBAAuthor Commented:
I broke the points apart, they ALL worked, and gave me grounds to build upon.

RajkumarGS actually granted me the ability to view every database, instance name, size, etc that were in the database.all at once. All grand scripts, and I'm glad and thankful you both answered so quickly. Wish they allowed us to grant more points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.