Solved

Only return results where dates are different...

Posted on 2011-03-08
9
234 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 31

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 31

Expert Comment

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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 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

14 Experts available now in Live!

Get 1:1 Help Now