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