September 16, 2014 at 4:56 pm
I have to create a parameter that refers to the date field, called "updated_date". My parameters have three values: GT30 (greater than 30 days); LT30 (Less than 30 days) and ALL (Everything.)
What we need to get, is the records of the updated_date with the following logic:
If the parameter = GT30, pick up all of the records from GETDATE()-30. For example, today's date is September 16, 2014, parameter GT30 will pick up the records of any records that is less than 08/16/2014 and beyond...
If the parameter = LT30, pick up all the records between GETDATE() and GETDATE()-30. Use the previous example, any records from 08/17/2014 until today.
If the parameter = ALL, pick up all the records.
---------------------------------------------
This is what I have in the where condition:
Where
updated_date <=
(CASE when @GT30 THEN Getdate()-30 -- this is for greater than 30 days parameter
when @LT30 THEN --- date between getdate() and getdate()-30
Else
updated_date END) -- this is for 'ALL' parameter
I have total of 1,300 records. When run on GT30, I have 1,200 records. But I could not write the statement to pick up that 100 records at all that have the dates between today and today-30.
Guess -- I am stuck with the less than 30 days parameter. How can I write the parameter that will pick up the values from the date that I want?
Please assist. Thanks.
September 16, 2014 at 5:13 pm
DECLARE @start_date datetime
DECLARE @end_date datetime
IF @parameter = 'GT30'
BEGIN
SET @start_date = '19000101'
SET @end_date = GETDATE() - 30
END --IF
ELSE
IF @parameter = 'LT30'
BEGIN
SET @start_date = GETDATE() - 30
SET @end_date = GETDATE()
END --IF
ELSE
BEGIN
SET @start_date = '19000101'
SET @end_date = GETDATE()
END --ELSE
SELECT ...
FROM ...
WHERE
updated_date BETWEEN @start_date AND @end_date
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 16, 2014 at 5:19 pm
I think you can use boolean logic as the following:
WHERE
( @PARAMETER = 'GT30' AND [ColumnDate] <= DATEADD(DAY,-30,GETDATE()) )
OR
( @PARAMETER = 'LT30' AND [ColumnDate] BETWEEN DATEADD(DAY,-30,GETDATE()) AND GETDATE() )
OR
( @PARAMETER = 'ALL')
September 16, 2014 at 5:34 pm
where @parameter = 'All'
or (@parameter = 'GT30' and updated_date < getdate() - 30)
or (@parameter = 'LT30' and updated_date between getdate()-30 and getdate()) ;
Tom
September 16, 2014 at 9:59 pm
But those conditions within the WHERE clause could convolute the query plan and thus the performance.
Much better to set variables and have a simple and consistent WHERE clause, allowing SQL can easily generate a good query plan.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 17, 2014 at 9:18 am
Thank you very much for posting.
Everything has been implemented.
----------------
However, the team would like to add two (2) date parameters, namely @StartDate and @EndDate. They declare these dates in the variables.
They would like to be able to manually add the time frame as well.
"The logic is:
If the parameter = 'GT30' and the StartDate is Not Null and the EndDate is Null, then, [Updated_Date] <= StartDate-30"
For example, we manually entered the StartDate, 12/1/2014 and parameter = 'GT30', the records that have the [Updated_Date] Column less than 11/1/2014 will be displayed.
WHERE
( @PARAMETER = 'GT30' AND [Updated_Date] <= DATEADD(DAY,-30,GETDATE()) ) --- This is working
I tried to write the logic with this clause, it was executing but it returns all of the records.
( @PARAMETER = 'GT30' AND (@StartDate IS NOT NULL AND @EndDate IS NULL) AND [Updated_Date]<= DATEADD(DAY,-30,@StartDate) ) --- the program executed but returned all of the records
I switched the place and had this clause in the FIRST order, but it still returned me the same as using just 'GT30' parameter.
Can anyone assist in correcting the above logic? Oh.. another thing, the property of the [Updated_Date] field is the database timestamp.
If this SQL works, we will replicate it and rewrite into the Oracle SQL.
Thank you very much.
September 17, 2014 at 12:23 pm
Your query should work, but I don't think you can use timestamp as a datetime column http://msdn.microsoft.com/en-us/library/ms182776%28v=sql.105%29.aspx
As far as I know, timestamp is to store rowversion, not datetime values.
Regards
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply