SQL Select Server Query

I have a table 'tblTaxRecs' in my SQL Server DB.  Some example records with fields that are pertinent to my question are:

RecID      Pay Status      DTDYear      DTDNum
1            1                      10            123
2            2                      11            345
3            3                     10            123
4            1                     12            456
5            1                     16            78919
6            1                     10            123
7            1                     12            456

There may be multiple records in the table with the same DTDYear and DTDNum Combinations or there may only be a single record with that DTDYear and DTDNum combination.  Some records don’t have a DTDYear or DTDNum, we don’t want those at all.

The only field selected in the query will be 'RecID'

Select all the record ID's of the records with the same DTDYear and DTDNum, if all the records have a pay status of 1.

If there is only one record with the DTDYear and DTDNum combo and it is paystatus 1, it gets selected.

If there are multiple records with the same DTDYear and DTDNum and all of them have pay status of 1, they all get selected.  If they don’t all have pay status of 1, none of them get selected.

From the above data

Result Record ID’s
2      because there was only 1(11 345)  and it is paid
4      because there are 2, (12 456) both paid
5      because the was only 1 (16 78919)) and it is paid.
7      because there are 2, (12 456) both paid

I can write a select query that will select only paid records but I don't have any idea how to include the requirement that all occurrences of a given DTDYEar, DTDNum combination must be paid to select all of those record, otherwise none are selected.

I know how to do it by writing some logic but I was hoping it could be done in a query.

Is it possible?
LVL 1
mlcktmguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nitin SontakkeDeveloperCommented:
If they don’t all have pay status of 1, none of them get selected.

Sorry, but i didn't get why you are getting 2 selected, it has the pay status of 2.

You never mentioned (at least i don't see it anywhere) what does paid means. Please do not use terms interchangeably. Stick to one term only. If it is separate term, describe it in terms of data shown not just word 'paid' as we will not understand it.
0
Nitin SontakkeDeveloperCommented:
Try following. You can modify it to have 2 selected as well if you know the criteria.

declare @tblTaxRecs table
(
   [RecID] integer not null
  ,[Pay Status] integer not null
  ,[DTDYear] integer not null
  ,[DTDNum] integer not null
)

insert into @tblTaxRecs values
 (1, 1, 10, 123)
,(2, 2, 11, 345)
,(3, 3, 10, 123)
,(4, 1, 12, 456)
,(5, 1, 16, 78919)
,(6, 1, 10, 123)
,(7, 1, 12, 456)

select [RecID]
from @tblTaxRecs
where ltrim(str([DTDYear])) + '|' +  ltrim(str([DTDNum])) in 
  (
    select ltrim(str([DTDYear])) + '|' +  ltrim(str([DTDNum]))
    from 
      (
         select [DTDYear], [DTDNum], sum(case when [Pay Status] = 1 then 1 else 0 end) [PayStatus1], sum(case when [Pay Status] = 2 then 1 else 0 end) [PayStatus2], sum(case when [Pay 
         Status] = 3 then 1 else 0 end) [PayStatus3]
         from @tblTaxRecs
         group by [DTDYear], [DTDNum], [Pay Status]
      ) tr
    group by ltrim(str([DTDYear])) + '|' +  ltrim(str([DTDNum]))
    having sum([PayStatus1]) != 0 and sum([PayStatus2]) = 0 and sum([PayStatus3]) = 0
  )

Open in new window

0
SharathData EngineerCommented:
try this
select t1.RecID
  from tblTaxRecs t1
  join (select DTDYear,DTDNum
          from tblTaxRecs 
         group by DTDYear,DTDNum
        having min(PayStatus) = 1
           and max(PayStatus) = 1) t2
     on t1.DTDYear = t2.DTDYear
    and t1.DTDNum = t2.DTDNum
 order by t1.RecID

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
SELECT RecID
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY DTDYear, DTDNum ORDER BY PayStatus DESC) AS row_num
    FROM dbo.tblTaxRecs
) AS derived
WHERE row_num = 1 AND PayStatus = 1

