I have a store proc with the following date which feed 3 different reports
1) Daily
2) Weekly
3)Monthly
but the problem is that when the 3 reports are displayed they all return the accurate data but in the display .It displays the date range for a day rather than a day for daily,month for monthly and week for weekly
SQL
CREATE PROCEDURE [dbo].[xxx} (
@p_StartDate DateTime = NULL,
@p_EndDate DateTime = NULL,
@p_UserTeam VarChar(100) = NULL
) AS
/*
*/
SET NOCOUNT ON
IF @p_StartDate IS NULL
SET @p_StartDate = CAST(CONVERT(Char(8), GETDATE()-1, 112) AS DateTime)
IF @p_EndDate IS NULL
SET @p_EndDate = CAST(CONVERT(Char(8), GETDATE()-1, 112) AS DateTime)
-- Make certain that the SP is pulling the entire end day's data only if a time is not already passed in
IF DATEPART(hh,@p_EndDate) = 0
SET @p_EndDate = CAST(CONVERT(Char(8), @p_EndDate, 112)+ ' 23:59:59' AS DateTime)
Present formula to implement the date range in crystal
'Test for dateonly state - Start time = 12:00:00 AM and End time = 11:59:59 PM. Otherwise display the time as well.
Dim dateonly as boolean
dateonly=True
Dim startHour as Number
Dim startMinute as Number
Dim startSecond as Number
Dim endHour as Number
Dim endMinute as Number
Dim endSecond as Number
startHour=Hour ({pr_HVDB_rpt_Teams_Individual;1.StartDate})
startMinute=Minute ({pr_HVDB_rpt_Teams_Individual;1.StartDate})
startSecond=Second ({pr_HVDB_rpt_Teams_Individual;1.StartDate})
endHour = Hour ({pr_HVDB_rpt_Teams_Individual;1.EndDate})
endMinute = Minute ({pr_HVDB_rpt_Teams_Individual;1.EndDate})
endSecond = Second ({pr_HVDB_rpt_Teams_Individual;1.EndDate})
If startHour <> 0 or startMinute <> 0 or startSecond <> 0 or endHour <> 23 or endMinute <> 59 or endSecond <> 59 Then
dateonly = False
End If
If dateonly = True then
Formula=ToText({pr_HVDB_rpt_Teams_Individual;1.StartDate},"MM/dd/yyyy") & " through "& ToText ({pr_HVDB_rpt_Teams_Individual;1.EndDate}, "MM/dd/yyyy")
Else
Formula=ToText({pr_HVDB_rpt_Teams_Individual;1.StartDate},"MM/dd/yyyy") & " " & ToText({pr_HVDB_rpt_Teams_Individual;1.StartDate},"hh:mm:ss tt") & " through "& ToText ({pr_HVDB_rpt_Teams_Individual;1.EndDate}, "MM/dd/yyyy") & " " & ToText ({pr_HVDB_rpt_Teams_Individual;1.EndDate}, "hh:mm:ss tt")
End If
|