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.
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 paymentdateFROM dba.TF2FDETAI ainner join dba.tf1cpxref b on b.id_internal=a.id_internaland a.id_acct=b.id_acct and a.cd_type_acct=b.cd_type_acctand a.dt_pd_acct_begin=b.dt_pd_acct_begin and a.dt_pd_acct_end=b.dt_pd_acct_endwhere 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 paymentdateFROM dba.TF2FDETAI ainner join dba.tf1cpxref b on b.id_internal=a.id_internaland a.id_acct=b.id_acct and a.cd_type_acct=b.cd_type_acctand a.dt_pd_acct_begin=b.dt_pd_acct_begin and a.dt_pd_acct_end=b.dt_pd_acct_endwhere dateadd(d,datediff(d,0,a.id_fin_detail),0) < dateadd(d,datediff(d,0,'2008/1/1'),0and id_org=1393 |
|
|
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 paymentdateFROM dba.TF2FDETAI ainner join dba.tf1cpxref b on b.id_internal=a.id_internaland a.id_acct=b.id_acct and a.cd_type_acct=b.cd_type_acctand a.dt_pd_acct_begin=b.dt_pd_acct_begin and a.dt_pd_acct_end=b.dt_pd_acct_endwhere a.id_fin_detail < '2008-01-01' and a.cd_type_trans not in(116, 604, 614, 625, 850)and b.id_case<>0and 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>0Any thought on this? Thanks, |
|
|
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? |
|
|
|
|
|
|
|