Selecting data from SQL Server table according to Month

Question

I have to come up with a way to get data from a SQL server table given it's month

There's a smalldatetime type field called "date" in the "events" table and a field from it looks like this:

29/01/2003 17:00:00

It should be one among those that appear in the gridview when I select jan/03 in a given DropDownList control in ASP.NET.

What would be the best way to do this? Assuming I only want to go as long as 10 years to the past, wouldn't making a dictionary be a good way? How do I change it so the top month is always the current, and each month another options appeaars in the dropdown ?

It should be a common issue, so that shall not be a trick, but I want a good and clean way of doing this, and (since I'm new to asp.net) I myself would come up with something messy after a long time trying.

So what I want is:
1) Select
2) Best way of populating DropDownList (with the current month always at the top)
3) Best way to bind the DropDownList user-friendly way of writing months to the select query-friendly way of writing datetimes

Info:
ASP.NET, (C# or VB.NET), SQL Server 2005

Answer 1

1) SQL for selecting Events based on the dropdown. Consdier this @Selected as a SQL datetime that you pass to a stored proc, valued from the dropdownlist.

@SelectedDate smalldatetime --param to stored proc

SELECT @SelectedDate  = '29/01/2003'

-- get the events for the desired date.
SELECT * FROM Events 
WHERE DATEADD(dd, DATEDIFF(dd, 0, [Date]), 0) = @SelectedDate

2) As a datasource for the dropdown list, gather the dates in the table.

 SELECT   CONVERT(varchar(3), [Date], 100) + ' ' + CONVERT(VARCHAR(5), YEAR([Date])) 
          AS EventDate
 FROM     Events
 GROUP BY CONVERT(varchar(3), [Date], 100) + ' ' + CONVERT(VARCHAR(5), YEAR([Date])) ,
      YEAR([Date])                                                         ,
      MONTH([Date])
 ORDER BY YEAR([Date]) DESC,
      MONTH([Date]) DESC

Answer 2

A million ways to do this, in most of my systems I have a date dimension table used for reporting and other various things that I want to filter by various date criteria. You can easily build a table like this using DATEPART & DATENAME functions of MSSQL.

Example Script (this is from some of my old code):

-- =============================================
-- Build Dimension Date Table
-- =============================================
DECLARE @StartDate as smalldatetime, @EndDate as smalldatetime

SET @StartDate = '04/01/2010'
SET @EndDate = '03/31/2011'

BEGIN
SELECT
DATEPART(dy, @StartDate) as DAY_OF_YEAR,
CASE
WHEN DATENAME(qq,@StartDate)-1=0 THEN
4
ELSE
DATENAME(qq,@StartDate)-1
END AS FISCAL_PERIOD,
DATENAME(m,@StartDate) AS MONTH_DESC,
DATEPART(m,@StartDate) AS MONTH_NUM,
DATEPART(qq,@StartDate) AS QUARTER_NUM,
CONVERT(smalldatetime, CONVERT(CHAR(10),@StartDate,110)) AS SALES_DATE,
REPLACE(CONVERT(CHAR(10),@StartDate,06),' ','-') AS SALES_DATE_SPL,
DATEPART(yy,@StartDate) AS YEAR_NUM,
DATEPART(d,@StartDate) AS DAY_OF_MONTH,
CASE
WHEN DATEPART(m,@StartDate)< 4 THEN
DATENAME(yy,@StartDate)-1
ELSE
DATENAME(yy,@StartDate)
END AS FISCAL_YEAR,
CASE
WHEN DATEPART(m,@StartDate)>3 THEN
DATEPART(m,@StartDate)-3
ELSE
12-(3-DATEPART(m,@StartDate))
END AS FISCAL_MONTH
INTO OLAP_DATE_DIMENSION

SELECT @StartDate = @StartDate + 1

END

WHILE (@StartDate <= @EndDate)
    BEGIN
     BEGIN
     INSERT INTO OLAP_DATE_DIMENSION SELECT
     DATEPART(dy, @StartDate) as DAY_OF_YEAR,
     CASE
     WHEN DATENAME(qq,@StartDate)-1=0 THEN
     4
     ELSE
     DATENAME(qq,@StartDate)-1
     END AS FISCAL_PERIOD,
     DATENAME(m,@StartDate) AS MONTH_DESC,
     DATEPART(m,@StartDate) AS MONTH_NUM,
     DATEPART(qq,@StartDate) AS QUARTER_NUM,
         CONVERT(smalldatetime, CONVERT(CHAR(10),@StartDate,110)) AS SALES_DATE,
     REPLACE(CONVERT(CHAR(10),@StartDate,06),' ','-') AS SALES_DATE_SPL,
     DATEPART(yy,@StartDate) AS YEAR_NUM,
     DATEPART(d,@StartDate) AS DAY_OF_MONTH,
     CASE
     WHEN DATEPART(m,@StartDate)< 4 THEN
     DATENAME(yy,@StartDate)-1
     ELSE
     DATENAME(yy,@StartDate)
     END AS FISCAL_YEAR,
     CASE
     WHEN DATEPART(m,@StartDate)>3 THEN
     DATEPART(m,@StartDate)-3
     ELSE
     12-(3-DATEPART(m,@StartDate))
     END AS FISCAL_MONTH
     END
     SELECT @StartDate = @StartDate + 1
    END

