September 13, 2010 at 12:02 pm
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
September 13, 2010 at 12:58 pm
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
September 14, 2010 at 5:34 am
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 ( ??????????
September 14, 2010 at 6:25 am
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
September 14, 2010 at 8:37 am
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
September 14, 2010 at 1:16 pm
thanks
September 14, 2010 at 2:05 pm
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