SQL Server 2000
Crystal 9.0
I'm having problems getting the following fields to link into my Crystal report:
@year, @period, @start_period_CY, @end_sun_CY, @start_period_LY, @end_sun_LY
Below is my Stored Procedure
CREATE PROCEDURE [bc].[Test_Fri]
@year int = NULL OUTPUT,
@period tinyint = NULL OUTPUT,
@start_period_CY smalldatetime = NULL OUTPUT,
@end_sun_CY smalldatetime = NULL OUTPUT,
@start_period_LY smalldatetime = NULL OUTPUT,
@end_sun_LY smalldatetime = NULL OUTPUT
AS
EXECUTE [bc].[sp_Period_To_Week_Dates_FRI] @year OUTPUT, @Period OUTPUT, @start_period_CY OUTPUT, @end_sun_CY OUTPUT, @start_period_LY OUTPUT, @end_sun_LY OUTPUT
SELECT *
FROM mc_cost_sum
WHERE (insertion_date BETWEEN @start_period_CY AND @end_sun_CY)
OR (insertion_date BETWEEN @start_period_LY AND @end_sun_LY)
GO
---------------------------------------------------------
Here's the Stored Procedure that I'm calling above:
-- Outputs current fiscal year.
-- Outputs current period.
-- Outputs start date of current period.
-- Outputs date of next Sunday.
-- Outputs start date of same period last fiscal year.
-- Outputs date of next Sunday last fiscal year.
CREATE PROCEDURE [bc].[sp_Period_To_Week_Dates_FRI]
@year int = NULL OUTPUT,
@period tinyint = NULL OUTPUT,
@start_period_CY smalldatetime = NULL OUTPUT,
@end_sun_CY smalldatetime = NULL OUTPUT,
@start_period_LY smalldatetime = NULL OUTPUT,
@end_sun_LY smalldatetime = NULL OUTPUT
AS
DECLARE @Current_Date smalldatetime,
@Delta_Days int
-- Get current date in ccyymmdd format without time.
SET @Current_Date = CONVERT(varchar(8), GETDATE(), 112)
-- Get fiscal year and period for current date.
SELECT @Fiscal_Year = MIN(period_year),
@Period = MIN(period)
FROM mc_period_dates
WHERE calendar_date = @Current_Date
-- Get start date of current period.
SELECT @Start_Period_CY = MIN(calendar_date)
FROM mc_period_dates
WHERE period_year = @Fiscal_Year
AND period = @Period
-- Get date of next Sunday.
SET @End_Sun_CY = @Current_Date
WHILE (DATENAME(weekday, @End_Sun_CY) <> 'Sunday')
SET @End_Sun_CY = DATEADD(Day, 1, @End_Sun_CY)
-- Get start date of same period last fiscal yesr.
SELECT @Start_Period_LY = MIN(calendar_date)
FROM mc_period_dates
WHERE period_year = @Fiscal_Year - 1
AND period = @Period
-- Get number of days between start of current period and next Sunday.
SET @Delta_Days = DATEDIFF(Day, @Start_Period_CY, @End_Sun_CY)
-- Get date of next Sunday for last fiscal year.
SET @End_Sun_LY = DATEADD(Day, @Delta_Days, @Start_Period_LY)
RETURN
GO
When I create my Crystal Report it return no dates. What I'm trying to accomplish is to not have to enter any parameters. Is there a way that Crystal can pick up the dates in the Stored Procedure (SP) without any manual entering of date parameters? When I run the SP in Query Analyzer I can see all the dates. Hopefully, someone can help me. Thanks in advance for your help.
Crystal 9.0
I'm having problems getting the following fields to link into my Crystal report:
@year, @period, @start_period_CY, @end_sun_CY, @start_period_LY, @end_sun_LY
Below is my Stored Procedure
CREATE PROCEDURE [bc].[Test_Fri]
@year int = NULL OUTPUT,
@period tinyint = NULL OUTPUT,
@start_period_CY smalldatetime = NULL OUTPUT,
@end_sun_CY smalldatetime = NULL OUTPUT,
@start_period_LY smalldatetime = NULL OUTPUT,
@end_sun_LY smalldatetime = NULL OUTPUT
AS
EXECUTE [bc].[sp_Period_To_Week_Dates_FRI] @year OUTPUT, @Period OUTPUT, @start_period_CY OUTPUT, @end_sun_CY OUTPUT, @start_period_LY OUTPUT, @end_sun_LY OUTPUT
SELECT *
FROM mc_cost_sum
WHERE (insertion_date BETWEEN @start_period_CY AND @end_sun_CY)
OR (insertion_date BETWEEN @start_period_LY AND @end_sun_LY)
GO
---------------------------------------------------------
Here's the Stored Procedure that I'm calling above:
-- Outputs current fiscal year.
-- Outputs current period.
-- Outputs start date of current period.
-- Outputs date of next Sunday.
-- Outputs start date of same period last fiscal year.
-- Outputs date of next Sunday last fiscal year.
CREATE PROCEDURE [bc].[sp_Period_To_Week_Dates_FRI]
@year int = NULL OUTPUT,
@period tinyint = NULL OUTPUT,
@start_period_CY smalldatetime = NULL OUTPUT,
@end_sun_CY smalldatetime = NULL OUTPUT,
@start_period_LY smalldatetime = NULL OUTPUT,
@end_sun_LY smalldatetime = NULL OUTPUT
AS
DECLARE @Current_Date smalldatetime,
@Delta_Days int
-- Get current date in ccyymmdd format without time.
SET @Current_Date = CONVERT(varchar(8), GETDATE(), 112)
-- Get fiscal year and period for current date.
SELECT @Fiscal_Year = MIN(period_year),
@Period = MIN(period)
FROM mc_period_dates
WHERE calendar_date = @Current_Date
-- Get start date of current period.
SELECT @Start_Period_CY = MIN(calendar_date)
FROM mc_period_dates
WHERE period_year = @Fiscal_Year
AND period = @Period
-- Get date of next Sunday.
SET @End_Sun_CY = @Current_Date
WHILE (DATENAME(weekday, @End_Sun_CY) <> 'Sunday')
SET @End_Sun_CY = DATEADD(Day, 1, @End_Sun_CY)
-- Get start date of same period last fiscal yesr.
SELECT @Start_Period_LY = MIN(calendar_date)
FROM mc_period_dates
WHERE period_year = @Fiscal_Year - 1
AND period = @Period
-- Get number of days between start of current period and next Sunday.
SET @Delta_Days = DATEDIFF(Day, @Start_Period_CY, @End_Sun_CY)
-- Get date of next Sunday for last fiscal year.
SET @End_Sun_LY = DATEADD(Day, @Delta_Days, @Start_Period_LY)
RETURN
GO
When I create my Crystal Report it return no dates. What I'm trying to accomplish is to not have to enter any parameters. Is there a way that Crystal can pick up the dates in the Stored Procedure (SP) without any manual entering of date parameters? When I run the SP in Query Analyzer I can see all the dates. Hopefully, someone can help me. Thanks in advance for your help.