EXCEPT function? - 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 2005 Forums

Transact-SQL (2005)

EXCEPT function?

Author

Topic

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-05-14 : 15:17:31

Hi. I currently have a view that is a union of 2 other views. One view is full of records with 0 hours and the other is full of records with non-zero hours. Here is an example:

ID....Activity....Hours......Week
1......23...........14......04/14/2008
1......28...........22......04/18/2008
1......23...........0.......04/26/2008
1......23...........6.......04/26/2008
1......34...........30......04/30/2008

What I need to do pertains to the bold records above. When an ID has two records for the same activity and week, I need to not include the record with 0 hours.

What do I need to do? Is this something I can just add to my current union view or is it more complicated?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-14 : 15:21:43

Perhaps this:

SELECT ID, Activity, MAX(Hours), Week
FROM YourView
GROUP BY ID, Activity, Week

Or if your query is more complicated, you could use a derived table instead.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-05-14 : 15:27:13

Well the view I'm working with is a union.

SELECT DISTINCT *
FROM Non_Zero_Table
UNION
SELECT DISTINCT *
FROM Zero_Table

So the select statement you describe wouldn't work on the the test data I entered in my first post. If I have to create another view that is based off my current union view that's fine.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-14 : 15:31:15

It doesn't matter what the view has in it as I'm not touching that with my solution.

If my solution didn't work, you probably did not do it correctly. Notice the FROM clause.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-05-14 : 15:46:32

I was actually over complicating it. Here is my solution:

SELECT DISTINCT *
FROM Myview
WHERE Hours = (SELECT MAX(Hours)
FROM Myview AS List
WHERE SSN = Myview.SSN AND
Weekend = Myview.Weekend AND
Activity = Myview.Activity))

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-14 : 15:58:39

Your solution will not be as efficient since it is hitting the view twice.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources