CID CName ActiveDateTime
1 A 2015-06-16 23:33:16.653
2 B 2015-07-16 22:33:16.653
3 C 2015-07-02 12:00:16.653
4 D 2015-07-10 01:33:16.653
5 E 2015-07-01 01:33:16.653
Current DateTime - 2015-07-01 05:30:16.653
When @Status = -1
Display ALL records
Result
1 A
2 B
3 C
4 D
5 E
When @Status = 1
Display Active records only i.e., DateTime greater than current datetime
2 B
3 C
4 D
When @Status = 0
Display InActive records only i.e., DateTime lesser than current datetime
DECLARE @status TINYINT = 1;
SELECT
CID, CNAME
FROM
Table1
WHERE
@status = -1
OR
(@status = 1 AND ActiveDateTime > GETDATE())
OR
(@status = 0 AND ActiveDateTime < GETDATE());
That is ok - the query I posted earlier is meant exactly for that situation. @Status is a local variable, not a column in the table. You would set the user input to the value of that variable and run the query.
You could even make a stored procedure with @status as a parameter and then run it using appropriate value for the parameters. If you want to do that, it would be like this:
CREATE PROCEDURE dbo.GetTable1Records
@Status TINYINT
AS
SET NOCOUNT,XACT_ABORT ON;
SELECT
CID, CNAME
FROM
Table1
WHERE
@status = -1
OR
(@status = 1 AND ActiveDateTime > GETDATE())
OR
(@status = 0 AND ActiveDateTime < GETDATE());
GO
Running the above code will create the stored proc in your database.
Then, you can use it like shown below:
EXEC dbo.GetTable1Records @status = 1;
-- or
EXEC dbo.GetTable1Records @status = 0;
-- or
EXEC dbo.GetTable1Records @status = -1;