Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2011
    Posts
    6

    Question Unanswered: Filter dates -> change frequency

    Hi everyone,

    I am new to SQL. I have a table containing one date field and one value field. I have daily entries i.e. every day I have a new date and a new value except for weekends. Now I am trying to run a query to change the frequency of the data to monthly for example. This means filtering the table in order to only keep all the end of month dates or more generally the last entry of each month (if the end of month is a weekend select the last date in the month).

    I tried using dateadd and datediff but this returns me the end of month and for only one date that is I don't get more than one date.

    Is this something MS Access SQL can deal with? Do you guys have any ideas how my SQL query should look like?

    Thanks for reading

    manu

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a solution for finding the last entry of each month:
    Code:
    SELECT Max(Tbl_SomeTable.SomeDate) AS MaxOfSomeDate
    FROM Tbl_SomeTable
    GROUP BY DatePart("yyyy",[SomeDate]) & DatePart("m",[SomeDate]);
    Have a nice day!

  3. #3
    Join Date
    Jul 2011
    Posts
    6
    Hi Sinndho,

    Thank you very much, it helps a lot! It works very well except that it also gets the last date in the table which is not an end of month. Would you have an idea to exclude the last month of the current year so that I only get last entry of each month?

    I tried to convert your query in order to transform the daily entries into a weekly frequency i.e. get the last entry of each week. So far the query does not do what i want. is it possible to do something like that in SQL?

    ++

  4. #4
    Join Date
    Jul 2011
    Posts
    6
    Hi everyone,

    I somehow progresses a bit and am now able to achieve what I want. I am running a subquery which returns what I want but which is extremly slow. I am pretty sure this simple query can be optimized big time with trying to avoid subqueries.

    does anyone has an idea?

    SELECT *
    FROM TS
    WHERE TS.Date IN (

    SELECT Max(TS.Date)
    FROM TS
    GROUP BY DatePart("yyyy",TS.Date) & DatePart("m",TS.Date))
    ORDER BY TS.Date DESC;

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Like this:
    Code:
    SELECT Max(SomeDate) AS MaxOfSomeDate
    FROM (SELECT Tbl_SomeTable.SomeDate FROM Tbl_SomeTable
          WHERE DatePart("m",Tbl_SomeTable.SomeDate)<> DatePart("m", Now) AND 
                DatePart("yyyy",Tbl_SomeTable.SomeDate) <> Datepart("yyyy",Now))
    GROUP BY DatePart("yyyy",SomeDate) & DatePart("m",SomeDate)
    Have a nice day!

  6. #6
    Join Date
    Jul 2011
    Posts
    6
    Thanks that works great to remove the last date! Now I am still struggling to filter the value in an optimal way. I (you ) got the date filter right, is there a way to avoid running a subquery like the one below.

    In my TS table I have. TS.Id, TS.Date, TS.Close. What I would like my query to do is to list the id, date and close for only the end of month entries. The below query carries that out succesfully but is very slow. I am pretty sure there is a way because I am in fact only working on one table...

    Do I make sense? Thanks for your help

    SELECT *
    FROM TS
    WHERE TS.Date IN (

    SELECT Max(TS.Date) AS MaxOfSomeDate
    FROM (SELECT TS.Date FROM TS
    WHERE DatePart("m",TS.Date)<> DatePart("m", Now) AND
    DatePart("yyyy",TS.Date) <> Datepart("yyyy",Now))
    GROUP BY DatePart("yyyy",TS.Date) & DatePart("m",TS.Date)
    ORDER BY TS.Date DESC;

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Unfortunately I don't see any other way to write the query (which does not mean there isn't any). And you're right: it's awfully slow. I converted it to a stored procedure on a SQL Server (MS SQL 2005), it's faster of course, but the execution plan looks ugly.

    Actually, this is one very rare case where a VBA procedure performs way better than a query. If you can adapt it to your application, this is a solution that's rather fast (average time on my system: 2.5 sec. for retrieving 175 rows from 25000 rows in a table on a SQL Server):
    Code:
    Function MaxOfDATE()
    
        Dim rst As DAO.Recordset
        Dim strsql As String
        Dim FirstDate As Date
        Dim LastDate As Date
        Dim MinDate As Date
        Dim MaxDate As Date
        
        strsql = "DELETE FROM Tbl_MaxDates;"
        CurrentDb.Execute strsql, dbFailOnError
        FirstDate = DMin("SomeDate", "Tbl_SomeTable")
        LastDate = DMax("SomeDate", "Tbl_SomeTable", "SomeDate < #" & Format(DateSerial(Year(Now), Month(Now), 1), "mm/dd/yyyy") & "#")
        MinDate = DateSerial(Year(FirstDate), Month(FirstDate), 1)
        MaxDate = DateAdd("m", 1, MinDate)
        Do Until MinDate >= LastDate
            strsql = "INSERT INTO Tbl_MaxDates ( SomeDate, SysCounter ) " & _
                     "SELECT Max(SomeDate), Max(SysCounter) " & _
                     "FROM (SELECT SomeDate, SysCounter " & _
                     "FROM Tbl_SomeTable " & _
                     "WHERE SomeDate >= #" & Format(MinDate, "mm/dd/yyyy") & "# AND SomeDate < #" & Format(MaxDate, "mm/dd/yyyy") & "# " & _
                     "ORDER BY SomeDate DESC) " & _
                     "HAVING MAX(SysCounter) IS NOT NULL;"
            CurrentDb.Execute strsql, dbFailOnError
            MinDate = MaxDate
            MaxDate = DateAdd("m", 1, MinDate)
        Loop
        
    End Function
    The function fills a local table [Tbl_MaxDates] with the required values. In the example I just collected the Primary Key ([SysCounter]) and the required date ([SomeDate]) but it's easy enough to modify the SQL statement for collecting every desired columns.

    If you don't care for duplicates [SomeDate] values in [Tbl_MaxDates] (you can filter it later on with a GROUP BY query) you can replace the SQL statement:
    Code:
            strsql = "INSERT INTO Tbl_MaxDates ( SomeDate, SysCounter ) " & _
                     "SELECT TOP 1 SomeDate, SysCounter " & _
                     "FROM Tbl_SomeTable " & _
                     "WHERE SomeDate >= #" & Format(MinDate, "mm/dd/yyyy") & "# AND SomeDate < #" & Format(MaxDate, "mm/dd/yyyy") & "# " & _
                     "ORDER BY SomeDate DESC;"
    In this case, the average time on my system is 1.35 sec. for retrieving 175 rows from 25000 rows in a table on a SQL Server.
    Have a nice day!

  8. #8
    Join Date
    Jul 2011
    Posts
    6
    Sinndho, thanks for taking the time to do all the testing! I will look into your VBA workaround.

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  10. #10
    Join Date
    Jul 2011
    Posts
    6
    Although your solution is very nice and elegant. I would rather use the query way even if slower. I will save the query in a table a then run the query once a month.

    I am still struggling to get the query exactly right. I actually have many different daily time series (field Id in TS column) in my database and the last entry for each month can be different for each time series. So I somehow have to take that into account. This is why I also included TS.Id in my GROUP BY statement. This unfortunately does not exactly resolve the problem given that my first WHERE concerns only dates i.e. no way to differentiate which time series (TS.Id) has what last entry for each month.

    This results in multiple entries for some time series e.g.
    Id Date Close
    1 30.12.2003 109.9611
    1 31.12.2003 109.9617

    I should therefore make some sort a grouped condition on Id and lastDayOfMonth together.

    Does someone has an idea?

    Code:
    SELECT * INTO TSM
    FROM TS
    WHERE (DatePart("m",TS.Date)<> DatePart("m", Now) OR 
                   DatePart("yyyy",TS.Date) <> Datepart("yyyy",Now))
                AND  TS.Date IN  (
    
    SELECT Max(TS.Date)
    FROM TS
    GROUP BY DatePart("yyyy",TS.Date) & DatePart("m",TS.Date) & TS.Id)
    ORDER BY TS.Date DESC;

    Thanks,

    Manu

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •