>>Hi,
>>
>>my brain is tired and I'm not seeing this one so would appreciate a new pair of eyes looking at it.
>>
>>I have been given a table with this definition:
>>
>>

CREATE TABLE [dbo].[Weight Rates-Special Rates](
>>	[BoxTypeID] [int] NULL,
>>	[SpecialRate] [nvarchar](10) NOT NULL,
>>	[StartDate] [datetime] NOT NULL,
>>	[EndDate] [datetime] NOT NULL,
>>	[Start1] [int] NOT NULL,
>>	[End1] [int] NOT NULL,
>>	[USRate1] [money] NOT NULL,
>>	[Start2] [int] NOT NULL,
>>	[End2] [int] NOT NULL,
>>	[USRate2] [money] NOT NULL,
>>	[Start3] [int] NOT NULL,
>>	[End3] [int] NOT NULL,
>>	[USRate3] [money] NOT NULL,
>>	[Start4] [int] NOT NULL,
>>	[End4] [int] NOT NULL,
>>	[USRate4] [money] NOT NULL,
>>	[Start5] [int] NOT NULL,
>>	[End5] [int] NOT NULL,
>>	[USRate5] [money] NOT NULL,
>>	[USRate6] [money] NOT NULL)

>>
>>Which has data like this in it:
>>
>>

BoxTypeID	SpecialRate	StartDate	EndDate	Start1	End1	USRate1	Start2	End2	USRate2	Start3	End3	USRate3	Start4	End4	USRate4	Start5	End5	USRate5	USRate6
>>9	Mail2	2008-04-28 00:00:00.000	2020-12-31 00:00:00.000	0	0	0.00	0	0	0.00	0	0	0.00	0	1	2.345	2	9999	2.345	2.345
>>10	Mail1	2008-04-28 00:00:00.000	2009-10-31 00:00:00.000	0	0	0.00	0	0	0.00	0	0	0.00	0	1	4.72	2	50	4.72	4.72
>>10	Mail1	2009-11-01 00:00:00.000	2020-12-31 00:00:00.000	0	0	0.00	0	0	0.00	0	0	0.00	0	1	0.00	2	50	4.702	4.702
>>10	ZipX1	2007-01-01 00:00:00.000	2007-04-30 00:00:00.000	0	0	0.00	0	0	0.00	0	0	0.00	0	1	7.00	2	50	2.00	2.00
>>10	ZipX2	2008-05-19 00:00:00.000	2008-06-18 00:00:00.000	0	0	0.00	0	0	0.00	0	0	0.00	0	1	7.80	2	50	2.35	2.35
>>10	ZipX2	2008-06-19 00:00:00.000	2010-08-09 00:00:00.000	0	0	0.00	0	0	0.00	0	0	0.00	0	1	8.00	2	50	2.10	2.10
>>10	ZipX2	2010-08-10 00:00:00.000	2010-09-21 00:00:00.000	0	0	0.00	0	0	0.00	0	0	0.00	0	1	5.60	2	50	2.82	2.82
>>10	ZipX2	2010-09-22 00:00:00.000	2020-12-31 00:00:00.000	0	0	0.00	0	0	0.00	0	0	0.00	0	1	5.00	2	100	2.50	2.50
>>10	ZipX3	2009-12-07 00:00:00.000	2010-09-13 00:00:00.000	0	0	0.00	0	0	0.00	0	0	0.00	0	1	2.10	2	50	2.10	2.10
>>10	ZipX3	2010-09-14 00:00:00.000	2020-12-31 00:00:00.000	0	0	0.00	0	0	0.00	0	0	0.00	0	1	2.50	2	50	2.50	2.50

>>
>>I want to normalise this into two tables:
>>
>>

CREATE TABLE [dbo].[Rates](
>>	[rat_pk] [uniqueidentifier] NOT NULL,
>>	[rat_name] [nvarchar](50) NOT NULL,
>>	[rat_type] [smallint] NOT NULL)
>>
>>CREATE TABLE [dbo].[RateDetails](
>>	[rdt_pk] [uniqueidentifier] NOT NULL,
>>	[rdt_ratfk] [uniqueidentifier] NOT NULL,
>>	[rdt_weightstart] [int] NOT NULL,
>>	[rdt_rate] [numeric](18, 2) NOT NULL,
>>	[rdt_start] [date] NOT NULL,
>>	[rdt_bxtfk] [int] NOT NULL)

>>
>>First problem is how to write an INSERT statement that will only insert one row per unique SpecialRate.
>>
>>My code would be something like this, but not quite:
>>
>>

INSERT INTO [Rates]
>>           ([rat_pk]
>>           ,[rat_name]
>>           ,[rat_type])
>>     (SELECT newid()
>>      ,([SpecialRate]
>>      ,case when SpecialRate like 'Mail%' then 2 else 3 end /* Standard = 1, Mail = 2, ZipX = 3 */
>>  FROM [Weight Rates-Special Rates])

>>
>>This pulls out a row for each row in [Weight Rates-Special Rates] but of course there are duplicates due to there being a rate for each BoxTypeId.
>
>From the data you show what you want as a final result in [Rates]?

Final result should have 5 rows in Rates:

newid(), Mail1, 2
newid(), Mail2, 2
newid(), ZipX1, 3
newid(), ZipX2, 3
newid(), ZipX3, 3