Automatically add missing records with SQL

Dev Shed Forums Sponsor:

December 27th, 2004, 06:50 AM

MsXgAmEs

Registered User

Join Date: Dec 2004

Posts: 4

Time spent in forums: < 1 sec
Reputation Power: 0

Automatically add missing records with SQL

Hi,

I have a table that contains 2 columns. The first column has the datetime, and the second has a value (see the "Before" table). In my SELECT query I want to select all records like 0:00:00 <= Date <= 0:40:00. However the missing 10-minutes value 0:30:00 should be automatically inserted with value = NULL.

Before:

Code:

Date                       Value
-------------------   -------------------
13/12/2004 0:00:00   15
13/12/2004 0:10:00   17
13/12/2004 0:20:00   21
13/12/2004 0:40:00   12
13/12/2004 0:50:00   13

After:

Code:

Date                       Value
-------------------   -------------------
13/12/2004 0:00:00   15
13/12/2004 0:10:00   17
13/12/2004 0:20:00   21
13/12/2004 0:30:00   NULL
13/12/2004 0:40:00   12

Can anyone help me? Thanks.

December 27th, 2004, 08:50 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,716

Time spent in forums: 1 Month 3 Weeks 6 Days 18 h 25 m 10 sec
Reputation Power: 986

Code:

create table MsXgAmEs
( date datetime
, value smallint
)

set dateformat dmy

insert into MsXgAmEs values ('13/12/2004 0:00:00',15)
insert into MsXgAmEs values ('13/12/2004 0:10:00',17)
insert into MsXgAmEs values ('13/12/2004 0:20:00',21)
insert into MsXgAmEs values ('13/12/2004 0:40:00',12)
insert into MsXgAmEs values ('13/12/2004 0:50:00',13)

create table integers (i integer)
insert into integers (i) values (0) 
insert into integers (i) values (1) 
insert into integers (i) values (2) 
insert into integers (i) values (3) 
insert into integers (i) values (4) 
insert into integers (i) values (5) 
insert into integers (i) values (6) 
insert into integers (i) values (7) 
insert into integers (i) values (8) 
insert into integers (i) values (9) 

select dateadd(mi,10*i,'13/12/2004 0:00:00') as starttime
     , MsXgAmEs.value
  from integers
left outer
  join MsXgAmEs
    on dateadd(mi,10*i,'13/12/2004 0:00:00')
     = MsXgAmEs.date
 where i between 0 and 5
order
    by starttime 
 
2004-12-13 00:00:00.000	15
2004-12-13 00:10:00.000	17
2004-12-13 00:20:00.000	21
2004-12-13 00:30:00.000	
2004-12-13 00:40:00.000	12
2004-12-13 00:50:00.000	13

__________________
r937.com | rudy.ca

December 27th, 2004, 03:42 PM

MsXgAmEs

Registered User

Join Date: Dec 2004

Posts: 4

Time spent in forums: < 1 sec
Reputation Power: 0

Thank you very much. This solved my problem. Next to do for me is to refresh my outer joins

December 28th, 2004, 07:54 AM

MsXgAmEs

Registered User

Join Date: Dec 2004

Posts: 4

Time spent in forums: < 1 sec
Reputation Power: 0

Yesterday, I tested the query on MySQL and PHP. Instead of using "dateadd" I used "date_add" function and everything worked perfectly. Today I am testing in the MSSQL environment (on PHP) and I can't get the LEFT OUTER JOIN to work.

Code:

$sql = "SELECT dateadd(mi, 10*i, '07/01/2004 0:00:00') AS StartTijd
           , CVTable.U1Avg
           FROM minutenwaarden
           LEFT OUTER JOIN CVTable ON dateadd(mi, 10*i, '07/01/2004 0:00:00') 
            = CVTable.StartTime
           WHERE i between 0 and 8733
            AND CVTable.MeasureNo = 2
           ORDER BY StartTijd";

I checked the manual and it states that an outer join returns all the values from one table (minutenwaarden) and in case there is a equal value in CVTable it returns this value in the second column, otherwise it returns NULL.

In my query this means that I expect 8734 date records (StartTijd) with the last value U1Avg <> NULL (because this is the first element in my table (CVTable) that is filled). However this query only returns 1 record: the 8734th. All the elements that cannot be found in the table CVTable are omitted. This is in my opinion against the definition of an OUTER JOIN. Why this behaviour?

December 28th, 2004, 08:05 AM

swampBoogie

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,775

Time spent in forums: 2 Weeks 4 Days 16 h 17 m
Reputation Power: 37

Because you have

Code:

AND CVTable.MeasureNo = 2

in the where clause. That will exclude all non matching rows. Move the predicate to the ON clause.

December 28th, 2004, 08:15 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,716

Time spent in forums: 1 Month 3 Weeks 6 Days 18 h 25 m 10 sec
Reputation Power: 986

you specified

... WHERE i between 0 and 8733

so you must either make sure you actually have those rows (i gave you only 0 through 9) or else you can try this --

Code:

select dateadd(mi, 10*(10000*tt.i+1000*t.i+100*h.i+10*x.i+u.i)
              , '07/01/2004 0:00:00') AS StartTijd
     , CVTable.U1Avg
  from minutenwaarden as u
cross join minutenwaarden as x
cross join minutenwaarden as h  
cross join minutenwaarden as t  
cross join minutenwaarden as tt  
left outer
  join CVTable 
    on dateadd(mi, 10*(10000*tt.i+1000*t.i+100*h.i+10*x.i+u.i)
              , '07/01/2004 0:00:00') 
     = CVTable.StartTime
   and CVTable.MeasureNo = 2
 where (10000*tt.i+1000*t.i+100*h.i+10*x.i+u.i)
         between 0 and 8733
order
    by StartTijd

December 28th, 2004, 08:25 AM

MsXgAmEs

Registered User

Join Date: Dec 2004

Posts: 4

Time spent in forums: < 1 sec
Reputation Power: 0

Thanks for the quick reply. I got this problem fixed too. Cool site.

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: