select top 1 days since eventDate use in formula

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)

select top 1 days since eventDate use in formula

Author

Topic

chapm4
Yak Posting Veteran

58 Posts

Posted - 2012-04-11 : 15:39:08

Being new in sql not sure if this is the best way so please correct if I am wrong in my thinking.

I need to use a number of days since an eventDate in a formula. Need to take number of days since eventDate * (hours worked in 12 months / number of days)

I have a field in table that computes number of days and the table is ordered by lastEventDate. So I get my days like this


SELECT TOP 1 (LLT_DAYS)
FROM tbl_HAW_HealthAndSafety_LLT
ORDER BY eveEventDate DESC

I just need to multiply that number (in this case 214) by the result of this


USE SAFETY_WEB
DECLARE @y date
DECLARE @d date

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

SELECT SUM(hsmTotalHoursWorkedByHourlyEmp + hsmTotalHoursWorkedBySalariedEmp) /365 * @x AS AVG_HOURS_DAILY
  FROM tbl_HealthAndSafetyMonthlyData
  WHERE Convert(date,hsmReportingDate) BETWEEN @y AND @d

to get my result (in this case the final number is 552334). I have tried to add the first code into the second but doesnt work. How can I do this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-04-11 : 15:48:32

declare @LLT_DAYS int

select @LLT_DAYS = TOP 1 (LLT_DAYS)
FROM tbl_HAW_HealthAndSafety_LLT
ORDER BY eveEventDate DESC

SELECT SUM(hsmTotalHoursWorkedByHourlyEmp + hsmTotalHoursWorkedBySalariedEmp) /365 * @x * @LLT_DAYS
FROM tbl_HealthAndSafetyMonthlyData
WHERE Convert(date,hsmReportingDate) BETWEEN @y AND @d

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog