We help IT Professionals succeed at work.

How to Display the Record as null

Hi All
 iam having the table with date column , if any date is missing in that month,
then i want to display the date but at the same time , i want to show the record count is zero for that date.

Please suggest!!!
Regards
Raj

Comment
Watch Question

Mike McCrackenSenior Consultant
SILVER EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
WHat tool are you using for this?

I gather what you are saying is

Data is

Customer     Date of Sale    AMount
Cust1             1 Oct 11         100
Cust1             3 Oct 11         150


You want to see
1 Oct 11       100
2 Oct 11           0
3 Oct 11       150

mlmcc
Senior Consultant
SILVER EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
awking00Information Technology Specialist
BRONZE EXPERT

Commented:
See attached.
query.txt
Mike McCrackenSenior Consultant
SILVER EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
awking00Information Technology Specialist
BRONZE EXPERT

Commented:
The answer provided is certainly a valid solution for Crystal Reports. However, the question was also posted in the Oracle zone and my response provided an Oracle solution. I'm not objecting, just making a comment.
Mike McCrackenSenior Consultant
SILVER EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Can you explain what it does?

mlmcc
awking00Information Technology Specialist
BRONZE EXPERT

Commented:
Yes, the subquery simply creates date values from the first day to the last day of the month previous to the current month, which can then be left joined to the table on the date fields being equal. Where the date field doesn't exist in the table, the count will be zero.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.