Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2012
    Posts
    6

    Unanswered: Table structure idea/help

    I need to enter incidents from a particular day, ill enter them as a date and for example July 18 2013. I need to capture the day of the week which I can get from this date. What I also need to capture is when the contractor last did a delivery, they do this particular run twice a week but the days vary. So in my example the day was Thursday they they did my deliveries on Tue/Wed

    Another example could be they have them Sat/Sun.

    How can I record this?

    Another example/situation could befor Monday 15 July and they had my deliveries Sat/Sun before but also Sat/ Sun after.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Calendar tables are your friend here...

    http://gvee.co.uk/files/sql/dbo.numb...o.calendar.sql
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    ISO weeks

    I need to enter incidents from a particular day, ill enter them as a date and for example July 18 2013. I need to capture the day of the week which I can get from this date. What I also need to capture is when the contractor last did a delivery, they do this particular run twice a week but the days vary
    Build a calendar table with one column for the calendar data and other columns to show whatever your business needs in the way of temporal information. Do not try to calculate holidays in SQL -- Easter alone requires too much math.

    I would build a calendar table with a ISO-8601 week-within-year format column in addition to the usual Common Era date. This format is 'yyyyWww-d' where yyyy is the year, W is a separator token, ww is (01-53) week number and d is (1-7) day of the week.

    You input any calendar date, find the week-within-year column and return the dates that match on a LIKE predicate.
    WHERE sale_day LIKE '2012W26-[67]'

    There are several websites with calendars you can cut & paste, but you can start your search with: Week number

  4. #4
    Join Date
    Jun 2012
    Posts
    6
    Sorry I may not have been that clear.

    I enter rows into my database currently, it's a datetime field with a increment ID and a note plus a user ID of who entered the new row.

    I use the date and from there I get the day of week. I run reports based on this.

    What I now need to do is capture against an incident when the person last did a particular delivery. They do a selected delivery twice a week and we want to report on if incidents are occurring before of after that delivery to see if there is a pattern.

    How can I capture that?

  5. #5
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    What I now need to do is capture against an incident when the person last did a particular delivery. They do a selected delivery twice a week and we want to report on if incidents are occurring before or after that delivery to see if there is a pattern.
    Let's follow Netiquette and get some DDL! Here is my guess:

    CREATE TABLE Deliveries
    (delivery_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL
    PRIMARY KEY,
    receiver_emp_id CHAR(10) NOT NULL
    REFERENCES Personnel(emp_id),
    delivery_type CHAR(8) NOT NULL,
    incident_note VARCHAR(50) NOT NULL);

    You can find the weeks with too few or two many deliveries.

    WITH X (delivery_wk)
    AS
    (SELECT SUBSTRING (C.week_date, 1, 7)
    FROM Calendar AS C, Deliveries AS D
    WHERE D.delivery_date = C.cal_date
    AND D.delivery_type = 'foobar')

    SELECT X.delivery_wk, COUNT(*) AS foobar_cnt
    FROM X
    GROUP BY X.delivery_wk
    HAVING COUNT(*) <> 2;

  6. #6
    Join Date
    Jun 2012
    Posts
    6
    Thanks, but I don't want to add the deliveries for each week, just where these deliveries where in comparison to when a incident happened either before or after

  7. #7
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    That makes no sense.

    I don't want to add the deliveries for each week, just where these deliveries where in comparison to when an incident happened either before or after
    That makes no sense. So I do not record the deliveries, but by magic I know that an incident will occur/ has occurred in a temporal relation to the incident?

    How about posting some sample data and DDL?

    Once I have the data grouped by weeks, it is easy to see if an incident and a delivery occur in the same week.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Do a three table join, all three aliases on the same table. Make the driving alias the incident, the lagging alias the prior delivery and the leading alias the subsequent delivery. This will give you all of the information that you requested. Without the table structure and some sample data, that's the best that I can offer you.

    If you show us the CREATE TABLE statement, and the sample data for three weeks of deliveries (with at least one incident), then we can be more specific helping you to compose a solution.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jun 2012
    Posts
    6
    Quote Originally Posted by Celko View Post
    That makes no sense. So I do not record the deliveries, but by magic I know that an incident will occur/ has occurred in a temporal relation to the incident?

    How about posting some sample data and DDL?

    Once I have the data grouped by weeks, it is easy to see if an incident and a delivery occur in the same week.
    Yeah it's tricky to explain. Currently I log "incidents" per contractor. Some may have 1 or 2 incidents per year, others have 15 to 20 for example. Currently I log the date, day of the the week, details of what happened etc.

    I'm now wanting to report on if there is a relationship between to doing these selected deliveries which occur twice a week at random and the incidents which occur

    An example might be

    Table: Incidents
    IncidentID: 213
    Contractor: John
    AddedBy: Mark
    DateIncidentOccured: Friday July 19 2013
    ...

    Say if John had an incident and he did my delivers Tuesday and Wednesday and next does them Monday, Tuesday

    Do I have columns called "DaysSince" and "DaysTill"

    In this example I would have 1 and 2, them being Thursday and Saturday/Sunday

    Is there a better way?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •