How to display dates used in stored procedure in crystal reports?

[swenri]

Hi all,

I have a stored procedure written in sql server 2008 @Fromdate and @ToDate which are variables in the stored preocedure. The SP works fine. How do i display the @Fromdate and @ToDate in the crystal report ? Below is the stored procedure that I wrote. This is extremely urgent...Thank you for the help.....


USE [DatamartDB2]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MMC_SP_BreastFeedingLDRReport]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MMC_SP_BreastFeedingLDRReport]
GO

USE [DatamartDB2]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[MMC_SP_Report]

AS

BEGIN

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @Location varchar(255)

--if (ISNULL(@Location,'')='')
begin


Declare @FromDate datetime
Declare @ToDate datetime
Declare @RptTimeFrameout VARCHAR(50)

SET @RptTimeFrameout='2' --Weekly
if @RptTimeFrameout='2' -- Weekly
Begin
select @FromDate =convert(datetime,convert(varchar(10),dateadd(dd,-7, getdate()), 101))
select @ToDate=dateadd(ss, -1, convert(datetime,convert(varchar(10), getdate(),101)))
End


SELECT
("MO_Demographics"."LastName" + ' ' + "MO_Demographics"."FirstName") AS PatientName,
("MO_Demographics".DateOfBirth) AS DOB,
"MO_Demographics"."MRN",
"MO_Demographics"."Race",

("SPDeliveryLog_Table"."GA") AS Gestational_Age,
("SPDeliveryLog_Table"."TimeOfDelivery") AS DeliveryTime,

("SPDeliveryLog_Table"."NeonateGender") AS NeonateSex,
("SPDeliveryLog_Table"."OB Formula TPAL (postpartum)") AS Obstetric_History,
("SPDeliveryLog_Table"."Delivering Providers") AS Delivering_MD_CNM_Attending_Surgeon,
("SPDeliveryLog_Table"."Assistants") AS Delivering_MD_CNM_Assitant_Surgeon,
("SPDeliveryLog_Table"."Nurses") AS Primary_Nurse_Circulating_Nurse,
("SPDeliveryLog_Table"."Type of Delivery") AS TypeofDelivery,
"SPDeliveryLog_Table"."FetalPresentation",

("MO_Neonate"."LiveBornStillBorn") AS LiveBorn,
"MO_Neonate"."BirthWeightGrams",
"SPDeliveryLog_Table"."BirthWeight(lb+oz)" AS BirthWeight,
"MO_Neonate"."Apgar1Min",
"MO_Neonate"."Apgar5Min",
"MO_Neonate"."Apgar10Min",

(select top 1 IPR_Neonate_Interventions_T_Delivery_Report_Neonate.Mom_And_Infant_Bonding
from IPR_Neonate_Interventions_T_Delivery_Report_Neonate
where MO_Demographics.SessionID = IPR_Neonate_Interventions_T_Delivery_Report_Neonate.SessionID) AS Moms_abdomen_directly,

(select top 1 IPR_General_Description_FU_Oth_Int_Del_Rep_Neo.[General_Description_f_Other_Intervention_Delivery__1_0]
from IPR_General_Description_FU_Oth_Int_Del_Rep_Neo
where MO_Demographics.SessionID = IPR_General_Description_FU_Oth_Int_Del_Rep_Neo.SessionID) AS Other,


"MO_Neonate"."InfantDisposition",

(select top 1 IPR_Feeding_Method_T.Infant_Feeding_Method
from IPR_Feeding_Method_T
where MO_Demographics.SessionID = IPR_Feeding_Method_T.SessionID) AS Feeding_Methodin_LDR

FROM
MO_Demographics
LEFT OUTER JOIN MO_Neonate
ON "MO_Demographics"."SessionID"="MO_Neonate"."sessionid"


LEFT OUTER JOIN BLSession_Extended
ON "MO_Demographics"."SessionID"= "BLSession_Extended"."sessionid"


LEFT OUTER JOIN "SPDeliveryLog_Table"
ON "MO_Demographics"."SessionID" = "SPDeliveryLog_Table"."sessionid"

LEFT OUTER JOIN "IPR_General_Description_FU_Oth_Int_Del_Rep_Neo"
ON "MO_Demographics"."SessionID" = "IPR_General_Description_FU_Oth_Int_Del_Rep_Neo"."sessionid"



WHERE SPDeliveryLog_Table.TimeOfDelivery
--BETWEEN CONVERT(datetime,ltrim(CONVERT(char(30),DATEADD(d,-7,getdate()),101))+ '00:00:00 AM')
--AND CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE(),101))+'11:59:59 PM')
BETWEEN @FromDate AND @ToDate

ORDER BY MO_Demographics.MRN,
"SPDeliveryLog_Table"."TimeOfDelivery" ASC


END


END

SET NOCOUNT OFF

SET ANSI_NULLS OFF

GO

GO



GRANT EXECUTE ON [dbo].[MMC_SP_BreastFeedingLDRReport] TO [Public]

 

[briangriffin]

Create a command object using the same data source but don't link it to anything. Since the @RptTimeFrameout value is always 'Weekly', you don't need much logic:

select convert(datetime,convert(varchar(10),dateadd(dd,-7, getdate()), 101)) as FromDate,
dateadd(ss, -1, convert(datetime,convert(varchar(10), getdate(),101))) as ToDate

Then you can use those values for display in the report. I'm guessing that as this report develops the @RptTimeFrameout variable will become an input variable so you can select different timeframes. If so, those input variables in the sp become parameters in the report, so the new command would become something like this:


declare
@FromDate datetime,
@ToDate datetime

if {?FromDate} = 'Weekly'
BEGIN
set @FromDate = convert(datetime,convert(varchar(10),dateadd(dd,-7, getdate()), 101))
set @ToDate = dateadd(ss, -1, convert(datetime,convert(varchar(10), getdate(),101)))
END

if {?FromDate} = 'Monthly'
BEGIN
set @FromDate = some other date
set @ToDate = someotherdate
END

select @FromDate as FromDate, @ToDate as ToDate

Regardless of the logic, if you can end up with a select statement that returns the two dates then you can drop them in the report anywhere.

 

[briangriffin]

You could also append @ToDate and @FromDate to the recordset being returned, then do a min or max in two Crystal formulas for display. A little more overhead but might be easier depending on where you're going.