How to add additional rows to result set by condition
- by Puzzled
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
