Prev: Gettting an SQL snippet from a query
Next: Query for non unique record
From: Luigi on 23 Apr 2010 09:49
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

From: Plamen Ratchev on 23 Apr 2010 12:00
Where do you get the values 11 and 31 from? Is the ReportCorrect the table with the final result?

--
Plamen Ratchev
http://www.SQLStudio.com
From: Luigi on 23 Apr 2010 14:57
"Plamen Ratchev" wrote:

> Where do you get the values 11 and 31 from? Is the ReportCorrect the table with the final result?

11 and 31 are the values of May and October in the table ReportCorrect.
In this case, I have to sum 11 with the value of June (34), to obtain 45. So
I insert the row:

Month - Value
6 - 45

in a third table.

Then I have to sum 31 to the value of November (12) to obtain 43, and insert
the row:

Month - Value
11 - 43

in the third table too.

But if I find two months with value=zero in the table Report, I have to sum
them with the first month with non zero value.

Luigi
From: Plamen Ratchev on 23 Apr 2010 18:31
This should do it for SQL Server 2000:

SELECT month, SUM(value) AS value
FROM (
SELECT R1.Month, C.Value
FROM Report AS R
JOIN ReportCorrect AS C
ON R.Month = C.Month
AND R.Value = 0
LEFT JOIN Report AS R1
ON R1.Month > R.Month
AND R1.Value <> 0
AND NOT EXISTS(SELECT *
FROM Report AS R2
WHERE R2.Month < R1.Month
AND R2.Value <> 0
AND R2.Month > R.Month)
UNION ALL
SELECT C.Month, C.Value
FROM Report AS R
JOIN ReportCorrect AS C
ON R.Month = C.Month
AND R.Value <> 0
AND EXISTS(SELECT *
FROM Report AS R2
WHERE R2.Month = R.Month - 1
AND R2.Value = 0)) AS T
GROUP BY month;

--
Plamen Ratchev
http://www.SQLStudio.com
From: Luigi on 24 Apr 2010 05:23
Hi Plamen, it's correct, thank you.
A little rule to add to this query.
If in the table Report I have December with zero value (12,0), I have to put
the December value (=14, always from table ReportCorrect) to the value of
november.
In this example, it wuold be:

12(value of November)+14(value of december)=26

But if also November has value=zero, I have to put 12+14 in October:

31+12+14=57

so to obtain from your query:

Month - Value
10 - 57

and so on.

Do you have idea how to solve?

Luigi
 |  Next  |  Last
Pages: 1 2 3
Prev: Gettting an SQL snippet from a query
Next: Query for non unique record