CODEWITHSUNDEEP
t-sqlsql-server-2008
Puzzled
Puzzled

Reputation: 378

How to add additional rows to result set by condition

I have a table like this:

ObjId Date Value
100 '20100401' 12
200 '20100501' 45
200 '20100401' 37
300 '20100501' 75
300 '20100401' 69
400 '20100401' 87

I have to add additional rows to result set for objId's, where there is no data at '20100501'

**100 '20100501' null**
100 '20100401' 12
200 '20100501' 45
200 '20100401' 37
300 '20100501' 75
300 '20100401' 69
**400 '20100501' null**
400 '20100401' 87

What is the best way to do this?

Here is the T-SQL script for the initial table:

declare @datesTable table (objId int, date smalldatetime, value int)
insert @datesTable
select 100, '20100401', 12
union all
select 200, '20100501', 45
union all
select 200, '20100401', 37
union all
select 300, '20100501', 75
union all
select 300, '20100401', 69
union all
select 400, '20100401', 87

select * from @datesTable

Upvotes: 0

Views: 1783

Answers (2)

marc_s
marc_s

Reputation: 754438

OK, I understand now :-) You want to find the objId values which have no entry with a date of 2010-05-01 and then insert extra rows with those objId and that date and a NULL value - use a CTE (Common Table Expression):

;WITH MissingObj AS
(
    SELECT objId 
    FROM @datesTable  d1
    WHERE NOT EXISTS (SELECT objId FROM @datesTable d2 WHERE d2.objId = d1.objId AND d2.date = '20100501')
)
INSERT INTO @datesTable(objId, date, value)
    SELECT
        mo.objId, '20100501', NULL
    FROM    
        MissingObj mo

The MissingObj CTE gets all the objId values where there's no entry for '2010-05-01', and then using that list of objId, the following INSERT statement inserts those into your @datesTable table.

As a side-note: I find this approach much easier for filling up sample tables:

declare @datesTable table (objId int, date smalldatetime, value int)

insert into @datesTable(objId, date, value)
   VALUES(100, '20100401', 12),
     (200, '20100501', 45),
     (200, '20100401', 37),
     (300, '20100501', 75)  -- and so forth

SQL Server 2008 allows you to pass in multiple tuples of values in (....) - much easier and more readable than a union all construct...

Upvotes: 1

sergiom
sergiom

Reputation: 4877

This can be a solution:

declare @datesTable table (objId int, date smalldatetime, value int)
insert @datesTable
select 100, '20100401', 12
union all
select 200, '20100501', 45
union all
select 200, '20100401', 37
union all
select 300, '20100501', 75
union all
select 300, '20100401', 69
union all
select 400, '20100401', 87


with objids as (
    select distinct objid
    from @datesTable )
insert into @datesTable(objId, date, value)
select D.objId, '20100501', NULL
from objids D 
where not exists(select * 
    from @datestable T
    where T.objId = D.objId
    and date = '20100501')

select *from @datesTable

Upvotes: 0

Related Questions