Multiple values from two subqueries

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

General SQL Server Forums

New to SQL Server Programming

Multiple values from two subqueries

Author

Topic

Alain_TV
Starting Member

12 Posts

Posted - 2015-04-07 : 04:31:16

Hi all,

I have an existing query, which runs pretty good. However, I need to create a new report, in which I would require to alter the existing query to return two values from each of the subqueries back to the main query.

The following is the current query:


SELECT
  tbLogTimeValues.DateTimeStamp as DateTimeStamp
  ,tbLogTimeValues.FloatVALUE AS Value
FROM
  tbLogTimeValues
WHERE
  ParentID = 
  (
	SELECT 
		ID
	FROM 
		tbLoggedEntities
	WHERE 
		Path LIKE '/Extended Logs/Tenancy Monitoring/L2/%'
		AND Path LIKE '%' + 'Temperature Interval%'
		AND Path Like '%' + 'Average Aisle A' + '%'
		AND Path Like '%' + '200' + '%'
  )
  AND tbLogTimeValues.FloatVALUE > 0
  AND tbLogTimeValues.FloatVALUE <= 100  
  AND tbLogTimeValues.DateTimeStamp > Convert(Date, @StartDate)
  AND tbLogTimeValues.DateTimeStamp < Convert(Date, @EndDate)
  
ORDER BY 
  DateTimeStamp

Could someone please help me, in putting together a query that returns two values from the first sub query (DateTimeStampTemperature and ValueTemperature) and from the second query return (DateTimeStampHumidity and ValueHumidity).

I would simply have to change the Path LIKE '%' + 'Temperature Interval%' within the humidity subquery to read:
Path LIKE '%' + 'Humidity Interval%'

Obviously, I would like to keep the DateTimeStamp comparisons as part of the main query, as the customer has access to this via parameters, and should be the same for both subqueries.

Thanks for your time and help!

Regards,
Alain

Alain_TV
Starting Member

12 Posts

Posted - 2015-04-07 : 07:37:06

I believe that I found the right query. However, I will have to wait till tomorrow morning, to test it in the office.

The following is my query, which compiles alright in SQL Management Studio 2012.


SELECT
  (resultA.DateTimeStamp) As DateTimeStampTemp
  ,(resultA.FloatVALUE) AS ValueTemp
  ,(resultB.DateTimeStamp) As DateTimeStampHumi
  ,(resultB.FloatVALUE) AS ValueHumi

FROM
	(
	SELECT
		tbLogTimeValues.DateTimeStamp
		,tbLogTimeValues.FloatVALUE
	FROM
		tbLogTimeValues
	WHERE
	ParentID = 
				(
				SELECT ID
				FROM tbLoggedEntities
				WHERE Path LIKE '/Extended Logs/Tenancy Monitoring/L2/%'
				AND Path LIKE '%' + 'Temperature Interval%'
				AND Path Like '%' + 'Average Aisle A' + '%'
				AND Path Like '%' + '200' + '%'
				)
	AND tbLogTimeValues.FloatVALUE > 0
	AND tbLogTimeValues.FloatVALUE <= 60
	AND tbLogTimeValues.DateTimeStamp > Convert(Date, @StartDate)
    AND tbLogTimeValues.DateTimeStamp < Convert(Date, @EndDate)
	) As resultA,

	(
	SELECT
		tbLogTimeValues.DateTimeStamp
		,tbLogTimeValues.FloatVALUE
	FROM
		tbLogTimeValues
	WHERE
  ParentID = 
			(
			SELECT ID
			FROM tbLoggedEntities
			WHERE Path LIKE '/Extended Logs/Tenancy Monitoring/L2/%'
			AND Path LIKE '%' + 'Humidity Interval%'
			AND Path Like '%' + 'Average Aisle A' + '%'
			AND Path Like '%' + '200' + '%'
			)
	AND tbLogTimeValues.FloatVALUE > 0
	AND tbLogTimeValues.FloatVALUE <= 100
	AND tbLogTimeValues.DateTimeStamp > Convert(Date, @StartDate)
    AND tbLogTimeValues.DateTimeStamp < Convert(Date, @EndDate)
  ) As resultB

Regards,
Alain

Alain_TV
Starting Member

12 Posts

Posted - 2015-04-07 : 22:28:04

Unfortunately, it returns the right columns and the data appears to be correct, however, it returns thousands of records, instead of 326 records that should return whenever I select a single day, within the date range.

Could someone help me, how to best add a LEFT JOIN (I think is what I need), to ensure, that it only returns the date time from one of the two select queries instead of every combination?

I would need to compare the two datetimestamp fields with each other, to ensure that they are the same, within a minute, as there could be discrepancies in regards to milliseconds between the two result sets.

Any help would be much appreciated.

Regards,
Alain

Alain_TV
Starting Member

12 Posts

Posted - 2015-04-08 : 00:19:41

Just in case anyone has a similar issue, the following is what I come up with.


Select a.DateTimeStamp, a.ValueTemp, b.ValueHumi from

	(SELECT
		a.DateTimeStamp
		,a.FloatVALUE as ValueTemp
	FROM
	tbLogTimeValues	a	
	WHERE
	ParentID =
				(
				SELECT ID
				FROM tbLoggedEntities
				WHERE Path LIKE '/Extended Logs/Tenancy Monitoring/L2/%'
				AND Path LIKE '%' + 'Temperature Interval%'
				AND Path Like '%' + 'Average Aisle A' + '%'
				AND Path Like '%' + '200' + '%'
				)	
	AND a.FloatVALUE > 0
	AND a.FloatVALUE <= 100
	AND a.DateTimeStamp > Convert(Date, @StartDate)
    AND a.DateTimeStamp < Convert(Date, @EndDate)
    
    GROUP BY   
    a.DateTimeStamp,
    a.FloatVALUE) as a,



	(SELECT
		b.DateTimeStamp
		,b.FloatVALUE as ValueHumi
	FROM
	tbLogTimeValues b		
	WHERE
	ParentID =
				(
				SELECT ID
				FROM tbLoggedEntities
				WHERE Path LIKE '/Extended Logs/Tenancy Monitoring/L2/%'
				AND Path LIKE '%' + 'Humidity Interval%'
				AND Path Like '%' + 'Average Aisle A' + '%'
				AND Path Like '%' + '200' + '%'
				)	
	AND b.FloatVALUE > 0
	AND b.FloatVALUE <= 100
	AND b.DateTimeStamp > Convert(Date, @StartDate)
    AND b.DateTimeStamp < Convert(Date, @EndDate)
    
    GROUP BY    
    b.DateTimeStamp,
    b.FloatVALUE) as b
     
     
    WHERE (DATEDIFF(MINUTE,a.DateTimeStamp,b.DateTimeStamp) =1)     
    GROUP BY a.DateTimeStamp, a.ValueTemp, b.ValueHumi

It does indeed work, returning 3 columns "DateTimeStamp/ValueTemp/ValueHumi" and 1440 entries when the date range is set to one day.

Thanks for reading,

Regards,
Alain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources