adding specific numbers in a column

Database

adding specific numbers in a column

Hello,
I have the following data set:

 

cell_IDdatehouroutage duration
abc420:05:00
abc430:01:02
abc5130:15:21
xyz5150:07:00

 

Every cell_ID has few hours in every date when there was an outage on that particular cell_id (i.e duration for which that cell was inacvtive) 

 

Every cell_id shoudl have 24 hours per day. It should show 24 rows for every cell and date including the hours when there was an outage and even those hours when there was no outage, with outage value as '0' or null.

 

Desired output (considering 1st cell only):

 

cell_IDdatehouroutage duration
abc400
abc410
abc420:05:00
abc430:01:02
abc440
abc450
abc460
abc470
abc480
abc490
abc4100
abc4110
abc4so on till 230

 

same should happend for cell_ID abc when the date changes. or the cell_ID changes. 

 

Can anyone help?


Accepted Solutions
Junior Contributor

Re: adding specific numbers in a column

You must Cross join a distinct list of cell_IDs to a calendar table with the range of dates you need and another Cross join to a table with 24 rows, one per hour.

This results in all ids/dates/hours and then it's Left joined to your existing data.

1 ACCEPTED SOLUTION
3 REPLIES

Re: adding specific numbers in a column

Just to simplify: I need to add remaining hours in (hour) column when there wasn't any outage. 

Junior Contributor

Re: adding specific numbers in a column

You must Cross join a distinct list of cell_IDs to a calendar table with the range of dates you need and another Cross join to a table with 24 rows, one per hour.

This results in all ids/dates/hours and then it's Left joined to your existing data.

Re: adding specific numbers in a column

Thanks dnoeth,

I tried almost the same approach a while ago and it worked.

tmp_table: actual data with cell_ids

cntr: 0-23 values.

 

SyntaxEditor Code Snippet

      sel 
      case when a.num = b.hr then cell_site_key else cell_site_key end as cell_site_key,
      case when a.num = b.hr then yr else yr end as yr,
      case when a.num = b.hr then mnth else mnth end as mnth,
      case when a.num = b.hr then dt else dt end as dt,
      case when a.num = b.hr then hr else a.num end as hr,
     case when a.num = b.hr then total_outage_perhour else cast ('00:00:00' as INTERVAL HOUR(2) TO SECOND(0)) end as outage
      
      from cntr a cross join tmp_table b