Results 1 to 8 of 8

Thread: how to query to list range dates

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    how to query to list range dates

    This involve to table field to query a result but I would like to list in rows a result of sql query between two dates. Im using mysql. I been thinking awhile now, i cant have idea of what statement to use.

    I tried select curdate() as mycurdate where curdate() between '2001-01-01' and '2001-12-31' but I got error statement error.

    I have hoping to have result

    |mycurdate|
    2001-01-01
    2001-01-02
    2001-01-03
    .
    .
    .
    2001-12-31
    Last edited by codesearcher; Jun 12th, 2016 at 01:49 AM.

  2. #2
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: how to query to list range dates

    maybe
    Code:
    Private Sub Command1_Click()
       Dim TheStartDate As Date
       Dim TheEnddate As Date
       Dim Adate As Date
    
       TheStartDate = #1/1/2016#
       TheEnddate = #12/31/2016#
       
       For Adate = TheStartDate To TheEnddate
          Debug.Print Adate
       Next
    End Sub
    ?
    do not put off till tomorrow what you can put off forever

  3. #3
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,695

    Re: how to query to list range dates

    Why use curdate() ?
    What is you table structure?
    Show the fields...

  4. #4

    Thread Starter
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    Re: how to query to list range dates

    I have this sample transactions table, for example

    tbl_transactions
    field1: trdate
    field2: userid

    Now if I have records like

    trdate | userid
    01/01/2016 | 1
    01/06/2016 | 2
    01/11/2016 | 5
    01/21/2016 | 10

    I wanted to present a list from 01/01/2016 to 01/31/2016 like to
    Date | No.of Users
    01/01/2016 | 1
    01/02/2016 | 0
    01/03/2016 | 0
    01/04/2016 | 0
    01/05/2016 | 0
    01/06/2016 | 1
    01/07/2016 | 0
    01/08/2016 | 0
    01/09/2016 | 0
    01/10/2016 | 0
    01/11/2016 | 1
    .
    .
    .
    01/31/2016 | 0

    As you can see, I have no table with data that has dates from 01/01 to 01/31/2016 so I cant do a relational query with it.

  5. #5
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: how to query to list range dates

    is that representative ?
    01/01/2016 | 1
    01/06/2016 | 2
    01/11/2016 | 5
    01/21/2016 | 10

    or is the following also possible ?
    01/01/2016 | 1
    01/01/2016 | 2
    01/01/2016 | 5
    01/21/2016 | 10

    and do you really want the dates that have no users included ?

    edit:
    As you can see, I have no table with data that has dates from 01/01 to 01/31/2016 so I cant do a relational query with it.
    creating such a table is not rocket science, is it ?
    Last edited by IkkeEnGij; Jun 12th, 2016 at 10:11 AM. Reason: creating table
    do not put off till tomorrow what you can put off forever

  6. #6

    Thread Starter
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    Re: how to query to list range dates

    Though is it possible to create a table with dates so I can do relational query to it, but I am thinking if it is also possible to get the same result without create a table with all the list of dates.

    The tbl_transactions table can have the records you stated above IkkeEnGij.

  7. #7
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,695

    Re: how to query to list range dates

    Quote Originally Posted by codesearcher View Post
    Though is it possible to create a table with dates so I can do relational query to it, but I am thinking if it is also possible to get the same result without create a table with all the list of dates.
    Sure?

  8. #8
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: how to query to list range dates

    the following gives the number of users for every date that has at least 1 user
    Code:
    SELECT TheTable.TheDate, Count(TheTable.TheDate) AS NumberOfUsers
    FROM TheTable
    GROUP BY TheTable.TheDate;
    if you join this with a table having all the dates, you have what you want

    alternatively you could have a 2 column flexgrid with 1 column having all the dates
    and fill the other column by looping trough the above

    alternatively you could use access with a table having all the dates (maybe for a few centurys)
    and a link to your table, and do the querying and joining in access
    just a suggestion
    do not put off till tomorrow what you can put off forever

Posting Permissions

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



Click Here to Expand Forum to Full Width