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

    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.

    Thanks

    Chris
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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