Taking account of weekends in SQL

[pullingteeth]

Hello, I have the following tables:

book:
book_number
...

book_event:
book_number --> book.book_number
event
timestamp


I devised a form which shows you the number of books which move from {one set of events} to {another set of events} within a specified interval, between a certain set of dates.

E.g., a user might query: "Show me the percentage of books which moved from {received, returned} to {sold} within [2 days] between 8/1/04 and 8/31/04"

The following function returns a query which selects all books which made the transition succesfully. The problem with this is that it doesn't take into consideration weekends; that is, this particular bookstore is CLOSED on weekends, and a book which is received on friday and sells on monday should be a candidate for a query with an interval of two days. So, how can I modify the following to take that into consideration?

(SQL output below)

Private Function getSet2(d1 As Date, d2 As Date, interval As Integer) As String
    getSet2 = "select distinct book_number from book where " _
        & " book_number in (select be2.book_number from book_event be2 where " & getSelectedEvents(List17) _
        & " and be2.timestamp between #" & d1 & "# and #" & (d2 + interval) & "#" _
        & " and be2.book_number in (select be1.book_number from book_event be1 where " _
        & getSelectedEvents(List15) _
        & " and be1.timestamp between #" & d1 & "# and #" & d2 & "# " _
        & " and ((be2.timestamp - be1.timestamp) <= " & interval & ")))" _
        & " order by book_number "
End Function

Private Function getSelectedEvents(ByRef list as ListBox) 
    'return SQL fragment listing all of the book_events 
End Function

(for your ease of reading, the query comes out roughly as follows):

SELECT DISTINCT distinct book_number 
FROM book 
WHERE book_number IN 
   (SELECT be2.book_number 
    FROM book_event be2 
    WHERE {getSelectedEvents(List17)}
        AND be2.timestamp between #8/1/04# AND #9/2/04#
        AND be2.book_number IN 
        (SELECT be1.book_number 
         FROM book_event be1 
         WHERE {getSelectedEvents(List15)}
           AND be1.timestamp between #8/1/04# AND #8/31/04# 
           AND ((be2.timestamp - be1.timestamp) <= 2)
         )
   )
ORDER BY book_number

Thanks!

 

[MichaelRed]

Depends on the SQL wngine you are using. It it is Jet, look at faq181-261, otherwise you need something different, as most fbengines do not support UDF's within query statements.





MichaelRed

 

[pullingteeth]

Hi MichaelRed, thanks for the tip. However, I'm trying to avoid a VBA based solution if at all possible, since that would involve extensive code-side parsing, joining, etc of large ResultSets. Possible, certainly; but the SQL alternative is much neater for this situation (since the outputs are being written to a ListBox)

 

[JonFer]

Have you looked at the DateDiff() function with the "w" (weekday) parameter? I've not used it before so I don't know if it is bug-free.

John

http://www.interautomateconsulting.com

 

[MichaelRed]

JonFer
"W", in DateDiff does stand for "WeekDay", but one needs to be quite careful of the 'definition', thereof. After all, there are seven days in a week.

PullingteethSorry, but I'm not aware of any pure SQLish way to identify specific days of the week.





MichaelRed

 

[KenReay]

Hi

It would not be extensive code

AND ((WeekDaysElapsed(be2.timestamp,be1.timestamp)) < 3

Public Function WeekDaysElapsed(Start as Date, Finish as Date) As Long
Dim l as Long
Dim s as Date
l = 0
s = Start
Do While s < Finish
If WeekDay(S) = vbSaturday or WeekDay(S) = vbSunday Then
Else
l = l +1
End if
s = DateAdd("d",1,s)
Loop
WeekDaysElapsed = L
End Sub

With a bit of thought I am sure it could be reduced in size, and make allowance for different weekend regimes


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[JonFer]

Kenreay - the loop can work like this, too:

Do While s < Finish
l = l + -1*(Not (WeekDay(s) = vbSaturday or WeekDay(s) = vbSunday))
s = s + 1
Loop


John

http://www.interautomateconsulting.com

 

[MichaelRed]

Ah, yes -but- if the issue is to NOT use vb the above are pointless. Otherwise, there are more complete soloutions in the faqs.





MichaelRed