x

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)

more ▼

asked Nov 12, 2009 at 11:57 AM in Default

avatar image

steveA
21 2 2 4

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered Nov 12, 2009 at 12:07 PM

avatar image

Kev Riley ♦♦
63.8k 48 61 81

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Nov 12, 2009 at 12:07 PM

avatar image

dave ballantyne
928 1 3 6

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x136
x48

asked: Nov 12, 2009 at 11:57 AM

Seen: 1791 times

Last Updated: Nov 12, 2009 at 12:08 PM

Copyright 2016 Redgate Software. Privacy Policy