We have a BAQ to show how many parts are produced on a certain day within a set of dates. That all works fine, but if there is one day where nothing is produced, it wont show up as a data point in a graph. Is there a calendar or something we could cross with to get every date within two points?
I think there’s an example here somewhere where someone did a union with all dates that had zeros in it making sure there was at least one record per day.
You’ll want to use a CASE statement (Calculated Field) to set the value to 0 if the value is NULL or empty. Something like this (tables/columns are made up - substitute for actual values):
CASE
WHEN JobHead.QtyCompleted IS NOT NULL OR JobHead.QtyCompleted<>''
THEN JobHead.QtyCompleted
ELSE 0
END
You need a recursive subquery/CTE that generates a list of dates between a start and end point. That means creating the anchor subquery, then a union all subquery tied to the anchor. Then join the anchor to your data.
/*This is a CTE subquery.
It doesn't have any tables in the phrase tab.
Just one calculated field.*/
with [Anchor] as
(select
(@Start) as [Calculated_Date]
//UnionAll subquery that uses Anchor as its only source table.
//Don't forget the where clause, otherwise this thing loops forever.
union all
select
(DateAdd(D,1, Anchor.Calculated_Date)) as [Calculated_Date]
from Anchor as Anchor
where (Anchor.Calculated_Date < @End))
/*This is a basic top level query that merely outputs the CTE
You'd left join your real data to this to get an inclusive list.
Just make sure you account for nulls.
That will happen when you have nothing to report on that day.
ISNULL() is your friend here.*/
select
[DateList].[Calculated_Date] as [Calculated_Date]
from Anchor as DateList
1 Like