help with multiple table select

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 2008 Forums

Transact-SQL (2008)

help with multiple table select

Author

Topic

chapm4
Yak Posting Veteran

58 Posts

Posted - 2012-04-13 : 11:56:17

Once again stuck. I am putting together a formula that does a select from two tables and should return .42 (current number is 0.42459058)

Original that works (except need help to round to .42)


DECLARE @y date
DECLARE @d date

SET @y = DATEADD(m, -12, CURRENT_TIMESTAMP)
SET @d = CAST(GETDATE() AS date) 

SELECT (COUNT(*) * 200000) / 942084.0
FROM tbl_HAW_HealthAndSafety_LRI
WHERE Convert(date, EventDate) BETWEEN @y AND @d

I want to use variables to compute the formula as below but doesn't like AS @hw


DECLARE @y date
DECLARE @d date
DECLARE @hw int
DECLARE @n int

SET @y = DATEADD(m, -12, CURRENT_TIMESTAMP)
SET @d = CAST(GETDATE() AS date) 
SET @n = 200000

--SUM of total hours worked in previous 12 months
	SELECT SUM(hsmTotalHoursWorkedByHourlyEmp + hsmTotalHoursWorkedBySalariedEmp) AS @hw
	FROM tbl_HealthAndSafetyMonthlyData
	WHERE Convert(date,hsmReportingDate) BETWEEN @y AND @d
  
--Data to Insert into table	
	INSERT INTO tbl_test (R12MRIR
	SELECT (COUNT(*) * @n) / @hw
	FROM tbl_HAW_HealthAndSafety_LRI
	WHERE Convert(date, EventDate) BETWEEN @y AND @d

Please help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-13 : 12:01:09

it should be


DECLARE @y date
DECLARE @d date
DECLARE @hw int
DECLARE @n int

SET @y = DATEADD(m, -12, CURRENT_TIMESTAMP)
SET @d = CAST(GETDATE() AS date) 
SET @n = 200000

--SUM of total hours worked in previous 12 months
	SELECT @hw =SUM(hsmTotalHoursWorkedByHourlyEmp + hsmTotalHoursWorkedBySalariedEmp) 	FROM tbl_HealthAndSafetyMonthlyData
	WHERE Convert(date,hsmReportingDate) BETWEEN @y AND @d
  
--Data to Insert into table	
	INSERT INTO tbl_test (R12MRIR
	SELECT (COUNT(*) * @n) * 1.0 / @hw 
	FROM tbl_HAW_HealthAndSafety_LRI
	WHERE Convert(date, EventDate) BETWEEN @y AND @d

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

chapm4
Yak Posting Veteran

58 Posts

Posted - 2012-04-13 : 12:20:24

Thanks for all of your help. I hope I can help others soon as you do.

Works perfect except I need to round show the result (0.424590588525) as .42
I tried ROUND but got an error.

I have another query that results in 942084.000000 and want to show 942,084
Once again an int type. How would I do that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-13 : 12:22:59

for rounding to two decimal places use ROUND(expression,2)

didnt understand why you got error. what was the error?

For second question its a formatting requirement and t-sql is not place to do it. you should be doing it at your presentation layer

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

chapm4
Yak Posting Veteran

58 Posts

Posted - 2012-04-13 : 13:50:44

My result is 0.42000000000

from


SELECT ROUND((COUNT(*) * @n) * 1.0 / @hw,2)

So it is rounding but showing the 0's?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-13 : 14:03:50

ROUND function returns a result with the same precision and scale as the input parameter if the input parameter is of type decimal. So if you do want it to show fewer decimal places, cast the result to a lower scale. For example:

SELECT CAST(ROUND((COUNT(*) * @n) * 1.0 / @hw,2) AS DECIMAL(18,2))

But, then you might ask, why round it at all, rather than just cast it to a decimal of lower scale:

CAST((COUNT(*) * @n) * 1.0 / @hw,2 AS DECIMAL(18,2))

Also, many people on this forum would advice you to do this type of formatting on the client side such as the presentation layer.

chapm4
Yak Posting Veteran

58 Posts

Posted - 2012-04-13 : 14:16:16

Why would they advise against doing it this way? I ultimately need the number to show on an asp page so is that my presentation layer? I wanted the asp to do all of the sql queries and put the result in the proper place on the page which works quite well for most sql, but the ones like the above where I have to declare variables to use with multiple selects it doesn't. That is why I am trying it this way on the sql server side. Then I throw this value in a table and retrieve it on the asp side.
Very open to suggestions.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-13 : 14:36:51

Yes, the ASP page would be the presentation layer.

Sometimes, I am guilty of doing formatting or other types of data manipulation on the SQL side which would be looked down upon by database purists. So I am not necessarily suggesting that you should NEVER do any formatting on SQL side.

In many cases, such formatting requires one to convert the data to character format. For example, if a date needs to be presented in a specific format, you would then convert it to a char/varchar. Once you do that, you lose the ability to sort and filter the dates correctly. That is one of the reasons people recommend against doing formatting in SQL.

That said, I think Microsoft may be coming over to our (the non-database purists) side if introduction of features such as FORMAT in SQL 2012 is any indication.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-13 : 15:05:17

Why would they advise against doing it this way?
If you're doing the conversion or rounding just for showing value in a particular in one of forms then thats the issue. Why should you mess with the accuracy of data retrieved from table for that? also it might be that in most cases you wanted to use the value with full precision for all the calculations internally but for just display sake you want to show it upto say 2 decimal places. In such cases, if you apply conversion while retrieving from table itself then you're losing on accuracy of data which might even have effect on end result you're showing on form especially when it involves aggregating large amount of data over period.
Another issue is when you're compromising on native data type of column just for displaying like converting a numeric value to varchar just for adding , thousand seperator,any furhet manipulations like sorting etc using the data can affect as it has changed its native datatype.
Also conversion operations are costly operations and if at all there's a way to easily do it at front end using formatting functions, why should you have to do this at sql end using conversion logics?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

chapm4
Yak Posting Veteran

58 Posts

Posted - 2012-04-13 : 17:02:25

Very interesting points. I get the (lucky) privilege to attend classes for SQL Server in the next 3 weeks (6231, 6232 and 2778) and am certainly going to be thinking and looking at the most efficient way of doing things, so thanks so much for the input. I hope to come back with great knowledge that I can offer to others as you do.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-13 : 20:53:46

Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources