Calculation cross three 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

SQL Server 2012 Forums

Transact-SQL (2012)

Calculation cross three tables

Author

Topic

Markus_B
Starting Member

6 Posts

Posted - 2014-02-20 : 11:08:11

Hi All,
I am new in creating SQL Statements and I have a Problem to create a specific select Statement to calculate a value.

My frist query Looks like this


select  cast(Eventdate as date) as [Date]
,       Name 
,       MAX (convert (decimal(20,2),Value)/10) - MIN(convert (decimal (20,2),Value)/10)
 
       
  from [Testdb].[dbo].[v_RAW] as p
  where CounterID = 2
group by 
        Name
,       cast(Eventdate as date)

I get the as result the differnece from each max and min value for each day.
But now must be done an addional calculation. Because on the result must be done a Division with a value in another table where the date machtes and also the CounterID.The date colum in the othe table is called timestamp.

Hope anyone has an idea how i can do this...

many thanks

Markus

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-02-20 : 12:14:07

It is not clear what you want to do based on your post. It will be really helpful if you can provide sample data, expected output and a clear explanation of what you want to do (the logic).

Here are some links that can help you prepare your question with more detail so we can help you better:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-23 : 10:27:56

[code]
select m.*,m.Diff/n.DivisorColumn AS NewColumn
from
(
select CounterID
, cast(Eventdate as date) as [Date]
, Name
, MAX (convert (decimal(20,2),Value)/10) - MIN(convert (decimal (20,2),Value)/10) AS Diff

from [Testdb].[dbo].[v_RAW] as p
where CounterID = 2
group by CounterID
, Name
, cast(Eventdate as date)
)m
INNER JOIN OtherTable n
ON n.CounterID = m.CounterID
AND n.[Date] = m.[Date]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Markus_B
Starting Member

6 Posts

Posted - 2014-02-24 : 06:31:15

Hi,

Many thanks for that :-)
Its nearlly working. I found a Problem and I dont't know what's happend there.
In "othetable" the date column is datatype Datetime. In the first part of the select Statement will be done a cast to date which is fine. When I try to Cast also the date column in the second table to "Date" it gives me no data as result. When i use as dataype "date" in the Column date (Othertable)it Woks fine.
I use follwing Statement at the Moment.


select m.*,m.Diff/n.Mat_Weight AS Consumption,n.Mat_Date

from
(
select  CounterID
,       cast(Eventdate as date) as [Date]
,       Name 
,       MAX (convert (decimal(20,2),Value)/10) - MIN(convert (decimal (20,2),Value)/10) AS Diff
 
       
  from [TestDB].[dbo].[v_RAW] as p
  where CounterID = 2
group by CounterID
,        Name
,       cast(Eventdate as date)
)m
inner JOIN Material n
ON n.CounterID = m.CounterID
and cast (n.[Mat_Date] as date) = m.[Date]

The second Problem is, how can i divide (1000) "n.Mat_Weight" direct in the first part? Or should it be done later in the Statement?

Many thanks for your help !!

Markus

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources