Insert a field from table into sub query

Question

I need to update zz_EBM_CostDevelopment.GRN_Period with a Value from a table called Period. The value depends on the Date of the transaction in zz_EBM_CostDevelopment

The script below correctly determines the Value from the period table using a fixed date. What I need to know is how to replace the date '2007-03-25' with the Date per row in zz_EBM_CostDevelopment when updating

UPDATE  zz_EBM_CostDevelopment
SET GRN_Period = 	
       (SELECT TOP (1) CAST(MONTH(PerDate) AS varchar(2)) + '/' 
                       + CAST(YEAR(PerDate) AS varchar(4)) 
        FROM Period 
            WHERE (PerDate >= CONVERT(DATETIME, '2007-03-25 00:00:00',102)) 
        ORDER BY Per)

Answer 1

0

UPDATE tablealias
SET GRN_Period = (SELECT TOP (1) CAST(MONTH(PerDate) AS varchar(2)) 
                   + '/' + 
                  CAST(YEAR(PerDate) AS varchar(4)) 
                 FROM Period WHERE (PerDate >= tablealias.Date) ORDER BY Per)
from zz_EBM_CostDevelopment tablealias

answered Nov 12 '09 at 12:07 PM

Kev Riley ♦♦
46k ● 38 ● 43 ● 69

(comments are locked)

Answer 2

0

I think this should work, if not please post DDL

UPDATE zz_EBM_CostDevelopment
   SET GRN_Period = Period.Per
 from zz_EBM_CostDevelopment
  cross apply 
      (SELECT TOP (1) CAST(MONTH(PerDate) AS varchar(2)) + '/' + CAST(YEAR(PerDate) AS varchar(4)) as Per
         FROM Period 
         WHERE (PerDate >= CONVERT(DATETIME, zz_EBM_CostDevelopment.Date, 102)) ORDER BY Per) as Period

answered Nov 12 '09 at 12:07 PM

dave ballantyne
918 ● 1 ● 1 ● 4

(comments are locked)

Insert a field from table into sub query

Follow this question

Related Questions