| Author |
 Topic  |
|
|
dazz_789
Starting Member
2 Posts |
 Posted - 05/29/2012 : 20:21:27
|
Hi,
I have data as follows:
owning_acct_no SERV_NO PRODUCT_CODE INST_DATE PROD_STATUS
960410282500 4450062 322 11/17/2001 Z
960410282500 4450062 322 12/07/2011 W
960410282500 4450062 335 10/12/2003 X
960410282500 4450062 335 09/06/2011 A
960410282500 4450065 332 05/11/2010 W
960410282500 4450065 332 01/22/2012 X
And I want to get the max effected date as follows:
owning_acct_no SERV_NO PRODUCT_CODE INST_DATE PROD_STATUS
960410282500 4450062 322 12/07/2011 W
960410282500 4450062 335 09/06/2011 A
960410282500 4450065 332 01/22/2012 X
The following sql just gives me the 3 lines above without the PROD_STATUS:
select cp.owning_acct_no,cp.serv_no, cp.product_code ,MAX(cp.inst_date) as dt
from cis_prod.cst_customer_product cp
where cp.owning_acct_no ='960410282500'
and cp.product_code in (322, 335)
group by cp.owning_acct_no,cp.serv_no,cp.product_code
What's the best way to bring in the PROD_STATUS to the results?
Thanks!
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 05/29/2012 : 21:15:46
|
add it as join to main table
SELECT t.owning_acct_no,
t.SERV_NO,
t.PRODUCT_CODE,
t.INST_DATE,
t.PROD_STATUS
FROM cis_prod.cst_customer_product t
INNER JOIN
(
select cp.owning_acct_no,cp.serv_no, cp.product_code ,MAX(cp.inst_date) as dt
from cis_prod.cst_customer_product cp
where cp.owning_acct_no ='960410282500'
and cp.product_code in (322, 335)
group by cp.owning_acct_no,cp.serv_no,cp.product_code
)t1
ON t1.owning_acct_no = t.owning_acct_no
AND t1.SERV_NO = t.SERV_NO
AND t1.PRODUCT_CODE = t.PRODUCT_CODE
AND t1.dt = t.INST_DATE
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
dazz_789
Starting Member
2 Posts |
Posted - 05/30/2012 : 00:45:28
|
Hi, this works great, thanks! I was actually trying to apply this to the code below because the cst_customer_product table contains multiple rows for each owning_acct_no, serv_no, product_code combination, but I only want it to select the rows with the max instillation date
e.g.
owning_acct_no, serv_no, product_code inst_date cust_prod_status
105036590000 4440270 8641 4/9/2009 W
105036590000 4440270 8641 11/21/2000 Z
So I would only want the first row to be extracted in my query results below. Cay you say how I can update the query below.. it returns errors when I try it..
Thanks again!
select cp.product_code, sp.product_description, cp.unique_serv_id, cp.serv_no,
cp.owning_acct_no, cp.personal_rental, cp.cust_prod_no,
cp.inst_date, cp.price_var_exist, cp.cust_prod_status,
nm.first_name, nm.surname, nm.company_name,
fpr.fpr_bill_year, fpr.fpr_bill_month, fpr.fpr_rental_charge
from cis_prod.cst_customer_product as cp
left outer
join cis_prod.cst_name as nm
on cp.owning_acct_no = nm.acct_no
left outer
join cis_prod.sys_product as sp
on cp.product_code = sp.product_code
left outer
join (select * from cis_prod.cst_full_period_rentals fp
where fp.fpr_bill_year = '2012' and fp.fpr_bill_month = '02' ) fpr
on cp.owning_acct_no = fpr.fpr_owning_acct_no
and cp.cust_prod_no = fpr.fpr_cust_prod_no and
cp.product_code = fpr.fpr_product_code
where (cp.product_code between 8640 and 8649 or cp.product_code between 8660 and 8669 ) and
nm.stop_time_stamp = '31-DEC-9999'
|
|
|
| |
Topic |
|
|
|
query with max dated records
