A query question - 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 2005 Forums

Transact-SQL (2005)

A query question

Author

Topic

ArviL
Starting Member

25 Posts

Posted - 2011-03-03 : 02:28:57

Hi

I have a table dbo.products, with structure:
prod_id init (PK),
prod_cl_date datetime,
prod_cl_vl_code varchar(3),
...
prod_eu_course_init numeric(30,20),
prod_eu_date_init datetime

I need to update fields prod_eu_course_init and prod_eu_date_init with according (latest at dbo.products.prod_cl_date) values (vl_eu_course and vl_date) from another table, dbo.valuta_log, with structure:
vl_row init (PK),
vl_date datetime,
vl_vl_code varchar(3),
vl_eu_course

, where the dbo.valuta_log.vl_vl_code = dbo.products.prod_cl_vl_code and dbo.valuta_log.vl_date <= dbo.products.prod_cl_date.

I can do this easily processing dbo.products row-wise, but is tehre a way to do this with a single SELECT clause?

Thanks in advance!
Arvi Laanemets

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-03 : 03:23:20

I don't understand your requirement completely but I guess that below update statement should give you some idea to start with:

update products
set prod_eu_course_init = vl_vl_code, prod_eu_date_init = vl_date
from products p inner join valuta_log v
on v.vl_vl_code = p.products.prod_cl_vl_code
and v.vl_date <= p.prod_cl_date

ArviL
Starting Member

25 Posts

Posted - 2011-03-03 : 03:59:25

quote:
Originally posted by pk_bohra

set prod_eu_course_init = vl_vl_code, prod_eu_date_init = vl_date
from products p inner join valuta_log v
on v.vl_vl_code = p.products.prod_cl_vl_code
and v.vl_date <= p.prod_cl_date

An example:
We have a product, with current price in USD estabilished at 01.02.2011
In valuta log, we have USD courses p.e.
12.03.2005 course050312
...
27.01.2011 course110127
28.01.2011 course110128
30.01.2011 course110130
02.02.2011 course110202
05.02.2011 course110205
...
(where course###### are really some numeric values)

The query must return for this product
prod_eu_course_init=course110130
and
prod_eu_date_init =30.01.2011

For another product, with current price in USD estabilished at 28.01.2011, the query must return
prod_eu_course_init=course110128
and
prod_eu_date_init =28.01.2011
etc.

ArviL
Starting Member

25 Posts

Posted - 2011-03-03 : 11:23:24

quote:
Originally posted by ArviL

Hi

I have a table dbo.products, with structure:
prod_id init (PK),
prod_cl_date datetime,
prod_cl_vl_code varchar(3),
...
prod_eu_course_init numeric(30,20),
prod_eu_date_init datetime

I need to update fields prod_eu_course_init and prod_eu_date_init with according (latest at dbo.products.prod_cl_date) values (vl_eu_course and vl_date) from another table, dbo.valuta_log, with structure:
vl_row init (PK),
vl_date datetime,
vl_vl_code varchar(3),
vl_eu_course

, where the dbo.valuta_log.vl_vl_code = dbo.products.prod_cl_vl_code and dbo.valuta_log.vl_date <= dbo.products.prod_cl_date.

I can do this easily processing dbo.products row-wise, but is tehre a way to do this with a single SELECT clause?

The solution (I'm not sure about typos there - I had to edit to make the query more basic):
---
;
WITH init_date AS (
SELECT
pr.prod_id,
pr.prod_cl_vl_code,
MAX(vl.vl_date) AS InitDate
FROM dbo.products pr
LEFT JOIN dbo.valuta_log vl ON pr.prod_cl_vl_code = vl.vl_vl_code AND vl.vll_date <= pr.prod_cl_date
GROUP BY
pr.prod_id,
pr.prod_cl_vl_code)
UPDATE dbo.products
SET
prod_eu_date = b.InitDate,
prod_eu_course = c.vl_eu_course
FROM dbo.products a
LEFT JOIN init_date b ON b.prod_id = a.prod_id
LEFT JOIN dbo.valuta_log c ON c.vl_vl_code = b.prod_cl_vl_kod AND c.vl_date=b.InitDate
---

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources