Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need Help With Query Condition

Status
Not open for further replies.

lrdave36

Technical User
Joined
Jan 6, 2010
Messages
77
Location
US
Hey guys,

I am trying to write a simple query that will retrieve new employees that started receiving payments. The indicator in the table is that the new employee will have a payment date of 05-01-2010.


However if I write this query:

Code:
select * from dsnp.pr01_t_anty_pymt
where payment_dt = '2010-05-01'


It will return everyone because past employees also received payments on this date. I need to find some way to tell the query to only retrieve employees were '2010-05-01' is their first payment record in the table. I don't want to see people who have payment dates occuring before this date.

Can anyone help?
 
I also tried this statement, but it returned zero records:

Code:
select * from dsnp.pr01_t_anty_pymt
where anty_pymt_dt = '2010-05-01'
and not exists (select * from dsnp.pr01_t_anty_pymt where anty_pymt_dt < '2010-05-01')
 
Code:
select T.* from dsnp.pr01_t_anty_pymt
inner join (select PersonID, min(Payment_Dt) as MinDate from .. T
group by PersonId) X on T.PersonID = X.PersonID and T.Date = MinDate where T.payment_dt = '20100501'

PluralSight Learning Library
 
SQL Server 2005+ solution
Code:
;with cte as (select *, row_number() over (partition by PersonID order by Payment_Dt) as row from myTable)

select * from cte where Row = 1 and Payment_Dt = '20100501'

PluralSight Learning Library
 
Hey Markros,

Thanks for the help. I totally forgot about the min function.

Can you show me how to take this query and add another condition to being this voucher id code? Here is what I wrote

Code:
select * from dsnp.pr01_t_anty_pymt A
inner join (select a.recip_ssn_nbr, min(a.anty_Pymt_Dt) as MinDate 
from dsnp.pr01_t_anty_pymt A,
     dsnp.pr01_t_recip_sys B

where b.RIGHT (VOUCHER_ID_CD,1) = 'D'

group by a.recip_ssn_nbr) X 
on A.recip_ssn_nbr = X.recip_ssn_nbr and A.anty_pymt_dt = MinDate where A.anty_pymt_dt = '2010-05-01'


Unfortunately, SQL doesn't like my where b.RIGHT (VOUCHER_ID_CD,1) = 'D'. I don't know where to insert it into the query. I'm not that familiar with derived tables.


Also, is there any way I can insert this into a case statement? Something like this:

Code:
SELECT   A.AGTY_SYS_CD, A.BENEF_STAT_CD, 
         sum(case when A.RECIP_RETIR_DT <= '2010-04-01' then 1 end)   AS "TOTAL RETIREES", 
         sum(case When RIGHT (VOUCHER_ID_CD,1) = 'D' and min(B.anty_pymt_dt) = '2010-05-01' then 1 end)   AS "DISABILITY RETIREES" 
         
FROM     DSNP.PR01_T_RECIP_SYS A, 
         DSNP.PR01_T_ANTY_PYMT B 
WHERE    A.RECIP_SSN_NBR=B.RECIP_SSN_NBR 
AND      A.BENEF_STAT_CD IN ('AC', 'DP') 
AND      A.RECIP_TYPE_CD = '10' AND      A.BENEF_SEQ_NBR = 1 
GROUP BY A.AGTY_SYS_CD, A.BENEF_STAT_CD 
ORDER BY 3 DESC
 
There is a typo in your first query:
Code:
select * from dsnp.pr01_t_anty_pymt A
inner join (select a.recip_ssn_nbr, min(a.anty_Pymt_Dt) as MinDate 
from dsnp.pr01_t_anty_pymt A,
     dsnp.pr01_t_recip_sys B

--where b.RIGHT (VOUCHER_ID_CD,1) = 'D' - b is in the wrong place
WHERE b.VOUCHER_ID_CD LIKE 'D%' -- this is better way 

group by a.recip_ssn_nbr) X 
on A.recip_ssn_nbr = X.recip_ssn_nbr and A.anty_pymt_dt = MinDate where A.anty_pymt_dt = '2010-05-01'

I'm not sure I understood your second question, but yes, of course, you can use CASE statements.

PluralSight Learning Library
 
Hey Markros,

Thanks for the catch. I actually ended up going with this query. Seems to work just fine and runs fast!

Code:
select A.recip_ssn_nbr, min(A.anty_pymt_dt) as "TEST"
from dsnp.pr01_t_anty_pymt A,
     dsnp.pr01_t_recip_sys B
where A.recip_ssn_nbr = B.recip_ssn_nbr
and right(B.voucher_id_cd,1) = 'D'
group by A.recip_ssn_nbr
having min (A.anty_pymt_dt) = '2010-05-01'


 
There are couple of minor problems in this query.

1. Don't use old style JOINS as where - use INNER JOIN ON in this case - the where style join is deprecated

2. Assuming you may have an index on Voucher_ID_Cd field this
will perform much faster
B.Voucher_ID_Cd LIKE 'D%'
than your version.

You may want to take a look at this blog post

and in addition explore links from the bottom of that post.

PluralSight Learning Library
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top