Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2014
    Posts
    5

    Unanswered: How to select all days in a date range even if no data exists for some days

    Hi. I have a table named services and a table called serviceCost which has the costs of the services for date ranges. The serviceCost table has 3 columns:
    DateFrom, DateTo and Cost.
    I want to show the cost of the service for a given date but if there is no cost for that date, I want to show the service too.
    How could I do that?
    regards

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm going to guess that a LEFT JOIN is going to be part of your solution.

    Without an example or a clearer statement of your problem, I can't give a more specific answer than that.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    A "tbDates" table comes in handy for stuff like this. One row per date, pre-populated out 20 years or so past and future; whatever range makes sense for your application.

    For my use, I've created a column for things like "same day last year", "week_start_date", "Week_end_date", etc - handy for reporting sales comparisons without having performance killing inline date functions. Because it's static; each column has it's own index.

  4. #4
    Join Date
    Jan 2014
    Posts
    5
    Thanks vich, but I dont understand your answer. I dont want (and I could not) modify the tables schema. I need to create a query to achieve the results that I posted here
    Thanks for you help.
    Any ideas??

  5. #5
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Can you create a memory table?

    I'm not savvy enough to return rows for stuff that's not there, but this guy is.
    Last edited by vich; 02-24-14 at 00:25.

Posting Permissions

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