Update Statement

Last Post 06 Nov 2008 11:02 AM by JASBRYDEN. 4 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
JASBRYDEN
New Member
New Member

--
04 Nov 2008 06:00 AM
Hi

I am trying to creating a SQL statement that would update the table BtStandardLoadHeader when the ImportStatus ='U'

I have a table called corporate_calendar, this table has would tell which day is a working day and which is a holiday.

e.g. of corpoarate_calendar

Dateid date holiday workday specialdate BeltekDate week

3 2008-01-03 00:00:00.000 0 1 5 6 1
4 2008-01-04 00:00:00.000 0 1 6 2 1
5 2008-01-05 00:00:00.000 1 0 7 2 1
6 2008-01-06 00:00:00.000 1 0 1 2 1
7 2008-01-07 00:00:00.000 0 1 2 3 1
8 2008-01-08 00:00:00.000 0 1 3 4 1



What I want to get is a code that would check the corpoarate_calendar table and check to see if it is a workday....if it is then check the BtStandardLoadHeader table see if the importstatus is U, once those two is true then change the date on the BtStandardLoadHeader.

If it is not a workday and importstatus is U then leave it, or it could change it to, does not matter would give the same result.

This is what I have thus far.

update dbo.To_BTStandardLoadHeader
set To_BTStandardLoadHeader.[Day]=BeltekDate
from dbo.To_BTStandardLoadHeader
left join dbo.Corporate_Calendar on
dbo.Corporate_Calendar.week=dbo.To_BTStandardLoadHeader.week and dbo.Corporate_Calendar.date=CONVERT(VARCHAR(10), GETDATE(),120)
and dbo.To_BTStandardLoadHeader.ImportStatus='U'



Please help to improve it.
THanks
JASBRYDEN
New Member
New Member

--
06 Nov 2008 05:24 AM
e.g. of the BTStandardLoadHeader


StandardNo RouteNo Warehouseno Week Day ImportStatus

867 511 511 1 4 P
900 511 511 1 4 P
912 503 503 1 4 P




I am using getgate() to call todays date.

The BTStandardLoadHeader does not have a date field.
JASBRYDEN
New Member
New Member

--
06 Nov 2008 11:02 AM
Looks good but getting this error


Msg 207, Level 16, State 1, Line 8
Invalid column name 'ImportStatus'.
JASBRYDEN
New Member
New Member

--
06 Nov 2008 11:05 AM
remember the importstatus is on the BTStandardLoadheader

The corpoarate_calendar does NOT have an importstatus
JASBRYDEN
New Member
New Member

--
07 Nov 2008 09:55 AM
It is good to go....i had to make a few changes to it.....thanks alot !!!!!!


Acceptable Use Policy
---