Display null values

Hello,

I'm facing a problem in my reporting.

I have a Customer table where is record various events like CustomerEventId, DateTime, StatusId, StatusTime, GroupId, ...

I also have a status table (Id, Description) and a group table (Id, Description).

I want to create a report where for a selected date range (From ... To ...) i can see (grouped by date) all status's the customer

went in. The possible status are :

Id Description

-

1 status 1

2 status 2

3 status 3

4 status 4

My query looks something like this :

SELECT CustomerEventId, DateTime, CONVERT(varchar, DateTime), 103) AS DATEVAL, StatusId,

status.description as StatusDescription, StatusTime, GroupId, group.Description as GroupDescription

From Customers inner join status on customers.StatusId = status.id

inner join group on customers.GroupId = group.id

Group By CustomerEventId, DateTime, StatusId, status.description, StatusTime, GroupId, group.Description

My reports has 3 parameters (From date, To date, Group)

In my report i have a table with two groups : GroupByDate (grouped on DATEVAL) and GroupByStatus

now my problem : let's say i have values for statusid 1,2 and 4

then my report will only display those 3 status.

How can i display the status where there is no data for :

now it shows :

DATEVAL Occurrences Time

01/07/2007

Status 1 15 125

Status 2 25 366

Status 4 8 66

I would like it to show:

DATEVAL Occurrences Time

01/07/2007

Status 1 15 125

Status 2 25 366

Status 3 0 0

Status 4 8 66

Anybody (i hope i have provide enough details ...)

Vinnie

Hello Vinnie,

You're going to need to modify your dataset to return those records (in your example data, "Status 3") with NULL for the rest of the values. Then you can replace the NULL's with 0, either in your SQL query or in the table.

Use an outer join to get all the status records back whether or not there are matching customer records. Something like this:

SELECT CustomerEventId, DateTime, CONVERT(varchar, DateTime), 103) AS DATEVAL, StatusId,

status.description as StatusDescription, StatusTime, GroupId, group.Description as GroupDescription

From Customers

inner join group on customers.GroupId = group.id

right outer join status on customers.StatusId = status.id

Group By CustomerEventId, DateTime, StatusId, status.description, StatusTime, GroupId, group.Description

Hope this helps.

Jarret