Datensätze einer Spalte summieren und Einheit anhängen

1

Hallo zusammen,

bin gerade dabei aus einer Datenbank Tabelle die Werte aus einer Spalte zu summieren und auf 2 Stellen nach dem Komma zu runden in Abhängigkeit eines anderen Spaltenwertes.
Das funktioniert, ist ja auch nicht so schwer.

Jetzt möchte ich aber an das jeweilige Ergebnis die Maßeinheit als Text mit anhängen.
Im Ergebnis habe ich den gewünschten Text 'fett' markiert.

Meine Abfrage lautet:
SELECT
mat.MatCode, mat.MatName,
round(sum(matverb.VerbrGewAuto),2) as VerbrGewAuto,
round(sum(matverb.VerbrGewHand),2) as VerbrGewHand

FROM mat, matverb

WHERE (mat.MatCode = matverb.MatCode
and
(matverb.ProdLinCode = 'M1')
and
(matverb.SchichtCode = 'Schicht A')
)

GROUP BY mat.MatCode, mat.MatName

Das Ergebnis:
MatCode MatName VerbrGewAuto VerbrGewHand
51000006 VESTOLIT P 1982K 59.0 kg 0.0 kg
51000014 VINNOLIT S3268P 203.9 kg 0.0 kg
51001143 VYNOVA S 6830 2437693.54 kg 0.0 kg
51001609 PRECARB 400 508528.15 kg 111.62 kg
51002001 SOLVIN 267 RC 653461.41 kg 0.0 kg

Vielen Dank für eure Hilfe im Voraus.

Grüßen von CopWorker

2

Bitte versuche dies (sorry if that's not correct, I'm an American, my German is schlecht):

SELECT
mat.MatCode, mat.MatName,
cast(round(sum(matverb.VerbrGewAuto),2) as varchar(30)) + ' kg' as VerbrGewAuto,
cast(round(sum(matverb.VerbrGewHand),2) as varchar(30)) + ' kg' as VerbrGewHand

FROM mat, matverb

WHERE (mat.MatCode = matverb.MatCode
and
(matverb.ProdLinCode = 'M1')
and
(matverb.SchichtCode = 'Schicht A')
)

GROUP BY mat.MatCode, mat.MatName
3

Hi ScottPetcher,

that is very well expressed in German.

I do it like this:

STR(sum(matverb.VerbrGewAuto), 15, 2) as VerbrGewAuto

It works.

But I take Your syntax and try it out.

Another question.

Do You have experience in SyBase database and SQL Server Report Services (SSRS)?

I´ve got a lot of trouble with the parameter sets and the multi selection of parameter.

In connection with a SQL data base it´s a little bit easier.

Why do You communicate via email and not via SqlTeam community?

Best regards

CopWorker

4

Use STR() if you want the results to line up with each other. CAST() won't do that. For example:

I have no experience with Sybase. I do know some about SSRS.

5

Hi ScottPetcher,

that sounds good.
Right now I´m working on a SSRS report. In that would like to insert a "WHERE" clause in the query in the dataset.
The "WHERE" clause is intended, among other things, to refer to a time range which, in turn, 2 parameter sets.
Here is the query.

<start query--------------------------------------------
SELECT
mat.MatCode, mat.MatName,
FROM mat, matverb

WHERE mat.MatCode = matverb.MatCode
and
matVerb.VerbrDatumZeit between ? and ?

GROUP BY mat.MatCode, mat.MatName
<end query---------------------------------------------

The 2 question marks should not irritate, these are as a reference to the parameter1 and parameter2 are used, see "param1 -and- param2.PNG.
Firste question mark for parameter1 and second question mark for parameter2, in order.
It is a database of type ODBC. But the queries and the contents of the tables are almost identical with SQL.
The "matVerb.VerbrDatumZeit column is of type "TIMESTAMP"

Now parameter1 (param_begin) and parameter2 (param_end) come into play.
These are set to data type "Date/Time", see "param_begin.PNG".

What must the comparison or the syntax in the expression parameter be? See "dataset_param_ausdr.PNG".
The problem is the column value "matVerb.VerbrDatumZeit" is of type "TIMESTAMP".
The value from the parameter comes from one, by type, "DateTimePicker". I don´t know exaxly.
I basolutely do not understand the conversion.
It is not possible to compare a value of type "TIMESTAMP" with a unknown type with the operator "=", I suspect.

Can You help me with that.

Sorry but I´m not allowed to send the pictuers.
A message appears: "Sorry, new users can only put one image in a post."
How does it works?

Thanks in advance.
Greetings from CopWorker

6

In SQL Server, a "timestamp" column is not a datetime at all, it is a binary counter (1, 2, 3, ...). There is no way to convert a timestamp to a datetime. It has nothing to do with date or time. You would need a different column in the table to compare to.

In Sybase, a timestamp is also binary, but it is a datetime offset, the number of microseconds since Jan 1, 1970. If it's Sybase, please state this again, and I will help work out the proper conversion to change the timestamp to a datetime.

7

Hi ScottPetcher,

thank you very much. I had to accept that, too.
Now I have got another solution.
In SSRS report I generate a further datase called "dsetGetDateTimeRange". In this datase I put the query for the timestamp with the highest value "MaxTime" and the lowest value "MinTime". I take the individual values as default values for the parameter set "param_begin" and "param_end". I do not use conversions anymore.
I works for the time begin.
Nevertheless, I would like to check everything carfully.

Thanks in advance.
Greetings from CopWorker