Solved

Query

Posted on 2001-06-13
12
304 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
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

679 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