January 18, 2018 at 9:45 am
Below is my requirement. I have given the one material number YHE30B21S as example.
ID Plant Quantity Material Component ValidFromDate ValidToDate Lvl
6 1002 1.0 5118617 364882 2015-07-24 2015-12-11 1
27 1002 1.0 5129641 5118617 2015-07-24 2015-12-11 2
95 1002 1.0 YHE30B21S 5129641 2015-07-24 9999-12-31 3
ID Plant Material QuantityMaterial Description ValidFromDate ValidToDate
118 1002 YHE30B21S 1.0 NULL 2015-07-24 9999-12-31
on the above two tables, i would like to get the below result wix Max(validfromd ate) and min(validtodate) for the material YHE30B21S . I need to pass the component as material and loop through the three records to get the max and min values of validfrom and validto date respectievly.
Aprreciate your help on this.
Thanks Sam
January 18, 2018 at 9:51 am
chozhanvijay 23273 - Thursday, January 18, 2018 9:45 AMBelow is my requirement. I have given the one material number YHE30B21S as example.
Table A
ID Plant Quantity Material Component ValidFromDate ValidToDate Lvl
6 1002 1.0 5118617 364882 2015-07-24 2015-12-11 1
27 1002 1.0 5129641 5118617 2015-07-24 2015-12-11 2
95 1002 1.0 YHE30B21S 5129641 2015-07-24 9999-12-31 3Table B
ID Plant Material QuantityMaterial Description ValidFromDate ValidToDate
118 1002 YHE30B21S 1.0 NULL 2015-07-24 9999-12-31on the above two tables, i would like to get the below result wix Max(validfromd ate) and min(validtodate) for the material YHE30B21S . I need to pass the component as material and loop through the three records to get the max and min values of validfrom and validto date respectievly.
Aprreciate your help on this.
Thanks Sam
What does 'wix' mean?
Can you provide both sample data and desired results in consumable format, please?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 18, 2018 at 9:51 am
Have a look in my signature on how to post a T-SQL question. Rather than pasting your data in a format that we can't use (and is difficult to read), ensure you provide relevant DDL and consumable sample data, as well as Expected results that can be easily interpreted.
Thanks. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 18, 2018 at 10:09 am
Sorry, Please find the script below and the sample data
CREATE TABLE dbo.MaterialsWhereUsed (ID INT IDENTITY(1,1) NOT NULL, Plant nvarchar(4), Quantity decimal(18,1), Material nvarchar(150),
Component nvarchar(150), ValidFromDate Datetime, ValidToDate Datetime, Lvl int)
CREATE TABLE EndLvlWhereUsed (ID INT IDENTITY(1,1) NOT NULL, Plant nvarchar(4), Material nvarchar(150), Quantity decimal(18,1),
MaterialDescription varchar(150), ValidFromDate Datetime, ValidToDate Datetime)
go
Insert into dbo.MaterialsWhereUsed values ('1002',1.0,'5118617','364882','2015-07-24 00:00:00.000','2015-12-11 00:00:00.000',1)
Insert into dbo.MaterialsWhereUsed values('1002',1.0,'5129641','5118617','2015-07-25 00:00:00.000','2015-12-11 00:00:00.000',2)
Insert into dbo.MaterialsWhereUsed values('1002',1.0,'YHE30B21S','5129641','2015-07-24 00:00:00.000','9999-12-31 00:00:00.000',3)
CREATE TABLE dbo.EndLvlWhereUsed (ID1 INT IDENTITY(1,1) NOT NULL, Plant nvarchar(4), Material nvarchar(150), Quantity decimal(18,1),
MaterialDescription varchar(150), ValidFromDate Datetime, ValidToDate Datetime)
Insert into dbo.EndLvlWhereUsed values('1002','YHE30B21S',1.0,NULL,'2015-07-24 00:00:00.000','9999-12-31 00:00:00.000')
Expected Result
----------------
Material Quantity MaterialDescription ValidFromDate ValidToDate RowNum
YHE30B21S 1 NULL 2015-07-25 00:00:00.000 2015-12-11 00:00:00.000 1
Validfromdate - Newesdate from dbo.MaterialsWhereUsed
Validtodate - Oldestdate from dbo.MaterialsWhereUsed
Thanks
Vijay
January 18, 2018 at 10:15 am
chozhanvijay 23273 - Thursday, January 18, 2018 10:09 AMSorry, Please find the script below and the sample data
CREATE TABLE dbo.MaterialsWhereUsed (ID INT IDENTITY(1,1) NOT NULL, Plant nvarchar(4), Quantity decimal(18,1), Material nvarchar(150),
Component nvarchar(150), ValidFromDate Datetime, ValidToDate Datetime, Lvl int)CREATE TABLE EndLvlWhereUsed (ID INT IDENTITY(1,1) NOT NULL, Plant nvarchar(4), Material nvarchar(150), Quantity decimal(18,1),
MaterialDescription varchar(150), ValidFromDate Datetime, ValidToDate Datetime)
goInsert into dbo.MaterialsWhereUsed values ('1002',1.0,'5118617','364882','2015-07-24 00:00:00.000','2015-12-11 00:00:00.000',1)
Insert into dbo.MaterialsWhereUsed values('1002',1.0,'5129641','5118617','2015-07-25 00:00:00.000','2015-12-11 00:00:00.000',2)
Insert into dbo.MaterialsWhereUsed values('1002',1.0,'YHE30B21S','5129641','2015-07-24 00:00:00.000','9999-12-31 00:00:00.000',3)CREATE TABLE dbo.EndLvlWhereUsed (ID1 INT IDENTITY(1,1) NOT NULL, Plant nvarchar(4), Material nvarchar(150), Quantity decimal(18,1),
MaterialDescription varchar(150), ValidFromDate Datetime, ValidToDate Datetime)Insert into dbo.EndLvlWhereUsed values('1002','YHE30B21S',1.0,NULL,'2015-07-24 00:00:00.000','9999-12-31 00:00:00.000')
Expected Result
----------------
Material Quantity MaterialDescription ValidFromDate ValidToDate RowNum
YHE30B21S 1 NULL 2015-07-25 00:00:00.000 2015-12-11 00:00:00.000 1
Formatted, however, have you tried running that SQL? 9999-12-31 00:00:00.000 cannot be inserted into a datetime. Should it be a datetime2(3)?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 18, 2018 at 10:22 am
Ya 9999-12-31 00:00:00.000 got inserted into the table. In addition to I have given the sample data for the one MaterialNumber , like the above records there are records present for more materialnumbers as well in both the tableA and TableB.
Thanks
Vijay
January 18, 2018 at 10:42 am
I am confused. Your expected results are already in the second table, [dbo].[EndLvlWhereUsed] as well being in the table [dbo].[MaterialWhereUsed] in the data row with ID = 3.
So please, help unconfuse me.
January 18, 2018 at 10:49 am
Delete the records in table A and insert like below.
Insert into dbo.MaterialsWhereUsed values ('1002',1.0,'5118617','364882','2015-07-25 00:00:00.000','2015-12-11 00:00:00.000',1)
Insert into dbo.MaterialsWhereUsed values('1002',1.0,'5129641','5118617','2015-07-24 00:00:00.000','2015-12-11 00:00:00.000',2)
Insert into dbo.MaterialsWhereUsed values('1002',1.0,'YHE30B21S','5129641','2015-07-24 00:00:00.000','9999-12-31 00:00:00.000',3)
Expected Output is same. I need to get the latest validfrom and Oldest validto from the table A by looping through the materialnumber YHE30B21S intially on tableA and pass the componentnumber 5129641 as materialnumber and pass the componentnumber 5118617 as materialnumber.
Material Quantity MaterialDescription ValidFromDate ValidToDate RowNum
YHE30B21S 1 NULL 2015-07-25 00:00:00.000 2015-12-11 00:00:00.000 1
Hope the above logic makes sense now and cleared your confusion.
January 18, 2018 at 10:58 am
Nope, still confused since a simple inspection of the data still shows everything you need is still in the record where ID = 3 and Material = YHE30B21S. Not seeing where there is a need to recurse through the data.
Never mind, looks like you changed your expected results.
January 18, 2018 at 11:04 am
Nope, I didnt change my expected result..Just changed the validfrom date in Table A only.
Record 3 has the validfromdate of 2015-07-24 00:00:00.00 which is not the latest date. Record 1 has the validfrom latestdate as 2015-07-25 00:00:00.000.
As per the application business , we have to start search with the materialnumber YHE30B21S in the tableA and give the equivalent component number 5129641 as materialnumber to get the subsequent row from table A. which is row 2. And then give the row 2 compoent number 5118617 as materialnumber in tableA to get the row 1.
January 18, 2018 at 11:07 am
Question now, do we start with TableB ([dbo].[EndLvlWhereUsed]) and use it to query TableA?
January 18, 2018 at 12:56 pm
Hope this works for you, any questions be sure to ask:
WITH BaseMaterial AS (
SELECT
[elwu].[Material] AS MasterMaterial
, [elwu].[MaterialDescription]
, [mwu].[ID]
, [mwu].[Plant]
, [elwu].[Quantity]
, [mwu].[Material]
, [mwu].[Component]
, [mwu].[ValidFromDate]
, [mwu].[ValidToDate]
, [mwu].[Lvl]
, CAST(1 AS INT) AS NewLvl
FROM
[dbo].[MaterialsWhereUsed] AS [mwu]
INNER JOIN [dbo].[EndLvlWhereUsed] AS [elwu]
ON [elwu].[Material] = [mwu].[Material]
UNION ALL
SELECT
[bm].[MasterMaterial]
, [bm].[MaterialDescription]
, [mwu].[ID]
, [mwu].[Plant]
, [bm].[Quantity]
, [mwu].[Material]
, [mwu].[Component]
, [mwu].[ValidFromDate]
, [mwu].[ValidToDate]
, [mwu].[Lvl]
, bm.NewLvl + 1 AS NewLvl
FROM
[dbo].[MaterialsWhereUsed] AS [mwu]
INNER JOIN [BaseMaterial] AS [bm]
ON [bm].[Component] = [mwu].[Material]
)
SELECT
[bm1].[MasterMaterial]
, [bm1].[MaterialDescription]
--, [bm1].[ID]
--, [bm1].[Plant]
, [bm1].[Quantity]
--, [bm1].[Material]
--, [bm1].[Component]
, MAX([bm1].[ValidFromDate]) AS ValidFromDate
, MIN([bm1].[ValidToDate]) AS ValidToDate
, MIN([bm1].[Lvl]) AS RowNum
--, [bm1].[NewLvl]
FROM
[BaseMaterial] AS [bm1]
GROUP BY
[bm1].[MasterMaterial]
, [bm1].[MaterialDescription]
, [bm1].[Quantity];
January 18, 2018 at 2:06 pm
Thanks Lynn .It works awesome..Can you please remove the rownum column and the corresponding logic from the query .
January 18, 2018 at 2:08 pm
chozhanvijay 23273 - Thursday, January 18, 2018 2:06 PMThanks Lynn .It works awesome..Can you please remove the rownum column and the corresponding logic from the query .
Nope, I am not supporting the code. That is now your job.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply