SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Calculation cross three tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Markus_B
Starting Member

Germany
2 Posts

Posted - 02/20/2014 :  11:08:11  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4604 Posts

Posted - 02/20/2014 :  12:14:07  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/23/2014 :  10:27:56  Show Profile  Reply with Quote

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]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Markus_B
Starting Member

Germany
2 Posts

Posted - 02/24/2014 :  06:31:15  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000