Complex sql formulation - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Complex sql formulation

Author

Topic

gamaz
Posting Yak Master

104 Posts

Posted - 2008-08-12 : 17:38:27

Hi,
The following is a sample table called tblAnalysis.
FinishedPartNo ProjectNo EstimateNo FirstTotalLaborCost FirstMaterialUnitCost LastTotalLaborCost LastMaterialUnitCost
297114901 & 279115001 08-02750-01 AA 0.42427964 0.044198 0.28967343 0.044198
297114901 & 279115001 08-02750-01 AA 0.42427964 0.044198 0.28967343 0.044198
297114901 & 279115001 08-02750-01 AA 0.42427964 0.044198 0.28967343 0.044198
297114901 & 297115001 08-02675-01 AA 0.42427964 0.042702 0.28967343 0.042702
297114901 & 297115001 08-02675-01 AA 0.42427964 0.042702 0.28967343 0.042702
Pack 102 & Pack 103 08-03232-00 AA 0.39855693 0.186711 0.39236738 0.186711
Pack 102 & Pack 103 08-03232-00 AA 0.39855693 0.186711 0.39236738 0.186711
Pack 160 & Pack 161 08-03235-00 AA 1.29094459 0.7092717 1.23038904 0.7092717
Pack 160 & Pack 161 08-03235-00 AA 1.29094459 0.7092717 1.23038904 0.7092717
Pack 807 & Pack 808 08-03241-00 AA 2.41041665 0.62769 1.44967947 0.62769
Pack 807 & Pack 808 08-03241-00 AA 2.41041665 0.62769 1.44967947 0.62769
Pack100 & Pack101 08-03189-00 AA 0.52469863 0.5641818 0.51930666 0.5641818
Pack100 & Pack101 08-03189-00 AA 0.52469863 0.5641818 0.51930666 0.5641818
Here finishedpartno has duplicate values. The projectno is also duplicate here. I need to come up with an sql that will pick one row corresponding to one finishedpartno. However while picking one of the duplicates the firstmaterialunitcost t should be sum of component firstmaterialcost and lastmaterialcost should be the sum of component lastmaterialunitcost in the corresponding rows. e.g.
In the present case corresponding to the finishedpartno 297114901 & 279115001 the FirstMaterialUnitCost should be = 0.044198 + 0.044198 + 0.044198 + 0.042702 + 0.042702 while the LastMaterialUnitCost should be = 0.044198 + 0.044198 + 0.044198 + 0.042702 + 0.042702.

Likewise corresponding to the finsihedpartno Pack100 & Pack101 one row should be picked. However the FirstMaterialUnitCost should be = 0.5641818 + 0.5641818 while the LastMaterialUnitCost should be = 0.5641818 + 0.5641818

I have no idea how to handle the sum part here. Any help is appreciated. Thanks.

gamaz
Posting Yak Master

104 Posts

Posted - 2008-08-12 : 18:34:43

OK, It seems the sample data is confusing. I got the sum part figured out. However the first part is not yet clear. I have come up with a simpler sample as shown below:

FinishedPartNo projectno estimateno FirstTotalLaborCost FirstMaterialUnitCost
001 08-02750-01 0.42427964 0.28967343
001 08-02750-01 0.42427964 0.21334533
001 08-02750-01 0.42427964 0.22321233
001 08-02750-01 0.42427964 0.28969878
002 08-03232-00 0.39855693 0.186711
002 08-03232-00 0.39855693 0.287932
003 08-03189-00 0.52469863 0.627693
003 08-03189-00 0.52469863 0.627773

I need to find out rows that does not show duplicate finishedpartno. Thus I am looking for output as follows:

001 08-02750-01 0.42427964 0.28967343
002 08-03232-00 0.39855693 0.186711
003 08-03189-00 0.52469863 0.627693

Here it is immaterial what row is chosen corresponding to FirstMaterialUnitCost (which have different values). However after the sql is executed there cannot be any duplicate values for finishedpartno in any of the rows
I would appreciate any help to formulate this sql. Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-12 : 18:48:58

SELECT FinishedPartNo, projectno, estimateno, FirstTotalLaborCost, MAX(FirstMaterialUnitCost) AS FirstMaterialUnitCost
FROM YourTable
GROUP BY FinishedPartNo, projectno, estimateno, FirstTotalLaborCost

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

gamaz
Posting Yak Master

104 Posts

Posted - 2008-08-12 : 19:31:10

Thanks Tara for the help. I appreciate it. Regards.

gamaz
Posting Yak Master

104 Posts

Posted - 2008-08-13 : 10:27:30

Hi Tara,
The spec changed a little. Here we have now a yearlyvol field. I have modified the sample here accordingly.

FinishedPartNo projectno estimateno YearlyVol FirstTotalLaborCost FirstMaterialUnitCost
001 08-02750-01 AA 477.13 0.42427964 0.28967343
001 08-02750-01 AA 477.13 0.42427964 0.21334533
001 08-02750-01 AA 204.21 0.42427964 0.22321233
001 08-02750-01 AA 204.21 0.42427964 0.28969878
002 08-03232-00 AA 100.22 0.39855693 0.186711
002 08-03232-00 AA 101.21 0.39855693 0.287932
003 08-03189-00 AA 131.32 0.52469863 0.627693
003 08-03189-00 AA 120.23 0.52469863 0.627773

I need to find out rows that does not show duplicate finishedpartno. Thus I am looking for output as follows:

001 08-02750-01 AA 477.13 0.42427964 0.28967343
002 08-03232-00 AA 100.22 0.39855693 0.186711
003 08-03189-00 AA 131.32 0.52469863 0.627693

It does not matter which row for a duplicate finishedpartno is chosen. But one need to be chosen.

I would appreciate your help for resolution of the above. Here the same solution will not hold as the row has now yearlyvol which is not having same value for particular finishedpartno. Thanks again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 11:08:51

[code]DECLARE @Sample TABLE
(
FinishedPartNo CHAR(3),
projectno CHAR(11),
estimateno CHAR(2),
YearlyVol decimal(5, 2),
FirstTotalLaborCost DECIMAL(9, 8),
FirstMaterialUnitCost DECIMAL(9, 8)
)

INSERT @Sample
SELECT '001', '08-02750-01', 'AA', 477.13, 0.42427964, 0.28967343 UNION ALL
SELECT '001', '08-02750-01', 'AA', 477.13, 0.42427964, 0.21334533 UNION ALL
SELECT '001', '08-02750-01', 'AA', 204.21, 0.42427964, 0.22321233 UNION ALL
SELECT '001', '08-02750-01', 'AA', 204.21, 0.42427964, 0.28969878 UNION ALL
SELECT '002', '08-03232-00', 'AA', 100.22, 0.39855693, 0.186711 UNION ALL
SELECT '002', '08-03232-00', 'AA', 101.21, 0.39855693, 0.287932 UNION ALL
SELECT '003', '08-03189-00', 'AA', 131.32, 0.52469863, 0.627693 UNION ALL
SELECT '003', '08-03189-00', 'AA', 120.23, 0.52469863, 0.627773

SELECT *
FROM @Sample AS s
INNER JOIN (
SELECT FinishedPartNo,
MAX(FirstMaterialUnitCost) AS FirstMaterialUnitCost
FROM @Sample
GROUP BY FinishedPartNo
) AS x ON x.FinishedPartNo = s.FinishedPartNo
WHERE s.FirstMaterialUnitCost = x.FirstMaterialUnitCost[/code]
If you have a unique column it is better to use that in favor of FirstMaterialUnitCost.
Create an IDENTITY column on the table and you have an easy solution.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 11:14:39

Here is another approach somewhat safer.

DECLARE	@Sample TABLE
	(
		FinishedPartNo CHAR(3),
		projectno CHAR(11),
		estimateno CHAR(2),
		YearlyVol decimal(5, 2),
		FirstTotalLaborCost DECIMAL(9, 8),
		FirstMaterialUnitCost DECIMAL(9, 8)
	)

