Prev: Check Contraint in Stored procedure
Next: Count Deleted from Output Trick?
From: Bogdan on 23 Feb 2010 13:10
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




From: Plamen Ratchev on 23 Feb 2010 13:46
Try something like this. Note if your date values have time portion different than midnight it is better to use
half-open interval, like [date] >= @start_date AND [date] < DATEADD(DAY, 1, @end_date).

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 @end_day);

--
Plamen Ratchev
http://www.SQLStudio.com
From: Gert-Jan Strik on 23 Feb 2010 16:08
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
From: Gert-Jan Strik on 23 Feb 2010 16:22
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
From: Bogdan on 24 Feb 2010 11:08

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:fpKdnYTJ_-wavxnWnZ2dnUVZ_tGqnZ2d(a)speakeasy.net...
> Try something like this. Note if your date values have time portion
> different than midnight it is better to use half-open interval, like
> [date] >= @start_date AND [date] < DATEADD(DAY, 1, @end_date).
>
> 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 @end_day);
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com

Thanks for the reply.

I'm assuming that @start_day and @end_day are variables corresponding to
days of week of start/end dates? Am I correct?

I could get them using DATEPART(weekday, @start_date), etc.

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.

Any suggestions?

Thanks again,
Bogdan


 |  Next  |  Last
Pages: 1 2
Prev: Check Contraint in Stored procedure
Next: Count Deleted from Output Trick?