How to disaply the count zero for missing record

Hi All

I am having two  tables
Table A location_index,Date ,location...etc
Table B: Location_Index,location_desc ...etc


There are some  missing Records in the Location for particular dates in given  month
so how to write the query to display the missing record as zero in the out put:

For ex:
date                              Location
1/10/2010                         6
2/10/2010                         8
3/10/2010                         0
4/10/2010                         9
5/10/2010                         0
 means if there is a record on that day for  the location it should that value else should display zero. Data base is SQL Server.
Front end query desing using Crystal reports.

Please Help!!!
Thanks  & Regards
Raj.










nrajasekhar7Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
Check this blog from the Crystal site

http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/27807

mlmcc
0
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
I think the approach described in that blog has several significant limitations.  A more robust solution is to create a DATES table in your database.  This table can simply contain one record for each possible date or could include other useful information (Fiscal Period, etc.).  When designing data warehouses, such a table is referred to as a Date Dimension is is a VERY common design element.

Once the table exists, you can Outer Join from it to your other tables.
0
Christoffer SwanströmPartnerCommented:
An alternative to using a date dimension is to use a query to generate the dates for which you want to show data, then left join to your data. E.g.:

SELECT
  TRUNC(SYSDATE) - level
FROM
  dual
CONNECT BY level <= nb_dates

Open in new window


0
DALSOMCommented:
Hi, try this, I have use other info, but , you just need to change some field names, and will get what you need !  

create table #alldates(dt datetime)
declare @dd int
set @dd = 1
insert into #alldates values(GETDATE())
while @dd <= 1000
begin
      insert into #alldates values(DATEADD(dd,(@dd*-1),getdate()))
      set @dd = @dd + 1
end

create table #invoices(invoiceno int not null,inv_date datetime,amount money,sold bit)
insert into #invoices values(1,'2011-12-25',100,0)
insert into #invoices values(2,'2011-12-25',200,0)
insert into #invoices values(3,'2011-12-22',100,0)
insert into #invoices values(5,'2011-12-21',100,0)
insert into #invoices values(8,'2011-12-20',100,0)


select d.dt as [Invoice Date],COUNT(i.inv_date) as [Invoice counts]
from #alldates d left outer join #invoices i on year(d.dt) = year(i.inv_date)
      and MONTH(d.dt) = MONTH(i.inv_date) and DAY(d.dt) = DAY(i.inv_date)
where YEAR(d.dt) = 2011 and MONTH(d.dt) = 12 and DAY(d.dt) >= 20
group by d.dt

Hope this help you ,
Merry C.
 Dalsom
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlmccCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.