Query improvement - SQL Server Forums

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)

Query improvement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-02-05 : 07:17:43

Martin writes "I have a query which, I'm sure, could be written far more efficiently. I've thought of derived tables etc., but this seems to be the only version which returns the results I need. Unsurprisingly, it takes minutes to retrieve the results.

I want to retrieve a count of each value in the [status] field for each RecordID and Source combination. Ideally I need the results returned in a single row for inclusion in a .NET datagrid.

Can anyone suggest how I improve this query?

SELECT
U.RecordID,
U.Source,
Count(*) AS TotalSkips,
(SELECT Count(*) from vw_UnionWorkingDaysLate where RecordID = U.RecordID AND Source=U.Source AND Status=0) 'Open',
(SELECT Count(*) from vw_UnionWorkingDaysLate where RecordID = U.RecordID AND Source=U.Source AND Status=7) 'Caught Up',
(SELECT Count(*) from vw_UnionWorkingDaysLate where RecordID = U.RecordID AND Source=U.Source AND Status=10) 'Business',
(SELECT Count(*) from vw_UnionWorkingDaysLate where RecordID = U.RecordID AND Source=U.Source AND Status=9) 'Recycled',
(SELECT Count(*) from vw_UnionWorkingDaysLate where RecordID = U.RecordID AND Source=U.Source AND Status=6) 'Incorrectly Reported',
(SELECT Count(*) from vw_UnionWorkingDaysLate where RecordID = U.RecordID AND Source=U.Source AND Status=96) 'ISCS Refresh',
(SELECT Count(*) from vw_UnionWorkingDaysLate where RecordID = U.RecordID AND Source=U.Source AND Status=97) 'PDCS Refresh',
(SELECT Count(*) from vw_UnionWorkingDaysLate where RecordID = U.RecordID AND Source=U.Source AND Status=98) 'JSA Refresh',
(SELECT Count(*) from vw_UnionWorkingDaysLate where RecordID = U.RecordID AND Source=U.Source AND Status<>0 AND Status<>6 AND Status<>7 AND Status<>9 AND Status<>10 AND Status<>96 AND Status<>97 AND Status<>98) 'Unspecified',
(SELECT MIN(Occurred) from vw_UnionWorkingDaysLate where RecordID = U.RecordID AND Source=U.Source) 'First',
(SELECT MAX(Occurred) from vw_UnionWorkingDaysLate where RecordID = U.RecordID AND Source=U.Source) 'Last'
FROM vw_UnionWorkingDaysLate U
GROUP BY U.Source, U.RecordID
ORDER BY U.Source, U.RecordID

Thanks
Martin..."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-02-05 : 07:23:09

SELECT U.RecordID, U.Source, Count(*) AS TotalSkips,
Sum(CASE WHEN Status=0 THEN 1 ELSE 0 END) Open,
Sum(CASE WHEN Status=7 THEN 1 ELSE 0 END) [Caught Up],
Sum(CASE WHEN Status=10 THEN 1 ELSE 0 END) Business,
Sum(CASE WHEN Status=9 THEN 1 ELSE 0 END) Recycled,
Sum(CASE WHEN Status=6 THEN 1 ELSE 0 END) [Incorrectly Reported],
Sum(CASE WHEN Status=96 THEN 1 ELSE 0 END) [ISCS Refresh],
Sum(CASE WHEN Status=97 THEN 1 ELSE 0 END) [PDCS Refresh],
Sum(CASE WHEN Status=98 THEN 1 ELSE 0 END) [JSA Refresh],
Sum(CASE WHEN Status NOT IN(0, 7, 10, 9, 6, 96, 97, 98) THEN 1 ELSE 0 END) Unspecified,
MIN(Occurred) [First], MAX(Occurred) [Last]
FROM vw_UnionWorkingDaysLate
GROUP BY Source, RecordID
ORDER BY Source, RecordID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources