substracting values

  • Below is my stored procedure that i am trying to get the value from period1 to subtract the value of period 2 to create one field.

    i need to then use this in a crystal report as a parameter

    the sp uses the date parameters to query out 2 periods by the start and end dates. the user enters one set of values for period one and another set for period 2.

    how do i get the sp to sum the entire period of values by customer then substract sum of period1 - sum of period2????

    create Procedure spActivityByRegion_TEST

    (@start_date1 datetime, @end_date1 datetime,

    @start_date2 datetime, @end_date2 datetime)

    As

    declare @startdate1 varchar(8)

    declare @enddate1 varchar(8)

    declare @date1 varchar(50)

    declare @startdate2 varchar(8)

    declare @enddate2 varchar(8)

    declare @date2 varchar(50)

    set @startdate1 = CONVERT(varchar(8), @start_date1, 112)

    set @enddate1 = CONVERT(varchar(8), @end_date1, 112)

    set @date1 = CONVERT(varchar(12), @start_date1, 101) + ' - ' + CONVERT(varchar(12), @end_date1, 101)

    set @startdate2 = CONVERT(varchar(8), @start_date2, 112)

    set @enddate2 = CONVERT(varchar(8), @end_date2, 112)

    set @date2 = CONVERT(varchar(12), @start_date2, 101) + ' - ' + CONVERT(varchar(12), @end_date2, 101)

    SELECT

    P1.period_1,

    P1.risk,

    P1.cust,

    P1.officer,

    P1.region,

    P1.dte,

    P1.sum_amnt_1,

    P2.period_2,

    P2.risk,

    P2.cust,

    P2.officer,

    P2.region,

    P2.dte,

    P2.sum_amnt_2,

    (P1.SUM_AMNT_1-P2.SUM_AMNT_2)

    FROM (/*FOR PERIOD 1*/

    select

    @date1as period_1,

    RISKas risk,

    CUSTOMER#as cust,

    OFFICERas officer,

    REGIONas region,

    DATEas dte,

    AMOUNTAS sum_amnt_1

    FROM dbo.SHARON_TESTING

    where date between @startdate1 and @enddate1)p1

    JOIN

    (

    /* For PERIOD_2 */

    select

    @date2as period_2,

    RISKas risk,

    CUSTOMER#as cust,

    OFFICERas officer,

    REGIONas region,

    DATEas dte,

    AMOUNTAS sum_amnt_2

    FROM dbo.SHARON_TESTING

    where date between @startdate1 and @enddate1)p2

    ON P1.CUST =P2.CUST

  • This should be of some help

    With period1 as (

    select

    @date1 as period_1,

    max(RISK) as risk,

    CUSTOMER# as cust,

    Max(OFFICER) as officer,

    Max(REGION) as region,

    Max(DATE) as dte,

    sum(AMOUNT) AS sum_amnt_1

    FROM dbo.SHARON_TESTING

    where date between @startdate1 and @enddate1

    Group By Customer#

    ), period2 as (

    select

    @date2 as period_2,

    max(RISK) as risk,

    CUSTOMER# as cust,

    Max(OFFICER) as officer,

    Max(REGION) as region,

    Max(DATE) as dte,

    sum(AMOUNT) AS sum_amnt_2

    FROM dbo.SHARON_TESTING

    where date between @startdate1 and @enddate1

    Group By Customer#

    )

    SELECT

    P1.period_1,

    P1.risk,

    P1.cust,

    P1.officer,

    P1.region,

    P1.dte,

    P1.sum_amnt_1,

    P2.period_2,

    P2.risk,

    P2.cust,

    P2.officer,

    P2.region,

    P2.dte,

    P2.sum_amnt_2,

    (P1.SUM_AMNT_1-P2.SUM_AMNT_2)

    FROM Period1 p1

    Inner JOIN Period2 p2

    ON P1.CUST =P2.CUST

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • this is a different query then i am use to using....

    i know need to declare my dates, where do i place them?

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    go

    ALTER Procedure [dbo].[spActivityByRegion_TEST] (@start_date1 datetime, @end_date1 datetime,

    @start_date2 datetime, @end_date2 datetime)

    As

    declare @startdate1 varchar(8)

    declare @enddate1 varchar(8)

    declare @date1 varchar(50)

    declare @startdate2 varchar(8)

    declare @enddate2 varchar(8)

    declare @date2 varchar(50)

    set @startdate1 = CONVERT(varchar(8), @start_date1, 112)

    set @enddate1 = CONVERT(varchar(8), @end_date1, 112)

    set @date1 = CONVERT(varchar(12), @start_date1, 101) + ' - ' + CONVERT(varchar(12), @end_date1, 101)

    set @startdate2 = CONVERT(varchar(8), @start_date2, 112)

    set @enddate2 = CONVERT(varchar(8), @end_date2, 112)

    set @date2 = CONVERT(varchar(12), @start_date2, 101) + ' - ' + CONVERT(varchar(12), @end_date2, 101)

    With period1 as ( ??????????

  • i have this as my sp so far... i am getting the following errors. what is incorrect??

    Msg 319, Level 15, State 1, Line 18

    Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

    Msg 102, Level 15, State 1, Line 30

    Incorrect syntax near ','.[/color]

    declare @startdate1 varchar(8)

    declare @enddate1 varchar(8)

    declare @date1 varchar(50)

    declare @startdate2 varchar(8)

    declare @enddate2 varchar(8)

    declare @date2 varchar(50)

    set @startdate1 = CONVERT(varchar(8), '2010-01-01 00:00:00:000', 112)

    set @enddate1 = CONVERT(varchar(8), '2010-01-31 00:00:00:000', 112)

    set @date1 = CONVERT(varchar(12), '2010-01-01 00:00:00:000', 101) + ' - ' + CONVERT(varchar(12), '2010-01-31 00:00:00:000', 101)

    set @startdate2 = CONVERT(varchar(8), '2010-04-01 00:00:00:000', 112)

    set @enddate2 = CONVERT(varchar(8), '2010-04-30 00:00:00:000', 112)

    set @date2 = CONVERT(varchar(12), '2010-04-01 00:00:00:000', 101) + ' - ' + CONVERT(varchar(12), '2010-04-30 00:00:00:000', 101)

    with period1 as (

    select

    @date1 as period_1,

    max(RISK) as risk,

    CUSTOMER# as cust,

    Max(OFFICER) as officer,

    Max(REGION) as region,

    Max(DATE) as dte,

    sum(AMOUNT) AS sum_amnt_1

    FROM dbo.SHARON_TESTING

    where date between @startdate1 and @enddate1

    Group By Customer#

    ), period2 as (

    select

    @date2 as period_2,

    max(RISK) as risk,

    CUSTOMER# as cust,

    Max(OFFICER) as officer,

    Max(REGION) as region,

    Max(DATE) as dte,

    sum(AMOUNT) AS sum_amnt_2

    FROM dbo.SHARON_TESTING

    where date between @startdate1 and @enddate1

    Group By Customer#

    )

    SELECT

    P1.period_1,

    P1.risk,

    P1.cust,

    P1.officer,

    P1.region,

    P1.dte,

    P1.sum_amnt_1,

    P2.period_2,

    P2.risk,

    P2.cust,

    P2.officer,

    P2.region,

    P2.dte,

    P2.sum_amnt_2,

    (P1.SUM_AMNT_1-P2.SUM_AMNT_2)

    FROM Period1 p1

    Inner JOIN Period2 p2

    ON P1.CUST =P2.CUST

  • sharonmtowler (9/14/2010)


    i have this as my sp so far... i am getting the following errors. what is incorrect??

    Msg 319, Level 15, State 1, Line 18

    Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

    Msg 102, Level 15, State 1, Line 30

    Incorrect syntax near ','.[/color]

    declare @startdate1 varchar(8)

    declare @enddate1 varchar(8)

    declare @date1 varchar(50)

    declare @startdate2 varchar(8)

    declare @enddate2 varchar(8)

    declare @date2 varchar(50)

    set @startdate1 = CONVERT(varchar(8), '2010-01-01 00:00:00:000', 112)

    set @enddate1 = CONVERT(varchar(8), '2010-01-31 00:00:00:000', 112)

    set @date1 = CONVERT(varchar(12), '2010-01-01 00:00:00:000', 101) + ' - ' + CONVERT(varchar(12), '2010-01-31 00:00:00:000', 101)

    set @startdate2 = CONVERT(varchar(8), '2010-04-01 00:00:00:000', 112)

    set @enddate2 = CONVERT(varchar(8), '2010-04-30 00:00:00:000', 112)

    set @date2 = CONVERT(varchar(12), '2010-04-01 00:00:00:000', 101) + ' - ' + CONVERT(varchar(12), '2010-04-30 00:00:00:000', 101)

    with period1 as (

    select

    @date1 as period_1,

    max(RISK) as risk,

    CUSTOMER# as cust,

    Max(OFFICER) as officer,

    Max(REGION) as region,

    Max(DATE) as dte,

    sum(AMOUNT) AS sum_amnt_1

    FROM dbo.SHARON_TESTING

    where date between @startdate1 and @enddate1

    Group By Customer#

    ), period2 as (

    select

    @date2 as period_2,

    max(RISK) as risk,

    CUSTOMER# as cust,

    Max(OFFICER) as officer,

    Max(REGION) as region,

    Max(DATE) as dte,

    sum(AMOUNT) AS sum_amnt_2

    FROM dbo.SHARON_TESTING

    where date between @startdate1 and @enddate1

    Group By Customer#

    )

    SELECT

    P1.period_1,

    P1.risk,

    P1.cust,

    P1.officer,

    P1.region,

    P1.dte,

    P1.sum_amnt_1,

    P2.period_2,

    P2.risk,

    P2.cust,

    P2.officer,

    P2.region,

    P2.dte,

    P2.sum_amnt_2,

    (P1.SUM_AMNT_1-P2.SUM_AMNT_2)

    FROM Period1 p1

    Inner JOIN Period2 p2

    ON P1.CUST =P2.CUST

    Place a ; (semi-colon) after you set your variables and before the "with" - like in the following snippet.

    set @startdate2 = CONVERT(varchar(8), '2010-04-01 00:00:00:000', 112)

    set @enddate2 = CONVERT(varchar(8), '2010-04-30 00:00:00:000', 112)

    set @date2 = CONVERT(varchar(12), '2010-04-01 00:00:00:000', 101) + ' - ' + CONVERT(varchar(12), '2010-04-30 00:00:00:000', 101)

    ;

    with period1 as (

    select

    @date1 as period_1,

    max(RISK) as risk,

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • thanks

  • You're welcome

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply