Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query Help

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2006-09-06 : 18:58:05

Hi there

Could someone help regarding this. I got one table called tblA which have records like this:

AccNo; Mth; Amt
XXX; 1; 100
XXX; 3; 150
YYY; 6; 100
YYY; 7; 150
...

I want to fill the gap of each AccNo where the Mth is missing (1 - 12). So the complete record will be:

AccNo; Mth; Amt
XXX; 1; 100
XXX; 2; 0
XXX; 3; 150
XXX; 4; 0
XXX; 5; 0
XXX; 6; 0
XXX; 7; 0
XXX; 8; 0
XXX; 9; 0
XXX; 10; 0
XXX; 11; 0
XXX; 12; 0
...

I'm thinking to do a scan which is missing and put into temp table and put it back in. But I think there will efficient way to do this.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-06 : 19:45:09

[code]
select c.AccNo, c.Mth, isnull(a.Amt, 0) as Amt
from
(
select AccNo, Mth
from AccNoTable
cross join
(
select 1 as Mth union all select 2 union all select 3 union all select 4 . . .
) m
) c
left join
tblA a
on c.AccNo = a.AccNo
and c.Mth = a.Mth
[/code]

dewacorp.alliances

452 Posts

Posted - 2006-09-06 : 20:05:38

Thanks Khtan.

That's the "select 1 as Mth union all select 2 union all select 3 union all select 4 . . ." ... I can probably replace this with a table that we have which containing month record between 1 and 12 ... can't I?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-06 : 21:07:17

Also refer
http://www.mindsdoor.net/SQLTsql/FindGapsInSequence.html

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-06 : 22:09:07

quote:
Originally posted by valdyv

Thanks Khtan.

That's the "select 1 as Mth union all select 2 union all select 3 union all select 4 . . ." ... I can probably replace this with a table that we have which containing month record between 1 and 12 ... can't I?

Sure why not.

dewacorp.alliances

452 Posts

Posted - 2006-09-08 : 00:27:13

Hi khtan

With script that you give me, how to make it that only insert the record has a gap on it. Cause at the moment I have a complete list and left outer join to it and get the one that is not on the list and then insert.

Which I think this query is overhead.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources