Forcing a 0 if the parameter does not come up in results

Ignition
1

I have a query where I'm looking for the Top 5 results of downtime over a 4-week period in our database, and getting the information from each of those 5 categories for each of those 4 weeks. The problem is that some of those weeks may not have any results for one of those 5 categories. How do I make the query return a result where it includes the parameter that had no result and then the quantity for the correlating column be 0?

And before you ask, the reason I need it this way is because it's going in a stacked bar chart, and without the 0 value, it will create gaps between the stacked bars.

image
image1612×448 45.6 KB

image
image1613×467 41.3 KB

SELECT dispatchl2ldb.dispatchType,
	dispatchl2ldb.reasonDesc,
	IF(dispatchl2ldb.reasonDesc = :topCat1,1,
	IF(dispatchl2ldb.reasonDesc = :topCat2,2,
	IF(dispatchl2ldb.reasonDesc = :topCat3,3,
	IF(dispatchl2ldb.reasonDesc = :topCat4,4,
	IF(dispatchl2ldb.reasonDesc = :topCat5,5,0))))) AS catOrder,
	SUM(dispatchl2ldb.minsDown) AS minTotal,
	WEEKOFYEAR(dispatchl2ldb.reported) AS Week
FROM dispatchl2ldb
WHERE dispatchl2ldb.reasonDesc = :topCat1
  AND dispatchl2ldb.line = :line
  AND dispatchl2ldb.shiftID = :shiftID
  AND dispatchl2ldb.dispatchType = "Waiting On"
  AND WEEKOFYEAR(dispatchl2ldb.reported) = :week
  OR dispatchl2ldb.reasonDesc = :topCat2
  AND dispatchl2ldb.line = :line
  AND dispatchl2ldb.shiftID = :shiftID
  AND dispatchl2ldb.dispatchType = "Waiting On"
  AND WEEKOFYEAR(dispatchl2ldb.reported) = :week
  OR dispatchl2ldb.reasonDesc = :topCat3
  AND dispatchl2ldb.line = :line
  AND dispatchl2ldb.shiftID = :shiftID
  AND dispatchl2ldb.dispatchType = "Waiting On"
  AND WEEKOFYEAR(dispatchl2ldb.reported) = :week
  OR dispatchl2ldb.reasonDesc = :topCat4
  AND dispatchl2ldb.line = :line
  AND dispatchl2ldb.shiftID = :shiftID
  AND dispatchl2ldb.dispatchType = "Waiting On"
  AND WEEKOFYEAR(dispatchl2ldb.reported) = :week
  OR dispatchl2ldb.reasonDesc = :topCat5
  AND dispatchl2ldb.line = :line
  AND dispatchl2ldb.shiftID = :shiftID
  AND dispatchl2ldb.dispatchType = "Waiting On"
  AND WEEKOFYEAR(dispatchl2ldb.reported) = :week
  OR dispatchl2ldb.reasonDesc = :topCat1
  AND dispatchl2ldb.line = :line
  AND dispatchl2ldb.shiftID = :shiftID
  AND dispatchl2ldb.dispatchType = "Operational Fix"
  AND WEEKOFYEAR(dispatchl2ldb.reported) = :week
  OR dispatchl2ldb.reasonDesc = :topCat2
  AND dispatchl2ldb.line = :line
  AND dispatchl2ldb.shiftID = :shiftID
  AND dispatchl2ldb.dispatchType = "Operational Fix"
  AND WEEKOFYEAR(dispatchl2ldb.reported) = :week
  OR dispatchl2ldb.reasonDesc = :topCat3
  AND dispatchl2ldb.line = :line
  AND dispatchl2ldb.shiftID = :shiftID
  AND dispatchl2ldb.dispatchType = "Operational Fix"
  AND WEEKOFYEAR(dispatchl2ldb.reported) = :week
  OR dispatchl2ldb.reasonDesc = :topCat4
  AND dispatchl2ldb.line = :line
  AND dispatchl2ldb.shiftID = :shiftID
  AND dispatchl2ldb.dispatchType = "Operational Fix"
  AND WEEKOFYEAR(dispatchl2ldb.reported) = :week
  OR dispatchl2ldb.reasonDesc = :topCat5
  AND dispatchl2ldb.line = :line
  AND dispatchl2ldb.shiftID = :shiftID
  AND dispatchl2ldb.dispatchType = "Operational Fix"
  AND WEEKOFYEAR(dispatchl2ldb.reported) = :week
  OR dispatchl2ldb.reasonDesc = :topCat1
  AND dispatchl2ldb.line = :line
  AND dispatchl2ldb.shiftID = :shiftID
  AND dispatchl2ldb.dispatchType = "FDT"
  AND WEEKOFYEAR(dispatchl2ldb.reported) = :week
  OR dispatchl2ldb.reasonDesc = :topCat2
  AND dispatchl2ldb.line = :line
  AND dispatchl2ldb.shiftID = :shiftID
  AND dispatchl2ldb.dispatchType = "FDT"
  AND WEEKOFYEAR(dispatchl2ldb.reported) = :week
  OR dispatchl2ldb.reasonDesc = :topCat3
  AND dispatchl2ldb.line = :line
  AND dispatchl2ldb.shiftID = :shiftID
  AND dispatchl2ldb.dispatchType = "FDT"
  AND WEEKOFYEAR(dispatchl2ldb.reported) = :week
  OR dispatchl2ldb.reasonDesc = :topCat4
  AND dispatchl2ldb.line = :line
  AND dispatchl2ldb.shiftID = :shiftID
  AND dispatchl2ldb.dispatchType = "FDT"
  AND WEEKOFYEAR(dispatchl2ldb.reported) = :week
  OR dispatchl2ldb.reasonDesc = :topCat5
  AND dispatchl2ldb.line = :line
  AND dispatchl2ldb.shiftID = :shiftID
  AND dispatchl2ldb.dispatchType = "FDT"
  AND WEEKOFYEAR(dispatchl2ldb.reported) = :week
GROUP BY reasonDesc, Week
ORDER BY catOrder ASC
2

While I don't know the solution to your problem, I can tell that you have a lot of redundancy in that query. It can be simplified to:

SELECT 
  d.dispatchType,
  d.reasonDesc,
  CASE d.reasonDesc
    WHEN :topCat1 THEN 1
    WHEN :topCat2 THEN 2
    WHEN :topCat3 THEN 3
    WHEN :topCat4 THEN 4
    WHEN :topCat5 THEN 5
    ELSE 0
  END AS catOrder,
  SUM(d.minsDown) AS minTotal,
  WEEKOFYEAR(d.reported) AS Week
FROM 
  dispatchl2ldb d
WHERE 
  d.reasonDesc IN (:topCat1, :topCat2, :topCat3, :topCat4, :topCat5)
  AND d.line = :line
  AND d.shiftID = :shiftID
  AND d.dispatchType IN ('Waiting On', 'Operational Fix', 'FDT')
  AND WEEKOFYEAR(d.reported) = :week
GROUP BY 
  d.reasonDesc, Week, d.dispatchType
ORDER BY 
  catOrder ASC;
3

i don't know the exact solution here, but you may want to look into

and SQL Server COALESCE() Function

1 Like
4

How does data get into the DB? It may be easier to force insert a "0" row at the end of each day. Or make a second, simple, query that you run before this one and if it returns nothing then insert a "0" row.

5

We ended up finding a way to make it work, and rewrote it to put the week totals in one table.

image
image786×603 45.2 KB

SELECT d.reasonDesc,
	SUM(CASE WEEKOFYEAR(d.reported) WHEN :week THEN d.minsDown ELSE 0 END) AS wC,
    SUM(CASE WEEKOFYEAR(d.reported) WHEN ((:week)-1) THEN d.minsDown ELSE 0 END) AS w1,
    SUM(CASE WEEKOFYEAR(d.reported) WHEN ((:week)-2) THEN d.minsDown ELSE 0 END) AS w2,
    SUM(CASE WEEKOFYEAR(d.reported) WHEN ((:week)-3) THEN d.minsDown ELSE 0 END) AS w3,
    CASE d.reasonDesc
    	WHEN :topCat1 THEN 1
    	WHEN :topCat2 THEN 2
    	WHEN :topCat3 THEN 3
    	WHEN :topCat4 THEN 4
    	WHEN :topCat5 THEN 5
    	ELSE 0
  	END AS catOrder
                
FROM
	dispatchl2ldb d
	
WHERE
	d.reasonDesc IN (:topCat1, :topCat2, :topCat3, :topCat4, :topCat5)
	AND d.line = :line
	AND d.shiftID = :shiftID
	AND d.dispatchType IN ('Waiting On', 'Operational Fix', 'FDT')
	AND d.deleted = 0

GROUP BY
	d.reasonDesc
	
ORDER BY
	catOrder ASC