TSQL - Fill in missing values as 0 for dates without a value-CodePudding

Apologies in advance for the pictures. I did these up as tables in Stack and they displayed fine in Preview but would not display properly once published.

I have a sample dataset like below with many more KPIs.

Picture Of Tables Because StackOverflow did not like them

The dataset currently returned is just a join of these 2 tables and returns date instead of Date ID, like below: Query

WITH ExampleQuery AS
(
    SELECT
        KPI, Value, Date
    FROM
        Date
    LEFT JOIN 
        KPI ON Date.DateID = KPI.DateID
)
SELECT
    KPI, 
    SUM(Value),
    Date
FROM
    ExampleQuery
GROUP BY
    Date, KPI

DataSet Returned

I need the dataset to return like below (notice the additional record for KPI B on the 02-01-2022

I can achieve this by below query, but it is very time consuming considering there are a large number of KPIs I need to do this for. Would greatly appreciate any assistance in how I can get this to work somewhat dynamically

WITH ExampleQuery AS
(
    SELECT
        CASE WHEN KPI = 'A' THEN Value ELSE 0 END AS ValueA,
        CASE WHEN KPI = 'B' THEN Value ELSE 0 END AS ValueB,
        Date,
        KPI
    FROM
        Date
    LEFT JOIN 
        KPI ON Date.DateID = KPI.DateID
)
SELECT
    KPI, ValueA, Date
FROM
    ExampleQuery
WHERE
    KPI = 'A'
UNION ALL
SELECT
    KPI, ValueB, Date
FROM
    ExampleQuery
WHERE
    KPI = 'B'

Any assistance would be very much appreciated before I spend the next day re-writing the query with hard-coded CASE statements

CodePudding user response：

Using a cross join approach, we can come up with the set of all KPI values and also the set of all dates. Then, left join this to your KPI table, to ensure that all dates appear for all KPI values.

SELECT k.KPI, COALESCE(t.Value, 0) AS Value, d.Date
FROM (SELECT DISTINCT KPI FROM KPI) k
CROSS JOIN (SELECT Date, DateID FROM Date) d
LEFT JOIN KPI t
    ON t.KPI = k.KPI AND t.DateID = d.DateID
ORDER BY k.KPI, d.Date;

CodePudding user response：

You want one result per date and KPI type. So cross join the two and then outer join your data.

select
  date.date,
  type.kpi,
  coalesce(sum(kpi.value), 0) as total
from date
cross join (select distinct kpi from kpi) type
left join kpi on kpi.dateid = date.dateid and kpi.kpi = type.kpi
group by date.date, type.kpi
order by date.date, type.kpi;