Query problem

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Gunnar Vøyenli
    #1

    Query problem

    Hi
    Some days ago I posted a problem with a query.
    Thaks to Dave and John, I got a little closer to a solution.
    Their suggestion was a solution to my problem as I stated it, but the real
    problem is a bit more complicated, so I have to expand the original problem
    a bit.
    The query has to handle unknown number of departments, and a date interval.

    I want:
    1) For each day, for each department: a list of (from at_work table) all
    employees at work.
    2) In the same list I want listed (from emp table) all emplyees that belongs
    to this department, but is not on work this date

    Here are new scripts:
    create table emp
    (
    empno int not null,
    depno int not null
    )
    alter table emp add primary key (empno)

    create table at_work
    (
    empno int not null,
    depno int not null,
    working_date int not null,
    duration int not null
    )
    alter table at_work add primary key (empno, depno, working_date)
    alter table at_work add constraint fk_at_work_emp foreign key (empno)
    references emp (empno)

    insert into emp (empno, depno) values (1,10)
    insert into emp (empno, depno) values (2,10)
    insert into emp (empno, depno) values (3,20)
    insert into emp (empno, depno) values (4,20)

    insert into at_work (empno, depno, working_date, duration) values
    (1,10,'20031017 ',5)
    insert into at_work (empno, depno, working_date, duration) values
    (3,10,'20031017 ',4)
    insert into at_work (empno, depno, working_date, duration) values
    (1,10,'20031018 ',6)
    insert into at_work (empno, depno, working_date, duration) values
    (4,10,'20031018 ',7)
    insert into at_work (empno, depno, working_date, duration) values
    (1,20,'20031017 ',3)
    insert into at_work (empno, depno, working_date, duration) values
    (3,20,'20031017 ',5)
    insert into at_work (empno, depno, working_date, duration) values
    (2,20,'20031018 ',6)
    insert into at_work (empno, depno, working_date, duration) values
    (3,20,'20031018 ',7)
    insert into at_work (empno, depno, working_date, duration) values
    (4,20,'20031018 ',8)

    The result set should now look like this:
    empno depno working_date duration
    ---------------------------------------------
    1 10 '20031017' 5
    3 10 '20031017' 4
    2 10 '20031017' NULL
    1 10 '20031018' 6
    4 10 '20031018' 7
    2 10 '20031018' NULL
    1 20 '20031017' 3
    3 20 '20031017' 5
    4 20 '20031017' NULL
    2 20 '20031018' 6
    3 20 '20031018' 7
    4 20 '20031018' 8

    Could someone please help me?

    Thanks in advance
    Regards,
    Gunnar Vøyenli
    EDB-konsulent as
    NORWAY


    Tags: None
    • John Gilson
      #2
      Re: Query problem

      "Gunnar Vøyenli" <gv@edbkonsulen t.no> wrote in message news:3f93e231$1 @news.broadpark .no...[color=blue]
      > Hi
      > Some days ago I posted a problem with a query.
      > Thaks to Dave and John, I got a little closer to a solution.
      > Their suggestion was a solution to my problem as I stated it, but the real
      > problem is a bit more complicated, so I have to expand the original problem
      > a bit.
      > The query has to handle unknown number of departments, and a date interval.
      >
      > I want:
      > 1) For each day, for each department: a list of (from at_work table) all
      > employees at work.
      > 2) In the same list I want listed (from emp table) all emplyees that belongs
      > to this department, but is not on work this date
      >
      > Here are new scripts:
      > create table emp
      > (
      > empno int not null,
      > depno int not null
      > )
      > alter table emp add primary key (empno)
      >
      > create table at_work
      > (
      > empno int not null,
      > depno int not null,
      > working_date int not null,
      > duration int not null
      > )
      > alter table at_work add primary key (empno, depno, working_date)
      > alter table at_work add constraint fk_at_work_emp foreign key (empno)
      > references emp (empno)
      >
      > insert into emp (empno, depno) values (1,10)
      > insert into emp (empno, depno) values (2,10)
      > insert into emp (empno, depno) values (3,20)
      > insert into emp (empno, depno) values (4,20)
      >
      > insert into at_work (empno, depno, working_date, duration) values
      > (1,10,'20031017 ',5)
      > insert into at_work (empno, depno, working_date, duration) values
      > (3,10,'20031017 ',4)
      > insert into at_work (empno, depno, working_date, duration) values
      > (1,10,'20031018 ',6)
      > insert into at_work (empno, depno, working_date, duration) values
      > (4,10,'20031018 ',7)
      > insert into at_work (empno, depno, working_date, duration) values
      > (1,20,'20031017 ',3)
      > insert into at_work (empno, depno, working_date, duration) values
      > (3,20,'20031017 ',5)
      > insert into at_work (empno, depno, working_date, duration) values
      > (2,20,'20031018 ',6)
      > insert into at_work (empno, depno, working_date, duration) values
      > (3,20,'20031018 ',7)
      > insert into at_work (empno, depno, working_date, duration) values
      > (4,20,'20031018 ',8)
      >
      > The result set should now look like this:
      > empno depno working_date duration
      > ---------------------------------------------
      > 1 10 '20031017' 5
      > 3 10 '20031017' 4
      > 2 10 '20031017' NULL
      > 1 10 '20031018' 6
      > 4 10 '20031018' 7
      > 2 10 '20031018' NULL
      > 1 20 '20031017' 3
      > 3 20 '20031017' 5
      > 4 20 '20031017' NULL
      > 2 20 '20031018' 6
      > 3 20 '20031018' 7
      > 4 20 '20031018' 8
      >
      > Could someone please help me?
      >
      > Thanks in advance
      > Regards,
      > Gunnar Vøyenli
      > EDB-konsulent as
      > NORWAY[/color]

      SELECT COALESCE(W.empn o, E.empno) AS empno,
      COALESCE(W.depn o, E.depno) AS depno,
      COALESCE(W.work ing_date, D.working_date) AS working_date,
      W.duration
      FROM Emp AS E
      CROSS JOIN
      (SELECT DISTINCT working_date FROM At_Work) AS D
      FULL OUTER JOIN
      At_Work AS W
      ON E.empno = W.empno AND
      E.depno = W.depno AND
      D.working_date = W.working_date
      ORDER BY depno, working_date, empno

      empno depno working_date duration
      1 10 20031017 5
      2 10 20031017 NULL
      3 10 20031017 4
      1 10 20031018 6
      2 10 20031018 NULL
      4 10 20031018 7
      1 20 20031017 3
      3 20 20031017 5
      4 20 20031017 NULL
      2 20 20031018 6
      3 20 20031018 7
      4 20 20031018 8

      Regards,
      jag


        Comment

        • David Portas
          #3
          Re: Query problem

          Since you want to report on dates which may or may not exist in your table,
          best create a Calendar table:

          CREATE TABLE Calendar
          (caldate DATETIME NOT NULL PRIMARY KEY)

          Populate with as many years as you need:

          INSERT INTO Calendar (caldate) VALUES ('20000101')

          WHILE (SELECT MAX(caldate) FROM Calendar)<'2010 1231'
          INSERT INTO Calendar (caldate)
          SELECT DATEADD(D,DATED IFF(D,'19991231 ',caldate),
          (SELECT MAX(caldate) FROM Calendar))
          FROM Calendar


          Here's the query:

          SELECT COALESCE(W.empn o, E.empno) AS empno,
          COALESCE(W.depn o, E.depno) AS depno,
          COALESCE(C.cald ate, W.working_date) AS working_date,
          W.duration
          FROM Calendar AS C
          CROSS JOIN Emp AS E
          FULL JOIN At_Work AS W
          ON E.empno=W.empno AND E.depno=W.depno AND C.caldate=W.wor king_date
          WHERE C.caldate BETWEEN '20031017' AND '20031018'
          OR C.caldate IS NULL

          --
          David Portas
          ------------
          Please reply only to the newsgroup
          --


            Comment

            Previous template Next
            Working...