June 1, 2010 at 7:44 pm
I've got the procedure below that takes the date values of two fields and massages them as needed, specifically capping them at a specific date if appropriate. I want to calculate the difference in the 'capped' dates for lack of a better way of describing them. The line 'DATEDIFF("d",dteReportLoadDate, dteReportUnloadDate) as ReportDuration' is throwing an error indicating that ReportDuration is invalid.
So if the start date is 5/1 and then end date is 5/31, you'd get the following results...
4/16 - 5/15 5/1 - 5/15
5/4 - 5/25 5/4 - 5/25
5/25 - 6/1 5/25 - 5/31
4/16 - 6/15 5/1 - 5/31
CREATE PROCEDURE [dbo].[sp_TrailerActivity_ByDateRange] @StartDate date, @EndDate date
AS
BEGIN
SELECT [txtTrailerDOTNumber]
,[txtShowNumber]
,[txtShowName]
,[lngTrailerActivityHeaderId]
,[txtTrailerLoadTypeDescription]
,[dteTrailerLoadDate] as dteActualLoadDate
,[dteTrailerUnloadDate] as dteActualUnloadDate
,CASE WHEN (dteTrailerLoadDate < @StartDate) THEN @StartDate ELSE dteTrailerLoadDate END AS dteReportLoadDate
,CASE WHEN (dteTrailerUnloadDate > @EndDate) THEN @EndDate ELSE dteTrailerUnloadDate END AS dteReportUnloadDate
,DATEDIFF("d",dteTrailerLoadDate, dteTrailerUnloadDate) as ActualDuration
,DATEDIFF("d",dteReportLoadDate, dteReportUnloadDate) as ReportDuration
FROM [TrailerManagementSystem].[dbo].[vw_TrailerActivity_Reporting]
WHERE
(((dteTrailerLoadDate) Between @StartDate And @EndDate))
OR
(((dteTrailerUnloadDate) Between @StartDate And @EndDate))
OR
(((dteTrailerLoadDate)<@StartDate) AND ((dteTrailerUnloadDate)>@EndDate))
END
June 1, 2010 at 9:20 pm
If you would really like some help, may I suggest that you post the DDL (CREATE Table statement(s)) for the table(s) involved, sample data (as a series of INSERT INTO statements) for the table(s) involved that is representative of your problem, and the expected (desired) output from the query (or in this case stored procedure).
For help with some of this, please read and follow the guidelines offered in the first article I reference below in my signature block regarding asking for assistance.
Follow that article and you will get many people jumping to help you and the bonus, tested code in return.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply