Getting latest 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

SQL Server 2000 Forums

Transact-SQL (2000)

Getting latest records

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-03-24 : 15:13:07

[code][/code]
The query below gives me all the records from the tbl_customer_exp_info table with the latest records for the cust_number
which meets the required criteria.Here I select all the customers with cust_status='Y'

SELECT
ld.ref_id,ddbi.exp_occurences,
ISNULL(ddbi.exp_desc,''),ddbi.amount,
'dares_refresh',ddbi.create_date,
'dares_refresh',getdate(),
'N' as update_flag
FROM
tbl_customer_exp_info ddbi
INNER JOIN cust.dbo.tbl_cust_detail ld ON
ld.cust_number=ddbi.cust_number AND
ld.cust_status='Y' AND
ddbi.used_flag='N' AND
ddbi.cust_indicator IN('A','C') AND
NOT EXISTS( SELECT exp_no FROM cust.dbo.tbl_customer_exp WHERE exp_no=ddbi.exp_occurences AND ref_id=ld.ref_id)
INNER JOIN
(
SELECT MAX(create_date) AS create_date,cust_number,exp_occurences
FROM tbl_customer_exp_info
WHERE used_flag='N' AND cust_indicator IN('A','C')
GROUP BY cust_number,exp_occurences
) mxc ON
mxc.cust_number=ddbi.cust_number AND
mxc.create_date=ddbi.create_date AND
mxc.exp_occurences=ddbi.exp_occurences
ORDER BY ddbi.create_date ASC

Now I have to deal with one cust_number and its getting confused.
In the stored procedure I have 2 paramerters :
@ref_id INT
@cust_number varchar
and the cust_number and ref_id I get are with cust_status='Y'
Now I need to avoid the
INNER JOIN cust.dbo.tbl_cust_detail ld ON
ld.cust_number=ddbi.cust_number AND
ld.cust_status='Y' AND
from the above query and pass the cust_number and ref_id where required and get the latest record

Please help me with that

jhermiz

3564 Posts

Posted - 2006-03-24 : 16:46:23

You dont state why you need to avoid it nor did you provide sample out put data (Expected results, expected data, incoming data, etc). If its because of the inner join change it to a left join?

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-03-24 : 17:44:52

Because the above code was from all the cust_numbers and now its going to be specific cust_number as it comes through the parameter and I need to avoid unnecessary codes as the cust_number that comes through the parameter already will be satisfy the condition cust_status='Y' so I don't need to inner join with cust.dbo.tbl_cust_detail ld table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources