SQLTeam.com | Weblogs | Forums

Help me

sql2014

#1

Please, Could help me with this source code.


#2

Please paste the code as actual text. A picture of text is useless to the rest of us.

Also, please provide an explanation as to what, specifically, you're having problems with along with any error messages.


#3

Hello I just want to know the explanation of the source code in question
There is no error in the syntax.
I will be very grateful if they could


#4

I'll be happy to do that as soon as you post the information that was requested.


#5

SELECT DISTINCT
hist_exproot.year AS Año, hist_exproot.month# AS Mes, hist_exproot.day AS Día,
hist_exproot.shift, hist_loads.excav AS Cargador, hist_loads.truck AS Volquete,
hist_loads.grade AS PolĂ­gono, hist_dumps.loc AS Destino, hist_dumps.dumpstons
AS Tonelada, hist_loads.loadingtim/60 AS [T carguio], hist_dumps.dumpingtim/60 AS
[T Descarga], hist_loads.queuetime/60 AS [T Cola], hist_loads.spottime/60 AS [T
Cuadrado], hist_loads.fullhaul/60 AS [T Lleno],
(hist_loads.loadingtim+hist_dumps.dumpingtim+hist_loads.queuetime+hist_loads.spottime+hist_loads.fullhaul+hist_loads.emptyhaul)/60 as Ciclo,
hist_loads.timeload/3600 AS [Hora Carga],
hist_dumps.timedump/3600 AS [Hora Descarga], load_enum.[material type] AS [Tipo
Material], LOAD_GRP_TYPE.name AS Material

FROM hist_exproot INNER JOIN
hist_loads ON hist_exproot.shiftindex=hist_loads.shiftindex INNER JOIN
hist_dumps ON hist_loads.shiftindex=hist_dumps.shiftindex AND hist_loads.dumprec =hist_dumps.ddbkey INNER JOIN
load_enum ON hist_loads.[load] = load_enum.[load] INNER JOIN
LOAD_GRP_TYPE ON load_enum.[load] = LOAD_GRP_TYPE.num

