Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Only return results where dates are different...

Posted on 2011-03-08
9
Medium Priority
?
280 Views
Last Modified: 2012-05-11
I have a result set like the below - what I need to be able to do - is that if any of the dates match - return the values - else, if all dates are different in the result set - then return nothing...

Sample Results
0
Comment
Question by:tbaseflug
[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
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35072423
what about:
with data as ( select error, dos, hcpcs from yourtable )
, dups as ( select error, convert(varchar(10), dos, 120) dos from data group by error, convert(varchar(10), dos, 120) having count(*) > 1 )
select * 
from data
where error in ( select error from dups ) 

Open in new window

0
 

Expert Comment

by:ZeroFactiX
ID: 35072442
Couldn't you do->

select * from table group by dos having count(*) >1;
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35072469
in MySQL, possibly. not in MSSQL ...
0
Certified OpenStack Administrator Course

We just refreshed our COA course based on the Newton exam.  With 14 labs, this course goes over the different OpenStack services that are part of the certification: Dashboard, Identity Service, Image Service, Networking, Compute, Object Storage, Block Storage, and Orchestration.

 
LVL 2

Accepted Solution

by:
EL_Barbado earned 2000 total points
ID: 35072515
It can be done with a query similar to the following:

Example SETUP:

CREATE TABLE #err(
      error VARCHAR(50),
      dos DATETIME,
      hcpcs INT
)

INSERT INTO #err(error, dos, hcpcs) VALUES('error3', '01/05/2011', 77280)
INSERT INTO #err(error, dos, hcpcs) VALUES('error3', '01/05/2011', 77290)
INSERT INTO #err(error, dos, hcpcs) VALUES('error3', '02/05/2011', 77234)
INSERT INTO #err(error, dos, hcpcs) VALUES('error3', '03/05/2011', 77236)
------------------------------------------------------------------------------------------------

Actual Query to get what you need.

SELECT
      e1.error,
      e1.dos,
      e1.hcpcs
FROM #err e1
INNER JOIN (
      SELECT e2.dos, COUNT(dos) CountDos
      FROM #err e2
      GROUP BY e2.dos
) AllDates ON e1.dos = AllDates.dos
AND CountDos > 1
--------------------------------------------------------------
0
 
LVL 32

Expert Comment

by:awking00
ID: 35073102
Not sure I understand the question. In your example, none of the dates are the same so you want to return nothing? But if one or more of the dates were the same, what would you want returned? Perhaps you can post a little more sample data and what your desired results should be.
0
 

Author Comment

by:tbaseflug
ID: 35074124
OK - well, in the below results - I only want to see the first two rows - where the dates are the same -- so out of all the results returned - where there is more than 1 of the same date - then return those rows - otherwise exclude  Sample
0
 
LVL 2

Expert Comment

by:EL_Barbado
ID: 35074214
My post above solves this problem.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35074374
ok so insert the result set into a temporary table then run a select against it...


select  ....
   into #temp
 from ...
 where ....

select * from #temp as t
 where exists (select dos from #temp as x
                         where x.dos=t.dos
                            group by t.dos
                           having count(*) > 1)
order by ...


0
 
LVL 32

Expert Comment

by:awking00
ID: 35083708
Could have also used an analytical query. See attached.
query.txt
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

661 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