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 2000 Forums
 Transact-SQL (2000)
 SQL question

Author  Topic 

ntn104
Posting Yak Master

175 Posts

Posted - 2009-02-24 : 07:25:48
Hello,

I have a query to run a report that indicated all payments that collected from collection agency during calendar year 2008. It worked good. However, I want to modify it to indicate prior payments were collected before calendar year 2008 (id_fin_detail<'2008-01-01'), but still within that agency (id_org=1393). Please see my below query and help me to modify to make it work. Thank you!
Note: This is query that ran report for all payments collected within calendar year 2008.

SELECT distinct b.id_case,a.am_tot_coll_effect as payment, a.ID_FIN_DETAIL as paymentdate
FROM dba.TF2FDETAI a
inner join dba.tf1cpxref b on b.id_internal=a.id_internal
and a.id_acct=b.id_acct
and a.cd_type_acct=b.cd_type_acct
and a.dt_pd_acct_begin=b.dt_pd_acct_begin
and a.dt_pd_acct_end=b.dt_pd_acct_end
where a.id_fin_detail between '2008-01-01' and '2008-12-31-23.59'
and a.cd_type_trans not in(116, 604, 614, 625, 850)
and b.id_case<>0

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-24 : 07:52:16
SELECT distinct b.id_case,a.am_tot_coll_effect as payment, a.ID_FIN_DETAIL as paymentdate
FROM dba.TF2FDETAI a
inner join dba.tf1cpxref b on b.id_internal=a.id_internal
and a.id_acct=b.id_acct
and a.cd_type_acct=b.cd_type_acct
and a.dt_pd_acct_begin=b.dt_pd_acct_begin
and a.dt_pd_acct_end=b.dt_pd_acct_end
where dateadd(d,datediff(d,0,a.id_fin_detail),0) < dateadd(d,datediff(d,0,'2008/1/1'),0
and id_org=1393
Go to Top of Page

ntn104
Posting Yak Master

175 Posts

Posted - 2009-02-24 : 08:04:45
Problem: I tried to get all payments from those cases that are currently referred to id_org=1393, but payment date <'2008-01-01'. I tried below query, I got the cases that are currently within agency =1393, but the payment was from previous assigned agency. How do we solve this? For example:

Case=1234 was assigned to agency=1 in 1/1/2007 and closed from this agency in 11/10/2007. And the same case=1234 was referred to agency=1393 in 12/1/07, so I want to get payment from 12/1/07 to 12/31/07....

SELECT distinct b.id_case,a.am_tot_coll_effect as payment, a.ID_FIN_DETAIL as paymentdate
FROM dba.TF2FDETAI a
inner join dba.tf1cpxref b on b.id_internal=a.id_internal
and a.id_acct=b.id_acct
and a.cd_type_acct=b.cd_type_acct
and a.dt_pd_acct_begin=b.dt_pd_acct_begin
and a.dt_pd_acct_end=b.dt_pd_acct_end
where a.id_fin_detail < '2008-01-01'
and a.cd_type_trans not in(116, 604, 614, 625, 850)
and b.id_case<>0
and b.id_case in (Select distinct a.id_case
from dba.tf1casdeh
where a.id_org='1393'
group by id_case
having min(dt_action) between '2008-01-01' and '2008-12-31')
and a.am_tot_coll_effect>0

Any thought on this? Thanks,

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-24 : 12:36:07
can you please explain with some sample data on what you want?
Go to Top of Page
   

- Advertisement -