Query conundrum

[tedsmith]

I a trying to make a query to activate a message only between certain times each day, over a certain number of days, the times being entered in a database or continuously from a given time to another time a week later.
This is an unattended shopfront type of display announcing specials only at certain times.

I have a columns for Startdate Stopdate StartTime StopTime
Case 1. I want it to show only during lunchtime only for the next week I would enter 10/8/11 17/8/11 12:00 13:00

Case 2. I might also want to show it continuously from 12:00 on 10/8/11 then stop showing at 13:00 or 17/8/11 which are the effectively the same dates.

My problem is how to construct the WHERE clause for the query that will work for both situations.

Other than have an extra column "Once a Day/Continuous" is there any other way?

 

[gmmastros]

In my opinion, if you want to show during lunch time for 7 different days, then you should have 7 different rows in the table.

I would also suggest that you use the Date data type for your columns and store the date and time in the same column. So, instead of StartDate, StopDate, StartTime, StopTime... you would have StartDateTime and StopDateTime.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tedsmith]

Thanks,
The rows are up to 50 different standard messages and I would rather not multiply this by 7.

I originally had only two columns as you suggest and simply used >Now and <Now

This worked fine if you only want to show it continuously between two points in time then never again.

I also want to be able to do this but also be able to show it for a short period each day (such as during lunch time for menu specials) and keep the data entry as simple as possible.

I though of using my 4 columns just entering times but leaving the date blank when I want it to be not date specific. The display would only show between the two times each day.

When I want it to be not time specific I would only enter dates
so it would show from midnight to midnight during the period.

But I have been unable to think of how to translate this into a query string.

I could then also show a different message for breakfast, lunch and dinner each day and a different set at the weekend.

The idea arrangement would be
All four colunms entered = Shows only between the times entered during the days entered (eg every lunch time for next week only)

Dates only = Shows continuously between midnights on the two dates (Eg all the time for a week)

Times only = Shows only between the times entered forever (eg every lunch from now on)

Start Date, Start and Stop Times (no Stop date) = Shows for one hour every day from the Start Date (eg. every lunch time starting first of next month)

I haven't been able to work out a Criteria to achieve this.

Where's that man with the thinking grenade?

 

[gmmastros]

Ted,

I strongly discourage you from hacking the system like this. What I mean is... if you attach special meaning to data that is missing vs. data that is in the DB, you will likely confuse yourself and certainly confuse any developers that work on the system in the future.

What I proposed is very straight forward and clear. Sure, it's more data in the database, but who cares? I mean.... it takes 8 bytes to store and Date/Time. You have 2 per row, so that's 16 bytes. Multiply your 50 rows by 7 and then by 16 bytes and you have 5,600. My point is, 50 rows is a super teeny-tiny table. Adding additional rows to make it 350 total rows is still super teeny-tiny. I have tables with millions of rows and the database community considers my DB small. Databases are designed to store tons of data while maintaining reasonably good performance.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tedsmith]

Thanks, you are right.

I am not worried so much about have a large database.
It is the data entry that I an trying to make simple as possible - just four entry text boxes in my case. I had hoped the query would work out form what is entered from that what is needed.

If I use multiple columns, I would need an initial start and final stop day column and a 'once a day" column as well to cover the requirement to show it only at lunch time every day for the next say three months then cease as opposed to continuously.

To have a different column for each day of the week suggests a lot more data entry is needed, unless there are some routine to automatically fill all the columns from the entry boxes.

I will ponder filling all these columns automatically from my 4 entry boxes depending on if a box is left blank instead of a column being blank.

 

[irlbinky]

Hi,
I am wondering if in trying to keep the data entry to 4 fields are you making it confusing for an end user? Would it be better to add in 1 column or drop down to indicate a priority of the date/time field? This new field could either be retrieved with the message or repeat your query for each value you have in the field.

If I look at your 2 scenarios above:
Case 1 (lunch time only for a week), the new column could be given a value of H to indicate that Hours are the priority field for the output.

Case 2 (continuous for a week), the new column could be given a value of D to indicate that the Dates are the priority field


I hope my thoughts help you some what,
Cathal

 

[tedsmith]

Thanks - good idea. however I think the idea of only entering times when you want lunchtime only and leaving them empty for continuous is simple enough (with a little help notice alongside the boxes). The database columns don't need to have the same as the boxes and could be filled by default values if left blank and your fifth column automatically generated.

Now we come to the hard part.

The big problem I have with "lunch time mode" in the query was getting a query to work when I had the start time on the start day after the stop time on the last day. This would be the situation say for an all night shift worker's meal time for a month.
Eg
Start Date 1/9/11
Lunch Start time 11:30 pm (23:30)
Stop date 30/9/11
Lunch Stop time 12:30 am (00:30)

Obviously you can just show it between the times listed.

Another problem is how to make the fifth H or D column change the type of query. Possibly running 2 queries?

 

[irlbinky]

I was thinking about your problem a bit more. Are you running the query once an hour or once a day? Are you populating a grid automatically with the reply from your query or are you/can you process the recordset your query returns? And do continuous items last for the full day?

If you are processing the results then your query shouldnt need to change much, just add a sort on the H or D column, or if you prefer C (continuous) or D (discrete), while processing the results add the message to the appropiate output (not sure if you have one display for continuous and another for discrete).

It would also be easier if you process your results to solve your problem with the start time being after the end time, so your SQL query would only be on the dates, then use a bunch of if statements to decide if and where the results are displayed.

I hope this helped some more,
~Cathal

 

[tedsmith]

Thanks
I run the query every 15 seconds or if the presentation is a movie I run it when it finishes to see if it should be restarted again or not.

It is used to show the data in other fields of that row in the form of a message on a TV screen in a shop or restaurant window.

The date management has since got a bit more complicated.

The data is simply the filename of the message (a Movie or Powerpoint presentation) that will show for the duration required (eg a lunch menu at lunch time and a different menu during breakfast time) These could be different at different days of the week (eg specials on Wednesday)

Obviously only one message can show at any one time. In the case of overlapping times the first has priority as I search for the first record that has dates and times either side of the current (Now) time.

For continuous, entering Dates only works (if you leave times blank) because the comparison Date>Now() works the same as Date/Time>Now()

What I want is when you enter the times, it only shows for that time (eg lunch time) during the date period set. This is OK when you only have one day set but everyday for say a month is the problem.

Also a straight time query can't tell the difference between a meal times 30 mins either side of midnight. It thinks it is a 23 hour long meal!
Somehow each day the query has to compare Now() with (Start 1/1/11 11:30 pm) (Finish 2/1/11 00:30 am)