Professional Web Applications Themes

Adding non exsisting rows in a view. - Microsoft SQL / MS SQL Server

HI, I'm currently working on a view to display production information, the view is broken down over a 24 hour period, displaying each production line, what it manufactured etc. The view is over a table which contains data on every item produced and is grouped by hour. This works fine provided that atleast one item is produced every hour, however if there are any hours in the day where an item is not produced then the view omits the hour. Is there any way to insert the missing hour into the view with a zero value? Many thanks Jim...

  1. #1

    Default Adding non exsisting rows in a view.

    HI,

    I'm currently working on a view to display production information, the
    view is broken down over a 24 hour period, displaying each production
    line, what it manufactured etc.

    The view is over a table which contains data on every item produced
    and is grouped by hour. This works fine provided that atleast one item
    is produced every hour, however if there are any hours in the day
    where an item is not produced then the view omits the hour.

    Is there any way to insert the missing hour into the view with a zero
    value?

    Many thanks

    Jim
    Jim Guest

  2. #2

    Default Re: Adding non exsisting rows in a view.

    DDL and DML?

    Wild guess would be that you need to use outer join!?!

    --
    Dean Savovic
    www.teched.hr


    "Jim" <com> wrote in message news:google.com... 


    Dean Guest

  3. #3

    Default Re: Adding non exsisting rows in a view.

    You have to use a separate numbers table with the values for all the hours
    to achieve that:

    CREATE TABLE numbers (number INT )
    DECLARE i INT
    SET i = 0
    WHILE i < 24
    BEGIN
    INSERT numbers (number) VALUES (i)
    SET i = i + 1
    END

    And then outer join your hours to this table:

    SELECT n.number, COUNT(p.product_date)
    FROM numbers n
    LEFT OUTER JOIN products p
    ON n.number = DATEPART(hh, p.product_date)
    GROUP BY ALL n.number


    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Jim" <com> wrote in message
    news:google.com... 


    Jacco Guest

Similar Threads

  1. ASP.NET 2 GridView: Adding rows
    By Edward in forum ASP Components
    Replies: 0
    Last Post: January 14th, 08:42 PM
  2. Adding more rows to one column/cell only?
    By kate_charlwood@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 2
    Last Post: October 18th, 12:47 PM
  3. adding rows
    By Perecli Manole in forum ASP.NET Data Grid Control
    Replies: 6
    Last Post: August 31st, 11:13 PM
  4. Adding rows to a Datagrid
    By Paul J in forum ASP.NET General
    Replies: 0
    Last Post: July 21st, 04:20 PM
  5. datagrid generated with rows from a view and its dillema
    By David in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: July 4th, 04:35 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139