?
Solved

Query

Posted on 2001-06-13
12
Medium Priority
?
328 Views
Last Modified: 2006-11-17
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!
0
Comment
Question by:PeterZG
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6185613
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
0
 
LVL 1

Expert Comment

by:EKassan
ID: 6186057
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
0
 
LVL 1

Author Comment

by:PeterZG
ID: 6186162
i've tried both solutions, but the queries still don't retrieve zero values
:(
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
LVL 1

Expert Comment

by:EKassan
ID: 6186179
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?
0
 
LVL 1

Author Comment

by:PeterZG
ID: 6186244
No, it wasn't returning nulls or zeros, just skipping rows where zero should be
0
 
LVL 1

Expert Comment

by:mgrajkumar
ID: 6191120
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






0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6191677
mgrajkumar, please review the comment/answer guidelines at the bottom of the page.
Cheers
0
 
LVL 1

Author Comment

by:PeterZG
ID: 6191963
Sorry mgrajkumar but it doesn't.
0
 
LVL 1

Expert Comment

by:mgrajkumar
ID: 6192172
I have tested the Query and its working fine for me.I think
you can make the question little bit more clear.
0
 
LVL 1

Author Comment

by:PeterZG
ID: 6194703
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.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 600 total points
ID: 6195352
aaaaaahhh

Here is the solution:

select
  L.EntryDate,
  L.Reference1,
  count(*)

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
  D.EntryDate,
  L.EntryDate
 

Hope i didn't write syntax error, as i couldn't test right now...

Cheers
0
 
LVL 1

Author Comment

by:PeterZG
ID: 6195417
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.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question