How do I run subqueries on a dataset?

Question

Post reply

How do I run subqueries on a dataset?

Don.

Ten Centuries

Points: 1293
More actions
August 16, 2012 at 12:19 am

#265101

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. ie
dsMain Results
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...
Company, Contact, Posting_Date, Matched_Date, Sent_Date, Interview_Date, etc...
etc...
Most of my Subqueries would need to get the average date between dates
The query below is what I use in SQL to get the difference between Average Matched_date and Average SENT_DATE
SubQuery
(Select DATEDIFF(Hour, CAST(AVG(CAST(MATCHED_DATE AS FLOAT)) AS DATETIME), CAST(AVG(CAST(SENT_DATE AS FLOAT)) AS DATETIME))
From #tbl
Where (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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hi Don

What are you grouping by, in order to use the aggregate operator AVG() in the original query containing this subquery?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Don. Ten Centuries Points: 1293 More actions · Answer 2

Thanks for your reply.

Sorry, Im still fairly new to SSRS.

Report results would be as bellow:

CELL00, CELL01, CELL02

Clients, CompanyName, AllCompanies

Matched2Send, Cell2,1, Cell2,2

Send2Interview, Cell3.1, Cell3.2

Row 2 would be grouped on Matched_Date and Sent_Date

Cell2.1 would contain the results of the subquery that I posted above. So Id probably need to Group that Cell on Matched_Date and Sent_Date, in addition there would be a where clause that limited to only select companies.

Cell2.2 Would be the same as Cell2.1, except there wouldnt be a where clause, it would go for all companies.would contain the results of the subquery that I posted above. So Id probably need to Group that Cell on Matched_Date and Sent_Date, in addition there would be a where clause that limited to only select companies.

Row3 would be grouped on Sent_Date and Interview_Date

etc.

Hope thats vaguely clear. 😉

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Hi Don, I can't help with the SSRS issue - however, if you were to choose to do the aggregation and calculations in a query, then I could certainly help with that.

One point though - is your brief to get the average lag between dates, or the lag between average dates? I'm not convinced that dates can be averaged in this way.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Don. Ten Centuries Points: 1293 More actions · Answer 4

ChrisM@Work (8/16/2012)
Hi Don, I can't help with the SSRS issue - however, if you were to choose to do the aggregation and calculations in a query, then I could certainly help with that.
One point though - is your brief to get the average lag between dates, or the lag between average dates? I'm not convinced that dates can be averaged in this way.

Hi Chris,

Thanks for mentioning grouping. Im looking into that and that seems to be the way forward. 🙂

Thanks also for the offer of SQL help, I have that sorted, Im just not familiar with how to implement it in SSRS.

As a matter of interest, do you have any suggestions as to how to go about averaging the time between rows of dates?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Something like this:

SELECT

Company,

AVG_MatchedToSent = AVG(DATEDIFF(hour,Matched_Date, Sent_Date))

FROM YourTable

GROUP BY Company

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Don. Ten Centuries Points: 1293 More actions · Answer 6

ChrisM@Work (8/16/2012)
Something like this:
SELECT
Company,
AVG_MatchedToSent = AVG(DATEDIFF(hour,Matched_Date, Sent_Date))
FROM YourTable
GROUP BY Company

Thanks for the suggestion.

I had to get a bit more complicated though as some of my date fields didn't have dates.

I've created some test data to work with.

What I want to achieve is the Average time between dates (ie between Matched_Date and Sent_Date).

I've managed this, but now I need to find out how to get this same data from a subset of companies.

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

DROP TABLE #mytable

--===== Create the test table with

CREATE TABLE #mytable

(

ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

Company VARCHAR(50),

Username VARCHAR(30),

Matched_Date DATETIME,

Sent_Date DATETIME,

Interview_Date DATETIME,

)

--===== Setup any special required conditions especially where dates are concerned

SET DATEFORMAT DMY

--===== All Inserts into the IDENTITY column

SET IDENTITY_INSERT #mytable ON

--===== Insert the test data into the test table

INSERT INTO #mytable

(ID, Company, Username, Matched_Date, Sent_Date, Interview_Date)

SELECT '1','ACME' ,'Don','01/08/2012 10:17', '02/08/2012 15:23', '03/08/2012 09:18' UNION ALL

SELECT '2','BrickCo','Joe','02/08/2012 11:17', '03/08/2012 09:23', '04/08/2012 11:13' UNION ALL

SELECT '3','BrickCo','Don','02/08/2012 14:17', NULL , '05/08/2012 09:03' UNION ALL

SELECT '4','Acme' ,'Joe','01/08/2012 09:15', '03/08/2012 09:20', '04/08/2012 09:50' UNION ALL

SELECT '5','Acme' ,'Joe','02/08/2012 11:15', '04/08/2012 09:46', '05/08/2012 15:12' UNION ALL

SELECT '6','BrickCo','Don','01/08/2012 14:18', NULL , NULL UNION ALL

SELECT '7','BrickCo','Don','01/08/2012 09:11', '03/08/2012 12:23', '05/08/2012 17:10' UNION ALL

SELECT '8','Acme' ,'Don','01/08/2012 12:11', NULL , '05/08/2012 10:23' UNION ALL

SELECT '9','Acme' ,'Joe','01/08/2012 09:11', '03/08/2012 12:11', '06/08/2012 14:39' UNION ALL

SELECT '10','Acme' ,'Joe','01/08/2012 11:11', '02/08/2012 16:17', NULL

--===== Set the identity insert back to normal

SET IDENTITY_INSERT #mytable ON

I started off with a dataset that queried myTable

SELECT ID, Company, Username, Matched_Date, Sent_Date, Interview_Date

FROM mytable

I then added a calculated field (M2S) to that dataset.

The M2S field calcultates the hourly date difference between the Matched and Sent dates.

If one of the fields is blank, then it should return 0

=IIF(ISDATE(Fields!Matched_Date.Value),

IIF(ISDATE(Fields!Sent_Date.Value),

DateDiff(DateInterval.Hour, Fields!Matched_Date.Value, Fields!Sent_Date.Value),

0)

,0)

Unfortunately I couldnt use the AVG function due to rows with 0 in them.

The following expression divides the total of the rows by the number of rows that arent 0.

On my Test report I've added a text box with the following expression:

=FormatNumber(

Sum(Fields!M2S.Value)

/

IIf(Count(Iif(Fields!M2S.Value > 0, 1, Nothing)) = 0, 1, Count(Iif(Fields!M2S.Value > 0, 1, Nothing)))

, 2)

The expression above returns the average to two decimal places.

The next step is to only query a subset of companies.

How would I manage this in an expression \ calculated field?

I've been trying for ages without much luck. :o(