• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

Only return results where dates are different...

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
tbaseflug
Asked:
tbaseflug
  • 2
  • 2
  • 2
  • +3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
ZeroFactiXCommented:
Couldn't you do->

select * from table group by dos having count(*) >1;
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in MySQL, possibly. not in MSSQL ...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
EL_BarbadoCommented:
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
 
awking00Commented:
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
 
tbaseflugAuthor Commented:
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
 
EL_BarbadoCommented:
My post above solves this problem.
0
 
LowfatspreadCommented:
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
 
awking00Commented:
Could have also used an analytical query. See attached.
query.txt
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now