Return Zero Count Regardless of Where statment

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

SQL Server Development (2000)

Return Zero Count Regardless of Where statment

Author

Topic

jbailey
Starting Member

4 Posts

Posted - 2009-03-03 : 12:32:06

I'm trying to create a view that shows the number of services done on each ship even if that count is zero. This works fine using a left outer joing until I put the WHERE clause in. Is there a way to make it retrun ships with no services as well as the ships with services?

Here is my basic query:

SELECT dbo.tblVessels.VesselName, COUNT(dbo.tblServiceRequest.ServiceRequestID) AS SvcCount, dbo.tblVessels.VesselID
FROM dbo.tblVessels LEFT OUTER JOIN
dbo.tblServiceRequest ON dbo.tblVessels.VesselID = dbo.tblServiceRequest.VesselID LEFT OUTER JOIN
dbo.tblContracts ON dbo.tblVessels.VesselID = dbo.tblContracts.VesselID
GROUP BY dbo.tblVessels.VesselName, dbo.tblVessels.VesselID

Ultimatley, I want to return the count of services in a date range that are contract covered services for vessels that have an active contract. However, I also want to know of the vessels that haven't had any services performed in that same date range.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 12:36:31

make it like this


SELECT dbo.tblVessels.VesselName, COUNT(dbo.tblServiceRequest.ServiceRequestID) AS SvcCount, dbo.tblVessels.VesselID
FROM dbo.tblVessels LEFT OUTER JOIN
dbo.tblServiceRequest ON dbo.tblVessels.VesselID = dbo.tblServiceRequest.VesselID LEFT OUTER JOIN
dbo.tblContracts ON dbo.tblVessels.VesselID = dbo.tblContracts.VesselID
AND <your date range condition here>
GROUP BY dbo.tblVessels.VesselName, dbo.tblVessels.VesselID

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-03 : 12:37:54

Put that where part of your query in the join clause.

SELECT dbo.tblVessels.VesselName, COUNT(dbo.tblServiceRequest.ServiceRequestID) AS SvcCount, dbo.tblVessels.VesselID
FROM dbo.tblVessels LEFT OUTER JOIN
dbo.tblServiceRequest ON dbo.tblVessels.VesselID = dbo.tblServiceRequest.VesselID and .....
LEFT OUTER JOIN
dbo.tblContracts ON dbo.tblVessels.VesselID = dbo.tblContracts.VesselID
GROUP BY dbo.tblVessels.VesselName, dbo.tblVessels.VesselID

Or post the full query with the where part as well.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 12:50:32

also see

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx

jbailey
Starting Member

4 Posts

Posted - 2009-03-03 : 16:08:53

Thanks!

That is exactly what I needed. The example helped a lot sakets 2000.

Nice blog, too. It has a new subscriber.

jbailey
Starting Member

4 Posts

Posted - 2009-03-03 : 16:27:16

I thought I'd paste my final query. In order to get contract vessels I created a new view called contracts and filtered it for NOT NULL contract start dates and NULL contract end dates which I linked it to the Vessel table with an Inner Join. This is the best way I could think of to get active contracts. A check box would've been much easier ;)

SELECT Vessels.VesselName, COUNT(Services.ServiceRequestID) AS SvcCount, Vessels.VesselID, Services.MaterialValue, Services.LabourValue, Services.TravelValue, Services.Total, Services.FreightValue, Services.MiscellaneousCosts
FROM dbo.tblVessels Vessels INNER JOIN
dbo.contracts ON Vessels.VesselName = dbo.contracts.VesselName LEFT OUTER JOIN
dbo.tblServiceRequest Services ON Vessels.VesselID = Services.VesselID AND Services.AttendanceDate BETWEEN CONVERT(DATETIME, '2009-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2009-01-31 00:00:00', 102) AND Services.RequestType = '1'
GROUP BY Vessels.VesselName, Vessels.VesselID, Services.MaterialValue, Services.LabourValue, Services.TravelValue, Services.Total, Services.FreightValue, Services.MiscellaneousCosts

Thanks again for you help. If you have any suggestions for returning active contracts I'd appreciate those, too. I'm not sure I'm doing it the best way.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 10:11:10

you dont need a new view for that. you can simply use a derived table in your current query or just add a new derived column in select list to distinguish contract vessels

jbailey
Starting Member

4 Posts

Posted - 2009-03-04 : 13:09:25

quote:
Originally posted by visakh16

you dont need a new view for that. you can simply use a derived table in your current query or just add a new derived column in select list to distinguish contract vessels

Thanks! that worked very well.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 13:10:59

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources