03. juni 2005 - 11:20
Der er
5 kommentarer og
1 løsning
Hjælp til 3 sql'er
Jeg har nogle SELECT statements som jeg skal have udbrygget, men jeg er ikke så stærk til joins... :(
Jeg skal have sat departmentid på mine SELECT'er således at jeg kun får medarbejdere fra een afdeling:
select 1:
"select e.* from employee e" & _
" where not exists" & _
" (select ed.employeeid" & _
" from employeeday ed" & _
" where ed.workdayid=" & daylist & _
" and ed.employeeid=e.id) " & _
" order by e.name"
select 2:
"SELECT Count(ED.id) as antalmedarb, WD2.name, WD2.dato, Sum( TS.a_hours ) as tothours " & _
"FROM workday WD2 LEFT JOIN (employeeday ED " & _
"INNER JOIN workday WD ON ED.workdayid=WD.id " & _
"INNER JOIN timespent TS ON TS.employeedayid=ED.id " & _
"INNER JOIN assignment A ON TS.assignmentid=A.id AND A.taskid=" & rs("id") & _
" ) " & _
"ON WD2.id=WD.id " & _
"WHERE WD2.workmonthid=" & SQLType( CLng(arrValue(rpt_value_monthid)) ) & _
" GROUP BY WD2.name, WD2.dato " & _
"ORDER BY WD2.dato"
select 3:
"SELECT WD2.name, WD2.dato, Sum( TS.a_hours ) as tothours " & _
"FROM workday WD2 LEFT JOIN (employeeday ED " & _
"INNER JOIN workday WD ON ED.workdayid=WD.id " & _
"INNER JOIN timespent TS ON TS.employeedayid=ED.id " & _
"INNER JOIN assignment A ON TS.assignmentid=A.id AND A.taskid=" & rs("id") & _
" and ts.t_hours>0" & _
" and A.employeeid=" & arrValue(rpt_value_empid) & ") " & _
"ON WD2.id=WD.id " & _
"WHERE WD2.workmonthid=" & SQLType( CLng(arrValue(rpt_value_monthid)) ) & _
" GROUP BY WD2.name, WD2.dato " & _
"ORDER BY WD2.dato"
point gives pr. rigtig sql. de to sidste giver naturligvis mest. :)
"hvilken kolonne skal departmentid joines med i de andre tabeller?"
Hvis jeg viste det, ville jeg ikke stille et spg her. ;)
"har employee en departmentid fx?"
Ja, det er KUN employee der har det - og department tabellen naturligvis. :)
"personligt ville jeg have lettere ved at løse de 3 sql'er hvis jeg kunne se dine tabel create sql:)"
Heh, ja, det bliver lidt svært at klare nu, da jeg ikke er på arbejdet, men mandag morgen kan jeg finde dem frem. :)
Forresten! jeg har jo lagt nogle af dem ud tidligere:
http://www.eksperten.dk/spm/617442#rid5562942 :)
Her er tabellerne:
Employee:
CREATE TABLE employee
(
id int8 NOT NULL,
name varchar(255),
fname varchar(50),
lname varchar(50),
email varchar(255),
pwd varchar(50),
departmentid int8,
employeetypeid int8,
dage varchar(30),
starttid time,
sluttid time,
incllunch bool,
CONSTRAINT employee_id PRIMARY KEY (id),
CONSTRAINT department_fk FOREIGN KEY (departmentid) REFERENCES department (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT employeetype_fk FOREIGN KEY (employeetypeid) REFERENCES employeetype (id) ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITHOUT OIDS;
Employeeday:
CREATE TABLE employeeday
(
id int8 NOT NULL,
employeeid int8 NOT NULL,
workdayid int8 NOT NULL,
arrival timestamp,
departure timestamp,
CONSTRAINT emplyeeday_id PRIMARY KEY (id),
CONSTRAINT employee_fk FOREIGN KEY (employeeid) REFERENCES employee (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT workday_fk FOREIGN KEY (workdayid) REFERENCES workday (id) ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITHOUT OIDS;
Workday:
CREATE TABLE workday
(
id int8 NOT NULL,
name varchar(255),
workmonthid int8 NOT NULL,
dato date,
CONSTRAINT workday_id PRIMARY KEY (id),
CONSTRAINT workmonthid_fk FOREIGN KEY (workmonthid) REFERENCES workmonth (id) ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITHOUT OIDS;
Timespent:
CREATE TABLE timespent
(
id int8 NOT NULL,
assignmentid int8 NOT NULL,
employeedayid int8 NOT NULL,
t_hours numeric(4,2),
a_hours numeric(4,2),
approved varchar(1) NOT NULL DEFAULT 0,
CONSTRAINT timespent_id PRIMARY KEY (id),
CONSTRAINT assignment_fk FOREIGN KEY (assignmentid) REFERENCES "assignment" (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT employeeday_pk FOREIGN KEY (employeedayid) REFERENCES employeeday (id) ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITHOUT OIDS;
Assignment:
CREATE TABLE "assignment"
(
id int8 NOT NULL,
taskid int8 NOT NULL,
employeeid int8 NOT NULL,
deletedate varchar(23),
CONSTRAINT assignment_id PRIMARY KEY (id),
CONSTRAINT employee_fk FOREIGN KEY (employeeid) REFERENCES employee (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT task_fk FOREIGN KEY (taskid) REFERENCES task (id) ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITHOUT OIDS;
Det var vist det. :)
Hvornår er sql'erne færdige? ;);)
