December 9, 2004 at 9:15 am
I have a table created with the following:
CREATE TABLE mytable(
id varchar(10),
mydate datetime,
myvalue int)
The data that is in the table is as follows:
id mydate myvalue
______ ________ _________
ABC 11/15/04 4
ABC 11/16/04 16
ABC 11/18/04 12
ABC 11/19/04 26
ABC 11/20/04 7
ABC 11/22/04 87
ABC 11/23/04 54
ABC 11/24/04 33
ABC 11/25/04 9
ABC 11/27/04 37
My data is missing dates 11/17, 11/21, and 11/26. I want to write a query that will update mytable with entries for these missing dates with the following values:
ABC 11/17/04 NULL
ABC 11/21/04 NULL
ABC 11/26/04 NULL
I have been trying several different scenarios but I always seem to come up short. I just can't figure out what I'm missing. I was hoping one of you could help me figure out the best way to do this.
Thanks.
----------------------------------------------------------
01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011
December 9, 2004 at 9:54 am
December 9, 2004 at 10:00 am
Thanks but I finally got it figured out right about the time you sent this e-mail.
Here is my solution:
INSERT INTO mytable(id, mydate)
SELECT a.id, DATEADD(dd, -1, a.mydate)
FROM mytable a JOIN mytable b ON a.id = b.id
AND a.mydate = b.mydate
WHERE a.mydate <> (SELECT TOP 1 DATEADD(dd, 1, c.mydate)
FROM mytable c
WHERE c.id = a.id
AND c.mydate < a.mydate
ORDER BY c.mydate DESC)
hawg
----------------------------------------------------------
01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011
December 9, 2004 at 12:17 pm
The logic here is pretty simple.
I have used a while loop to get the difference between the Min and Max date and then insert the missing records into the table using the (IF Exist) clause
Declare @cnt int
declare @incr int
declare @mindate datetime
declare @incrdate datetime
select @cnt=datediff(d,min(mydate),max(mydate)),@mindate=min(mydate) from mytable
set @incr=1
begin
while @incr!=@cnt
Begin
set @incrdate=dateadd(d,@incr,@mindate)
if not exists ( select * from mytable where mydate =@incrdate)
begin
insert into mytable values
('ABC',@incrdate,NULL)
end
set @incr=@incr + 1
End
end
December 9, 2004 at 12:22 pm
When reading your post, I realized I didn't mention that I was trying to do this without looping. I was looking for a one query solution. (See my solution above.)
I knew I could do it with a loop but I was hoping to find a different/better (?) way.
----------------------------------------------------------
01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply