SourceNo from UniqueConsumption2...

SourceNo from UniqueConsumption2 table  is mapped to the materialnumber of Productionorder table . Its many to many relationship .
Thanks
Vijay

Thanks Lynn .It works awesome..Can you please remove the rownum column and the corresponding logic  from the query .

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...

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)

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...

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...

hi,

ya you are correct. Below query fetches the unique records.Thanks for your help

SELECT ModelNo
  , ROW_NUMBER() OVER (ORDER BY ModelNo) AS SEQ
  , SUM(Qty)...

Please find the updated script below,

USE [SFA]
GO

/****** Object: Table [dbo].[USTLineItems]  Script Date: 03/16/2017 18:27:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Sorry i missed it in my first post.My Bad.

I updated the query slightly  to get the min price. I still facing the issue of getting the Unique model numbers as i have some more columns in the table...

Hi

Actually I got stuck in the above script which I shared in my first post .

This is not a homework ??and it's my project and I got stuck here as...

Thanks for your reply.Actually i need the sequence number to be displayed in the front end and so needs to build that too in the select query as well.

hi,

Ya just got this solution few mins back. thanks for your help.

Regards

vijay