Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Posts
    111

    Unanswered: Filling in missing data for a continous range, e.g. x-axis ..

    Hi,

    I want to display some stats using captured information. the x-axis is the date on which something occured. the y-axis is the number of occurance on that day.

    The problem is that the x-axis need to be of continous range, so say from 1st Mar 2006 to 8th Mar 2006, I need to display every date within that range.

    I've gathered the neccessary stats with a group statement but I was hoping I can fill in the gaps without having to loop through the returned data to identify missing dates, is there any built int SQL Server function which will allow me to do this within my SELECT statement?

    Cheers
    james
    `

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    I dont think there is such a inbuilt function available in sql server. But if you could post the ddl ,some sample data and the expected result, i m sure someone here would definately be able to help you.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    One way to do this is to enumerate the values that you need, something like:
    Code:
    SELECT d
       FROM (SELECT Convert(DATETIME, '2006-03-01') AS d
       UNION SELECT '2006-03-02'
       UNION SELECT '2006-03-03'
       UNION SELECT '2006-03-04'
       UNION SELECT '2006-03-05'
       UNION SELECT '2006-03-06'
       UNION SELECT '2006-03-07'
       UNION SELECT '2006-03-08') AS z
    -PatP

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Or more generically, create a table of sequential numbers in your database starting with zero, from which you can dynamically create any sequence of dates. Like this:
    Code:
    select	XValues.XValue,
    	YourData.YValue
    from	--XValues
    	(select	dateadd(day, SeqValue, '2006-01-01') as XValue
    	from	SequentialNumbers) XValues
    	left outer join YourData on XValues.XValue = YourData.XValue
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    Most times that I have need to do something like this, I have generated a calendar master table with dates and perhaps some bit flags to indicate other generic data about that date (eg, weekends, public holidays, non-working day).

    Then I can join (or outer join) my recordset to the calendar master table for reporting purposes.

    Regards,

    hmscott
    Have you hugged your backup today?

  6. #6
    Join Date
    Aug 2003
    Posts
    111
    Quote Originally Posted by hmscott
    Most times that I have need to do something like this, I have generated a calendar master table with dates and perhaps some bit flags to indicate other generic data about that date (eg, weekends, public holidays, non-working day).

    Then I can join (or outer join) my recordset to the calendar master table for reporting purposes.

    Regards,

    hmscott
    Thank you all
    I think blindman and htmscott have described pretty much the same method.
    I will give these a try and see how it goes.

    Cheers
    James
    Last edited by nano_electronix; 03-14-06 at 20:22.

  7. #7
    Join Date
    Aug 2003
    Posts
    111
    Thanks again guys, the method suggested by you guys works great.

    Cheers
    James

Posting Permissions

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