After you have the data you need (probably load 20 years, 10 back and 10 forward) all you have to do is select the data you want. Since you want current month back, I would do a query for everything with a YEAR/MONTH <= TODAY and >= OLDEST DATE ALLOWED.

Example:

SELECT * 
    FROM OLAP_DATE_DIMENSION 
    WHERE SALES_DATE <=  CONVERT(CHAR(10),GETDATE(),110)) 
        AND DAY_OF_MONTH = '1' 
    ORDER BY SALES_DATE DESC;

** This query against the table above (which is probably way over kill for what you need), will return the first day of each month for every month in the table prior to today. Then you can take the various date parts to create a pretty display label to show in your select (e.g. March 2010, February 2010, January 2010, etc...).

Hopefully this makes sense, the only long term "maintenance" task would be updating your table with addition years of data. Since it's only 365 day per/year, you could really load a ton of data with very little impact to your system. If your pages gets lots of hits, you can cache the results so the data is in memory!

Answer 3

Step 1: Make a procedure to generate a list of months before or after today's date. Use a negative parameter to go backwards in time, or a positive to go forward.

create proc Timing.spGetMonthsFromToday(@months int) 
as
BEGIN

    -- Modified Jeff Moden solution found at:
    -- http://www.sqlservercentral.com/Forums/Topic494640-149-1.aspx
    ;WITH
    cteTally AS
    (   
        SELECT TOP (ABS(@months)) 
            N = case when @months < 0 then -1 else 1 end 
                * ROW_NUMBER() OVER (ORDER BY t1.Object_ID)         
        FROM Master.sys.All_Columns t1
            CROSS JOIN Master.sys.All_Columns t2
        UNION
        SELECT 0
    )

    SELECT 
        YearNumber = YEAR(DATEADD(mm,DATEDIFF(mm,0,GETDATE())+t.N,0))
        , MonthNumber = MONTH(DATEADD(mm,DATEDIFF(mm,0,GETDATE())+t.N,0))
        , MonthDesc = DATENAME(month,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+t.N,0)) 
                        + ' ' 
                        + DATENAME(year,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+t.N,0)) 

        , MonthStartDate = DATEADD(mm,DATEDIFF(mm,0,GETDATE())+t.N,0)
        , NextMonthStartDate = DATEADD(mm,DATEDIFF(mm,0,GETDATE())+t.N+1,0)
    FROM 
        cteTally t
    ORDER BY
        N DESC                                      

END
GO
exec Timing.spGetMonthsFromToday -120

Step 2: Make the function callable within .NET. If you don't want to, or don't have an existing DL, you can use a quick Linq to SQL class. Go into Solution Explorer, add a Linq to SQL class. Go into Server Explorer and make a connection to your database. Find the stored procedure and drag it into the functions area of the L2SQL class.

Step 3: Call the function inside of a .NET class and push it into a List that's easily consumable and reusable by your ASP.NET code.

namespace Sample
{
    class Timing
    {
        public struct ListMonth
        {
            public readonly int Year;
            public readonly int Month;
            public readonly string Description;
            public readonly DateTime StartDate;
            public readonly DateTime NextStartDate;
            public ListMonth(int year, int month, string description, DateTime startDate, DateTime nextStartDate)
            {
                Year = year;
                Month = month;
                Description = description;
                StartDate = startDate;
                NextStartDate = nextStartDate;
            }
            public override string ToString()
            {
                return Description;
            }
        }
        public List<ListMonth> GetMonthsFromToday(int months)
        {
            List<ListMonth> _return = new List<ListMonth>();

            dcTimingDataContext dc = new dcTimingDataContext();
            var data = dc.spGetMonthsFromToday(months);
            foreach (var row in data)
            {
                ListMonth month = new ListMonth(row.YearNumber.Value, row.MonthNumber.Value, row.MonthDesc, row.MonthStartDate.Value, row.NextMonthStartDate.Value);
                _return.Add(month);               
            }
            return _return;
        }           
    }
}

Step 4: Bind the list to your dropdown list. I used a WPF front-end for this, but the databinding should be exactly, or very close to, the same. I used -120 as the parameter since you wanted to go back 10 years, but it's adjustable to whatever.

private void LoadMonths()
{
    Sample.Timing timing = new Sample.Timing();
    cboMonths.ItemsSource = timing.GetMonthsFromToday(-120).OrderByDescending(row => row.StartDate);            
}

private void cboMonths_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
    if (((ComboBox)sender).SelectedItem != null)
    {
        Sample.Timing.ListMonth month = (Sample.Timing.ListMonth)((ComboBox)sender).SelectedItem;
        Console.WriteLine(month.StartDate); // just showing these dates are accessible under the hood
        Console.WriteLine(month.NextStartDate); // just showing these dates are accessible under the hood
        /* use these dates in your query against data set where eventdate >= month.StartDate and eventdate < month.EndDate */
    }
}

Step 5: As you can see at the end of Step 4, when a user makes a selection, you can get the actual datetimes for the beginning and end of the month. Then pass those to your query against your "Events" table. Make sure to use >= with StartDate and < with NextStartDate.

Cheers, hope this helps!

Answer 4

Thank you guys for the answers, I've used one that looks like pcampbell's (+1 to him):

SELECT * FROM whatevertable where CONVERT(CHAR(7),DataHoraInicio,120) = '2002-10'

(this gets every registry from october 2002)

I don't see any set back for this method, in fact, it's working flawlessly

Thanks again!