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.
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 prodwinSET 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] |
|
|
Sambhav
Starting Member
31 Posts |
Posted - 2007-07-09 : 12:23:35
|
In PRODWIN table there are 3 date cloumns MFGDT EXPIRYDTPRODRECDDTIn incidents table there are 2 coulmns AWAREDTINVCOMPLETEDDTNow 2004-04-06 suppose is the AWAREDT I want that MFGDT should be something like 2004-03-02same way 2004-04-06 suppose is the AWAREDT I want that EXPIRYDT should be something like 2004-06-24and 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 awaredtin all mfgdt,expirydt,prodrecddt and then I was thinking of updateing these dates like thisUPDATE prodwinSET expirydt =awaredt + DATEPART(hour, invcompleteddt)%5 + 1 but this is giving me error---"Invalid column name"How should I go about it ??? |
|
|
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. |
|
|
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]GOCREATE 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]GOsambhav jain |
|
|
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? |
|
|
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 workingselect 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 !!! |
|
|
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 ? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
|
|
|
|