Hey everyone, I have a nasty query i'm trying to create here, here's the basics of it:
We have a system that facilities respond 3 times a day with stock. These reports have to come in very specific windows. Morning 2-6,Afternoon 8-12, and night 4-8
Some days, facilities respond 4 times a day, other times 3, other times 2. What I want to eventually do create monthly charts showing the % of compliance of the 3
time windows.
The data comes out as excel with the facility name and the time/date(in a single column) of the report Example:
FACILITY DATETIME
blarg 3/1/2014 4:56
I'm thinking my first step should be to convert this into a format like this:
FACILITY DATE WINDOW1 WINDOW2 WINDOW3
blarg 3/1/2014 YES NO YES
The tricky part is, making a query smart enough to tell if any of the values for a day fall in each window and not get confused if they're extra values. Any Ideas?