Prev: Gettting an SQL snippet from a query
Next: Query for non unique record
From: Luigi on 27 Apr 2010 05:45
Hi Erland,
now the problem has been simplified.
There are only two tables, with these structures and these test data:

Create table dbo. Report (UP Fuel int, int, int, float Value Month)
go
create table dbo. ReportComplete (month int, float $ value)
go
--We have two UP (770 and 771) and two Fuels (1 and 2)
--January
insert dbo. Report values (770,1, 1, 12.4), (770,2, 1, 12.6), (771,1, 1, 2,
3.5), (771,2, 1, 5.5)
--February
insert dbo. Report values (770,1, 2, 12.4), (770,2, 2, 12.6), (771,1, 2,
3.5), (771,2, 2, 5.5)
--March
insert dbo. Report values (770,1, 3, 12.4), (770,2, 3, 12.6), (771,1, 3,
3.5), (771,2, 3, 5.5)
--April
insert dbo. Report values (770,1, 4, 12.4), (770,2, 4, 12.6), (771,1, 4, 2,
3.5), (771,2 2,3,4,5.5)
--May
insert dbo. Report values (770,1, 5, 0, 0) 770,2 (6, 5, 0, 0) 771,1 (6, 5,
0, 0) 771,2 (6, 5, 0, 0)
--June
insert dbo. Report values (770,1, 6, 12.4), (770,2, 6, 12.6), (771,1, 6, 2,
3.5), (771,2, 6, 5.5)
--July
insert dbo. Report values (770,1, 7, 12.4), (770,2, 7, 12.6), (771,1, 7, 2,
3.5), (771,2, 7, 5.5)
--August
insert dbo. Report values (770,1, 8, 1: 12.4), (770,2, 8, 12.6), (771,1, 8,
2, 3.5), (771,2, 8, 5.5)
--September
insert dbo. Report values (770,1, 9, 12.4), (770,2, 9, 12.6), (771,1, 9, 2,
3.5), (771,2, 9, 5.5)
--October
insert dbo. Report values (770,1, 10, 0), (770,2, 10, 0), (771,1, 10, 0),
(771,2, 10, 0)
--November
insert dbo. Report values (770,1, 11, 12.4), (770,2, 11, 12.6), (771,1, 11,
2, 3.5), (771,2, 11, 5.5)
--December
insert dbo. Report values (770,1, 12, 12.4), (770,2, 12, 12.6), (771,1, 12,
2, 3.5), (771,2, 12, 5.5)
go

insert dbo. ReportComplete values (1.34), (2: 35), (3.45), (4.34), (5.2.2),
(6.45), (7,34), (8.35), (9.45.8), (10.6.4), (11,55), (12,25)

I see that in table dbo. Report, for the months of May and October, I have
value = 0.

I must then take the corresponding Value from the table ReportComplete (2.2
and 6.4) and enter a new record in the table dbo. Report having as the sum of
these values (2.2 + 4 = 6.8.6).
This new record will have the month the first month of table report where
Value <> 0 (in this case, January), and any UP and Fuel (indifferently 770
that 771 and 1 and 2).
Practically should I insert a new record like this:

Up - Fuel - Month - Value
770 - 1 - 1 - 8.6

Do you have any idea how to solve?

Luigi

From: Erland Sommarskog on 27 Apr 2010 17:51
Luigi (Luigi(a)discussions.microsoft.com) writes:
> I see that in table dbo. Report, for the months of May and October, I have
> value = 0.
>
> I must then take the corresponding Value from the table ReportComplete
> (2.2 and 6.4) and enter a new record in the table dbo. Report having as
> the sum of these values (2.2 + 4 = 6.8.6).
> This new record will have the month the first month of table report where
> Value <> 0 (in this case, January), and any UP and Fuel (indifferently 770
> that 771 and 1 and 2).
> Practically should I insert a new record like this:
>
> Up - Fuel - Month - Value
> 770 - 1 - 1 - 8.6
>
> Do you have any idea how to solve?

I'm still in the dark. First, there were errors in your script, so
it was very difficult to decode. I gave up.

Next, could you post the complete defintion of these tables, including
primary key defintions? It could possibly help to understand what is going
on.

I'm afraid that it's beyond me why you would take data from May and
October, and them together and enter them as January. Not the least
since there also seems to be a row for January...

You say that there are a value of zero for May and October. But what is
there is a value of 0 for some rows only? Or should we add a January
row for each UP/Fuel?

There must be some underlying business problem that you are trying to
solve. If you could explain that, then maybe I could understand this
better.

Here is a query that may match your description, but it may be totally
off the mark.


SELECT r.UP, r.Fuel,
(SELECT MIN(r2.Month)
FROM Report r2
WHERE r.UP = r2.UP
AND r.Fuel = r2.Fuel),
SUM(rc.value)
FROM Report r
JOIN ReportComplete rc ON r.Month = rc.month
WHERE r.Value = 0
GROUP BY r.UP, r.Fuel


--
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  | 
Pages: 1 2 3
Prev: Gettting an SQL snippet from a query
Next: Query for non unique record