Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Date update based on another table

Author  Topic 

Sambhav
Starting Member

31 Posts

Posted - 2007-07-09 : 11:39:31


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)

17689 Posts

Posted - 2007-07-09 : 11:44:10
Can you post your table structure, sample data and expected result ?


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

Sambhav
Starting Member

31 Posts

Posted - 2007-07-09 : 12:23:35
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

391 Posts

Posted - 2007-07-09 : 15:07:15
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

31 Posts

Posted - 2007-07-09 : 16:29:46
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

391 Posts

Posted - 2007-07-10 : 14:54:54
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?
Go to Top of Page

Sambhav
Starting Member

31 Posts

Posted - 2007-07-10 : 15:50:40
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

31 Posts

Posted - 2007-07-12 : 16:46:41
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)

17689 Posts

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


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page
   

- Advertisement -