从SQL Server中的两个不同临时表中减去两列

I am trying to compare data from the previous year and the current year parameter. I created three temp tables called #currentyear, #previous_year, and #comparison_over_previous_year.

我试图比较上一年和当前年度参数的数据。我创建了三个名为#currentyear,#previous_year和#comparison_over_previous_year的临时表。

#currentyear data is pulling from a stored procedure and I want to pull the previous year data from the stored procedure as well. When trying to pass a DATEADD function while executing a stored procedure, T-SQL doesn't like it. For now, I passed a static data in there.

#currentyear数据从存储过程中提取,我也想从存储过程中提取上一年的数据。在尝试在执行存储过程时传递DATEADD函数时,T-SQL不喜欢它。现在,我在那里传递了静态数据。

I want to subtract the Money field from #currentyear temp table and the #previous_year temp table but I am having a tough time.

我想从#currentyear临时表和#previous_year临时表中减去Money字段,但我很难过。

My code so far is below:

我的代码到目前为止如下:

IF OBJECT_ID('tempdb..#currentyear') IS NOT NULL 
    DROP TABLE #currentyear

IF OBJECT_ID('tempdb..#previousyear') IS NOT NULL 
    DROP TABLE #previousyear

IF OBJECT_ID('tempdb..#comparison_over_previous_year') IS NOT NULL 
    DROP TABLE #comparison_over_previous_year

CREATE TABLE #currentyear
(
     [Date] DATE,
     [Carrier] VARCHAR(100),
     [Direct Ceded Written Premium] MONEY,
     [begofmonth] DATE 
)

CREATE TABLE #previousyear
(
     [Date] DATE,
     [Carrier] VARCHAR(100),
     [Direct Ceded Written Premium] MONEY,
     [begofmonth] DATE 
)

CREATE TABLE #comparison_over_previous_year
(
     [Date] DATE,
     [Carrier] VARCHAR(100),
     [Direct Ceded Written Premium] MONEY,
     [begofmonth] DATE 
)

INSERT INTO #currentyear
    EXECUTE [dbo].[rpt_written_premium_extract_CURRENT_YEAR_RPT-4518] 
                  @StartDate = '20160101', @EndDate = '20161130', 
                  @ResEQCarrierCd = 'Palomar'

INSERT INTO #previousyear
    EXECUTE [dbo].[rpt_written_premium_extract_CURRENT_YEAR_RPT-4518] 
           --DATEADD(YEAR,-1,@StartDate) = '20160101'
           --DATEADD(YEAR,-1,@EndDate) = '20161130'
           @StartDate = '20150101', **--Using this static value from now as the script above is not playing nice**
           @EndDate = '20151130', @ResEQCarrierCd = 'Palomar'

INSERT INTO #comparison_over_previous_year
    SELECT 
        cy.[Date], cy.[Carrier],
        cy.[Direct Ceded Written Premium] - py.[Direct Ceded Written Premium],
        cy.[begofmonth]
    FROM
        #currentyear cy
    JOIN 
        #previousyear py ON py.[Carrier] = cy.[Carrier]

----Selects the data in order to dump into SSRS
SELECT *
FROM #comparison_over_previous_year

SO I have two problems here:

所以我有两个问题:

• one is that the DATEADD function cannot be passed through EXEC statement

  一个是DATEADD函数不能通过EXEC语句传递

• and two is that I have been trying to subtract the value of [Direct Ceded Written Premium] columns from #currentyear and the #previous_year tables. Looks like I would need to group by carrier and begofmonth. Last year may not have all carriers for each month. For example, the month of januray 2015 may not have palomar as a carrier but january 2016 would. So they would not have to be subtracted. if palomar exists on both years, then it would be subtracted. Hope that makes sense.

  两个是我一直试图从#currentyear和#previous_year表中减去[Direct Ceded Written Premium]列的值。看起来我需要按承运人分组并等待。去年可能没有每个月的所有运营商。例如,2015年januray月可能没有palomar作为承运人,但2016年1月将。所以他们不必减去。如果两年都存在palomar,那么它将被减去。希望有道理。

Here is the outcome that I want to output in SSRS when I export it to excel:

当我将它导出到excel时,我想在SSRS中输出结果:

2 个解决方案

#1

Why not just use a static variable in your code and set it where you need it?

为什么不在代码中使用静态变量并将其设置在您需要的位置?

IF OBJECT_ID('tempdb..#currentyear') IS NOT NULL 
    DROP TABLE #currentyear

IF OBJECT_ID('tempdb..#previousyear') IS NOT NULL 
    DROP TABLE #previousyear

IF OBJECT_ID('tempdb..#comparison_over_previous_year') IS NOT NULL 
    DROP TABLE #comparison_over_previous_year

CREATE TABLE #currentyear
(
     [Date] DATE,
     [Carrier] VARCHAR(100),
     [Direct Ceded Written Premium] MONEY,
     [begofmonth] DATE 
)

CREATE TABLE #previousyear
(
     [Date] DATE,
     [Carrier] VARCHAR(100),
     [Direct Ceded Written Premium] MONEY,
     [begofmonth] DATE 
)

CREATE TABLE #comparison_over_previous_year
(
     [Date] DATE,
     [Carrier] VARCHAR(100),
     [Direct Ceded Written Premium] MONEY,
     [begofmonth] DATE 
)

DECLARE @start datetime, @end datetime

--variables to hold dates in procs
set @start = '20160101'
set @end = '20161130'

INSERT INTO #currentyear
    EXECUTE [dbo].[rpt_written_premium_extract_CURRENT_YEAR_RPT-4518] 
                  @StartDate = @start, @EndDate = @end, 
                  @ResEQCarrierCd = 'Palomar'

--reset them to -1 year of themselves
set @start = DATEADD(YEAR,-1,@start)
set @end = DATEADD(YEAR,-1,@end)

INSERT INTO #previousyear
    EXECUTE [dbo].[rpt_written_premium_extract_CURRENT_YEAR_RPT-4518] 

           @StartDate = @start,
           @EndDate = @end

INSERT INTO #comparison_over_previous_year
    SELECT 
        cy.[Date], cy.[Carrier],
        cy.[Direct Ceded Written Premium] - py.[Direct Ceded Written Premium],
        cy.[begofmonth]
    FROM
        #currentyear cy
    JOIN 
        #previousyear py ON py.[Carrier] = cy.[Carrier]

----Selects the data in order to dump into SSRS
SELECT *
FROM #comparison_over_previous_y

However, your subtraction that you are trying to accomplish relies on only 1 row for each date for each carrier. You should alter your procedure to aggregate the data before you insert it, but here I am doing it into another temp table so you don't have to change the proc.

但是,您尝试完成的减法仅依赖于每个运营商的每个日期的1行。您应该在插入数据之前更改您的过程以聚合数据,但是在这里我将其放入另一个临时表中,因此您不必更改过程。

IF OBJECT_ID('tempdb..#currentyear') IS NOT NULL 
    DROP TABLE #currentyear

IF OBJECT_ID('tempdb..#previousyear') IS NOT NULL 
    DROP TABLE #previousyear

IF OBJECT_ID('tempdb..#currentyearAggregate') IS NOT NULL 
DROP TABLE #currentyearAggregate

IF OBJECT_ID('tempdb..#previousyearAggregate') IS NOT NULL 
DROP TABLE #previousyearAggregate

IF OBJECT_ID('tempdb..#comparison_over_previous_year') IS NOT NULL 
    DROP TABLE #comparison_over_previous_year

CREATE TABLE #currentyear
(
        [Date] DATE,
        [Carrier] VARCHAR(100),
        [Direct Ceded Written Premium] MONEY,
        [begofmonth] DATE 
)

--New Temp Table to hold Aggregated Data
CREATE TABLE #currentyearAggregate
(
        [Date] DATE,
        [Carrier] VARCHAR(100),
        [Direct Ceded Written Premium] MONEY,
        [begofmonth] DATE 
)

CREATE TABLE #previousyear
(
        [Date] DATE,
        [Carrier] VARCHAR(100),
        [Direct Ceded Written Premium] MONEY,
        [begofmonth] DATE 
)

--New Temp Table to hold Aggregated Data
CREATE TABLE #previousyearAggregate
(
        [Date] DATE,
        [Carrier] VARCHAR(100),
        [Direct Ceded Written Premium] MONEY,
        [begofmonth] DATE 
)

CREATE TABLE #comparison_over_previous_year
(
        [Date] DATE,
        [Carrier] VARCHAR(100),
        [Direct Ceded Written Premium] MONEY,
        [begofmonth] DATE 
)

DECLARE @start datetime, @end datetime

--variables to hold dates in procs
set @start = '20160101'
set @end = '20161130'

INSERT INTO #currentyear
    EXECUTE [dbo].[rpt_written_premium_extract_CURRENT_YEAR_RPT-4518] 
                    @StartDate = @start, @EndDate = @end, 
                    @ResEQCarrierCd = 'Palomar'

--reset them to -1 year of themselves
set @start = DATEADD(YEAR,-1,@start)
set @end = DATEADD(YEAR,-1,@end)

INSERT INTO #previousyear
    EXECUTE [dbo].[rpt_written_premium_extract_CURRENT_YEAR_RPT-4518] 
            @StartDate = @start,
            @EndDate = @end

--Sum up the premiums for each day
INSERT INTO #previousyearAggregate
    SELECT
        [Date],
        [Carrier],
        SUM([Direct Ceded Written Premium]) as [Direct Ceded Written Premium],
        [begofmonth]
    FROM 
        #previousyear
    GROUP BY
        [Date],
        [Carrier],
        [begofmonth]

--Sum up the premiums for each day
INSERT INTO #currentyearAggregate
    SELECT
        [Date],
        [Carrier],
        SUM([Direct Ceded Written Premium]) as [Direct Ceded Written Premium],
        [begofmonth]
    FROM 
        #currentyear
    GROUP BY
        [Date],
        [Carrier],
        [begofmonth]

INSERT INTO #comparison_over_previous_year
    SELECT 
        cy.[Date], 
        cy.[Carrier],
        cy.[Direct Ceded Written Premium] - py.[Direct Ceded Written Premium],
        cy.[begofmonth]
    FROM
        #currentyearAggregate cy
    JOIN 
        #previousyearAggregate py ON 
        py.[Carrier] = cy.[Carrier] 
        and py.[Date] = cy.[Date]              --Added this join condition

--Selects the data in order to dump into SSRS
SELECT *
FROM #comparison_over_previous_year

#2

Dateadd function cannot be used as parameter. you can declare a variable like this

Dateadd函数不能用作参数。你可以像这样声明一个变量

Declare @ProcStartdate datetime = DATEADD(YEAR,-1,@startdate)
Declare @ProcEnddate datetime = DATEADD(YEAR,-1,@Enddate)

INSERT INTO #previousyear
EXECUTE [dbo].[rpt_written_premium_extract_CURRENT_YEAR_RPT-4518] 

@ProcStartdate 
,@ProcEnddate 
,@ResEQCarrierCd = 'Palomar'