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.
| Author |
Topic |
|
Don.
Starting Member
1 Post |
 Posted - 2012-08-16 : 05:07:51
|
| Hi Guys,I have a dataset which could potentially be fairly large depending on the date range that the user selects, so Id prefer to run it only once, and get my results from that...(In SQL, I created a temp table and ran my subqueries against that.)This dataset will mainly be composed of dates and I have to get the average time between dates. iedsMain ResultsCompany, Contact, Posting_Date, Matched_Date, Sent_Date, Interview_Date, etc...Company, Contact, Posting_Date, Matched_Date, Sent_Date, Interview_Date, etc...Company, Contact, Posting_Date, Matched_Date, Sent_Date, Interview_Date, etc...Company, Contact, Posting_Date, Matched_Date, Sent_Date, Interview_Date, etc...etc...Most of my Subqueries would need to get the average date between datesThe query below is what I use in SQL to get the difference between Average Matched_date and Average SENT_DATESubQuery(Select DATEDIFF(Hour, CAST(AVG(CAST(MATCHED_DATE AS FLOAT)) AS DATETIME), CAST(AVG(CAST(SENT_DATE AS FLOAT)) AS DATETIME))From #tblWhere (Matched_Date is not null and Matched_Date <> '')AND (Sent_Date is not null and Sent_Date <> '') AND Company_Name IN ('COMPANYNAME')) AS "CompanyName",I spent ages trying to convert this subquery to an expression, but it doesnt look like expressions allow the CAST function.Thanks in advance. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-08-16 : 06:53:53
|
SQL Server internally represents DATETIME as a floating point number - that is why you are able to do it in SQL. You can simulate it in SSRS by replacing it with this:AVG(DATEDIFF(hh,'1/1/1900',MATCHED_DATE)) - AVG(DATEDIFF(hh,'1/1/1900',SENT_DATE)) It may not be exact because I am using hours in DATEDIFF. You can get more precision by using seconds. Also, the 1/1/1900 is an arbitrary date - you can use any arbitrary date. |
 |
|
|
|
|
|
|
|
How do I run subqueries on a Dataset?