troubleshooting Question

How to write the procedures for missing record using sql or sql-server.

Avatar of nrajasekhar7
nrajasekhar7 asked on
Crystal ReportsMicrosoft SQL Server
8 Comments1 Solution474 ViewsLast Modified:

We have four tables:
and Passing three parameters :sdate,edate,loc:
We have ten different locations, For Few locations there is no records for  for few days which is missing:
Date            Lane1   Lane2    Lane3  Total
20-dec-09      0             1          2         3
21-dec-09       1            2          4         7
23-dec-09        2            1        3          6
 means here there is no  record for these loc on 22-dec-09 in the database ,
but i want to show these record as  0 in the  crystal report output

Date            Lane1   Lane2    Lane3  Total
20-dec-09      0             1          2         3
21-dec-09       1            2          4         7
22-dec-09      0             0         0         0
23-dec-09        2           1        3          6

please find my attach report:

iam using a cross tab report:

SELECT "ofn_data1"."ofn_date", "ofn_data"."ofn_location_index", "ofn_data1"."ofn_date_tp", "lane"."lane", "ofn_data"."ofn_index", "location"."location_lta_description"
 FROM   (("A"."dbo"."ofn_data" "ofn_data" INNER JOIN "A"."dbo"."location" "location" ON "ofn_data"."ofn_location_index"="location"."location_index") INNER JOIN "A"."dbo"."ofn_data1" "ofn_data1" ON "ofn_data"."ofn_index"="ofn_data1"."ofn_index") INNER JOIN "A"."dbo"."lane" "lane" ON "ofn_data1"."ofn_lane_tp"="lane"."ofn_lane_tp"
 WHERE  ("ofn_data1"."ofn_date">={ts '2009-12-09 00:00:00'} AND "ofn_data1"."ofn_date"<{ts '2009-12-31 00:00:00'}) AND "ofn_data"."ofn_location_index"=7

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros