Identify a Series using T-SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Identify a Series using T-SQL

Author

Topic

bconner
Starting Member

48 Posts

Posted - 2011-01-12 : 07:55:26

I have been tasked with identifying invoices where a series of CPT (Test Codes) are present. For example below are a list of invoices if I want to identify invoices where both 88305 and 88306 exist on the same invoice how would I do that using T-SQL?


INVOICE	         CPT
111	        88305
111	        88306
111	        88307
111	        87145
222	        87170
222	        88305
222	        88252
333	        88305
333	        88306
333	        86275
444             88305
444             88798
444             56895
444             87452

Any help is greatly appreciated....

Brian

Sachin.Nand

2937 Posts

Posted - 2011-01-12 : 08:16:46

Select INVOICE from yourtable
group by INVOICE
having count(case CPT when 88305 or 88306 then 1 else 0 end)=2

I havent tested it on a real table

PBUH

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-01-12 : 08:19:39

I think Sachin's version should use SUM and not COUNT.

You could also try the following:


SELECT Invoice
FROM Invoices
WHERE CPT in (88305, 88306)
GROUP BY Invoice
HAVING COUNT(*) = 2

-- or
;WITH InvCnts
AS
(
SELECT Invoice, CPT
	,SUM(CASE WHEN CPT in (88305, 88306) THEN 1 ELSE 0 END) OVER (PARTITION BY Invoice) AS InvCnt
FROM Invoices
)
SELECT Invoice, CPT
FROM InvCnts
WHERE InvCnt = 2

Sachin.Nand

2937 Posts

Posted - 2011-01-12 : 08:21:03

quote:
I think Sachin's version should use SUM and not COUNT.

Yes you are right.Thanks for the catch.

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 08:56:50

[code]
Select INVOICE
from yourtable
WHERE CPT in (88305, 88306)
group by INVOICE
HAVING MIN(CPT) <> MAX(CPT)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bconner
Starting Member

48 Posts

Posted - 2011-01-12 : 09:03:18

Thanks Sachin.Nand and IFor for your help, I have been racking my brains trying to figure out.

Brian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources