Sql-server – SQL Query with multiple conditions on date

datetimesql server

I want to write a query with condition on dates for below table and sometimes it requires great thinking.

StartDtm                             StopDtm
2013-11-03 00:00:00.000               NULL
2013-11-05 08:00:00.000               NULL
2013-11-18 09:00:00.000               NULL
2013-11-18 08:00:00.000               NULL
2013-11-19 08:00:00.000              2013-11-26 07:59:00.000
2013-11-20 08:00:00.000              2013-11-27 07:59:00.000
2013-11-19 08:00:00.000              2013-12-19 07:59:00.000

The table shows the task start date and task end date. Start date can never be null.
Last record shows that the task starts at 19th Nov. and will end at 19th Dec. So if I pass any dates, it should show all the tasks which need to be performed on any date between passed date range.

Conditions are as below:

1.Any date or both date parameters may be null. If so all data should come.

2.If @StartDtm is null and @StopDtm is passed, then it should bring all data based on StopDtm field only but it should not bring those data whose StartDtm is greater than @StopDtm. Same for @StopDtm parameter

3.If StopDtm field is null, It should bring all data based on StartDtm field only

I tried few ways but not succeeded. I to keep condition as short as possible. If possible, then please provide multiple queries with little explanation.

My query is as below:

DECLARE @StartDtm datetime='2013-11-15'
DECLARE @StopDtm datetime='2013-11-21'

select * from #TempTbl
where  ((StartDtm<=@StartDtm OR @StartDtm IS NULL)  AND (StopDtm>=@StopDtm OR StopDtm IS NULL))
    OR((StartDtm>=@StartDtm OR @StartDtm IS NULL) AND (StopDtm>=@StopDtm AND StartDtm<= @StopDtm OR StopDtm IS NULL OR @StopDtm IS NULL))
    OR((StartDtm>=@StartDtm OR @StartDtm IS NULL) AND (StopDtm<=@StopDtm OR StopDtm IS NULL OR @StopDtm IS NULL))

Thanks in advance..
The question is already asked in StackOverflow but I cannot find how to migrate it here. So asked here again.

Created another query which seems working fine:

DECLARE @StartDtm datetime='2013-11-20'
DECLARE @StopDtm datetime='2013-11-30'

IF(@StartDtm is null)
begin
    SET @StartDtm='1900-01-01'
end
IF(@StopDtm IS NULL)
begin
    SET @StopDtm='9999-12-31'
end

select * from #TempTbl
where (startDtm<= @StartDtm and ISNULL(StopDtm,@StopDtm)>=@StopDtm )
  OR(StartDtm>=@StartDtm and ISNULL(StopDtm,@StopDtm)<=@StopDtm )
  OR(StartDtm between @StartDtm AND @StopDtm and ISNULL(StopDtm,@StopDtm)>=@StopDtm  )
  OR(StartDtm<=@StartDtm AND ISNULL(StopDtm,@StopDtm) between @StartDtm AND  @StopDtm)

Suggest if any logical error/change/improvement.

Best Answer

You can use isnull to take care of the parameters when they are null.

Something like this should do what you want.

select T.*
from #TempTbl as T
where T.StartDtm <= isnull(@StopDtm, '99991231') and 
      (T.StopDtm >= isnull(@StartDtm, '17530101') or T.StopDtm is null)

Related Solutions

Sql-server – calculate days and equally distribute remaining

hope this works for you. It meets your expected results I believe:

| TASK |      BEGIN |        END | HOURS | WEEKDAYS |             M1H |             M2H |    M3H |    M4H |    M5H |    M6H |
|------|------------|------------|-------|----------|-----------------|-----------------|--------|--------|--------|--------|
|    1 | 2013-11-05 | 2013-11-05 |     5 |        1 |               5 |          (null) | (null) | (null) | (null) | (null) |
|    2 | 2013-10-01 | 2013-11-15 |     2 |       34 |  0.647058823529 |          (null) | (null) | (null) | (null) | (null) |
|    3 | 2013-11-15 | 2013-12-31 |    80 |       33 | 26.666666666667 | 53.333333333333 | (null) | (null) | (null) | (null) |
|    4 | 2013-05-01 | 2013-12-04 |     2 |      156 |  0.269230769231 |  0.038461538462 | (null) | (null) | (null) | (null) |
|    5 | 2013-07-01 | 2013-11-27 |    15 |      108 |  2.638888888889 |          (null) | (null) | (null) | (null) | (null) |
|    6 | 2013-11-01 | 2013-12-10 |    40 |       28 |              30 |              10 | (null) | (null) | (null) | (null) |


DECLARE @periodStart date, @periodEnd date

SET @periodStart = '2013-11-01'
SET @periodEnd   = '2014-05-01'

SELECT
          [task]
        , [begin]
        , [end]
        , [hours]
        , max(task_wd) AS weekdays
        , max( CASE WHEN yrmnth = ( YEAR(@periodStart) * 100 + MONTH(@periodStart) )                                   THEN ([hours] / task_wd) * mnth_wd END ) AS m1h
        , max( CASE WHEN yrmnth = ( YEAR(dateadd(MONTH,1,@periodStart)) * 100 + MONTH(dateadd(MONTH,1,@periodStart)) ) THEN ([hours] / task_wd) * mnth_wd END ) AS m2h
        , max( CASE WHEN yrmnth = ( YEAR(dateadd(MONTH,2,@periodStart)) * 100 + MONTH(dateadd(MONTH,2,@periodStart)) ) THEN ([hours] / task_wd) * mnth_wd END ) AS m3h
        , max( CASE WHEN yrmnth = ( YEAR(dateadd(MONTH,3,@periodStart)) * 100 + MONTH(dateadd(MONTH,3,@periodStart)) ) THEN ([hours] / task_wd) * mnth_wd END ) AS m4h
        , max( CASE WHEN yrmnth = ( YEAR(dateadd(MONTH,4,@periodStart)) * 100 + MONTH(dateadd(MONTH,4,@periodStart)) ) THEN ([hours] / task_wd) * mnth_wd END ) AS m5h
        , max( CASE WHEN yrmnth = ( YEAR(dateadd(MONTH,5,@periodStart)) * 100 + MONTH(dateadd(MONTH,5,@periodStart)) ) THEN ([hours] / task_wd) * mnth_wd END ) AS m6h
FROM (
        SELECT
                  t.[task]
                , t.[begin]
                , t.[end]
                , t.[hours]
                , (year(c.d) * 100 + MONTH(c.d))                                 AS yrmnth

                , count(CASE
                         WHEN c.d >= @periodStart AND c.d < @periodEnd THEN c.d
                        END
                       ) over (partition BY t.[task], year(c.d), MONTH(c.d))     AS mnth_wd
                , count(c.d) over (partition BY t.[task]) * 1.0                  AS task_wd
        FROM Calendar AS c
        INNER JOIN tasks AS t ON c.d BETWEEN t.[begin] and t.[end]
        WHERE c.weekday = 1
        AND t.[begin] <= @periodEnd
        AND t.[end] >= @periodStart
     ) AS derived
GROUP BY
          [task]
        , [begin]
        , [end]
        , [hours]

I did this is here http://sqlfiddle.com/#!3/682ff/42 , but I also simulated your calendar table witha CTE so the code there is a bit messier.

Sql-server – Delete All Tables With Dates By Date

You should try printing your commands when they yield errors. If you issued this instead of executing it:

PRINT @cmd;

You would see that this:

WHERE ValueDate BETWEEN ' + CONVERT(VARCHAR,@b,121) + ' AND ' + CONVERT(VARCHAR,@E,121)

Yields this:

WHERE ValueDate BETWEEN 2012-07-01 AND 2014-01-25

Which, since those look to SQL Server like three integers with a couple of subtraction operators, becomes:

WHERE ValueDate BETWEEN 2004 AND 1988

Which isn't even a valid BETWEEN operation, even if it could translate those to dates.

What you should be doing is passing these as proper parameters, e.g.

SET @cmd = 'DELETE FROM dbo.DateTable -- always use SCHEMA prefix
 WHERE ValueDate BETWEEN @b AND @e;';

EXEC sp_executesql @cmd, N'@b DATE, @e DATE', @b, @e;

If any of these columns are DATETIME, not DATE, you really should not be using BETWEEN at all. Instead:

WHERE ValueDate >= @b AND ValueDate < DATEADD(DAY, 1, @e);

Please read:

As for the overall requirement of repeating this query against all date columns in the database, here is how I would do it (I've looked at the SO question you reference, and it just shows how to do this for one table):

DECLARE @b DATE = '2012-07-01', @e DATE = '2014-01-25', 
        @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
DELETE ' + QUOTENAME(s.name) 
 + '.' + QUOTENAME(t.name) + '
  WHERE ' + QUOTENAME(c.name) + ' >= @b
  AND ' + QUOTENAME(c.name) + ' < DATEADD(DAY, 1, @e);'
 FROM sys.tables AS t
 INNER JOIN sys.schemas AS s
 ON t.[schema_id] = s.[schema_id]
 INNER JOIN sys.columns AS c
 ON t.[object_id] = c.[object_id]
 WHERE c.system_type_id IN (40,42,43,58,61);
       -- or just = 40 if you're only interested in DATE columns

PRINT @sql;
-- EXEC sp_executesql @sql, N'@b DATE, @e DATE', @b, @e;

Best Answer

Related Solutions

Sql-server – calculate days and equally distribute remaining

Sql-server – Delete All Tables With Dates By Date

Related Question