asked on

Query

I had this problem before, but I'm just stuck now, grrr..
I have two tables:

DataTable fields:
UniqueID
Reference1
EntryDate
SomeData

ReferenceTable fields:
Reference1

Tables are linked by Reference1 fields.
Now I need to create a query to pull the data from DataTable by Reference1 and EntryDate.

My query is:
select count(DataTable.UniqueID) as Counter, DataTable.EntryDate, ReferenceTable.Reference1
from DataTable right outher join ReferenceTable on
DataTable.Reference1 = ReferenceTable.Reference1
group by DataTable.EntryDate, ReferenceTable.Reference1

The problem is that in any given EntryDate not all Reference1 entries from ReferenceTable exist in DataTable so I don't get full list of ReferenceTable.Reference1 and then 0 (zero) values if Reference1 entry doesn't exist in DataTable. (uhh, hope someone will understand this :))

Help!

Guy Hengel [angelIII / a3]

You might try this:

select coalesce(count(DataTable.UniqueID),0) as Counter, DataTable.EntryDate, COALESCE(DataTable.Reference1,ReferenceTable.Reference1)
from DataTable full outher join ReferenceTable on
DataTable.Reference1 = ReferenceTable.Reference1
group by DataTable.EntryDate, COALESCE(DataTable.Reference1,ReferenceTable.Reference1)

Cheers

Eric Kassan

I would recommend the following:

select sum(case when DataTable.Reference1 =
ReferenceTable.Reference1 then 1 else 0 end)
as Counter,
DataTable.EntryDate,
ReferenceTable.Reference1
from DataTable
outer join ReferenceTable
group by DataTable.EntryDate, ReferenceTable.Reference1

PeterZG

ASKER

i've tried both solutions, but the queries still don't retrieve zero values
:(

Eric Kassan

Oops. Try addint the isNull function:

select isnull(sum(case when DataTable.Reference1 =
ReferenceTable.Reference1 then 1 else 0 end),0)
as Counter,
DataTable.EntryDate,
ReferenceTable.Reference1
from DataTable
outer join ReferenceTable
group by DataTable.EntryDate, ReferenceTable.Reference1

I'm guessing it was returning nulls instead of zeros?

PeterZG

ASKER

No, it wasn't returning nulls or zeros, just skipping rows where zero should be

mgrajkumar

This should meet your requirements Definitely

select count(DataTable.UniqueID) as Counter, DataTable.EntryDate, ReferenceTable.Reference1
from DataTable right outher join ReferenceTable on
DataTable.Reference1 = ReferenceTable.Reference1
and DataTable.entrydate = @Date
group by DataTable.EntryDate, ReferenceTable.Reference1

Guy Hengel [angelIII / a3]

mgrajkumar, please review the comment/answer guidelines at the bottom of the page.
Cheers

PeterZG

ASKER

Sorry mgrajkumar but it doesn't.

mgrajkumar

I have tested the Query and its working fine for me.I think
you can make the question little bit more clear.

PeterZG

ASKER

Your query works fine, but it requires to specify a particular EntryDate. This is not an option for me, that's why its not good for me.

Lets say that my DataTable has the following entries:
UniqueID Reference1 EntryDate
1 Ref1 01/01/2001
2 Ref1 01/01/2001
3 Ref3 01/01/2001
4 Ref2 01/01/2001
5 Ref3 02/01/2001

and my ReferenceTable:
Reference1
Ref1
Ref2
Ref3

I need a query that will retrieve the following data:
EntryDate Reference1 Count
01/01/2001 Ref1 2
01/01/2001 Ref2 1
01/01/2001 Ref3 1
02/01/2001 Ref1 0
02/01/2001 Ref2 0
02/01/2001 Ref3 1

Hope that I made it more clear.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PeterZG

ASKER

Small error, but it gave me the idea..

SELECT L.EntryDate, L.Reference1, COUNT(D.UniqueID)
FROM (SELECT DISTINCT D.EntryDate, R.Reference1
FROM DataTable D CROSS JOIN
ReferenceTable R) AS L LEFT JOIN
DataTable D ON D.EntryDate = L.EntryDate AND
D.Reference1 = L.Reference1
GROUP BY L.Reference1, L.EntryDate

Cheers.