Solved

Query

Posted on 2001-06-13
12
308 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 150 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 ?
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

737 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