Some help with a group by query w/criteria

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 2012 Forums

Transact-SQL (2012)

Some help with a group by query w/criteria

Author

Topic

ipisors
Starting Member

39 Posts

Posted - 2015-04-23 : 14:59:04

My objective is to only select records where the following TWO things are true in the source table:

1. There is only one record containing a given value for rxClmID
2. That record has a positive number for DaysSupply

I thought that this query was correct, but it's not. I am wondering if anyone can point me in the direction of what I'm doing wrong...

select
RxClmID,count(*) 'thecount'
from
[database].dbo.[table]
group by RxClmID,dayssupply
having count(*)=1 and dayssupply>0

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-23 : 15:01:02

select RxClmID, count(*) as thecount
from [database].dbo.[table]
where dayssupply > 0
group by RxClmID
having count(*) = 1

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ipisors
Starting Member

39 Posts

Posted - 2015-04-23 : 15:02:58

Ahhh...See I might have arrived at that, had I been prompted by it erring, but it compiled and ran, who knows what ugly RAT it built behind the scenes based on my guess.

THANK YOU! So the Having is only for criteria on the aggregated columns and a regular Where on a non-aggregated column must come prior.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-23 : 15:09:02

Think of the HAVING as a WHERE clause for the GROUP BY, or at least that's how I think of it.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ipisors
Starting Member

39 Posts

Posted - 2015-04-23 : 15:12:10

Can I ask this one step further...For some reason, I am still getting unexpected results. I know it's my data and you can only do so much from there, but can you just glance at this and see if you spot a particular reason as to why I would still get ANY records with negative numbers for DaysSupply in the final table which is being selected from in the last line of code? DaysSupply is int. rxclmID is numeric.


truncate table [dbo].[FinalAction]
;
/**************************************************************************************************************
--FIRST GET RECORDS THAT HAVE NO ISSUES AT ALL (only one claim number exists so there are no reversals):
--(they can't be negative either)
*************************************************************************************************************/
with NonIssues as
	(
	select 
		RxClmID,count(*) 'thecount'
	from
		[database].dbo.[table]
	where DaysSupply>0
	group by RxClmID
	having count(*)=1
	)
select * into #NonIssues from NonIssues;

with qry1 as
(
	SELECT 
		rx.RxClmID,
		rx.NABP,
		rx.ActualPdAmt,
		rx.NDCNum,
		rx.DaysSupply,
		rx.DOS,
		rx.OriginalQuantity,
		rx.PrescrNPI,
		rx.IngCost,
		rx.DAWcd,
		rx.FormularyChk,
		rx.GenericProductIdentifier,
		rx.[TherapeuticClassCode_AHFS],
		rx.MemberID
	FROM [database].dbo.[table] rx
	where rxclmid in (select RxClmID from #NonIssues)
)
--INSERT OUR NON-ISSUES INTO THE FINAL TABLE:
insert [dbo].[FinalAction] select * from qry1;

If the first sql statement is only records where a single rxclmid exists AND it is positive....and that goes in a temp table...and then we select from the original table ONLY where rxclmid IN() the list of rxclmid's in that temp table....how can I possibly be ending up with records where DaysSupply < 0 in my final table? It seems impossible, but I have a lot of them.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-23 : 15:21:33

Is rxclmid unique in [table]?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ipisors
Starting Member

39 Posts

Posted - 2015-04-23 : 16:23:45

Well ... No. It repeats, with (for example), 6 of them, values identical across all columns actually, except for a few numerical columns (including DaysSupply).

But some rxclmid's are unique - some repeat and some don't.

In the case of that particular piece of the code I'm trying to isolate those that DON'T (one instance only) and ALSO have >0 on DaysSupply.

But it seemed like my logic was sound...if an rxclmid is part of the subset (that you first helped me get) that IS unique on rxclmid, and HAS DaysSupply > 0, then to go back to the very same table and say, now select those which are 'in' that dataset....? Seemed sound to me, guess I'm missing something here. I must be thinking of it wrong.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-23 : 16:39:13

I'll need to see sample data, what it's currently returning and what it should be returning. The key is SAMPLE data, not real data.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ipisors
Starting Member

39 Posts

Posted - 2015-04-23 : 18:51:50

OK. I may have trouble putting that together (but I completely understand the need for it at this point).
Thank you

ipisors
Starting Member

39 Posts

Posted - 2015-04-23 : 19:15:01

I am starting to wonder if it is this: (just a guess) based on the order of operations:

by the time the 'where dayssupply>0' is processed, there IS only count(*)=1

But that doesn't mean there is a count(*)=1 in the entire dataset.

In other words, like I described, I have records where the rxclmid repeats, but dayssupply is varying between negative and positive.

My thought was that in that one particular sql snippet, I could identify the ones where there was only ONE unique rxclmid AND dayssupply>0.

I'm thinking that what happens when it gets to records where there are, say, 2 repeating of the same rxclmid with one negative and one positive, the sql is processed as, where dayssupply>0 (that gets us down to 1), and then "having count(*)=1" is true at that point.

Understanding that you can't be sure at this point without me having provided sample data, does that seem like an accurate viewpoint though?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-24 : 17:38:26

The WHERE clause is processed before the GROUP BY/HAVING.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ipisors
Starting Member

39 Posts

Posted - 2015-04-25 : 22:09:20

exactly that's what I'm saying. So the original solution I don't think is working and that's why it's not.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-27 : 12:31:55

I need to see sample data that illustrates the problem to help.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/