Displaying 0 Count - 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 2008 Forums

Transact-SQL (2008)

Displaying 0 Count

Author

Topic

wndrboy2k3
Starting Member

37 Posts

Posted - 2010-03-07 : 11:32:34

Hi everyone! I'm trying to do something that is probably very simple. I'm want to count the number of parts in a location and then group them by location, but when there are no parts there I want it to display 0. Right now the location just doesn't show up.

ALTER PROCEDURE dbo.sp_parbyloc
	
	@startDate datetime,
	@endDate datetime
	
AS
		

SELECT     Operator, LocCode as 'Location', CASE WHEN COUNT(PARTID) < 0 THEN 0 Else COUNT(PARTID) END AS 'TOTAL PARTS'
FROM       Part.dbo.vw_PartDetails
WHERE     (Date BETWEEN (@startDate) AND (@endDate)) AND                       
					(Operator = 'me') AND 
                    (PARTID <> 'OTHER')
GROUP BY Operator, AppointmentLocCode
ORDER BY Operator, AppointmentLocCode
                               
RETURN

any help would be greatly appreciated!

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-03-07 : 13:00:45

ALTER PROCEDURE dbo.sp_parbyloc

@startDate datetime,
@endDate datetime

AS

SELECT Operator, LocCode as 'Location', CASE WHEN COUNT(PARTID) > 0 THEN COUNT(PARTID) Else 0 END AS 'TOTAL PARTS'
FROM Part.dbo.vw_PartDetails
WHERE (Date BETWEEN (@startDate) AND (@endDate)) AND
(Operator = 'me') AND
(PARTID <> 'OTHER')
GROUP BY Operator, AppointmentLocCode
ORDER BY Operator, AppointmentLocCode

RETURN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-07 : 13:44:32

may be this


ALTER PROCEDURE dbo.sp_parbyloc
	
	@startDate datetime,
	@endDate datetime
	
AS
		
SELECT t.Operator, t.LocCode,
COALESCE(r.TOTAL PARTS,0) AS TOTAL PARTS
FROM (SELECT DISTINCT Operator, LocCode FROM  Part.dbo.vw_PartDetails) t
LEFT JOIN
(
SELECT     Operator, LocCode as 'Location', CASE WHEN COUNT(PARTID) < 0 THEN 0 Else COUNT(PARTID) END AS 'TOTAL PARTS'
FROM       Part.dbo.vw_PartDetails
WHERE     (Date BETWEEN (@startDate) AND (@endDate)) AND                       
					(Operator = 'me') AND 
                    (PARTID <> 'OTHER')
GROUP BY Operator, AppointmentLocCode
)r
ON r.Operator = t.Operator
AND r.LocCode = t.LocCode
ORDER BY t.Operator, t.LocCode
                               
RETURN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wndrboy2k3
Starting Member

37 Posts

Posted - 2010-03-08 : 03:31:06

Thanks so much for your responses. I have few problems

@namman - Unfortunately, this brings the same results I had with mine.

@visakh16 - This returns all 0's. I'm sure it's because the first part of it just says to make a column where everything is 0 so the second half is only counting the number in the new columns. Is that right?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 10:35:48

Nope. what i'm doing is take all possible combinations of Operator, LocCode and in second part I'm counting occurance of PARTID for each. this will make sure you get a record for each Operator, LocCode group irrespective of whether they have partid in given period ( (Date BETWEEN (@startDate) AND (@endDate)))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wndrboy2k3
Starting Member

37 Posts

Posted - 2010-03-08 : 12:28:48

Okay... I redid it 10 times and for some reason (I swear nothing changed) It worked!!!

I have one problem though. I want multiple agents but only a filtered few of them out of the bunch. For example

DAVID
JOHN
TIM
MARY
SANDRA

I want DAVID AND JOHN

So I did this




ALTER PROCEDURE dbo.sp_parbyloc
	
	@startDate datetime,
	@endDate datetime
	
AS
		
SELECT t.Operator, t.LocCode,
COALESCE(r.TOTAL PARTS,0) AS TOTAL PARTS
FROM (SELECT DISTINCT Operator, LocCode FROM  Part.dbo.vw_PartDetails WHERE (Operator='DAVID') AND (Operator = 'JOHN')) t
LEFT JOIN
(
SELECT     Operator, LocCode as 'Location', CASE WHEN COUNT(PARTID) < 0 THEN 0 Else COUNT(PARTID) END AS 'TOTAL PARTS'
FROM       Part.dbo.vw_PartDetails
WHERE     (Date BETWEEN (@startDate) AND (@endDate)) AND                       
		    (Operator = 'DAVID')OR
                    (Operator = 'JOHN' AND 
                    (PARTID <> 'OTHER')
GROUP BY Operator,LocCode
)r
ON r.Operator = t.Operator
AND r.LocCode = t.LocCode
ORDER BY t.Operator, t.LocCode
                               
RETURN

If I do it for only one operator the numbers come out right. As soon as I start doing it for more the numbers go crazy and jump to the thousands. Any suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 12:37:34

it takes Operator, LocCode combinations for passed on value of Operator so it will be definitely more than i row per operator

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wndrboy2k3
Starting Member

37 Posts

Posted - 2010-03-08 : 14:19:11

Right that's all find but the values change just by me adding another operator and nothing else.

ie. if I just do the query with DAVID it comes up

David Location Total
-NORTH - 10
-SOUTH - 11

BUT if I do the same query with DAVID AND JOHN and nothing else changed I get

David Location Total
-NORTH - 268
-SOUTH - 516

John Location Total
-NORTH - 589
-SOUTH - 109

Nothing else has changed in the script. I'm sure what you're saying is very clear but I am just not getting the results I want.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources