SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Jul 2008
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Show last 30 days, even if no records exist

    I've done a bit of searching but no one has this this specific example.

    I have a transaction database which stores the datetime stamp as a unix value. I am trying to create a report that shows a count of transactions that happen per day. The select statement converts the unix timestamp to '2010-03-16' format so I would get a result of

    day|count
    2010-03-16 | 7
    2010-03-15 | 3
    2010-03-13 | 2
    2010-03-12 | 5
    2010-03-10 | 6

    My problem, as shown above, is that there are days where no transactions occurred. I need every day shown, even if it has zero.

    My first idea was to do a sub-query that would output the last 30 days in the same format, then do an outer join so nulls will fill in the blanks. But since I can't find any examples of anything like this, I'm beginning to wonder if this is the best way.

    So my question is two-fold:
    1) Is this best method for filling in the blank days? Admittedly I could create a dedicated table of every day, but that seems like a huge waste.
    2) If I'm on the right track, how can I generate a sub-query of the last 30 days?

    Thanks

    /Cyberfunkr

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The answer is two-fold:

    1) Display logic belongs in your application, not the database. The appropriate place to format data for output is in your app, where you can easily display a range of dates.

    2) If you must do it in SQL, then create a table with the numbers 1 to 31. Use a join against that (perhaps with a little string concatenation) to get the dates. You can use LAST_DAY() in your WHERE clause to not pass 28/30 if it's not a 31 day month.


Bookmarks

Posting Permissions

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