?
Solved

Query

Posted on 2001-06-13
12
Medium Priority
?
315 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

801 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