INSERT	@Sample
SELECT	'001', '08-02750-01', 'AA', 477.13, 0.42427964, 0.28967343 UNION ALL
SELECT	'001', '08-02750-01', 'AA', 477.13, 0.42427964, 0.21334533 UNION ALL
SELECT	'001', '08-02750-01', 'AA', 204.21, 0.42427964, 0.22321233 UNION ALL
SELECT	'001', '08-02750-01', 'AA', 204.21, 0.42427964, 0.28969878 UNION ALL
SELECT	'002', '08-03232-00', 'AA', 100.22, 0.39855693, 0.186711   UNION ALL
SELECT	'002', '08-03232-00', 'AA', 101.21, 0.39855693, 0.287932   UNION ALL
SELECT	'003', '08-03189-00', 'AA', 131.32, 0.52469863, 0.627693   UNION ALL
SELECT	'003', '08-03189-00', 'AA', 120.23, 0.52469863, 0.627773

SELECT		*
FROM		(
			SELECT	*,
				CHECKSUM(*) AS id
			FROM	@Sample
		) AS s
INNER JOIN	(
			SELECT		FinishedPartNo,
					MAX(CHECKSUM(*)) AS id
			FROM		@Sample
			GROUP BY	FinishedPartNo
		) AS x ON x.FinishedPartNo = s.FinishedPartNo
WHERE		s.id = x.id

But an identity column is still the most safe way to go.

E 12°55'05.25"
N 56°04'39.16"

gamaz
Posting Yak Master

104 Posts

Posted - 2008-08-13 : 17:00:10

Hello Peso,
Thanks a lot for the code. It worked great with my prototype. However when I extended the concept to my actual table it did not work. This table has more columns than I specified in the prototype. I used the following code for the real table.

SELECT *
FROM tblAnalysis AS s
INNER JOIN (
SELECT FinishedPartNo,
MAX(FirstMaterialUnitCost) AS FirstMaterialUnitCost
FROM tblAnalysis
GROUP BY FinishedPartNo
) AS x ON x.FinishedPartNo = s.FinishedPartNo
WHERE s.FirstMaterialUnitCost = x.FirstMaterialUnitCost

This returned all the rows in the table instead of returned only one row per finishedpartno. Do you have any further thought. I appreciate your generous help. Thanks again.

TABLE SCRIPT:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblAnalysis]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblAnalysis]
GO

CREATE TABLE [dbo].[tblAnalysis] (
[FinishedPartNo] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProjectNo] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EstimateNo] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EstimateTime] [datetime] NULL ,
[FamilyCode] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QtyPer] [float] NULL ,
[QuoteUnitCost] [float] NULL ,
[FGCost] [float] NULL ,
[YearlyQty] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[aqty] [float] NULL ,
[annVol1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstReleaseQty] [float] NULL ,
[FirstMaterialUnitCost] [float] NULL ,
[FirstTotalLaborCost] [float] NULL ,
[FirstTotalUnitCost] [float] NULL ,
[LastReleaseQty] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastMaterialUnitCost] [float] NULL ,
[LastTotalLaborCost] [float] NULL ,
[LastTotalUnitCost] [float] NULL
) ON [PRIMARY]
GO

gamaz
Posting Yak Master

104 Posts

Posted - 2008-08-13 : 19:25:46

Hi Peso,
I thought of posting the DDL of my real table along with the data. Do you think the Identity method can handle this issue. Can you help resolve this issue with identity method? Thanks

DDD

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblAnalysis]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblAnalysis]
GO

CREATE TABLE [dbo].[tblAnalysis] (
[FinishedPartNo] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProjectNo] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EstimateNo] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EstimateTime] [datetime] NULL ,
[FamilyCode] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QtyPer] [float] NULL ,
[QuoteUnitCost] [float] NULL ,
[FGCost] [float] NULL ,
[YearlyQty] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[aqty] [float] NULL ,
[annVol1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstReleaseQty] [float] NULL ,
[FirstMaterialUnitCost] [float] NULL ,
[FirstTotalLaborCost] [float] NULL ,
[FirstTotalUnitCost] [float] NULL ,
[LastReleaseQty] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastMaterialUnitCost] [float] NULL ,
[LastTotalLaborCost] [float] NULL ,
[LastTotalUnitCost] [float] NULL
) ON [PRIMARY]
GO

DATA INSERT SCRIPT:

INSERT [tblAnalysis] ([FinishedPartNo], [ProjectNo], [EstimateNo], [EstimateTime], [FamilyCode], [QtyPer], [QuoteUnitCost], [FGCost], [YearlyQty], [aqty], [annVol1], [FirstReleaseQty], [FirstMaterialUnitCost], [FirstTotalLaborCost], [FirstTotalUnitCost], [LastReleaseQty], [LastMaterialUnitCost], [LastTotalLaborCost], [LastTotalUnitCost]) VALUES ('297114901 & 279115001', '08-02750-01', 'AA', '4/17/2008 1:18:00 PM', '1100', '0.0399', '0.93', '0.037107', '', '3716', '3716', '1000', '0.044198', '0.42427964', '0.46847764', '3716', '0.044198', '0.28967343', '0.33387143')
INSERT [tblAnalysis] ([FinishedPartNo], [ProjectNo], [EstimateNo], [EstimateTime], [FamilyCode], [QtyPer], [QuoteUnitCost], [FGCost], [YearlyQty], [aqty], [annVol1], [FirstReleaseQty], [FirstMaterialUnitCost], [FirstTotalLaborCost], [FirstTotalUnitCost], [LastReleaseQty], [LastMaterialUnitCost], [LastTotalLaborCost], [LastTotalUnitCost]) VALUES ('297114901 & 279115001', '08-02750-01', 'AA', '4/17/2008 1:18:00 PM', '1100', '0.0024', '0.93', '0.002232', '', '3716', '3716', '1000', '0.044198', '0.42427964', '0.46847764', '3716', '0.044198', '0.28967343', '0.33387143')
INSERT [tblAnalysis] ([FinishedPartNo], [ProjectNo], [EstimateNo], [EstimateTime], [FamilyCode], [QtyPer], [QuoteUnitCost], [FGCost], [YearlyQty], [aqty], [annVol1], [FirstReleaseQty], [FirstMaterialUnitCost], [FirstTotalLaborCost], [FirstTotalUnitCost], [LastReleaseQty], [LastMaterialUnitCost], [LastTotalLaborCost], [LastTotalUnitCost]) VALUES ('297114901 & 279115001', '08-02750-01', 'AA', '4/17/2008 1:18:00 PM', '1100', '0.0044', '0.93', '0.004092', '', '3716', '3716', '1000', '0.044198', '0.42427964', '0.46847764', '3716', '0.044198', '0.28967343', '0.33387143')
INSERT [tblAnalysis] ([FinishedPartNo], [ProjectNo], [EstimateNo], [EstimateTime], [FamilyCode], [QtyPer], [QuoteUnitCost], [FGCost], [YearlyQty], [aqty], [annVol1], [FirstReleaseQty], [FirstMaterialUnitCost], [FirstTotalLaborCost], [FirstTotalUnitCost], [LastReleaseQty], [LastMaterialUnitCost], [LastTotalLaborCost], [LastTotalUnitCost]) VALUES ('297114901 & 297115001', '08-02675-01', 'AA', '4/17/2008 1:11:00 PM', '1100', '0.0024', '0.93', '0.002232', '', '3716', '3716', '1000', '0.042702', '0.42427964', '0.46698164', '3716', '0.042702', '0.28967343', '0.33237543')
INSERT [tblAnalysis] ([FinishedPartNo], [ProjectNo], [EstimateNo], [EstimateTime], [FamilyCode], [QtyPer], [QuoteUnitCost], [FGCost], [YearlyQty], [aqty], [annVol1], [FirstReleaseQty], [FirstMaterialUnitCost], [FirstTotalLaborCost], [FirstTotalUnitCost], [LastReleaseQty], [LastMaterialUnitCost], [LastTotalLaborCost], [LastTotalUnitCost]) VALUES ('297114901 & 297115001', '08-02675-01', 'AA', '4/17/2008 1:11:00 PM', '1100', '0.0399', '0.93', '0.037107', '', '3716', '3716', '1000', '0.042702', '0.42427964', '0.46698164', '3716', '0.042702', '0.28967343', '0.33237543')
INSERT [tblAnalysis] ([FinishedPartNo], [ProjectNo], [EstimateNo], [EstimateTime], [FamilyCode], [QtyPer], [QuoteUnitCost], [FGCost], [YearlyQty], [aqty], [annVol1], [FirstReleaseQty], [FirstMaterialUnitCost], [FirstTotalLaborCost], [FirstTotalUnitCost], [LastReleaseQty], [LastMaterialUnitCost], [LastTotalLaborCost], [LastTotalUnitCost]) VALUES ('Pack 102 & Pack 103', '08-03232-00', 'AA', '4/21/2008 1:22:00 PM', '1100', '0.0855', '0.93', '0.079515', '', '116900', '', '9750', '0.186711', '0.39855693', '0.58526793', '14600', '0.186711', '0.39236738', '0.57907838')
INSERT [tblAnalysis] ([FinishedPartNo], [ProjectNo], [EstimateNo], [EstimateTime], [FamilyCode], [QtyPer], [QuoteUnitCost], [FGCost], [YearlyQty], [aqty], [annVol1], [FirstReleaseQty], [FirstMaterialUnitCost], [FirstTotalLaborCost], [FirstTotalUnitCost], [LastReleaseQty], [LastMaterialUnitCost], [LastTotalLaborCost], [LastTotalUnitCost]) VALUES ('Pack 102 & Pack 103', '08-03232-00', 'AA', '4/21/2008 1:22:00 PM', '1100', '0.0832', '0.93', '0.077376', '', '116900', '', '9750', '0.186711', '0.39855693', '0.58526793', '14600', '0.186711', '0.39236738', '0.57907838')
INSERT [tblAnalysis] ([FinishedPartNo], [ProjectNo], [EstimateNo], [EstimateTime], [FamilyCode], [QtyPer], [QuoteUnitCost], [FGCost], [YearlyQty], [aqty], [annVol1], [FirstReleaseQty], [FirstMaterialUnitCost], [FirstTotalLaborCost], [FirstTotalUnitCost], [LastReleaseQty], [LastMaterialUnitCost], [LastTotalLaborCost], [LastTotalUnitCost]) VALUES ('Pack 160 & Pack 161', '08-03235-00', 'AA', '4/21/2008 1:22:00 PM', '1100', '0.30279', '0.93', '0.2815947', '', '12096', '', '1000', '0.7092717', '1.29094459', '2.00021629', '1500', '0.7092717', '1.23038904', '1.93966074')
INSERT [tblAnalysis] ([FinishedPartNo], [ProjectNo], [EstimateNo], [EstimateTime], [FamilyCode], [QtyPer], [QuoteUnitCost], [FGCost], [YearlyQty], [aqty], [annVol1], [FirstReleaseQty], [FirstMaterialUnitCost], [FirstTotalLaborCost], [FirstTotalUnitCost], [LastReleaseQty], [LastMaterialUnitCost], [LastTotalLaborCost], [LastTotalUnitCost]) VALUES ('Pack 160 & Pack 161', '08-03235-00', 'AA', '4/21/2008 1:22:00 PM', '1100', '0.4013', '0.93', '0.373209', '', '12096', '', '1000', '0.7092717', '1.29094459', '2.00021629', '1500', '0.7092717', '1.23038904', '1.93966074')
INSERT [tblAnalysis] ([FinishedPartNo], [ProjectNo], [EstimateNo], [EstimateTime], [FamilyCode], [QtyPer], [QuoteUnitCost], [FGCost], [YearlyQty], [aqty], [annVol1], [FirstReleaseQty], [FirstMaterialUnitCost], [FirstTotalLaborCost], [FirstTotalUnitCost], [LastReleaseQty], [LastMaterialUnitCost], [LastTotalLaborCost], [LastTotalUnitCost]) VALUES ('Pack 807 & Pack 808', '08-03241-00', 'AA', '4/21/2008 12:56:00 PM', '1100', '0.2065', '0.93', '0.192045', '', '1', '', '160', '0.62769', '2.41041665', '3.03810665', '1040', '0.62769', '1.44967947', '2.07736947')
INSERT [tblAnalysis] ([FinishedPartNo], [ProjectNo], [EstimateNo], [EstimateTime], [FamilyCode], [QtyPer], [QuoteUnitCost], [FGCost], [YearlyQty], [aqty], [annVol1], [FirstReleaseQty], [FirstMaterialUnitCost], [FirstTotalLaborCost], [FirstTotalUnitCost], [LastReleaseQty], [LastMaterialUnitCost], [LastTotalLaborCost], [LastTotalUnitCost]) VALUES ('Pack 807 & Pack 808', '08-03241-00', 'AA', '4/21/2008 12:56:00 PM', '1100', '0.3615', '0.93', '0.336195', '', '1', '', '160', '0.62769', '2.41041665', '3.03810665', '1040', '0.62769', '1.44967947', '2.07736947')
INSERT [tblAnalysis] ([FinishedPartNo], [ProjectNo], [EstimateNo], [EstimateTime], [FamilyCode], [QtyPer], [QuoteUnitCost], [FGCost], [YearlyQty], [aqty], [annVol1], [FirstReleaseQty], [FirstMaterialUnitCost], [FirstTotalLaborCost], [FirstTotalUnitCost], [LastReleaseQty], [LastMaterialUnitCost], [LastTotalLaborCost], [LastTotalUnitCost]) VALUES ('Pack100 & Pack101', '08-03189-00', 'AA', '4/21/2008 1:15:00 PM', '1100', '0.309', '0.93', '0.28737', '', NULL, '', '18000', '0.5641818', '0.52469863', '1.08888043', '27000', '0.5641818', '0.51930666', '1.08348846')
INSERT [tblAnalysis] ([FinishedPartNo], [ProjectNo], [EstimateNo], [EstimateTime], [FamilyCode], [QtyPer], [QuoteUnitCost], [FGCost], [YearlyQty], [aqty], [annVol1], [FirstReleaseQty], [FirstMaterialUnitCost], [FirstTotalLaborCost], [FirstTotalUnitCost], [LastReleaseQty], [LastMaterialUnitCost], [LastTotalLaborCost], [LastTotalUnitCost]) VALUES ('Pack100 & Pack101', '08-03189-00', 'AA', '4/21/2008 1:15:00 PM', '1100', '0.2579', '0.93', '0.239847', '', NULL, '', '18000', '0.5641818', '0.52469863', '1.08888043', '27000', '0.5641818', '0.51930666', '1.08348846')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 03:17:16