WHERE (hist_exproot.year=07) AND (hist_exproot.month#=9)

Ok it's there The text line breaks are as it is in the picture. I hope your help please


#6

Here is the code, reformatted and aliased, to make it a bit more legible...

SELECT DISTINCT
	he.year AS Año, 
	he.month# AS Mes, 
	he.day AS DĂ­a,
	he.shift, 
	hl.excav AS Cargador, 
	hl.truck AS Volquete,
	hl.grade AS PolĂ­gono, 
	hd.loc AS Destino, 
	hd.dumpstons AS Tonelada, 
	hl.loadingtim / 60 AS [T carguio], 
	hd.dumpingtim / 60 AS [T Descarga], 
	hl.queuetime / 60 AS [T Cola], 
	hl.spottime / 60 AS [T Cuadrado], 
	hl.fullhaul / 60 AS [T Lleno],
	(hl.loadingtim + hd.dumpingtim + hl.queuetime + hl.spottime + hl.fullhaul + hl.emptyhaul) / 60 as Ciclo,
	hl.timeload / 3600 AS [Hora Carga],
	hd.timedump / 3600 AS [Hora Descarga], 
	le.[material type] AS [Tipo Material], 
	lgt.name AS Material
FROM 
	dbo.hist_exproot he
	JOIN dbo.hist_loads hl
		ON he.shiftindex = hl.shiftindex
	JOIN dbo.hist_dumps  hd
		ON hl.shiftindex=hd.shiftindex 
		AND hl.dumprec =hd.ddbkey
	JOIN dbo.load_enum le
		ON hl.[load] = le.[load]
	JOIN dbo.LOAD_GRP_TYPE lgt
		ON le.[load] = lgt.num
WHERE 
	he.[year] = 07 
	AND he.[month#] = 9;

As far as an explanation for what it's doing... It appears to be report code for shipping KPIs... Specifically for the month of July, 2007.

Based the formatting, lack of table aliases and actual spaces in the column aliases, it appears to be machine generated code... That said it isn't doing anything tricky at all... It's just a bog standard SELECT query. It's selecting values from 5 joined tables. I don't speak Spanish (I'm assuming that these names are Spanish), so I can't say specifically what everything is, based on the table & column names, but when I see division by 60 or 3600, it's a strong indication that time units (stored as number of seconds) are being converted to minutes & hours.

The lack of any aggregation, combined with the presence of DISTINCT, would 1) make me question the need for the DISTINCT, or 2) give me concerns that needed data rows are being ignored.

I'm not sure what else I can say about it... If you have any specific questions


#7

SELECT DISTINCT
he.year AS Año,
he.month# AS Mes,
he.day AS DĂ­a,
he.shift,
hl.excav AS Cargador,
hl.truck AS Volquete,
hl.grade AS PolĂ­gono,
hd.loc AS Destino,
hd.dumpstons AS Tonelada,
hl.loadingtim / 60 AS [T carguio],
hd.dumpingtim / 60 AS [T Descarga],
hl.queuetime / 60 AS [T Cola],
hl.spottime / 60 AS [T Cuadrado],
hl.fullhaul / 60 AS [T Lleno],
hl.emptyhaul / 60 AS [T Vacio],
hl.disteh / 1000 AS [D Vacio],
hd.dist / 1000 AS [D Lleno],
(hl.loadingtim + hd.dumpingtim + hl.queuetime + hl.spottime + hl.fullhaul + hl.emptyhaul) / 60 as Ciclo,
hl.timeload / 3600 AS [Hora Carga],
hd.timedump / 3600 AS [Hora Descarga],
le.[material type] AS [Tipo Material],
lgt.name AS Material
FROM
dbo.hist_exproot he
JOIN dbo.hist_loads hl
ON he.shiftindex = hl.shiftindex
JOIN dbo.hist_dumps hd
ON hl.shiftindex=hd.shiftindex
AND hl.dumprec =hd.ddbkey
JOIN dbo.load_enum le
ON hl.[load] = le.[load]
JOIN dbo.LOAD_GRP_TYPE lgt
ON le.[load] = lgt.num
WHERE
he.[year] = 07
AND he.[month#] = 9;

Thank you very much.

Sorry, in the source code (although in the image shown) I was missing typing the hl.emptyhaul [T Empty], hl.disteh [D Empty] and hd.dist [D Full]. I rewrote it now, if it is complete.

When you say that you select values ​​from 5 tables that are linked, which ones you mean (I understand that you do not speak Spanish, but you could name it with the alias).

On the units if I understand it well.

I understand it like this:

What this source code does is a question (which should give us unique values ​​since it makes use of "SELECT DISTINCT") to know all the events of the month of September of year 2007.

(Sequence order may vary).

An empty truck after a certain time (hl.emptyhaul) to travel a certain distance (hl.disteh) is directed towards a mine poligono (hl.grade) where a blade (hl.excav) will load it Load time, square And tail -> hl.loadingtim + hl.queuetime + hl.spottime) with a certain tonnage (hd.dumpstons) of material (le.material type -> lgt.name) to be taken after a certain time (hl.fullhaul ), A destination (hd.loc) after traveling a certain distance (hd.dist) and downloading it (hd.dumpingtim), all the time the shovel deposits the first bucket in the truck Hl.timeload) and then takes this Material to the aforementioned destination for another to be registered another time (hd.timedump).

Tu que opinas?


#8

That should be self explanatory... They are the 5 tables in the FROM clause...

FROM
	dbo.hist_exproot he
	JOIN dbo.hist_loads hl
		ON he.shiftindex = hl.shiftindex
	JOIN dbo.hist_dumps hd
		ON hl.shiftindex=hd.shiftindex
		AND hl.dumprec =hd.ddbkey
	JOIN dbo.load_enum le
		ON hl.[load] = le.[load]
	JOIN dbo.LOAD_GRP_TYPE lgt
		ON le.[load] = lgt.num

I would suspect that you already have distinct values (so no need for the added of doing a DISTINCT) but I could easily be wrong. Should be worth testing though.


#9

The name of the tables

  1. hist_exproot
  2. hist_loads
  3. hist_dumps
  4. load_enum
  5. LOAD_GRP_TYPE

Another question, "dbo" means dashboard?

If so,

The contents of dbo.hist_exproot would be:

A. Year as "Year"
B. Month # as "Month"
C. Day as "Day"
D. Shift

From dbo.hist_loads would be:

A. Excav as "Cargador"
B. Truck as "Camion"
C. Grade as "Poligono"
D. Loadingtim as "T load"
E. Queuetime as "T cola"
F. Spottime as "T Cuadrado"
G. Fullhaul as "T Lleno"
H. Emptyhaul as "T Vacio"
I. Disteh as "D vacio"
J. Timeload as "Hora carga"

From dbo.hist_dumps

A. Loc as "Destino"
B. Dumptons as "Toneladas"
C. Dumpingtim as "T descarga"
D. Dist as "D Lleno"
E. Timedump as "Hora descarga"

From dbo.load_enum

A. Material type as "Tipo material"

And finally the dbo. LOAD_GRP_Type

A. Name as "Material"

The varialble "Ciclo" where would you place it?


#10

"dbo" is the default schema name for SQL Server. You should get in the habit of qualifying your tables with the schema that it belongs to.

Ciclo isn't a "variable", it's just a named calculation. The calculation includes columns from two tables (hist_loads & hist_dumps). As far as "placing it" some where... I don't know if that's really a pertinent question. Once you get into more complex queries, you can end up getting data columns that don't have any actual tables as their source.


#11

I understand:

If I say the variables of dbo.hist_exproot:

A. Year as "Year"
B. Month # as "Month"
C. Day as "Day"
D. Change

That's wrong ?.

Now I have another doubt, when joining tables by INNER JOIN, these tables that are joining should have some variable that a certain?

dbo.hist_exproot he
INNER JOIN dbo.hist_loads hl
ON he.shiftindex = hl.shiftindex ..... CĂłmo podrĂ­a entender eso?


#12

First thing first... Words mean things. When you insist on using the wrong words for things you only create confusion. A variable, in SQL Server is a specific thing. https://technet.microsoft.com/en-us/library/ms187953(v=sql.105).aspx

I know English isn't your 1st language and I'm not trying to beat you up over terminology... but learning the basics will be a benefit is the long term.

Tables have "columns". So it would be fair to say that columns: year, month#, day & shift, are all being retrieved from hist_exproot.

As far as the table joins question... When joining one table to another, yes - they are usually joined on a column (or set of columns) that they have in common. I say "typically" because, 99% of the time that's the case... but not 100%. CROSS JOINs for example, don't have an ON clause.


#13

For people who are trying to get their heads around SQL for the 1st time, I recommend picking up a copy of T-SQL Fundamentals by Itzik Ben-Gan. The author is a SQL genius. More importantly, he's VERY good at explaining things in such always that they are very easy to grasp. You may also be able to find a pdf version floating around the web if purchasing isn't an option.


#14

You're right, excuse me.

But I still do not understand what the query exactly, regarding the month = 09 and year = 07.


#15

The WHERE clause is where you add criteria to filter the results.
So in this case, only rows year = 7 AND month# = 9 will be returned... All other rows will be filtered out.


#16

Thanks for the patience in truth.

That part if I became clear, what I do not understand are the conditions (ON) when joining the tables.

For example:

Dbo.hist_exproot he
JOIN dbo.hist_loads hl
ON he.shiftindex = hl.shiftindex
JOIN dbo.hist_dumps hd
ON hl.shiftindex = hd.shiftindex AND hl.dumprec = hd.ddbkey
JOIN dbo.load_enum le
ON hl. [Load] = le. [Load]
JOIN dbo.LOAD_GRP_TYPE lgt
ON le. [Load] = lgt.num

I was looking for and found in a book that:

Shiftindex = Shift number, is of type "int" and is related to "hist_exproot.shiftindex"
Dumprec = link to hist_dumps record for this load, is of type "real"
Ddbkey = Unique identifier within this shiftindex, is of type "real" and is related to "hist_dumps.ddbkey"
Load = material type, is of type "Real" and is related to "enum_LOAD.num"


#17

I can't keep explaining like this... You really need to do your homework and do some reading so that you understand the basic structure of a simple select query. My personal recommendation is in an earlier post, but there are also plenty of online resources to get you going in the right direction.


#18

Maybe you're right, thank you very much anyway.

It is the first time I see programming in SQL and I came across that source code that is part of a course of mine planning.


#19

Ahhh... Now I get where you're coming from. If you're considering on taking a SQL course, you should do it. It's a very easy language to get started with but has enough nuance to keep old pros learning new things every day.

So that you can get the concept of the query, read it like a normal sentence... Or, more accurately a request...

SELECT the data FROM some table that's JOIN'd to another table ON these columns that relate them WHERE these conditions are met.


#20

Thank you,

Well, so old I'm not, I'm still in college.

When they gave me that source code, if I could understand it as you tell me.

But I wanted to go deeper into the subject.

As for the book, I'm looking for it. Will not you have it in Spanish? Hahahahaha, thanks you.