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.