Solved

Query

Posted on 2001-06-13
12
271 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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
Comment Utility
i've tried both solutions, but the queries still don't retrieve zero values
:(
0
 
LVL 1

Expert Comment

by:EKassan
Comment Utility
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
Comment Utility
No, it wasn't returning nulls or zeros, just skipping rows where zero should be
0
 
LVL 1

Expert Comment

by:mgrajkumar
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 142

Expert Comment

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

Author Comment

by:PeterZG
Comment Utility
Sorry mgrajkumar but it doesn't.
0
 
LVL 1

Expert Comment

by:mgrajkumar
Comment Utility
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
Comment Utility
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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 150 total points
Comment Utility
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
Comment Utility
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
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 setup several different housekeeping processes for a SQL Server.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now