SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Date update based on another table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sambhav
Starting Member

USA
31 Posts

Posted - 07/09/2007 :  11:39:31  Show Profile  Send Sambhav a Yahoo! Message  Reply with Quote


I have to update dates column based on other table

1)MFGDt date which is a column in PRODWIN table should be before awaredt(say between 1 to 3 months)
which is in table incidents

2)Expirydt date from prodwin table should be after awaredt (incidents table)(say between 2 or 3 months)


UPDATE prodwin
SET expirydt =awaredt + DATEPART(hour, invcompleteddt)%5 + 1

It is giving me this error because not in the same table "Invalid column name"



3)prodrecddt should be after AWAREDT and before Invcomleteddt (incidents table)

khtan
In (Som, Ni, Yak)

Singapore
17645 Posts

Posted - 07/09/2007 :  11:44:10  Show Profile  Reply with Quote
Can you post your table structure, sample data and expected result ?


KH
Time is always against us

Go to Top of Page

Sambhav
Starting Member

USA
31 Posts

Posted - 07/09/2007 :  12:23:35  Show Profile  Send Sambhav a Yahoo! Message  Reply with Quote
In PRODWIN table there are 3 date cloumns
MFGDT
EXPIRYDT
PRODRECDDT
In incidents table there are 2 coulmns
AWAREDT
INVCOMPLETEDDT

Now 2004-04-06 suppose is the AWAREDT I want that MFGDT should be something like 2004-03-02
same way 2004-04-06 suppose is the AWAREDT I want that EXPIRYDT should be something like 2004-06-24
and PRODRECDDT should lie now between 2004-04-06(AWAREDT) and 2004-04-27(INVCOMPLETEDDT)something like 2004-04-20.

What right now I have done is since I am populating the data in the table initially i put awaredt
in all mfgdt,expirydt,prodrecddt and then I was thinking of updateing these dates like this

UPDATE prodwin
SET expirydt =awaredt + DATEPART(hour, invcompleteddt)%5 + 1


but this is giving me error---"Invalid column name"

How should I go about it ???
Go to Top of Page

KenW
Constraint Violating Yak Guru

USA
391 Posts

Posted - 07/09/2007 :  15:07:15  Show Profile  Reply with Quote
Sambhav,

khtan asked you to post the table structure, sample data, and expected results. That's for a reason.

You'll need to JOIN prodwin to the other two tables in order to do what you want. I can't help you with that, however, because you didn't provide enough information to do so, even after being asked.

Please provide the table structure (in DDL - CREATE TABLE() - format), sample data (in the form of INSERT..UNION ALL statements) and expected results. I'm sure someone can help you easily.
Go to Top of Page

Sambhav
Starting Member

USA
31 Posts

Posted - 07/09/2007 :  16:29:46  Show Profile  Send Sambhav a Yahoo! Message  Reply with Quote
CREATE TABLE [PRODWIN] (
[INCIDENTNO] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SEQNO] [int] NULL ,
[PRODUCTID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CATALOGNO] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PRODUCT] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PRODUCTFAMILY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MFGSITE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MFGDT] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOTNO] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SERIALNO] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EXPIRYDT] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QUANTITYINVOLVED] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PRODRETQ] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PRODRECDDT] [datetime] NULL ,
[PRODRECDDTFLAG] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QUANTITYRECD] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RECDCONDITION] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CURRENTLOCATION] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FINALDISPOSITION] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [INCIDENTS] (
[INCIDENTNO] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INITDT] [datetime] NULL ,
[AWAREDT] [datetime] NULL ,
[AWAREDTFLAG] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INCIDENTTYPE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INITIATOR] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INITDEPT] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FACILITY] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[REPORTINGPERSON] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RPSOURCE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RPPHONE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RPEMAIL] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RPTITLE] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INCIDENTRECDVIA] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RMANO] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SERVICENO] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INCIDENTDT] [datetime] NULL ,
[INCIDENTDTFLAG] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PATIENTINVOLVEDQ] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WHENINCDDETECTED] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USEDASLABELEDQ] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OPERATOROFDEVICE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CONCOMITANTDEVICE] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INCIDENTOUTCOME] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INITCOMPLETEDBY] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INITCOMPLETEDDT] [datetime] NULL ,
[COMPLAINTQ] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IFNOTCOMPLAINT] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ACKBY] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ACKTITLE] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ACKDT] [datetime] NULL ,
[INVREQUIREDQ] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IFNOINVREQUIRED] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INVDECISIONBY] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INVDECISIONDT] [datetime] NULL ,
[INVASSIGNEDTO] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INVASSIGNEDDT] [datetime] NULL ,
[MFGREVWQ] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MFGREVWRESULTS] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MFGREVWCOMMENTS] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MFGREVWBY] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MFGREVWDT] [datetime] NULL ,
[FAILURECAUSE] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INVCOMPLETEDBY] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INVCOMPLETEDDT] [datetime] NULL ,
[INVREVIEWEDBY] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INVREVIEWEDDT] [datetime] NULL ,
[FINALRESPBY] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FINALRESPTITLE] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FINALRESPDT] [datetime] NULL ,
[CAPAASSESSMENT] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IFNOCAPA] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CAPADECISIONBY] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CAPADECISIONDT] [datetime] NULL ,
[CAPANO] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CONCLUSION] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INCIDENTVOID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLOSEDBY] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLOSEDDT] [datetime] NULL ,
[INCIDENTSTATUS] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[REGULATORYSTATUS] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO





sambhav jain
Go to Top of Page

KenW
Constraint Violating Yak Guru

USA
391 Posts

Posted - 07/10/2007 :  14:54:54  Show Profile  Reply with Quote
Sambhav,

Ok. You've been asked two times to post three pieces of information (Schema, sample data, desired output) and you've managed to post one piece (schema).

Do we have to ask twice more for each additional information item before you'll provide it? Ok.

Please post your sample data (in the form of INSERT statements) and desired output.
Please post your sample data (in the form of INSERT statements) and desired output.
Please post your sample data (in the form of INSERT statements) and desired output.
Please post your sample data (in the form of INSERT statements) and desired output.

That's two times for sample data, and two times for desired output.

Try again?

Edited by - KenW on 07/10/2007 14:55:26
Go to Top of Page

Sambhav
Starting Member

USA
31 Posts

Posted - 07/10/2007 :  15:50:40  Show Profile  Send Sambhav a Yahoo! Message  Reply with Quote
INSERT INTO prodwin(incidentno,mfgdt,expirydt and prodrecddt)
VALUES (incidentno (incidentno),(awaredt),(awaredt),(awaredt)) from incidents (Table)

Desired output--> incidentno in prodwin[table] should be exactly same as incidentno in incidents[table]
and mfgdt,expirydt,prodrecddt should be same as awaredt .[but awaredt should be corrosponding to the incidentno.]

and after that I have to

Update mfgdt such that (mfgdt < awaredt)
Update expirydt such that (expirydt > awaredt)
Update prodrecddt such that(awaredt< prodrecddt < invcompleteddt)

what I tried was this but it is not working

select i.incidentno as incidentno , i.INCIDENTDT as mfgdt,i.AWAREDT as expirydt ,
i.AWAREDT as prodrecddt from dbo.INCIDENTS i join prodwin p on i.INCIDENTNO = p.INCIDENTNO


and I am really sorry ..I am new to all this so thats why even now if u dosent understand i doono wht to do then !!!
Go to Top of Page

Sambhav
Starting Member

USA
31 Posts

Posted - 07/12/2007 :  16:46:41  Show Profile  Send Sambhav a Yahoo! Message  Reply with Quote
Hi Ken ,
I have the sample data but when i am pasting it here its not in the right format...I mean its ot in the table format so wht shoul I do ? there is no way i can send a txt file or wht ?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17645 Posts

Posted - 07/12/2007 :  18:26:13  Show Profile  Reply with Quote
refer to this thread http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000