query question - 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 2005 Forums

Transact-SQL (2005)

query question

Author

Topic

madtown_nihs
Starting Member

7 Posts

Posted - 2009-01-21 : 10:40:12

Hi All,
I need to add also refine the query by selecting records where the max(trans_date) of that particular m.mid is < getdate() - 90.

So basically the trans_date of the m.mid and the greatest trans_date of that m.mid has to be older than 90 days. Any help would be great. Thanks

Ludwig

select
a.owner_first
, a.owner_last
--, coalesce(a.fed_taxid, a.signor_ssn)
, coalesce(a.fed_taxid, a.signor_ssn)
, a.phone1
, null -- pob#
, a.phone2 -- alternate#
, a.location_add1
, a.location_add2
, a.location_city
, a.location_state
, a.location_zip
, 'Pipeline Data'
, Rs.[CMS_Amount] - isnull(RA.Amount,0) -- original balance
, getdate() --charge-off date
, a.created_date --open date
, isnull(max(dateplaced),'')--last payment date
, m.mid --acct# (mid)
, m.DBA
, CMS_ADJ_DESC + '-'
+ REJECT_CODE + '-'
+ 'CANCELLED' + '-'
+ CAST(R.CMS_AMOUNT AS VARCHAR(10)) + '-'
+ CONVERT(VARCHAR(10),TRANS_DATE,111) + '-'
+ CONVERT(VARCHAR(10),DATE_POSTED,111)
-- , RS.[CMS_Amount] - isnull(RA.Amount,0)
-- --activity-reject code-reason/status-amount-trans date-posted date--'

from tblrecovery r
inner join middba m
on r.intmid = m.intmid
inner join tblpimsfdslookup p
on m.intmid = p.intmid and upgraded is null
inner join ach_merdemo a
on p.efs_network_id = a.efs_network_id
left outer join tblachrecoveryamounts ar
on m.intmid = ar.intmid and ar.status in ('A','S')
INNER JOIN
( SELECT SUM(isnull(CMS_AMOUNT,0)) AS CMS_AMOUNT
, IntMid
FROM Pims2.dbo.tblRecovery R with (NOLOCK)
WHERE Waived = 0
GROUP BY IntMid
) AS Rs
ON r.IntMid = Rs.IntMid
LEFT OUTER JOIN
(
SELECT sum(isnull(Amount,0)) as amount
, intmid
FROM Pims2.dbo.tblACHRecoveryAmounts RA with (NOLOCK)
WHERE RA.Status IN ('A','P','M','S')
Group by intmid
) AS RA
ON r.IntMid = RA.IntMid
WHERE a.account_status = 'CANCELLED'
and r.trans_date < getdate() - 90
and waived = 0

madtown_nihs
Starting Member

7 Posts

Posted - 2009-01-21 : 10:44:13

To clarify. the Tr table has a bunch of transactions with trans_date. I want all transactions that are 90 days or older and the max(trans_date) that has the same mid has to be 90 and older as well.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 11:12:38

[code]
select *
from
(
select
a.owner_first
, a.owner_last
--, coalesce(a.fed_taxid, a.signor_ssn)
, coalesce(a.fed_taxid, a.signor_ssn)
, a.phone1
, null -- pob#
, a.phone2 -- alternate#
, a.location_add1
, a.location_add2
, a.location_city
, a.location_state
, a.location_zip
, 'Pipeline Data'
, Rs.[CMS_Amount] - isnull(RA.Amount,0) -- original balance
, getdate() --charge-off date
, a.created_date --open date
, isnull(max(dateplaced),'')--last payment date
, m.mid --acct# (mid)
, m.DBA
, CMS_ADJ_DESC + '-'
+ REJECT_CODE + '-'
+ 'CANCELLED' + '-'
+ CAST(R.CMS_AMOUNT AS VARCHAR(10)) + '-'
+ CONVERT(VARCHAR(10),TRANS_DATE,111) + '-'
+ CONVERT(VARCHAR(10),DATE_POSTED,111),
max(r.trans_date) over (partition by m.mid) as maxtrandate
-- , RS.[CMS_Amount] - isnull(RA.Amount,0)
-- --activity-reject code-reason/status-amount-trans date-posted date--'

from tblrecovery r
inner join middba m
on r.intmid = m.intmid
inner join tblpimsfdslookup p
on m.intmid = p.intmid and upgraded is null
inner join ach_merdemo a
on p.efs_network_id = a.efs_network_id
left outer join tblachrecoveryamounts ar
on m.intmid = ar.intmid and ar.status in ('A','S')
INNER JOIN
( SELECT SUM(isnull(CMS_AMOUNT,0)) AS CMS_AMOUNT
, IntMid
FROM Pims2.dbo.tblRecovery R with (NOLOCK)
WHERE Waived = 0
GROUP BY IntMid
) AS Rs
ON r.IntMid = Rs.IntMid
LEFT OUTER JOIN
(
SELECT sum(isnull(Amount,0)) as amount
, intmid
FROM Pims2.dbo.tblACHRecoveryAmounts RA with (NOLOCK)
WHERE RA.Status IN ('A','P','M','S')
Group by intmid
) AS RA
ON r.IntMid = RA.IntMid
WHERE a.account_status = 'CANCELLED'
and waived = 0
)t
WHERE maxtrandate<dateadd(dd,datediff(dd,0,getdate())-90,0)
[/code]

madtown_nihs
Starting Member

7 Posts

Posted - 2009-01-21 : 11:46:54

what if we are on sql 2003? thanks

madtown_nihs
Starting Member

7 Posts

Posted - 2009-01-21 : 11:50:22

actualy 2000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 12:00:51

quote:
Originally posted by madtown_nihs

actualy 2000

then why have you posted on 2005 forum?


select 
a.owner_first
, a.owner_last
--, coalesce(a.fed_taxid, a.signor_ssn)
, coalesce(a.fed_taxid, a.signor_ssn)
, a.phone1
, null -- pob#
, a.phone2 -- alternate#
, a.location_add1
, a.location_add2
, a.location_city
, a.location_state
, a.location_zip
, 'Pipeline Data'
, Rs.[CMS_Amount] - isnull(RA.Amount,0) -- original balance	
, getdate() --charge-off date 
, a.created_date --open date	
, isnull(max(dateplaced),'')--last payment date 
, m.mid --acct# (mid)	
, m.DBA 
, CMS_ADJ_DESC + '-' 
+ REJECT_CODE + '-'
+ 'CANCELLED' + '-'
+ CAST(R.CMS_AMOUNT AS VARCHAR(10)) + '-'
+ CONVERT(VARCHAR(10),TRANS_DATE,111) + '-'
+ CONVERT(VARCHAR(10),DATE_POSTED,111)
-- , RS.[CMS_Amount] - isnull(RA.Amount,0)
-- --activity-reject code-reason/status-amount-trans date-posted date--'

from tblrecovery r
inner join middba m
on r.intmid = m.intmid
inner join tblpimsfdslookup p
on m.intmid = p.intmid and upgraded is null
inner join ach_merdemo a
on p.efs_network_id = a.efs_network_id
left outer join tblachrecoveryamounts ar
on m.intmid = ar.intmid and ar.status in ('A','S')
INNER JOIN 
( SELECT SUM(isnull(CMS_AMOUNT,0)) AS CMS_AMOUNT
, IntMid
FROM Pims2.dbo.tblRecovery R with (NOLOCK) 
WHERE Waived = 0
GROUP BY IntMid
) AS Rs
ON r.IntMid = Rs.IntMid
LEFT OUTER JOIN
(
SELECT sum(isnull(Amount,0)) as amount 
, intmid
FROM	Pims2.dbo.tblACHRecoveryAmounts RA with (NOLOCK) 
WHERE RA.Status IN ('A','P','M','S')
Group by intmid
) AS RA
ON r.IntMid = RA.IntMid
INNER JOIN (SELECT m1.mid,MAX(r1.trans_date) as latest
            FROM tblrecovery r1
            inner join middba m1
            on r1.intmid = m1.intmid
            group by m1.mid)tmp
on tmp.mid=m.mid
WHERE a.account_status = 'CANCELLED'
and tmp. latest < getdate() - 90
and waived = 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources