2

Hello sorry this might be a long one, I have a strange problem that has popped up. I'm trying to create an accounting like app in which several columns are aggregated by the group they belong to. However several of these columns could contain duplicate data which I don't want to include in the totals.

My table looks like:

CREATE TABLE [dbo].[RawDataTable] (
[Id]                     INT             IDENTITY (1, 1) NOT NULL,
[CheckDate]              DATE            NULL,
[PropNum]                INT             NOT NULL,
[PropSeqNum]             INT             NULL,
[PropName]               NVARCHAR (100)  NOT NULL,
[ProductionMonth]        INT             NULL,
[Product]                INT             NOT NULL,
[LeaseVolume]            DECIMAL (18, 2) NOT NULL,
[Price]                  DECIMAL (18, 2) NOT NULL,
[LeaseGrossValue]        DECIMAL (18, 2) NOT NULL,
[LeaseTaxes]             DECIMAL (18, 2) NULL,
[LeaseOtherDeductions]   DECIMAL (18, 2) NOT NULL,
[LeaseNetValue]          DECIMAL (18, 2) NOT NULL,
[DisbursementDecimal]    DECIMAL (18)    NULL,
[InterestType]           NVARCHAR (10)   NULL,
[InterestGrossValue]     DECIMAL (18, 2) NOT NULL,
[InterestTaxes]          DECIMAL (18, 2) NOT NULL,
[IntrestOtherDeductions] DECIMAL (18, 2) NOT NULL,
[InterestNetValue]       DECIMAL (18, 2) NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);

First I try to get rid of any duplicate data with this stored procedure: 

CREATE PROCEDURE [dbo].[EraseDuplicates]
AS
UPDATE  T1
SET     T1.LeaseVolume = 0,
        T1.Price = 0,
        T1.LeaseGrossValue = 0,
        T1.LeaseTaxes = 0,
        T1.LeaseOtherDeductions = 0,
        T1.LeaseNetValue = 0
FROM
    (SELECT  *
     FROM    RawDataTable
     WHERE   Product >= 400 OR
             Id NOT IN 
                (SELECT MIN(Id)
                 FROM RawDataTable
                 GROUP BY CheckDate, PropNum, PropSeqNum, PropName, ProductionMonth, Product)) AS T1

My problem comes when I return from the EraseDuplicates stored procedure, it's zeroing out the data correctly, however it's also copying the rows and adding them back into the table, this is the result:

MAJOR EDIT

I have narrowed it down to this stored procedure. As soon as I return from this method

public static void EraseDuplicateData(string connString)
    {
        using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings[connString].ConnectionString))
        {
            db.Query("EraseDuplicates", commandType: CommandType.StoredProcedure);
        }
    }

The database all of a sudden has duplicated the rows. The values are being zeroed out correctly, but I just don't understand how an update command can be creating rows.

ORIGINAL DATA

+------------+----------+-------------+-------------------------------+------------------+----------+--------------+--------+------------------+-------------+-----------------------+----------------+----------------------+---------------+---------------------+----------------+-------------------------+-------------------+--------------+--------------+--+--+--+
|  CheckDate |  PropNum |  PropSeqNum |            PropName           |  ProductionMonth |  Product |  LeaseVolume |  Price |  LeaseGrossValue |  LeaseTaxes |  LeaseOtherDeductions |  LeaseNetValue |  DisbursementDecimal |  InterestType |  InterestGrossValue |  InterestTaxes |  IntrestOtherDeductions |  InterestNetValue |  RecordCount |  CheckAmount |  |  |  |
+------------+----------+-------------+-------------------------------+------------------+----------+--------------+--------+------------------+-------------+-----------------------+----------------+----------------------+---------------+---------------------+----------------+-------------------------+-------------------+--------------+--------------+--+--+--+
| 1/25/2015  |   100004 |          25 | BEAVER LODGE DEVON UT TR-0025 |           122014 |      100 | 774.96       | 51.93  | 40243.64         | -4628.03    | 0                     | 35615.61       | 0.0026932            | RI 01         | 108.38              | -12.46         | 0                       | 95.92             |              |              |  |  |  |
| 1/25/2015  |   100004 |          25 | BEAVER LODGE DEVON UT TR-0025 |           122014 |      100 | 774.96       | 51.93  | 40243.64         | -4628.03    | 0                     | 35615.61       | 0.0050669            | RI 02         | 203.91              | -23.45         | 0                       | 180.46            |              |              |  |  |  |
| 1/25/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |            42013 |      204 | -0.27        | 4.037  | -1.09            | -0.11       | 0.13                  | -1.07          | 0.0026932            | RI 01         | -0.01               | 0              | 0                       | -0.01             |              |              |  |  |  |
| 1/25/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |           112014 |      204 | 116.76       | 3.992  | 466.11           | -60.63      | -1511.54              | -1106.06       | 0.0026932            | RI 01         | 1.26                | -0.16          | -4.07                   | -2.97             |              |              |  |  |  |
| 1/25/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |           112014 |      204 | 116.76       | 3.992  | 466.11           | -60.63      | -1511.54              | -1106.06       | 0.0050669            | RI 02         | 2.36                | -0.31          | -7.66                   | -5.61             |              |              |  |  |  |
| 1/25/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |            42013 |      400 | -1.59        | 1.1006 | -1.75            | 0           | 0                     | -1.75          | 0.0050669            | RI 02         | -0.01               | 0              | 0                       | -0.01             |              |              |  |  |  |
| 1/25/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |           112014 |      400 | 3380.17      | 0.6214 | 2100.4           | 0           | 0                     | 2100.4         | 0.0026932            | RI 01         | 5.66                | 0              | 0                       | 5.66              |              |              |  |  |  |
| 1/25/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |           112014 |      400 | 3380.17      | 0.6214 | 2100.4           | 0           | 0                     | 2100.4         | 0.0050669            | RI 02         | 10.64               | 0              | 0                       | 10.64             |              |              |  |  |  |
+------------+----------+-------------+-------------------------------+------------------+----------+--------------+--------+------------------+-------------+-----------------------+----------------+----------------------+---------------+---------------------+----------------+-------------------------+-------------------+--------------+--------------+--+--+--+

EXPECTED RESULT 

+------------+----------+-------------+-------------------------------+------------------+----------+--------------+--------+------------------+-------------+-----------------------+----------------+----------------------+---------------+---------------------+----------------+-------------------------+-------------------+--------------+--------------+--+--+--+
|  CheckDate |  PropNum |  PropSeqNum |            PropName           |  ProductionMonth |  Product |  LeaseVolume |  Price |  LeaseGrossValue |  LeaseTaxes |  LeaseOtherDeductions |  LeaseNetValue |  DisbursementDecimal |  InterestType |  InterestGrossValue |  InterestTaxes |  IntrestOtherDeductions |  InterestNetValue |  RecordCount |  CheckAmount |  |  |  |
+------------+----------+-------------+-------------------------------+------------------+----------+--------------+--------+------------------+-------------+-----------------------+----------------+----------------------+---------------+---------------------+----------------+-------------------------+-------------------+--------------+--------------+--+--+--+
| 1/25/2015  |   100004 |          25 | BEAVER LODGE DEVON UT TR-0025 |           122014 |      100 | 774.96       | 51.93  | 40243.64         | -4628.03    | 0                     | 35615.61       | 0.0026932            | RI 01         | 108.38              | -12.46         | 0                       | 95.92             |              |              |  |  |  |
| 1/25/2015  |   100004 |          25 | BEAVER LODGE DEVON UT TR-0025 |           122014 |      100 | 0            | 0      | 0                | 0           | 0                     | 0              | 0.0050669            | RI 02         | 203.91              | -23.45         | 0                       | 180.46            |              |              |  |  |  |
| 1/31/2015  |   100004 |          25 | BEAVER LODGE DEVON UT TR-0026 |                  |      100 | 774.96       | 51.93  | 40243.64         | -4628.03    | 0                     | 35615.61       |                      |               | 312.29              | -35.91         | 0                       | 276.38            |              |              |  |  |  |
| 1/25/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |            42013 |      204 | -0.27        | 4.037  | -1.09            | -0.11       | 0.13                  | -1.07          | 0.0026932            | RI 01         | -0.01               | 0              | 0                       | -0.01             |              |              |  |  |  |
| 1/25/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |           112014 |      204 | 116.76       | 3.992  | 466.11           | -60.63      | -1511.54              | -1106.06       | 0.0026932            | RI 01         | 1.26                | -0.16          | -4.07                   | -2.97             |              |              |  |  |  |
| 1/25/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |           112014 |      204 | 0            | 0      | 0                | 0           | 0                     | 0              | 0.0050669            | RI 02         | 2.36                | -0.31          | -7.66                   | -5.61             |              |              |  |  |  |
| 1/31/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |                  |      204 | 116.49       | 8.029  | 465.02           | -60.74      | -1511.41              | -1107.13       |                      |               | 3.61                | -0.47          | -11.73                  | -8.59             |              |              |  |  |  |
| 1/25/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |            42013 |      400 | -1.59        | 1.1006 | -1.75            | 0           | 0                     | -1.75          | 0.0050669            | RI 02         | -0.01               | 0              | 0                       | -0.01             |              |              |  |  |  |
| 1/25/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |           112014 |      400 | 3380.17      | 0.6214 | 2100.4           | 0           | 0                     | 2100.4         | 0.0026932            | RI 01         | 5.66                | 0              | 0                       | 5.66              |              |              |  |  |  |
| 1/25/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |           112014 |      400 | 0            | 0      | 0                | 0           | 0                     | 0              | 0.0050669            | RI 02         | 10.64               | 0              | 0                       | 10.64             |              |              |  |  |  |
| 1/31/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |                  |      400 | 3378.58      | 1.722  | 2098.65          | 0           | 0                     | 2098.65        |                      |               | 16.29               | 0              | 0                       | 16.29             |              |              |  |  |  |
+------------+----------+-------------+-------------------------------+------------------+----------+--------------+--------+------------------+-------------+-----------------------+----------------+----------------------+---------------+---------------------+----------------+-------------------------+-------------------+--------------+--------------+--+--+--+

ACTUAL RESULTS (after EraseDuplicates Procedure)

+-----+-----------------------+----------+-------------+-------------------------------+------------------+----------+--------------+--------+------------------+-------------+-----------------------+----------------+----------------------+---------------+---------------------+----------------+-------------------------+-------------------+--------------+--------------+--+--+--+
| Id  |       CheckDate       |  PropNum |  PropSeqNum |            PropName           |  ProductionMonth |  Product |  LeaseVolume |  Price |  LeaseGrossValue |  LeaseTaxes |  LeaseOtherDeductions |  LeaseNetValue |  DisbursementDecimal |  InterestType |  InterestGrossValue |  InterestTaxes |  IntrestOtherDeductions |  InterestNetValue |  RecordCount |  CheckAmount |  |  |  |
+-----+-----------------------+----------+-------------+-------------------------------+------------------+----------+--------------+--------+------------------+-------------+-----------------------+----------------+----------------------+---------------+---------------------+----------------+-------------------------+-------------------+--------------+--------------+--+--+--+
| 464 | 1/1/0001 12:00:00 AM  | NULL     | NULL        |                               | NULL             | NULL     | NULL         | NULL   | NULL             | NULL        | NULL                  | NULL           | NULL                 |               | NULL                | NULL           | NULL                    | NULL              |              |              |  |  |  |
| 926 | 1/1/0001 12:00:00 AM  | NULL     | NULL        |                               | NULL             | NULL     | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | NULL                 |               | NULL                | NULL           | NULL                    | NULL              |              |              |  |  |  |
| 465 | 1/25/2015 12:00:00 AM | 100004   | 25          | BEAVER LODGE DEVON UT TR-0025 | 122014           | 100      | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | 0.0050669000         | RI 02         | 203.91              | -23.45         | 0.00                    | 180.46            |              |              |  |  |  |
|   1 | 1/25/2015 12:00:00 AM | 100004   | 25          | BEAVER LODGE DEVON UT TR-0025 | 122014           | 100      | 774.96       | 51.93  | 40243.64         | -4628.03    | 0.00                  | 35615.61       | 0.0026932000         | RI 01         | 108.38              | -12.46         | 0.00                    | 95.92             |              |              |  |  |  |
|   2 | 1/25/2015 12:00:00 AM | 100004   | 25          | BEAVER LODGE DEVON UT TR-0025 | 122014           | 100      | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | 0.0050669000         | RI 02         | 203.91              | -23.45         | 0.00                    | 180.46            |              |              |  |  |  |
|  29 | 1/25/2015 12:00:00 AM | 100004   | 25          | BEAVER LODGE DEVON UT TR-0025 | 122014           | 100      | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | 0.0026932000         | RI 01         | 108.38              | -12.46         | 0.00                    | 95.92             |              |              |  |  |  |
|  44 | 1/25/2015 12:00:00 AM | 100004   | 40111       | BEAVER LODGE DEVON UT TR-0025 | 42013            | 204      | -0.27        | 4.03   | -1.09            | -0.11       | 0.13                  | -1.07          | 0.0026932000         | RI 01         | -0.01               | 0.00           | 0.00                    | -0.01             |              |              |  |  |  |
|  46 | 1/25/2015 12:00:00 AM | 100004   | 40111       | BEAVER LODGE DEVON UT TR-0025 | 112014           | 204      | 116.76       | 3.99   | 466.11           | -60.63      | -1511.54              | -1106.06       | 0.0026932000         | RI 01         | 1.26                | -0.16          | -4.07                   | -2.97             |              |              |  |  |  |
|  47 | 1/25/2015 12:00:00 AM | 100004   | 40111       | BEAVER LODGE DEVON UT TR-0025 | 112014           | 204      | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | 0.0050669000         | RI 02         | 2.36                | -0.31          | -7.66                   | -5.61             |              |              |  |  |  |
| 506 | 1/25/2015 12:00:00 AM | 100004   | 40111       | BEAVER LODGE DEVON UT TR-0025 | 42013            | 204      | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | 0.0026932000         | RI 01         | -0.01               | 0.00           | 0.00                    | -0.01             |              |              |  |  |  |
| 508 | 1/25/2015 12:00:00 AM | 100004   | 40111       | BEAVER LODGE DEVON UT TR-0025 | 112014           | 204      | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | 0.0026932000         | RI 01         | 1.26                | -0.16          | -4.07                   | -2.97             |              |              |  |  |  |
| 509 | 1/25/2015 12:00:00 AM | 100004   | 40111       | BEAVER LODGE DEVON UT TR-0025 | 112014           | 204      | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | 0.0050669000         | RI 02         | 2.36                | -0.31          | -7.66                   | -5.61             |              |              |  |  |  |
| 510 | 1/25/2015 12:00:00 AM | 100004   | 40111       | BEAVER LODGE DEVON UT TR-0025 | 112014           | 400      | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | 0.0026932000         | RI 01         | 5.66                | 0.00           | 0.00                    | 5.66              |              |              |  |  |  |
| 511 | 1/25/2015 12:00:00 AM | 100004   | 40111       | BEAVER LODGE DEVON UT TR-0025 | 112014           | 400      | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | 0.0050669000         | RI 02         | 10.64               | 0.00           | 0.00                    | 10.64             |              |              |  |  |  |
| 507 | 1/25/2015 12:00:00 AM | 100004   | 40111       | BEAVER LODGE DEVON UT TR-0025 | 42013            | 400      | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | 0.0050669000         | RI 02         | -0.01               | 0.00           | 0.00                    | -0.01             |              |              |  |  |  |
|  48 | 1/25/2015 12:00:00 AM | 100004   | 40111       | BEAVER LODGE DEVON UT TR-0025 | 112014           | 400      | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | 0.0026932000         | RI 01         | 5.66                | 0.00           | 0.00                    | 5.66              |              |              |  |  |  |
|  49 | 1/25/2015 12:00:00 AM | 100004   | 40111       | BEAVER LODGE DEVON UT TR-0025 | 112014           | 400      | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | 0.0050669000         | RI 02         | 10.64               | 0.00           | 0.00                    | 10.64             |              |              |  |  |  |
|  45 | 1/25/2015 12:00:00 AM | 100004   | 40111       | BEAVER LODGE DEVON UT TR-0025 | 42013            | 400      | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | 0.0050669000         | RI 02         | -0.01               | 0.00           | 0.00                    | -0.01             |              |              |  |  |  |
+-----+-----------------------+----------+-------------+-------------------------------+------------------+----------+--------------+--------+------------------+-------------+-----------------------+----------------+----------------------+---------------+---------------------+----------------+-------------------------+-------------------+--------------+--------------+--+--+--+
Michael
  • 59
  • 4

2 Answers2

1

Can we just do it all at once?

create procedure [dbo].[dedup_and_calc] (
    @startMonth int
  , @endMonth int
  , @tvp dbo.tvp readonly 
) as
begin;
  set nocount on;

  declare @date date = (
    select eomonth(max(checkdate))
    from @tvp
    where month(checkdate) >= @startmonth 
      and month(checkdate) <= @endmonth
      );
  /* using row_number() to zero out using rn > 1 in query below */
  ;with cte as (
    select *
     , rn = row_number() over (
         partition by CheckDate, PropNum, PropSeqNum, PropName, ProductionMonth, Product
         order by Header /* There is no Id from @tvp ?*/
         ) 
         /* artifically increase rownumber for Product >= 400 */
         + case when Product >= 400 then 1 else 0 end 
    from @tvp
  )
  insert into RawDataTable(
      CheckDate
    , PropNum
    , PropSeqNum
    , PropName
    , ProductionMonth
    , Product
    , LeaseVolume
    , Price
    , LeaseGrossValue
    , LeaseTaxes
    , LeaseOtherDeductions
    , LeaseNetValue
    , DisbursementDecimal
    , InterestType
    , InterestGrossValue
    , InterestTaxes
    , IntrestOtherDeductions
    , InterestNetValue
  )
  select
       CheckDate             
     , PropNum
     , PropSeqNum            
     , PropName
     , ProductionMonth       
     , Product
     , LeaseVolume           = case when rn =1 then LeaseVolume else 0 end
     , Price                 = case when rn =1 then Price else 0 end
     , LeaseGrossValue       = case when rn =1 then LeaseGrossValue else 0 end
     , LeaseTaxes            = case when rn =1 then LeaseTaxes else 0 end
     , LeaseOtherDeductions  = case when rn =1 then LeaseOtherDeductions else 0 end
     , LeaseNetValue         = case when rn =1 then LeaseNetValue else 0 end
     , DisbursementDecimal   
     , InterestType          
     , InterestGrossValue    = InterestGrossValue
     , InterestTaxes         = InterestTaxes
     , IntrestOtherDeductions= IntrestOtherDeductions
     , InterestNetValue      = InterestNetValue
    from cte 
    where month(CheckDate) >= @startMonth 
      and month(CheckDate) <= @endMonth
  union all 
  select
     CheckDate             = @date
   , PropNum
   , PropSeqNum            = null
   , PropName
   , ProductionMonth       = null
   , Product
   , LeaseVolume           = sum(case when rn =1 then LeaseVolume else null end)
   , Price                 = sum(case when rn =1 then Price else null end)
   , LeaseGrossValue       = sum(case when rn =1 then LeaseGrossValue else null end)
   , LeaseTaxes            = sum(case when rn =1 then LeaseTaxes else null end)
   , LeaseOtherDeductions  = sum(case when rn =1 then LeaseOtherDeductions else null end)
   , LeaseNetValue         = sum(case when rn =1 then LeaseNetValue else null end)
   , DisbursementDecimal   = null
   , InterestType          = null
   , InterestGrossValue    = sum(InterestGrossValue)
   , InterestTaxes         = sum(InterestTaxes)
   , IntrestOtherDeductions= sum(IntrestOtherDeductions)
   , InterestNetValue      = sum(InterestNetValue)
  from cte 
  where month(CheckDate) >= @startMonth 
    and month(CheckDate) <= @endMonth
  group by PropNum, PropName, Product 
end;
go

test: 
CREATE TABLE [dbo].[RawDataTable] (
[Id]                     INT             IDENTITY (1, 1) NOT NULL,
[CheckDate]              DATE            NULL,
[PropNum]                INT             NOT NULL,
[PropSeqNum]             INT             NULL,
[PropName]               NVARCHAR (100)  NOT NULL,
[ProductionMonth]        INT             NULL,
[Product]                INT             NOT NULL,
[LeaseVolume]            DECIMAL (18, 2) NOT NULL,
[Price]                  DECIMAL (18, 2) NOT NULL,
[LeaseGrossValue]        DECIMAL (18, 2) NOT NULL,
[LeaseTaxes]             DECIMAL (18, 2) NULL,
[LeaseOtherDeductions]   DECIMAL (18, 2) NOT NULL,
[LeaseNetValue]          DECIMAL (18, 2) NOT NULL,
[DisbursementDecimal]    DECIMAL (18)    NULL,
[InterestType]           NVARCHAR (10)   NULL,
[InterestGrossValue]     DECIMAL (18, 2) NOT NULL,
[InterestTaxes]          DECIMAL (18, 2) NOT NULL,
[IntrestOtherDeductions] DECIMAL (18, 2) NOT NULL,
[InterestNetValue]       DECIMAL (18, 2) NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);
go
create type dbo.tvp as table (
    Header                  int
  , OwnerNumber             int
  , CheckNum                varchar(32)
  , CheckDate               date
  , PropNum                 int
  , PropSeqNum              int
  , PropName                varchar(64)
  , ProductionMonth         int
  , Product                 int
  , LeaseVolume             decimal (18, 2)
  , Price                   decimal (18, 2)
  , LeaseGrossValue         decimal (18, 2)
  , LeaseTaxes              decimal (18, 2)
  , LeaseOtherDeductions    decimal (18, 2)
  , LeaseNetValue           decimal (18, 2)
  , DisbursementDecimal     decimal (18, 2)
  , InterestType            varchar(10)
  , InterestGrossValue      decimal (18, 2)
  , InterestTaxes           decimal (18, 2)
  , IntrestOtherDeductions  decimal (18, 2)
  , InterestNetValue        decimal (18, 2)
  --, RecordCount             decimal (18, 2)
  --, CheckAmount             decimal (18, 2)
)

go
create procedure [dbo].[dedup_and_calc] (
    @startMonth int
  , @endMonth int
  , @tvp dbo.tvp readonly 
) as
begin;
  set nocount on;
  declare @date date = (
    select NextMonthStart = dateadd(day,-1,dateadd(month, datediff(month, 0,max(checkdate))+1, 0))
    from @tvp
    where month(checkdate) >= @startmonth 
      and month(checkdate) <= @endmonth
      );
  /* using row_number() to zero out using rn > 1 in query below */
  ;with cte as (
    select *
     , rn = row_number() over (
         partition by CheckDate, PropNum, PropSeqNum, PropName, ProductionMonth, Product
         order by Header
         ) 
         /* artifically increase rownumber for Product >= 400 */
         + case when Product >= 400 then 1 else 0 end 
    from @tvp
  )
  insert into RawDataTable(
      CheckDate
    , PropNum
    , PropSeqNum
    , PropName
    , ProductionMonth
    , Product
    , LeaseVolume
    , Price
    , LeaseGrossValue
    , LeaseTaxes
    , LeaseOtherDeductions
    , LeaseNetValue
    , DisbursementDecimal
    , InterestType
    , InterestGrossValue
    , InterestTaxes
    , IntrestOtherDeductions
    , InterestNetValue
  )
  select
       CheckDate             
     , PropNum
     , PropSeqNum            
     , PropName
     , ProductionMonth       
     , Product
     , LeaseVolume           = case when rn =1 then LeaseVolume else 0 end
     , Price                 = case when rn =1 then Price else 0 end
     , LeaseGrossValue       = case when rn =1 then LeaseGrossValue else 0 end
     , LeaseTaxes            = case when rn =1 then LeaseTaxes else 0 end
     , LeaseOtherDeductions  = case when rn =1 then LeaseOtherDeductions else 0 end
     , LeaseNetValue         = case when rn =1 then LeaseNetValue else 0 end
     , DisbursementDecimal   
     , InterestType          
     , InterestGrossValue    = InterestGrossValue
     , InterestTaxes         = InterestTaxes
     , IntrestOtherDeductions= IntrestOtherDeductions
     , InterestNetValue      = InterestNetValue
    from cte 
    where month(CheckDate) >= @startMonth 
      and month(CheckDate) <= @endMonth
  union all 
  select
     CheckDate             = @date
   , PropNum
   , PropSeqNum            = null
   , PropName
   , ProductionMonth       = null
   , Product
   , LeaseVolume           = sum(case when rn =1 then LeaseVolume else null end)
   , Price                 = sum(case when rn =1 then Price else null end)
   , LeaseGrossValue       = sum(case when rn =1 then LeaseGrossValue else null end)
   , LeaseTaxes            = sum(case when rn =1 then LeaseTaxes else null end)
   , LeaseOtherDeductions  = sum(case when rn =1 then LeaseOtherDeductions else null end)
   , LeaseNetValue         = sum(case when rn =1 then LeaseNetValue else null end)
   , DisbursementDecimal   = null
   , InterestType          = null
   , InterestGrossValue    = sum(InterestGrossValue)
   , InterestTaxes         = sum(InterestTaxes)
   , IntrestOtherDeductions= sum(IntrestOtherDeductions)
   , InterestNetValue      = sum(InterestNetValue)
  from cte 
  where month(CheckDate) >= @startMonth 
    and month(CheckDate) <= @endMonth
  group by PropNum, PropName, Product 

end;
go
declare @tvp dbo.tvp
insert into @tvp values
  ('1','13280701','E008613928','1/25/2015','100004','25','BEAVER LODGE DEVON UT TR-0025','122014','100','774.96','51.93','40243.64','-4628.03','0','35615.61','0.0026932','RI 01','108.38','-12.46','0','95.92')
, ('1','13280701','E008613928','1/25/2015','100004','25','BEAVER LODGE DEVON UT TR-0025','122014','100','774.96','51.93','40243.64','-4628.03','0','35615.61','0.0050669','RI 02','203.91','-23.45','0','180.46')
exec [dbo].[dedup_and_calc] 1,3, @tvp 
select * from RawDataTable as rdt

drop table rawdatatable
drop procedure dedup_and_calc
drop type dbo.tvp

returns:

+----+------------+---------+------------+-------------------------------+-----------------+---------+-------------+-------+-----------------+------------+----------------------+---------------+---------------------+--------------+--------------------+---------------+------------------------+------------------+
| Id | CheckDate  | PropNum | PropSeqNum |           PropName            | ProductionMonth | Product | LeaseVolume | Price | LeaseGrossValue | LeaseTaxes | LeaseOtherDeductions | LeaseNetValue | DisbursementDecimal | InterestType | InterestGrossValue | InterestTaxes | IntrestOtherDeductions | InterestNetValue |
+----+------------+---------+------------+-------------------------------+-----------------+---------+-------------+-------+-----------------+------------+----------------------+---------------+---------------------+--------------+--------------------+---------------+------------------------+------------------+
|  1 | 2015-01-25 |  100004 | 25         | BEAVER LODGE DEVON UT TR-0025 | 122014          |     100 | 774.96      | 51.93 | 40243.64        | -4628.03   | 0.00                 | 35615.61      | 0                   | RI 01        | 108.38             | -12.46        | 0.00                   | 95.92            |
|  2 | 2015-01-25 |  100004 | 25         | BEAVER LODGE DEVON UT TR-0025 | 122014          |     100 | 0.00        | 0.00  | 0.00            | 0.00       | 0.00                 | 0.00          | 0                   | RI 02        | 203.91             | -23.45        | 0.00                   | 180.46           |
|  3 | 2015-01-31 |  100004 | NULL       | BEAVER LODGE DEVON UT TR-0025 | NULL            |     100 | 774.96      | 51.93 | 40243.64        | -4628.03   | 0.00                 | 35615.61      | NULL                | NULL         | 312.29             | -35.91        | 0.00                   | 276.38           |
+----+------------+---------+------------+-------------------------------+-----------------+---------+-------------+-------+-----------------+------------+----------------------+---------------+---------------------+--------------+--------------------+---------------+------------------------+------------------+
SqlZim
  • 37,248
  • 6
  • 41
  • 59
  • Hey appreciate the answer, I ran your code and I'm not seeing anything inserted into my table. – Michael Feb 20 '17 at 21:26
  • @Michael I've got it working for me, but there was no Id on your "original data" and no schema for `dbo.tvp`. – SqlZim Feb 20 '17 at 21:47
  • @Michael updated to return all 3 rows instead of just the total. -- and preserve the original `checkdate` for the non total row – SqlZim Feb 20 '17 at 21:54
  • sorry it tooks so long, I could not get this to work right for awhile. But now the problem is bigger... the propseqnums are all set to null, which I will need later, as well as the production month. Also I am not getting a results row like you are, and on top of that, I'm still ending up with duplicate rows for every single row. I'm wondering if it was not my sql queries but rather that somehow my C# code is calling the procedure more than once and that's what creating all this extra nonsense. – Michael Feb 20 '17 at 22:20
  • took out the `= null` for `propseqnum` and `production month`. easy fix. – SqlZim Feb 20 '17 at 22:38
0

Alright this is just a silly mistake on my part. I had a drag and drop event that was firing twice because I programmatically declared it in my Form_Load and it was also declared in my Form's designer. It took much longer than I care to admit to figure this out (but you can probably tell from the post time to when I submit this).

For anyone else that runs into this issue if you have something like this in your code:

AllowDrop = true;
DragEnter += new DragEventHandler(Form1_DragEnter);
DragDrop += new DragEventHandler(Form1_DragDrop);

Check your Form's designer for something similar. Try commenting it out of your code and see if it fixes it.

Also just want to say thanks to anyone who tried to help me out!

Michael
  • 59
  • 4