March 19, 2009 at 7:01 pm
The WHERE Clause in my query below needs to do 2 things.
1) WHERE TodaysDate(Which is GETDATE()) > DueDate
2) WHERE DueDate BETWEEN '2009-01-13' AND '2009-03-19')
Basically, the query is used for a report and by default when the report is executed for the first time the first option above is used However, the user wants to also specify a date range which is option 2 above at any time also.
How do I structure the WHERE CLASUE to cater for both options?
SELECT OrderID, OrderItem, OrderDate, DueDate, DeliveryDate
FROM OrderDetails
WHERE ?????????????????????????????????????
March 19, 2009 at 8:17 pm
Hi,
Is this in a stored proc? If so you can add a parameter that will define what criteria to use. See below:
---- Table and sample data
drop table #OrderDetails
go
create table #OrderDetails (
OrderID int, OrderItem int, OrderDate datetime, DueDate datetime, DeliveryDate datetime
)
insert #OrderDetails
select 1, 1, '2009-02-01', '2009-02-01', '2009-02-01'
union all select 1, 1, '2009-01-01', '2009-01-01', '2009-01-01'
union all select 1, 1, '2009-01-20', '2009-01-20', '2009-01-20'
union all select 1, 1, '2009-02-01', '2009-02-01', '2009-02-01'
union all select 1, 1, '2009-02-20', '2009-02-20', '2009-02-20'
union all select 1, 1, '2009-03-01', '2009-03-01', '2009-03-01'
union all select 1, 1, '2009-03-20', '2009-03-20', '2009-03-20'
union all select 1, 1, '2009-04-01', '2009-04-01', '2009-04-01'
union all select 1, 1, '2009-04-20', '2009-04-20', '2009-04-20'
union all select 1, 1, '2009-05-01', '2009-05-01', '2009-05-01'
-- First query
SELECT OrderID, OrderItem, OrderDate, DueDate, DeliveryDate
FROM #OrderDetails
WHERE GETDATE() > DueDate
-- Second query
SELECT OrderID, OrderItem, OrderDate, DueDate, DeliveryDate
FROM #OrderDetails
WHERE DueDate >= '2009-01-13' AND DueDate < '2009-03-19'
-- how do we allow the user to choose which one? Use a bit value parameter!
declare @CompareTodaysDate as bit
set @CompareTodaysDate = 0
SELECT OrderID, OrderItem, OrderDate, DueDate, DeliveryDate
FROM #OrderDetails
WHERE (GETDATE() > DueDate AND @CompareTodaysDate = 1)
OR (DueDate >= '2009-01-13' AND DueDate < '2009-03-19' AND @CompareTodaysDate = 0)
Bevan
March 19, 2009 at 8:23 pm
I was just re-reading OP. If you have start and end date parameters you could just check if they are null...
-- Or you could use null as the default values...
declare @startdate as datetime
declare @enddate as datetime
set @startdate = null
set @enddate = null
SELECT OrderID, OrderItem, OrderDate, DueDate, DeliveryDate
FROM #OrderDetails
WHERE (GETDATE() > DueDate AND @startdate is null AND @enddate is null)
OR (DueDate >= @startdate AND DueDate < @enddate)
set @startdate = '2009-01-13'
set @enddate = '2009-03-19'
SELECT OrderID, OrderItem, OrderDate, DueDate, DeliveryDate
FROM #OrderDetails
WHERE (GETDATE() > DueDate AND @startdate is null)
OR (DueDate >= @startdate AND DueDate < @enddate)
March 19, 2009 at 11:20 pm
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply