Compare two tables and fill the validfrom and c
-
hi,
I would like to fill the fromdate and Todate dates of the UniqueConsumption2 table
fromdate = Postingdate column value of table UniqueConsumption2
Todate = Next availaible postingdate column value of table UniqueConsumption2last todate of the material will be the next available postingdate from the second table Productionorder. Any help is appreciated
/****** Object: Table [dbo].[UniqueConsumption2] Script Date: 01/22/2018 14:17:40 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOSET ANSI_PADDING ON
GOCREATE TABLE [dbo].[UniqueConsumption2](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SourceNo] [varchar](50) NULL,
[OrderNO] [varchar](50) NULL,
[Quantity] [decimal](18, 4) NULL,
[PostingDate] [datetime] NULL,
[ItemNo] [varchar](50) NULL,
[FromDate] [datetime] NULL,
[ToDate] [datetime] NULL
) ON [PRIMARY]GO
SET ANSI_PADDING OFF
GOCREATE TABLE [dbo].[ProductionOrder](
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[PostingDate] [datetime] NOT NULL,
[MaterialNumber] [nvarchar](256) NULL
) ON [PRIMARY]GO
-- Insert queries------------
INSERT INTO [dbo].[UniqueConsumption2]
([SourceNo]
,[OrderNO]
,[Quantity]
,[PostingDate]
,[ItemNo]
,[FromDate]
,[ToDate])
VALUES
('WSHK-075S'
,'131787'
,'2.0000'
,'2004-01-19 00:00:00.000'
,'HAST-075')
INSERT INTO [SmartSearchNewQA].[dbo].[UniqueConsumption2]
([SourceNo]
,[OrderNO]
,[Quantity]
,[PostingDate]
,[ItemNo]
,[FromDate]
,[ToDate])
VALUES
('WSHK-075S'
,'131270'
,'2.0000'
,'2003-06-30 00:00:00.000'
,'HAST-075')INSERT INTO [SmartSearchNewQA].[dbo].[UniqueConsumption2]
([SourceNo]
,[OrderNO]
,[Quantity]
,[PostingDate]
,[ItemNo]
,[FromDate]
,[ToDate])
VALUES
('WSHK-075S'
,'130705'
,'2.0000'
,'2003-05-29 00:00:00.000'
,'HAST-075')
INSERT INTO [SmartSearchNewQA].[dbo].[UniqueConsumption2]
([SourceNo]
,[OrderNO]
,[Quantity]
,[PostingDate]
,[ItemNo]
,[FromDate]
,[ToDate])
VALUES
('WSHK-075R'
,'130382'
,'2.0000'
,'2005-06-05 00:00:00.000'
,'HAR-075')
INSERT INTO [SmartSearchNewQA].[dbo].[UniqueConsumption2]
([SourceNo]
,[OrderNO]
,[Quantity]
,[PostingDate]
,[ItemNo]
,[FromDate]
,[ToDate])
VALUES
('WSHK-075R'
,'130381'
,'2.0000'
,'2003-06-05 00:00:00.000'
,'HAR-075')INSERT INTO [SmartSearchNewQA].[dbo].[ProductionOrder]
([PostingDate],
[MaterialNumber])
values
('WSHK-075S', '2011-05-29 00:00:00.000')
INSERT INTO [SmartSearchNewQA].[dbo].[ProductionOrder]
([PostingDate],
[MaterialNumber])
values
('WSHK-075S', '2003-07-29 00:00:00.000')INSERT INTO [SmartSearchNewQA].[dbo].[ProductionOrder]
([PostingDate],
[MaterialNumber])
values
('WSHK-075S', '2003-08-29 00:00:00.000')
INSERT INTO [SmartSearchNewQA].[dbo].[ProductionOrder]
([PostingDate],
[MaterialNumber])
values
('WSHK-075R', '2006-05-29 00:00:00.000')
INSERT INTO [SmartSearchNewQA].[dbo].[ProductionOrder]
([PostingDate],
[MaterialNumber])
values
('WSHK-075R', '2003-05-29 00:00:00.000')The desired output is
-
What is the relationship between the two tables - UniqueConsumption2 and Productionorder?
Sue
-
SourceNo from UniqueConsumption2 table is mapped to the materialnumber of Productionorder table . Its many to many relationship .
Thanks
Vijay - Sue_H - Monday, January 22, 2018 6:30 PM
SourceNo from UniqueConsumption2 table is mapped to the materialnumber of Productionorder table . Its many to many relationship .
Thanks
Vijay - chozhanvijay 23273 - Tuesday, January 23, 2018 8:48 AM
What is the other table you are using to resolve the many to many relationship?
It's an associative entity in theory but call a lot of different things such as intermediary table, junction table, etc.Sue
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply