Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003

    Unanswered: Display date even if null

    Good day,

    I have a query that returns hours of vacation time taken for each specfic day of the year. But if no time is taken on for example Jan. 19th, that date do not show up in the query. I am wondering is it possible to return all 365 days and display a zero,if nothing is booked for that day. Attached is the db.


    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    The best way to handle this situation is to create a table in your database that consists of nothing but sequential numbers, from 0 up to at least 365. Then you can create queries that apply the dateadd() function to this table to create dynamic lists of times, by day/hour/week or whatever interval you want.

    Your final query will be based on this "datelist" query, and will left join to your production data so that a record is returned for every day.

    Tables of sequential values can be used to solve many thorny SQL problems, especially those involving date ranges.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

Posting Permissions

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