Longwinded Union Join - better solutions? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

Longwinded Union Join - better solutions?

Author

Topic

chenko
Starting Member

24 Posts

Posted - 2007-10-23 : 07:18:26

This is what is being used now


DECLARE @DateFrom datetime
DECLARE @DateTo datetime

SET @DateFrom = '1 October 2007'
SET @DateTo = '31 October 2007'

SELECT     'TDK' As Machine, ISNULL(SUM(Weight), 0) AS Sum_Weight, ISNULL(SUM(Length), 0) / 1000 AS Sum_Length, COUNT(Weight) AS Cnt
FROM         SteelRecords
WHERE     (Processed_TDK_DateTime BETWEEN @DateFrom AND @DateTo)
UNION ALL
SELECT     'BDL' As Machine, ISNULL(SUM(Weight), 0) AS Sum_Weight, ISNULL(SUM(Length), 0) / 1000 AS Sum_Length, COUNT(Weight) AS Cnt
FROM         SteelRecords
WHERE     (Processed_BDL_DateTime BETWEEN @DateFrom AND @DateTo)
UNION ALL
SELECT     'APS' As Machine, ISNULL(SUM(Weight), 0) AS Sum_Weight, ISNULL(SUM(Length), 0) / 1000 AS Sum_Length, COUNT(Weight) AS Cnt
FROM         SteelRecords
WHERE     (Processed_APS_DateTime BETWEEN @DateFrom AND @DateTo)
UNION ALL
SELECT     'FDB' As Machine, ISNULL(SUM(Weight), 0) AS Sum_Weight, ISNULL(SUM(Length), 0) / 1000 AS Sum_Length, COUNT(Weight) AS Cnt
FROM         SteelRecords
WHERE     (Processed_FDB_DateTime BETWEEN @DateFrom AND @DateTo)
UNION ALL
SELECT     'ABC' As Machine, ISNULL(SUM(Weight), 0) AS Sum_Weight, ISNULL(SUM(Length), 0) / 1000 AS Sum_Length, COUNT(Weight) AS Cnt
FROM         SteelRecords
WHERE     (Processed_ABC_DateTime BETWEEN @DateFrom AND @DateTo)

Works fine, fast enough really, just runs a simple report page.

But im curious as to if there is any better way of doing this?

(Hopefully the code is sufficent enough to be understandable, if not I will do some example data)

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 07:28:01

You could do


SELECT	CASE WHEN (Processed_TDK_DateTime BETWEEN @DateFrom AND @DateTo) THEN 'TDK' 
	CASE WHEN (Processed_BDL_DateTime BETWEEN @DateFrom AND @DateTo) THEN 'BDL' 
	...
	As Machine,
	...

but if a given row could be both TDK and BDL that would not work. Also if you then have to do:


WHERE	   (Processed_TDK_DateTime BETWEEN @DateFrom AND @DateTo)
	OR (Processed_BDL_DateTime BETWEEN @DateFrom AND @DateTo)
	...

the performance will be rubbish, and your UNION will be faster.

The only thing I dislike about your UNION is that the Expressions for Sum_Weight are duplicated - thus a Maintenance change to noe of those may not be applied to the others

So I suppose you could improve that with:


SELECT     Machine, ISNULL(SUM_Weight), 0) AS Sum_Weight, ISNULL(SUM_Length), 0) / 1000 AS Sum_Length, COUNT_Weight AS Cnt
FROM
(
	SELECT     'TDK' As Machine, SUM(Weight) AS SUM_Weight, SUM(Length) AS SUM_Length, COUNT(Weight) AS COUNT_Weight
	FROM         SteelRecords
	WHERE     (Processed_TDK_DateTime BETWEEN @DateFrom AND @DateTo)
	UNION ALL
	...
) AS X

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources