Prev: Check Contraint in Stored procedure
Next: Count Deleted from Output Trick?
From: Bogdan on 24 Feb 2010 11:58

"Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> wrote in message
news:4B844713.34AB408(a)xs4all.nl...
> Gert-Jan Strik wrote:
>>
>> Bogdan wrote:
>> >
>> > Hi,
>> >
>> > I have a table with the following columns:
>> > Date smalldatetime
>> > DayOfWeek int
>> > [...]
>> >
>> > Both, Date and DayOfWeek can be NULL but only one of them can be
>> > non-NULL.
>> > DayOfWeek, if not NULL, can have a value 0 - 6 (0 = Sunday).
>> >
>> > When given a period (i.e. date range) I need to select rows that
>> > satisfy the
>> > following conditions:
>> > 1) both, Date and DayOfWeek are NULL
>> > or
>> > 2) if Date is not NULL then it must fall within the period
>> > or
>> > 3) if DayOfWeek is not NULL then it must be included in the period
>> >
>> > Could someone please help me with the query?
>> >
>> > Thanks,
>> > Bogdan
>>
>> Here is another solution:
>>
>> SELECT "Date", DayOfWeek
>> FROM my_table
>> WHERE @startdate <= COALESCE("Date", '17530101')
>> AND @enddate >= COALESCE("Date", '99991231')
>> AND @startDOW >= COALESCE(DayOfWeek, 0)
>> AND @endDOW <= COALESCE(DayOfWeek, 6)
>>
>> --
>> Gert-Jan
>
> Correction:
>
> SELECT "Date", DayOfWeek
> FROM my_table
> WHERE @startdate <= COALESCE("Date", '99991231')
> AND @enddate >= COALESCE("Date", '17530101')
> AND @startDOW <= COALESCE(DayOfWeek, 6)
> AND @endDOW >= COALESCE(DayOfWeek, 0)
>
> --
> Gert-Jan

Thanks for the reply.

Judging from your and Plamen's responses I think I did not state the problem
clearly enough in my initial post. Sorry for that.

Anyway, I do not pass start/end days of week to the query. I can only pass
start/end dates. I could obtain start/end days of week using DATEPART() on
each date. The problem is that I don't know how to test if a given day of
week falls into to the supplied date range. For example, if the date range
defines a period between Friday and Monday (i.e. between week days 6 and 2)
then
the test above is not going to work.

Any suggestions?

Thanks again,
Bogdan



From: Plamen Ratchev on 24 Feb 2010 12:33
> I'm assuming that @start_day and @end_day are variables corresponding to
> days of week of start/end dates? Am I correct?
>

Correct, you did not provide any details so I made this assumption.

> The problem is that I can't figure out how to determine if a non-NULL
> DayOfWeek is included in a given date range.
>
> Example:
> The date range is '2010-02-27' to '2010-02-28' (Saturday to Sunday, days of
> week 6 - 0). In this example 'BETWEEN @start_day AND @end_day' in the WHERE
> clause is not going to work.
>
Try this:

SELECT [date], [DayofWeek]
FROM Foo
WHERE ([date] IS NULL
OR [date] BETWEEN @start_date AND @end_date)
AND ([DayOfWeek] IS NULL
OR [DayOfWeek] BETWEEN @start_day AND CASE WHEN @end_day < @start_day
THEN 7
ELSE @end_day
END
OR [DayOfWeek] BETWEEN CASE WHEN @end_day < @start_day
THEN 1
ELSE @start_day
END AND @end_day);


--
Plamen Ratchev
http://www.SQLStudio.com
First  |  Prev  | 
Pages: 1 2
Prev: Check Contraint in Stored procedure
Next: Count Deleted from Output Trick?