/* Note that the "*" in the inner query doesn't hurt performance, because SQL will only retrieve column(s) that are actually used by the outer query, not all columns.
    Thus, it's more convenient to use "*" in case another column needs added to the outer query later. */
ORDER BY RecID
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Olaf DoschkeSoftware DeveloperCommented:
Group by DTDYear, DTDNum and take all those pairs having  min(pay_status)=1 and max(pay_status)=1.
Now you have that you can left join all recIDs with these DTDYear, DTDNum combinations.

declare @tblTaxRecs as table
(
   [RecID] integer not null
  ,[Pay Status] integer not null
  ,[DTDYear] integer not null
  ,[DTDNum] integer not null
)

insert into @tblTaxRecs values
 (1, 1, 10, 123)
,(2, 2, 11, 345)
,(3, 3, 10, 123)
,(4, 1, 12, 456)
,(5, 1, 16, 78919)
,(6, 1, 10, 123)
,(7, 1, 12, 456)

Select recid from 
(Select dtdyear,dtdnum from @tblTaxRecs group by dtdyear,dtdnum 
having min([Pay Status])=1 and max([Pay Status])=1) as groups
Left join @tblTaxRecs as recs 
on groups.DTDNum = recs.DTDNum and groups.DTDYear = recs.DTDYear 

Open in new window


Thanks, Nitin, for preparing the sample data. We have same results: 4,5, and 7.

@mlcktmguy: RecID 2 is not in the result per your requirement to only have Pay Status=1, The record with RecID 2 has a unique Pay Status, but that's 2.

If you want RecIds with any unique pay status you just change the having clause to min([Pay Status])=max([Pay Status])
 
Bye, Olaf.
0
Scott PletcherSenior DBACommented:
CORRECTION:

SELECT RecID
FROM (
    SELECT *, DENSE_RANK() OVER(PARTITION BY DTDYear, DTDNum ORDER BY PayStatus DESC) AS row_num
    FROM dbo.tblTaxRecs
) AS derived
WHERE row_num = 1 AND PayStatus = 1

Anything that requires a join will have to do more I/O.  If your data is small enough, it may not matter, but for larger data you will see a difference.
0
mlcktmguyAuthor Commented:
Thanks to all.  You are all correct that I shouldn't have included Rec 2 in the results.  
I evaluated all of the responses.  They all work and I learned something from all of them but on my production table with 24 million records, Scott's works in half the time of the next quickest.
0
Olaf DoschkeSoftware DeveloperCommented:
You should nevertheless mark the DENSE_RANK() solution, as ROW_NUMBER() is the wrong way to determine the uniquely Pay Status 1 groups. Also, see how it plays a role to sort DESC by PayStatus and aim for PayStatus=1. The DENSE_RANK() solution would become harder to maintain if you'd be interested in another PayStatus number than the lowest. Because it depends very much on the fact all PayStatus=1 rows get the dense_rank 1 if they are the only pay status per partition.

It doesn't matter in terms of awarded points, but I'd like you to confirm you do use the dense_rank solution.

Bye, Olaf.
0
Scott PletcherSenior DBACommented:
The DENSE_RANK() solution would become harder to maintain if you'd be interested in another PayStatus number than the lowest. Because it depends very much on the fact all PayStatus=1 rows get the dense_rank 1 if they are the only pay status per partition.

On the surface, yes.  But I think it should be easy enough to use a CASE statement to move, say, status 3 to the top:

SELECT RecID
FROM (
    SELECT *, DENSE_RANK() OVER(PARTITION BY DTDYear, DTDNum
        ORDER BY CASE WHEN PayStatus = 3 THEN 1 ELSE 2 END) AS row_num
    FROM dbo.tblTaxRecs
) AS derived
WHERE row_num = 1 AND PayStatus = 3
1
mlcktmguyAuthor Commented:
Thanks to both Olaf and Scott for the follow ups.  I'm trying to learn and you explanations really help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.