question

steveA avatar image
steveA asked

Insert a field from table into sub query

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)
insertsub-query
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dave ballantyne avatar image
dave ballantyne answered

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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.