August 17, 2013 at 11:12 am
I created a procedure to filter data somehow the
if....else condition didn't bind the query
use AdventureWorks2008R2
GO
alter PROCEDURE dbo.filterbydate
(@startdate datetime,
@endDate datetime)
as
Begin
Declare
@start datetime,
@end datetime
set @start = @startdate
set @end = @endDate
IF datediff(yy, @startdate, @endDate) >1
Begin
RAISERROR ('Date range can not exceed one year',10,1)
End
Else if @startdate is null and @endDate is not null
Begin
set @startdate=dateadd(yy, -1, @endDate)
End
Else if @endDate is null and @startdate is not null
Begin
set @enddate = dateadd(yy, 1, @startdate)
end
Else if @startdate is null and @endDate is null
Begin
set @endDate = getdate()
set @startdate = dateadd(yy, -1, getdate())
end
SELECT
SH.OrderDate
,SH.SalesOrderNumber
,SD.OrderQty
,SD.LineTotal
,P.Name AS [Product]
,PS.Name AS [Subcategory]
FROM
Sales.SalesOrderHeader AS SH
INNER JOIN Sales.SalesOrderDetail AS SD
ON SH.SalesOrderID = SD.SalesOrderID
INNER JOIN Production.Product AS P
ON SD.ProductID = P.ProductID
INNER JOIN Production.ProductSubcategory AS PS
ON PS.ProductSubcategoryID = P.ProductSubcategoryID
INNER JOIN Production.ProductCategory AS PC
ON PC.ProductCategoryID = PS.ProductCategoryID
WHERE PC.Name = 'Clothing' AND SH.OnlineOrderFlag = 0
AND (SH.OrderDate BETWEEN (@start) AND (@end))
End
August 19, 2013 at 7:11 am
You're doing a lot in you If/else, couldn't you do something like:
SET @start = COALESCE(@startdate, DATEADD(yy, -1, @endDate), GETDATE());
SET @end = COALESCE(@enddate, DATEADD(yy, 1, @startdate), DATEADD(yy, 1, GETDATE()));
Seems cleaner this way, and seems to get the dates you're after. I didn't look at the rest of your query.
HTH,
Rob
August 19, 2013 at 7:19 am
robert.gerald.taylor (8/19/2013)
You're doing a lot in you If/else, couldn't you do something like:
SET @start = COALESCE(@startdate, DATEADD(yy, -1, @endDate), GETDATE());SET @end = COALESCE(@enddate, DATEADD(yy, 1, @startdate), DATEADD(yy, 1, GETDATE()));
Seems cleaner this way, and seems to get the dates you're after. I didn't look at the rest of your query.
HTH,
Rob
AND (SH.OrderDate BETWEEN (@start) AND (@end)) :rolleyes:
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 19, 2013 at 8:28 am
ChrisM@Work (8/19/2013)
robert.gerald.taylor (8/19/2013)
You're doing a lot in you If/else, couldn't you do something like:
SET @start = COALESCE(@startdate, DATEADD(yy, -1, @endDate), GETDATE());SET @end = COALESCE(@enddate, DATEADD(yy, 1, @startdate), DATEADD(yy, 1, GETDATE()));
Seems cleaner this way, and seems to get the dates you're after. I didn't look at the rest of your query.
AND (SH.OrderDate BETWEEN (@start) AND (@end)) :rolleyes:
Didn't the OP already have that part okay -- and I did mention that I hadn't bothered with the rest of the query.
Rob
August 19, 2013 at 8:50 am
robert.gerald.taylor (8/19/2013)
ChrisM@Work (8/19/2013)
robert.gerald.taylor (8/19/2013)
You're doing a lot in you If/else, couldn't you do something like:
SET @start = COALESCE(@startdate, DATEADD(yy, -1, @endDate), GETDATE());SET @end = COALESCE(@enddate, DATEADD(yy, 1, @startdate), DATEADD(yy, 1, GETDATE()));
Seems cleaner this way, and seems to get the dates you're after. I didn't look at the rest of your query.
AND (SH.OrderDate BETWEEN (@start) AND (@end)) :rolleyes:
Didn't the OP already have that part okay -- and I did mention that I hadn't bothered with the rest of the query.
Rob
The OP forgot to assign the redefined startdate and enddate to @start and @end. You picked up on that - I'm guessing OP is having coffee 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 19, 2013 at 8:56 am
ChrisM@Work (8/19/2013)
The OP forgot to assign the redefined startdate and enddate to @start and @end. You picked up on that - I'm guessing OP is having coffee 😉
Ahhh-- I see.
Thanks,
Rob
August 19, 2013 at 10:52 pm
Hi, Bob
You guys are SQL genius. IT does look much cleaner that way.
Do you think the first line should be changed to:
set @start = coalesce(@startdate, dateadd(yy,-1,@enddate), dateadd(yy,-1,getdate())
Thank you again! You've been so helpful!
August 20, 2013 at 6:36 am
MaggieW (8/19/2013)
Do you think the first line should be changed to:set @start = coalesce(@startdate, dateadd(yy,-1,@enddate), dateadd(yy,-1,getdate())
Maggie, I'm not sure whether you want your start date to be today or one year prior to today; but the easiest way to test this is to just pull out the date calc part and test that piece in SSMS until the date values are coming out how you want.
----
-- Declare local variables and initialize to act as the parameters for this test
----
DECLARE @startdate datetime = NULL, @enddate datetime = '08/20/2013';
----
-- Declare local variables
----
DECLARE @start datetime, @end datetime;
----
-- Calculate your dates
----
SET @start = COALESCE(@startdate, DATEADD(yy, -1, @endDate), GETDATE());
SET @end = COALESCE(@enddate, DATEADD(yy, 1, @startdate), DATEADD(yy, 1, GETDATE()));
----
-- Display your calculated dates
----
SELECT @start AS TestStartValue, @end AS TestEndValue;
HTH,
Rob
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply