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

INSERT INTO [Rates]
           ([rat_pk]
           ,[rat_name]
           ,[rat_type])
     (SELECT newid()
           ,[SpecialRate]
           ,[rat_type]
      FROM (SELECT DISTINCT SpecialRate
                          ,case when SpecialRate like 'Mail%' 
                                     then 2
                           case when SpecialRate like 'ZipX%' 
                                     then 3
                           else 1 end AS [rat_type]
            FROM [Weight Rates-Special Rates]) Test)

NOT TESTED!