Sql-server – How to do two date calculations in one SQL Statement

From what I can see you have two problems. The first is the doubling. At a guess it is because of the SELECT statement in your INSERT statement.

       SELECT @NewMetricID, 
            NULL, 
            sm.sortorder, 
            ISNULL(sm.MetricOrder, 1), 
            ISNULL(sm.CategoryOrder, 1), 
            sm.RptCurrentGroup, 
            'System', 
            GetDate(), 
            NULL, 
            NULL, 
            'N', 
            NULL,
            NULL
        FROM Metric_Instance mi 
        INNER JOIN Shared_Metrics sm on mi.MetricID = sm.MetricID
        WHERE mi.MetricID = @CurrentMetricID 
            AND mi.MetricDisposition <> 'Suspended' 
            AND mi.isDeleted <> 'Y'
            AND sm.isDeleted <> 'Y';

I would be willing to bet your query is returning more than one row. Any time you have a join like that you run a risk of creating duplicate rows. The easy way to fix it is to do this:

       SELECT DISTINCT @NewMetricID, 
            NULL, 
            sm.sortorder, 
            ISNULL(sm.MetricOrder, 1), 
            ...
            ...
            ...

The only real change is that that I'm putting a DISTINCT at the top of the query. This means that if there are any VALID duplicates you are still going to pick them up. You are not actually referencing any columns from Metric_Instance so you could change it from a JOIN to a WHERE EXISTS (SELECT 1 ...

On to problem two. In your code you are using scope_identity(). This is only going to return the last identity value created. If you insert more than one row the previous rows are going to be missed. The best solution here is to get rid of the cursor and change this into a batch process. INSERT all possible rows initially using the OUTPUT clause to dump a list of identity values and CurrentMetricIDs into a temp table. Then do your update using that temp table to update all of the values at once. Not only will this fix your problem but will probably run quite a bit faster as well.

Create date tables or table variables with your ranges and do the join query. Assuming SQL Server as the database, my sample solution is given below.

Declare @Range1Begin date = '10/16/2014',
        @Range1End   date = '10/28/2014',
        @Range2Begin date = '9/22/2014',
        @Range2End   date = '10/21/2014';

Declare @DateTable1 table (Date1 date);

Insert into @DateTable1
select dateadd(day, number, @Range1Begin)
from 
    (select distinct number from master.dbo.spt_values
     where name is null) n
where dateadd(day, number, @Range1Begin) <= @Range1End;

Declare @DateTable2 table (Date2 date);

Insert into @DateTable2
select dateadd(day, number, @Range2Begin)
from 
    (select distinct number from master.dbo.spt_values
     where name is null ) n
where dateadd(day, number, @Range2Begin) <= @Range2End;


-- Get individual dates
select dt1.Date1 as date, datename(w, dt1.date1) as weekday from @DateTable1 dt1
inner join @DateTable2 dt2
on dt1.date1 = dt2.date2
where datepart(w, dt1.date1) not in (1,7);


-- Get total days
select count(*) as TotalDays from @DateTable1 dt1
inner join @DateTable2 dt2
on dt1.date1 = dt2.date2
where datepart(w, dt1.date1) not in (1,7);

Without declaring table variables, following is the solution.

Declare @Range1Begin date = '10/16/2014',
        @Range1End   date = '10/28/2014',
        @Range2Begin date = '9/22/2014',
        @Range2End   date = '10/21/2014';

-- Get total days
select count(*) as TotalDays from (select dateadd(day, number, @Range1Begin) date1
from 
    (select distinct number from master.dbo.spt_values
     where name is null) n
where dateadd(day, number, @Range1Begin) <= @Range1End) dt1
inner join (select dateadd(day, number, @Range2Begin) date2
from 
    (select distinct number from master.dbo.spt_values
     where name is null ) n
where dateadd(day, number, @Range2Begin) <= @Range2End) dt2
on dt1.date1 = dt2.date2
where datepart(w, dt1.date1) not in (1,7);