Prev: Gettting an SQL snippet from a query
Next: Query for non unique record
From: Luigi on 24 Apr 2010 09:00


"Luigi" wrote:

> 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?
>
> LuigiA similar question about these "Report" tables.
I have again the 2 Report tables:


Create Table Report(Month int, Value float)
Go
Create Table ReportCorrect(Month int, Value float)
Go
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)

Again, in Report table, May and October has Value=0.

Now there is a third table, Percentage, that has this structure:

Create Table Percentage (UP int, Fuel int, Month int, Percentage float) --
UP -> Production Unit

with these values:

Insert Percentage values (1,1,1,20) -- Percentage for UP=1, Fuel=1, January
Insert Percentage values (1,2,1,25) -- Percentage for UP=1, Fuel=2, January
Insert Percentage values (2,1,1,15) -- Percentage for UP=2, Fuel=1, January
Insert Percentage values (2,2,1,35) -- Percentage for UP=2, Fuel=2, January
go
Insert Percentage values (1,1,2,20) -- Percentage for UP=1, Fuel=1, February
Insert Percentage values (1,2,2,25) -- Percentage for UP=1, Fuel=2, February
Insert Percentage values (2,1,2,18) -- Percentage for UP=2, Fuel=1, February
Insert Percentage values (2,2,2,25) -- Percentage for UP=2, Fuel=2, February
go
Insert Percentage values (1,1,3,33) -- Percentage for UP=1, Fuel=1, March
Insert Percentage values (1,2,3,15) -- Percentage for UP=1, Fuel=2, March
Insert Percentage values (2,1,3,25) -- Percentage for UP=2, Fuel=1, March
Insert Percentage values (2,2,3,17) -- Percentage for UP=2, Fuel=2, March
go
Insert Percentage values (1,1,4,26) -- Percentage for UP=1, Fuel=1, April
Insert Percentage values (1,2,4,25) -- Percentage for UP=1, Fuel=2, April
Insert Percentage values (2,1,4,14) -- Percentage for UP=2, Fuel=1, April
Insert Percentage values (2,2,4,32) -- Percentage for UP=2, Fuel=2, April
go
Insert Percentage values (1,1,5,24) -- Percentage for UP=1, Fuel=1, May
Insert Percentage values (1,2,5,25) -- Percentage for UP=1, Fuel=2, May
Insert Percentage values (2,1,5,35) -- Percentage for UP=2, Fuel=1, May
Insert Percentage values (2,2,5,15) -- Percentage for UP=2, Fuel=2, May
go
Insert Percentage values (1,1,6,10) -- Percentage for UP=1, Fuel=1, June
Insert Percentage values (1,2,6,26) -- Percentage for UP=1, Fuel=2, June
Insert Percentage values (2,1,6,25) -- Percentage for UP=2, Fuel=1, June
Insert Percentage values (2,2,6,36) -- Percentage for UP=2, Fuel=2, June
go
Insert Percentage values (1,1,7,24) -- Percentage for UP=1, Fuel=1, July
Insert Percentage values (1,2,7,15) -- Percentage for UP=1, Fuel=2, July
Insert Percentage values (2,1,7,36) -- Percentage for UP=2, Fuel=1, July
Insert Percentage values (2,2,7,50) -- Percentage for UP=2, Fuel=2, July
go
Insert Percentage values (1,1,8,30) -- Percentage for UP=1, Fuel=1, August
Insert Percentage values (1,2,8,16) -- Percentage for UP=1, Fuel=2, August
Insert Percentage values (2,1,8,21) -- Percentage for UP=2, Fuel=1, August
Insert Percentage values (2,2,8,37) -- Percentage for UP=2, Fuel=2, August

etc etc ......

-- November it's necessary for this example (because October has value=0 in
Report table)
Insert Percentage values (1,1,11,22) -- Percentage for UP=1, Fuel=1, November
Insert Percentage values (1,2,11,15) -- Percentage for UP=1, Fuel=2, November
Insert Percentage values (2,1,11,25) -- Percentage for UP=2, Fuel=1, November
Insert Percentage values (2,2,11,17) -- Percentage for UP=2, Fuel=2, November


Now, with the same logic as seen before, I have to perform these calculation:

In words:
May has value=0 in Report table, and June has value<>0
So I have to take the value of May in ReportCorrect table (=11) and create 4
new records (for June) made in this way:

UP - Fuel - Month - Value
1 - 1 - 6 - (11 * 10%)=1.1
1 - 2 - 6 - (11 * 26%)=2.86
2 - 1 - 6 - (11 * 25%)=2.75
2 - 2 - 6 - (11 * 36%)=3.96

then these 4 records I'll put in another table with these four fields.

Like before, if also June has value=0 (and July has value <>0), I have to
sum (May+June, from ReportCorrect table), and

create 8 records:

UP - Fuel - Month - Value
1 - 1 - 6 - (11 * 10%)=1.1
1 - 2 - 6 - (11 * 26%)=2.86
2 - 1 - 6 - (11 * 25%)=2.75
2 - 2 - 6 - (11 * 36%)=3.96

1 - 1 - 7 - (34 * 24%)=8.16
1 - 2 - 7 - (34 * 15%)=5.1
2 - 1 - 7 - (34 * 36%)=12.24
2 - 2 - 7 - (34 * 50%)=17


How can I solve this very complicated calculation?

From: Erland Sommarskog on 24 Apr 2010 17:52
Luigi (Luigi(a)discussions.microsoft.com) writes:
> Now, with the same logic as seen before, I have to perform these
> calculation:
>
> In words:
> May has value=0 in Report table, and June has value<>0 So I have to take
> the value of May in ReportCorrect table (=11) and create 4 new records
> (for June) made in this way:
>
> UP - Fuel - Month - Value
> 1 - 1 - 6 - (11 * 10%)=1.1
> 1 - 2 - 6 - (11 * 26%)=2.86
> 2 - 1 - 6 - (11 * 25%)=2.75
> 2 - 2 - 6 - (11 * 36%)=3.96
>
> then these 4 records I'll put in another table with these four fields.

It's really great that you have provided tables and sample data. The more
frustrating that I understand anything. I think what is missing is a
background description of the problem you are trying to solve. That is,
what does these tables signify? And what are the purposes of the
calculations you are performing?


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Luigi on 25 Apr 2010 06:21
"Erland Sommarskog" wrote:

>
> It's really great that you have provided tables and sample data. The more
> frustrating that I understand anything. I think what is missing is a
> background description of the problem you are trying to solve. That is,
> what does these tables signify? And what are the purposes of the
> calculations you are performing?

Hi Erland,
the problem is to amend the Report table, based on the ReportCorrect table.
But there are some business rules to adhere.
When I find zero value in my Report table, I take the same month value from
ReportCorrect table, e and put it in the following month, but not in in the
Report table, but create new records that will be inserted in a new table,
with the rules written before.

Luigi

From: Luigi on 25 Apr 2010 15:47
I write a partial solution, this one:


declare @i int, @FollowingMonth int, @Value float, @ValueCorrect float,
@FollowingValue float
set @i = 1

while @i <= 12
begin

select @Value = Value from dbo.Report where [MONTH] = @i
if @Value <> 0
begin
set @i = @i + 1
continue;
end

else
begin
select @FollowingValue = Value from dbo.Report where [MONTH] = @i+1
if @FollowingValue <> 0
begin
select @ValueCorrect = Value from dbo.ReportCorrect where [MONTH] = @i
print cast(@valueCorrect as varchar)
insert dbo.ReportNew(UP,Fuel,[Month],Value)
select UP, Fuel, @i+1, @ValueCorrect * Percentage/100
from dbo.Percentage
where [MONTH] = @i
end

else -- Also following month has value=0
begin
declare @ValueCorrect1 float, @ValueCorrect2 float
select @ValueCorrect1 = Value from dbo.ReportCorrect where [MONTH] = @i
select @ValueCorrect2 = Value from dbo.ReportCorrect where [MONTH] = @i + 1

--print cast(@valueCorrect as varchar)
insert dbo.ReportNew(UP,Fuel,[Month],Value)
select UP, Fuel, @i + 2, (@ValueCorrect1 + @ValueCorrect2) * Percentage/100
from dbo.Percentage
where [MONTH] = @i + 2
set @i = @i + 2
continue
end
end
set @i = @i + 1
end


Unfortunately does work in only 2 cases:
1) There is one month alone with value=0 (in every position of the year)
(for example, May=0 and October=0, but July<>0 and November <>0).
2) Two months (the actual and the following). For example May and June has
values=0.

Does not work if there are 3 or more joined months with value=0 (for example
if May=June=July=0) and in the case of December=0, where I have to implement
the same mechanism but backward.

I find this task excessive complicate to make it in T-SQL.


From: Erland Sommarskog on 26 Apr 2010 18:03
Luigi (Luigi(a)discussions.microsoft.com) writes:
> Unfortunately does work in only 2 cases:
> 1) There is one month alone with value=0 (in every position of the year)
> (for example, May=0 and October=0, but July<>0 and November <>0).
> 2) Two months (the actual and the following). For example May and June has
> values=0.
>
> Does not work if there are 3 or more joined months with value=0 (for
> example if May=June=July=0) and in the case of December=0, where I have
> to implement the same mechanism but backward.
>
> I find this task excessive complicate to make it in T-SQL.

Yes, it looks complicated. I still have not fully understood what you
are trying to achieve so it is difficult to help.

Is this intended to be a one-off to correct some problem, or is this a
recurring task?

Using some sort of cursor or loop seems to be the right way to go; that
may make the logic a little simpler.



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

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