Crosstab Placeholder

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got a crosstab query performing counts on two items within a column. For
the sake of the example let's just say the values are "A" & "B". Totals are
calculated by the number of occurrences of "A" & "B" for the previous day.
Some days may have no occurrence of "A" or "B" and when that happens the
crosstab column for "A" or "B" will be absent because obviously no
occurrrence happened to count. I need a conditional to state that if there's
no occurrence of "A" or "B", build a placeholder column of zeros. I'm more
than ok if the conditional has to exist in a secondary select query...

Example....

Ok with this occurrence:
Yesterday had both "A" & "B" values
Date---------|A|B|
2007-10-29-|3|4|

Not Ok with this one...
Yesterday only had "A" values
Date---------|A|
2007-10-29-|3|

When the 2nd example occurs, I need a placeholder of zeros for the absent
"B" values.
Date---------|A|B|
2007-10-29-|3|0|

I hope I was able to explain myself clear enough. Any thoughts or
suggestions would be greatly appreciated.

Thanks,
 
TRANSFORM Sum(YourTable.debit) AS SumOfdebit
SELECT YourTable.YourDate
FROM YourTable
GROUP BY YourTable.YourDate
PIVOT YourTable.PA IN("A", "B");
 
Back
Top