Help dividing two values from different tables.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help dividing two values from different tables.

Author

Topic

recruta42
Starting Member

1 Post

Posted - 2007-07-14 : 09:15:57

Hi, I am completly new in ASP , but, due the necessity of the production in the company I work, I need
urgently to learn do deal with scripts and SQL.

I did a script that shows on the production screen the consume tax of the gas instantly on time and it

worked. Now I have to do a table formed by the DIVISION of a valor V from one table by the valor V in

another table, mas the values must be taken at the same time do give me the gas consumed by ton produced.

Explaining in details:

I have two tabels:

1 - PDE#HD#Consumption#RP_Consumption_Gas
2 - PDE#HD#SpeedCurves#Productivity

Inside each of these tables, there are two columms with the same name for both -> V, T. The V columm of the

table PDE#HD#Consumption#RP_Consumption_Gas gives me the cas consumed and the V table of

PDE#HD#SpeedCurves#Productivity the productivity. The V1/V2 graph must be ploted in a 24 hours period. What

happens is that if I do it for 24 houres, I mean, 1 day, the graph is ploted completly wrong in time! If I

do the same thing, but using only one columm, not the division by another, it works! Why this is happening ?

What is wrong with the following script ?? I need to know the answer for this as fast as possible! If you

need more details, I will give.

The script:

DayForm = Request.Form("D1")

Dim hoje
Dim dia

If DayForm = "" OR DayForm = 0 Then hoje = true

If hoje Then _
Set rs = conn.Execute("SELECT

convert(varchar,PDE#HD#Consumption#RP_Consumption_Gas.T,108),(PDE#HD#Consumption
#RP_Consumption_Gas.V/PDE#HD#SpeedCurves#Productivity.V) As Consumo FROM

PDE#HD#Consumption#RP_Consumption_Gas INNER JOIN PDE#HD#SpeedCurves#Productivity ON

PDE#HD#Consumption#RP_Consumption_Gas.T = PDE#HD#SpeedCurves#Productivity.T WHERE

PDE#HD#Consumption#RP_Consumption_Gas.T > DATEADD(dd, -1, GetDate()) ORDER BY

PDE#HD#Consumption#RP_Consumption_Gas.T") _
Else _
Set rs = conn.Execute("SELECT

convert(varchar,PDE#HD#Consumption#RP_Consumption_Gas.T,108),(PDE#HD#Consumption
#RP_Consumption_Gas.V/PDE#HD#SpeedCurves#Productivity.V) As Consumo FROM

PDE#HD#Consumption#RP_Consumption_Gas INNER JOIN PDE#HD#SpeedCurves#Productivity ON

PDE#HD#Consumption#RP_Consumption_Gas.T = PDE#HD#SpeedCurves#Productivity.T WHERE

PDE#HD#Consumption#RP_Consumption_Gas.T > convert(date, DATEADD(dd, " & DayForm & ", GetDate())) AND

PDE#HD#Consumption#RP_Consumption_Gas.T < convert(date, DATEADD(dd, " & DayForm+1 & ", GetDate())) ORDER BY

PDE#HD#Consumption#RP_Consumption_Gas.T ")

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-07-16 : 12:04:47

1. slow down...
2. don't panic.
3. any chance of some sample input data and matching expected results?
descriptions of problems are good....but are far better when supported by something visual.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-16 : 14:34:59

If hoje Then _
Set rs = conn.Execute("
SELECT CONVERT(VARCHAR, cg.T, 108) AS theTime,
CASE WHEN p.V = 0 THEN 0.0 ELSE 1.0 * cg.V / p.V END As Consumo
FROM PDE#HD#Consumption#RP_Consumption_Gas AS cg
INNER JOIN PDE#HD#SpeedCurves#Productivity AS p ON p.T = cg.T
WHERE cg.T >= DATEADD(DAY, DATEDIFF(DAY, 1, CURRENT_TIMESTAMP), 0))
ORDER BY cg.T
")
Else
Set rs = conn.Execute("
SELECT CONVERT(VARCHAR, cg.T, 108) AS theTime,
CASE WHEN p.V = 0 THEN 0.0 ELSE 1.0 * cg.V / p.V END AS Consumo
FROM PDE#HD#Consumption#RP_Consumption_Gas AS cg
INNER JOIN PDE#HD#SpeedCurves#Productivity AS p ON p.T = cg.T
WHERE cg.T >= DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), " & DayForm & ")
AND cg.T < DATEADD(DAY, DATEDIFF(DAY, -1, CURRENT_TIMESTAMP), " & DayForm & ")
ORDER BY cg.T
")

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources