• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

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
0
concretesailors
Asked:
concretesailors
  • 2
  • 2
3 Solutions
 
Rajkumar GsSoftware 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 GsSoftware 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é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
 
concretesailorsAuthor Commented:
@EliasSaade
I don't understand why all the MAX syntax?
0
 
concretesailorsAuthor 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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now