Solved

Only return results where dates are different...

Posted on 2011-03-08
9
241 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Complex SQL script 1 30
Query syntax 10 40
Distributed Replay - When should i use it? 1 24
TSQL previous 5 21
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

947 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

19 Experts available now in Live!

Get 1:1 Help Now