Solved

Only return results where dates are different...

Posted on 2011-03-08
9
252 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
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 142

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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35072469
in MySQL, possibly. not in MSSQL ...
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 2

Accepted Solution

by:
EL_Barbado earned 500 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

813 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

12 Experts available now in Live!

Get 1:1 Help Now