query with max dated records - 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

General SQL Server Forums

New to SQL Server Programming

query with max dated records

Author

Topic

dazz_789
Starting Member

2 Posts

Posted - 2012-05-29 : 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

52326 Posts

Posted - 2012-05-29 : 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 - 2012-05-30 : 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'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources