quote:

---

Originally posted by Ciupaz

Hi,
I have 2 similar tables, like these:

Create Table Report(Month int, Value float)

Create Table ReportCorrect(Month int, Value float)

with these values:

Insert Report values(1,16),(2,11),(3,12),(4,15),(5,0),(6,34),(7,56),(8,14),(9,24),(10,0),(11,12),(12,14)
Go
Insert ReportCorrect values(1,16),(2,11),(3,12),(4,15),(5,11),(6,34),(7,56),(8,14),(9,24),(10,31),(11,12),(12,14)

In the Report table, for the months 5 (May) and 10 (October), I have Value=0.

Now I have to insert this gap (11 and 31) in another table (with the same structure), only if the following month has not zero value. If has zero value, I have to sum these values and put in the following month, and so on.

In this case I should obtain

Month - Value
6 - 11
11 - 31

How can I solve this particular problem?
(I have to use only SQL Server 2000 features)

Thanks a lot.

Luigi

---

quote:

---

Originally posted by Ciupaz

Yes, you'are right.

Luigi

---

quote:

---

Originally posted by Ciupaz

Yes, you'are right.

Luigi

---

quote:

---

Originally posted by hanbingl

What if month 12 has 0 value? do you insert to month 1?

quote:

---

Originally posted by Ciupaz

Yes, you'are right.

Luigi

---

---

quote:

---

Originally posted by Ciupaz

If December has zero value, I have to put the value in Novembere, if this month has a value <> 0. If also November has zero value, I put them in October, and so on.

quote:

---

Originally posted by hanbingl

What if month 12 has 0 value? do you insert to month 1?

quote:

---

Originally posted by Ciupaz

Yes, you'are right.

Luigi

---

---

---

Month       Value                                                 
----------- ----------------------------------------------------- 
1           16.0
2           11.0
3           12.0
4           15.0
5           0.0
6           34.0
7           56.0
8           0.0
9           24.0
10          0.0
11          0.0
12          0.0

quote:

---

Originally posted by visakh16

you need to state all your set of rules for somebody to suggest you an accurate solution

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by Ciupaz

Uhm, interesting case...
So, in this case September will became:

24 + 31(October) + 12(November) + 14(December) + 14(August) -> 105

Month - Value
9 - 105

PS
I have to check this particular case with the analyst however.

---

declare @i int
declare @sumval int
declare @curmonth int
declare @firstzero int
set @firstzero = 0
set @curmonth = 0
set @sumval = 0
set @i = 1
while @i <= 12
begin
select @curmonth = a.month, @sumval = @sumval + b.value from #report a join #reportcorrect b 
on a.month = b.month and a.value <> b.value where a.month = @i
if (@firstzero = 0 and @curmonth > 0)
set @firstzero = @curmonth
if (@curmonth = 12) 
begin
if exists (select 1 from #reportinsert where month = @firstzero-1)
UPDATE #reportinsert set value = value+@sumval where month = @firstzero-1
ELSE
INSERT INTO #reportinsert values(@firstzero-1, @sumval)
break
end
if (@curmonth <> 0 and @curmonth <> @i)
begin
INSERT INTO #reportinsert values(@curmonth+1, @sumval)
set @curmonth = 0
set @sumval = 0
set @firstzero = 0
end

print @i 
print @curmonth
print @sumval
print @firstzero
print '-------------'

set @i=@i+1

end

quote:

---

Originally posted by Ciupaz

There are some problems. For example, in this case:

Table Report

Month - Value
1 16
2 11
3 12
4 15
5 0
6 11
7 56
8 14
9 24
10 10
11 12
12 0


Table ReportCorrect

Month - Value
1 16
2 11,6
3 12
4 15,5
5 11
6 34
7 56,5
8 14
9 24,2
10 31
11 12
12 14

I obtain

Month Value
3 11
8 116
11 69

instead of:

Month - Value
6 - 45
11 - 26

---