Here is a failsafe approach!

CREATE TABLE [dbo].[tblAnalysis] (
[RowID] INT IDENTITY(1, 1),
[FinishedPartNo] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProjectNo] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EstimateNo] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EstimateTime] [datetime] NULL ,
[FamilyCode] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QtyPer] [float] NULL ,
[QuoteUnitCost] [float] NULL ,
[FGCost] [float] NULL ,
[YearlyQty] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[aqty] [float] NULL ,
[annVol1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstReleaseQty] [float] NULL ,
[FirstMaterialUnitCost] [float] NULL ,
[FirstTotalLaborCost] [float] NULL ,
[FirstTotalUnitCost] [float] NULL ,
[LastReleaseQty] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastMaterialUnitCost] [float] NULL ,
[LastTotalLaborCost] [float] NULL ,
[LastTotalUnitCost] [float] NULL 
) ON [PRIMARY]

And then simply run this query

SELECT		a.*
FROM		[tblAnalysis] AS a
INNER JOIN	(
			SELECT		FinishedPartNo,
					MAX(RowID) AS maxRowID
			FROM		[tblAnalysis]
			GROUP BY	FinishedPartNo
		) AS x ON x.FinishedPartNo = a.FinishedPartNo
WHERE		a.RowID = x.maxRowID

E 12°55'05.25"
N 56°04'39.16"

gamaz
Posting Yak Master

104 Posts

Posted - 2008-08-14 : 10:29:37

That worked perfect and it is fail safe too. I appreciate this 'a lot'. Best regards.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources