Update

Last Post 28 Jan 2010 07:28 AM by SQLUSA. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
JASBRYDEN
New Member
New Member

--
27 Jan 2010 04:52 AM
Hi

I have two tables

Table 1

company, forecast year , forecast period, itemno , average cost

1, 2008, 4 , 4356dy, null
1 , 2009 , 5 , 6546rt, null
1, 2009, 12 , 3245ty, null
1, 2010, 1, 4587, null


Table 2

company, inventorydate, year, month, itemno, avgcost

1, 20080405, 2008, 4 , 4356dy, 52.26
1 , 20090516, 2009 , 5 , 6546rt, 125.23
1, 20091205, 2009, 12 , 3245ty, 86.02
1, 20100115, 2010, 1, 4587, 158.25


I need to join these two tables and update the average cost in table 1.........that is the easy part.

NOTE:
The average cost for Jan (period 1) MUST be the equal to average cost of Feb (period 2)....in other words I need to move everything one space down.

But the tricky part is the year.

How do I do this ???
SQLUSA
New Member
New Member

--
28 Jan 2010 07:28 AM
If you represent YYYYMM as YYYY-MM-01, it is very easy. Just use the dateadd function.

DECLARE @CurrentMonth datetime = '2012-12-01', @PrevMonth datetime

SET (or SELECT) @PrevMonth = dateadd (mm,-1, @CurrentMonth)

To get year: YEAR(@CurrentMonth)

To get month: MONTH(@CurrentMonth)

In SQL Server 2008, you can use DATE type (no time attached to it).

Kalman Toth, SQL Server 2008 Training
http://www.sqlusa.com
JASBRYDEN
New Member
New Member

--
01 Feb 2010 05:17 AM
update ASB_VSAFCDT
set ASB_VSAFCDT.averagecost=asb_averagecost.averagecost
from ASB_VSAFCDT
left join asb_averagecost on
forecast_year=[year] and
forecast_period=dateadd(month,1,[month])



This the code that I would like to use.

What do you think would this work??

ASB_VSAFCDT

[DeleteCode] char (1) NOT NULL,
[Company] char (2) NOT NULL,
[Forecast_Schedule] char (2) NOT NULL,
[Forecast_Type] char (1) NOT NULL,
[Forecast_Year] decimal (4,0) NOT NULL,
[Forecast_Period] decimal (2,0) NOT NULL,
[Location] char (4) NOT NULL,
[Division] varchar(4) NULL,
[InventoryClass] varchar (4) NOT NULL,
[SalesmanNo] varchar (5) NOT NULL,
[ItemNo] char (20) NOT NULL,
[NetForecastQuantity] decimal (11,2)NULL,
[averagecost] decimal (11,2)NULL,
[sellingPrice1] decimal (11,2)NULL,
[sellingPrice2] decimal (11,2)NULL,
[sellingPrice3] decimal (11,2)NULL,
[sellingPrice4] decimal (11,2)NULL,
[sellingPrice5] decimal (11,2)NULL,
[sellingPrice6] decimal (11,2)NULL,
[sellingPrice7] decimal (11,2)NULL,
[sellingPrice8] decimal (11,2)NULL,
[sellingPrice9] decimal (11,2)NULL,
[sellingPrice10] decimal (11,2)NULL



ASB_AVERAGECOST

[InvoiceDate] char (8) NOT NULL,
[Year] char(4) NOT NULL,
[Month] char (2) NOT NULL,
[ItemNo] char (20) NOT NULL,
[averagecost] decimal (11,2)NULL


Acceptable Use Policy
---