Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2010
    Posts
    32

    Unanswered: Order Counts by hour, with hours that have no data and need to show 0

    Hello,

    I have a month's worth of data to count by hour. most hours have data, some do not. when i count them by hour, i might not get all 24 rows as no data for an hour means it will not have a row. I still need the row, and it should show 0.

    sample data

    Code:
    WITH tbl1 (dd1, hr1, value1) AS ( 
      VALUES 
        ('2015-12-01', '00', '1234')
      , ('2015-12-01', '02', '2345') 
    ) ( 
      SELECT dd1, hr1, value1 
      FROM tbl1 
    ) WITH UR
    this results in:

    Code:
    2015-12-01	00	1234
    2015-12-01	02	2345
    I need to fabricate hour "01" with "0" for the value. I came up with this.

    Code:
    WITH tbl1 (dd1, hr1, value1) AS ( 
      VALUES 
        ('2015-12-01', '00', '1234')
      , ('2015-12-01', '02', '2345') 
    ), tbl2 (dd2, hr2, value2) AS ( 
      VALUES 
        ('2015-12-01', '00', '0')
      , ('2015-12-01', '01', '0') 
      , ('2015-12-01', '02', '0') 
    ) ( 
      SELECT COALESCE(dd1, dd2), COALESCE(hr1, hr2), COALESCE(value1 , value2) 
      FROM tbl1 
      RIGHT OUTER JOIN tbl2 ON dd1=dd2 AND hr1=hr2 
    ) WITH UR
    this results what i need:

    Code:
    2015-12-01	00	1234
    2015-12-01	02	2345
    2015-12-01	01	0
    but it is not scalable because every column in the original data needs to show up in the temp table, enlarging it exponentially with every field that it's grouped by. this example is dumbed down so no aggregation is present but it's simpler for ease of understanding.

    I can only do a select, but since this is going in excel, an excel solution to fill in the blanks would work as well. it actually needs to go in iSeries db but i can translate it later. I just need to get the logic down.

    please advise what the best way to do this.. right in db2/iseries, or excel or anywhere else, and briefly what a method might look like?

    thanks,
    -don

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I don't think that the code you posted is syntactically correct, but I don't have a DB2 machine handy to test it.

    I concocted a "rude start" that might help you get going using a different SQL dialect, but it ought to either run "as is" or at least give you some ideas:
    Code:
    WITH hours (h) AS (
    SELECT h
       FROM (VALUES ('00'), ('01'), ('02'), ('03'), ('04'), ('05')
    ,     ('06'), ('07'), ('08'), ('09'), ('10'), ('11')
    ,     ('12'), ('13'), ('14'), ('15'), ('16'), ('17')
    ,     ('18'), ('19'), ('20'), ('21'), ('22'), ('23')) AS z(h)
    ), tbl1 (dd1, hr1, value1) AS ( 
       SELECT dd1, hr1, value1
          FROM (VALUES 
             ('2015-12-01', '00', '1234')
    ,        ('2015-12-01', '02', '2345')) AS z2(dd1, hr1, value1)
    ), tbl2 (dd2, hr2, value2) AS ( 
       SELECT dd2, hr2, value2
          FROM (VALUES 
             ('2015-12-01', '00', '0')
    ,        ('2015-12-01', '01', '0') 
    ,        ('2015-12-01', '02', '0')) AS z3(dd2, hr2, value2)
    )
    SELECT dd, h, v
      FROM hours
      FULL OUTER JOIN (SELECT * FROM tbl1
         UNION ALL SELECT * FROM tbl2) AS z4 (dd, hr, v)
         ON (hours.h = z4.hr)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    Don,

    There is no need for tbl2. See the following example.

    Code:
    with tbl_h ( hr1 ) as
    ( select * from  (VALUES ('00'), ('01'), ('02'), ('03'), ('04'), ('05')
    ,     ('06'), ('07'), ('08'), ('09'), ('10'), ('11')
    ,     ('12'), ('13'), ('14'), ('15'), ('16'), ('17')
    ,     ('18'), ('19'), ('20'), ('21'), ('22'), ('23') ) ) ,
    
    tbl1 (dd1, hr1, value1) AS 
    ( select * from (   VALUES 
        ('2015-12-01', '00', '1234') ,
        ('2015-12-01', '02', '2345') ))
    
    select '2015-12-01' , -- date for which query is executed
    tbl_h.hr1 , coalesce(tbl1.value1,'0')
    from tbl_h 
    left outer join tbl1 on  tbl_h.hr1 = tbl1.hr1
    order by tbl_h.hr1
    Satya

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hello,

    try this:
    Code:
    WITH tbl1 (dd1, hr1, value1) AS (VALUES 
      ('2015-12-01', '00', '1234')
    , ('2015-12-01', '02', '2345') 
    , ('2015-12-03', '03', '3456') 
    )
    , h (hr) as (
    values 0
      union all
    select hr+1
    from h
    where hr<23  
    )  
    SELECT d.dd, digits(dec(h.hr, 2)) hr, coalesce(t.value1, 0) value1 
    FROM (
      select distinct dd1
      from tbl1
    ) d (dd)
    cross join h 
    left join tbl1 t on t.dd1=d.dd and t.hr1=digits(dec(h.hr, 2))
    order by d.dd, h.hr
    If you want to fill date gaps as well (for example, to have 24 rows with zeroes for 2015-12-02), this statement can be modified.
    Regards,
    Mark.

  5. #5
    Join Date
    Apr 2010
    Posts
    32
    thanks everyone for the replies!

    the recursion one works for multiple dates. i see how it's done. i can add missing dates using this as well. i forgot to add this in my original data sample.

    appreciate it!

    -don

Posting Permissions

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