Hello All, after writing my simple SQL i don't know how to fix this problem, SUM problem, won't run because of it.
I'm so stressed out, please help, need to find the problem
this is the code:
1. REPORT CARD
CREATE VIEW REPORT_CARD AS
SELECT DEPT_NAME, COURSE_NUM, COURSE_DESC, GRADE_L, COURSE_CREDIT as
CREDITS_TAKEN, COURSE_CREDIT AS COURSE_EARNED, GRADE_N*COURSE_CREDIT AS
GRADE_POINTS,
SUM(COURSE_CREDIT) AS SEMESTER_CREDITS_TAKEN, SUM(COURSE_CREDIT) AS
SEMESTER_CREDITS_TAKEN, SUM(GRADE_N*COURSE_CREDIT) AS TOTAL_POINTS,
TOTAL_POINTS/SEMESTER_CREDITS_TAKEN AS GPA
SUM(COURSE_CREDIT) AS SEMESTER_CREDITS_TAKEN, SUM(COURSE_CREDIT) AS
SEMESTER_CREDITS_TAKEN, SUM(GRADE_N*COURSE_CREDIT) AS TOTAL_POINTS,
TOTAL_POINTS/SEMESTER_CREDITS_TAKEN AS GPA
CLASS.SEM_ID,
ENROLLMENT.S_ID, S_FNAME, S_MI, S_LNAME, S_L_ADD, S_L_CITY, S_L_STATE,
S_L_ZIP,
S_P_ADD, S_P_CITY, S_P_STATE, S_P_ZIP
FROM STUDENT, DEPARTMENT, COURSE, ENROLLMENT, CLASS, SEMESTER
WHERE
STUDENT.S_ID=ENROLLMENT.S_ID
AND ENROLLMENT.SCHED_CODE=CLASS.SCHED_CODE
AND CLASS.CLASS_ID=COURSE.COURSE_ID
AND COURSE.DEPT_CODE=DEPARTMENT.DEPT_CODE
AND SEMESTER.SEM_ID=CLASS.SEM_ID;
2. CLASS LIST
CREATE VIEW CLASS_LIST AS
SELECT COURSE.DEPT_CODE "DEPARTMENT", COURSE_NUM "COURSE", SEC_NUM
"SECTION', CLASS.SCHED_CODE, DEPT_NAME, COURSE_DESC, COURSE_CREDIT,
SEM_ID,
TIME, DAY, ROOM_NUM, BLDG_CODE,
CLASS.F_ID, F_FNAME, F_LNAME
ENROLLMENT.S_ID, S_FNAME, S_MI, S_LNAME, GRADE
FROM DEPARTMENT, CLASS, COURSE, ROOM, STUDENT, ENROLLMENT, FACULTY
WHERE
ROOM.ROOM_ID=CLASS.ROOM_ID
AND COURSE.DEPT_CODE=DEPARTMENT.DEPT_CODE
AND CLASS.SCHED_CODE=ENROLLMENT.SCHED_CODE
AND ENROLLMENT S_ID=STUDENT.S_ID;
3.GRADE VERIFICATION REPORT
CREATE VIEW GRADE_VERIFICATION AS
SELECT * FROM CLASS_LIST;
4.TIME SCHEDULE
CREATE VIEW TIME_SCHEDULE AS
SELECT SEMESTER.*, COURSE_NUM, COURSE_DESC, CLASS.SCHED_CODE, SEC_NUM,
DAY, TIME, ROOM_NUM, BLDG_CODE, CLASS.F_ID, F_LNAME, COURSE_CREDIT, PREREQ
FROM CLASS, COURSE, FACULTY, ROOM
WHERE CLASS.CLASS_ID = COURSE_COURSE_ID
AND CLASS.F_ID=FACULTY.F_ID
AND CLASS.ROOM_ID=ROOM.ROOM_ID
AND SEMESTER.SEM_ID=CLASS.SEM_ID;
6.STUDENT SCHEDULE
CREATE VIEW STUDENT_SCHEDULE AS
SELECT STUDENT.S_ID, SEM_ID, S_FNAME, S_MI, S_LNAME, S_L_ADD, S_L_CITY,
S_L_STATE, S_L_ZIP, S_P_ADD, S_P_CITY, S_P_STATE, S_P_ZIP,
CLASS.SCHED_CODE, CLASS_CLASS_ID, COURSE_DESC, SEC_NUM,
COURSE_CREDIT, TIME, DAY, ROOM_NUM, BLDG_CODE, SUM(COURSE_CREDIT)
FROM STUDENT, CLASS, COURSE, ROOM, ENROLLMENT
WHERE STUDENT.S_ID=ENROLLMENT.S_ID
AND CLASS.SCHED_CODE=ENROLLMENT.SCHED_CODE
AND CLASS.CLASS_ID=COURSE.COURSE_ID
AND CLASS.ROOM_ID=ROOM.ROOM_ID;
7.FULL STUDENT INFORMATION
CREATE VIEW FULL_STUDENT_INFO AS
SELECT STUDENT.*, SEM_ID,
COURSE_NUM, COURSE_DES, COURSE_CREDIT,
GRADE_L, GRADE_N*COURSE_CREDIT AS "GRADE POINTS",
SUM(COURSE_CREDIT) AS "CREDITS ATTEMPTED",
SUM(COURSE_CREDIT) AS "CREDITS ATTEMPTED",
SUM(GRADE_N*COURSE_CREDIT) AS "TOTAL GRADE POINTS",
S_GPA
FROM STUDENT, ENROLLMENT, COURSE, CLASS
WHERE STUDENT.S_ID=ENROLLMENT.S_ID
AND ENROLLMENT.SCHED_ID=CLASS.SCHED_ID
AND CLASS.CLASS_ID=COURSE_COURSE_ID;
8. FACULTY INFORMATION REPORT
CREATE VIEW FACULTY_INFO AS
SELECT FACULTY.*,
S_ID, S_FNAME, S_MI, S_LNAME, S_L_ADD, S_L_CITY, S_L_STATE, S_L_ZIP,
S_P_ADD, S_P_CITY, S_P_STATE, S_P_ZIP,
DEPT_CODE, MAJOR_NAME
FROM FACULTY, STUDENT, MAJOR
WHERE
FACULTY.F_ID=STUDENT.F_ID
AND STUDENT.MAJOR_ID=MAJOR.MAJOR_ID;
9.WORK VERSION OF THE TIME SCHEDULE
CREATE VIEW TIME_SCHED_INTERNAL AS
SELECT SEMESTER.*, COURSE_NUM, COURSE_DESC, CLASS.SCHED_CODE, SEC_NUM,
DAY, TIME, ROOM_NUM, BLDG_CODE, CLASS.F_ID, F_LNAME, COURSE_CREDIT,
PREREQ,
COUNT(ENROLL_ID), MAX_ENROLL
FROM CLASS, COURSE, FACULTY, ROOM, ENROLLMENT
WHERE CLASS.CLASS_ID = COURSE_COURSE_ID
AND CLASS.F_ID=FACULTY.F_ID
AND CLASS.ROOM_ID=ROOM.ROOM_ID
AND SEMESTER.SEM_ID=CLASS.SEM_ID
AND ENROLLMENT.SCHED_CODE=CLASS.SCHED_CODE;
10.CREATE VIEW COURSE_REPORT AS
SELECT COURSE_NUM, COURSE_DESC, COURSE_CREDIT,
DEPT_DESC, COURSE.DEPT_CODE
FROM COURSE, DEPARTMENT
WHERE COURSE.DEPT_CODE=DEPARTMENT.DEPT.CODE;
11.UPDATE ENROLLMENT-NEED TO CREATE A TRIGGER TO CHECK UPON INSERT INTO
THE ENROLLMENT TABLE WHERE COURSE.PREREQ NOT NULL AND ENROLLMENT.GRADE NOT
NULL FOR THAT STUDENT
SYNTAX WILL BE SIMILAR TO:
CREATE OR REPLACE TRIGGER VERIFY_ENROLLMENT
BEFORE INSERT ON ENROLLMENT
BEGIN
******
END
12.POST GRADES: EXAMPLE
UPDATE ENROLLMENT
SET GRADE='A'
WHERE S_ID='123';
13. PURGE:EXAMPLE
DELETE FROM CLASS
WHERE CLASS < 1010;can u post the ddl for the base tables ?
and which view is causing the problem?
Originally posted by Mari9922
Hello All, after writing my simple SQL i don't know how to fix this problem, SUM problem, won't run because of it.
I'm so stressed out, please help, need to find the problem
this is the code:
1. REPORT CARD
CREATE VIEW REPORT_CARD AS
SELECT DEPT_NAME, COURSE_NUM, COURSE_DESC, GRADE_L, COURSE_CREDIT as
CREDITS_TAKEN, COURSE_CREDIT AS COURSE_EARNED, GRADE_N*COURSE_CREDIT AS
GRADE_POINTS,
SUM(COURSE_CREDIT) AS SEMESTER_CREDITS_TAKEN, SUM(COURSE_CREDIT) AS
SEMESTER_CREDITS_TAKEN, SUM(GRADE_N*COURSE_CREDIT) AS TOTAL_POINTS,
TOTAL_POINTS/SEMESTER_CREDITS_TAKEN AS GPA
SUM(COURSE_CREDIT) AS SEMESTER_CREDITS_TAKEN, SUM(COURSE_CREDIT) AS
SEMESTER_CREDITS_TAKEN, SUM(GRADE_N*COURSE_CREDIT) AS TOTAL_POINTS,
TOTAL_POINTS/SEMESTER_CREDITS_TAKEN AS GPA
CLASS.SEM_ID,
ENROLLMENT.S_ID, S_FNAME, S_MI, S_LNAME, S_L_ADD, S_L_CITY, S_L_STATE,
S_L_ZIP,
S_P_ADD, S_P_CITY, S_P_STATE, S_P_ZIP
FROM STUDENT, DEPARTMENT, COURSE, ENROLLMENT, CLASS, SEMESTER
WHERE
STUDENT.S_ID=ENROLLMENT.S_ID
AND ENROLLMENT.SCHED_CODE=CLASS.SCHED_CODE
AND CLASS.CLASS_ID=COURSE.COURSE_ID
AND COURSE.DEPT_CODE=DEPARTMENT.DEPT_CODE
AND SEMESTER.SEM_ID=CLASS.SEM_ID;
2. CLASS LIST
CREATE VIEW CLASS_LIST AS
SELECT COURSE.DEPT_CODE "DEPARTMENT", COURSE_NUM "COURSE", SEC_NUM
"SECTION', CLASS.SCHED_CODE, DEPT_NAME, COURSE_DESC, COURSE_CREDIT,
SEM_ID,
TIME, DAY, ROOM_NUM, BLDG_CODE,
CLASS.F_ID, F_FNAME, F_LNAME
ENROLLMENT.S_ID, S_FNAME, S_MI, S_LNAME, GRADE
FROM DEPARTMENT, CLASS, COURSE, ROOM, STUDENT, ENROLLMENT, FACULTY
WHERE
ROOM.ROOM_ID=CLASS.ROOM_ID
AND COURSE.DEPT_CODE=DEPARTMENT.DEPT_CODE
AND CLASS.SCHED_CODE=ENROLLMENT.SCHED_CODE
AND ENROLLMENT S_ID=STUDENT.S_ID;
3.GRADE VERIFICATION REPORT
CREATE VIEW GRADE_VERIFICATION AS
SELECT * FROM CLASS_LIST;
4.TIME SCHEDULE
CREATE VIEW TIME_SCHEDULE AS
SELECT SEMESTER.*, COURSE_NUM, COURSE_DESC, CLASS.SCHED_CODE, SEC_NUM,
DAY, TIME, ROOM_NUM, BLDG_CODE, CLASS.F_ID, F_LNAME, COURSE_CREDIT, PREREQ
FROM CLASS, COURSE, FACULTY, ROOM
WHERE CLASS.CLASS_ID = COURSE_COURSE_ID
AND CLASS.F_ID=FACULTY.F_ID
AND CLASS.ROOM_ID=ROOM.ROOM_ID
AND SEMESTER.SEM_ID=CLASS.SEM_ID;
6.STUDENT SCHEDULE
CREATE VIEW STUDENT_SCHEDULE AS
SELECT STUDENT.S_ID, SEM_ID, S_FNAME, S_MI, S_LNAME, S_L_ADD, S_L_CITY,
S_L_STATE, S_L_ZIP, S_P_ADD, S_P_CITY, S_P_STATE, S_P_ZIP,
CLASS.SCHED_CODE, CLASS_CLASS_ID, COURSE_DESC, SEC_NUM,
COURSE_CREDIT, TIME, DAY, ROOM_NUM, BLDG_CODE, SUM(COURSE_CREDIT)
FROM STUDENT, CLASS, COURSE, ROOM, ENROLLMENT
WHERE STUDENT.S_ID=ENROLLMENT.S_ID
AND CLASS.SCHED_CODE=ENROLLMENT.SCHED_CODE
AND CLASS.CLASS_ID=COURSE.COURSE_ID
AND CLASS.ROOM_ID=ROOM.ROOM_ID;
7.FULL STUDENT INFORMATION
CREATE VIEW FULL_STUDENT_INFO AS
SELECT STUDENT.*, SEM_ID,
COURSE_NUM, COURSE_DES, COURSE_CREDIT,
GRADE_L, GRADE_N*COURSE_CREDIT AS "GRADE POINTS",
SUM(COURSE_CREDIT) AS "CREDITS ATTEMPTED",
SUM(COURSE_CREDIT) AS "CREDITS ATTEMPTED",
SUM(GRADE_N*COURSE_CREDIT) AS "TOTAL GRADE POINTS",
S_GPA
FROM STUDENT, ENROLLMENT, COURSE, CLASS
WHERE STUDENT.S_ID=ENROLLMENT.S_ID
AND ENROLLMENT.SCHED_ID=CLASS.SCHED_ID
AND CLASS.CLASS_ID=COURSE_COURSE_ID;
8. FACULTY INFORMATION REPORT
CREATE VIEW FACULTY_INFO AS
SELECT FACULTY.*,
S_ID, S_FNAME, S_MI, S_LNAME, S_L_ADD, S_L_CITY, S_L_STATE, S_L_ZIP,
S_P_ADD, S_P_CITY, S_P_STATE, S_P_ZIP,
DEPT_CODE, MAJOR_NAME
FROM FACULTY, STUDENT, MAJOR
WHERE
FACULTY.F_ID=STUDENT.F_ID
AND STUDENT.MAJOR_ID=MAJOR.MAJOR_ID;
9.WORK VERSION OF THE TIME SCHEDULE
CREATE VIEW TIME_SCHED_INTERNAL AS
SELECT SEMESTER.*, COURSE_NUM, COURSE_DESC, CLASS.SCHED_CODE, SEC_NUM,
DAY, TIME, ROOM_NUM, BLDG_CODE, CLASS.F_ID, F_LNAME, COURSE_CREDIT,
PREREQ,
COUNT(ENROLL_ID), MAX_ENROLL
FROM CLASS, COURSE, FACULTY, ROOM, ENROLLMENT
WHERE CLASS.CLASS_ID = COURSE_COURSE_ID
AND CLASS.F_ID=FACULTY.F_ID
AND CLASS.ROOM_ID=ROOM.ROOM_ID
AND SEMESTER.SEM_ID=CLASS.SEM_ID
AND ENROLLMENT.SCHED_CODE=CLASS.SCHED_CODE;
10.CREATE VIEW COURSE_REPORT AS
SELECT COURSE_NUM, COURSE_DESC, COURSE_CREDIT,
DEPT_DESC, COURSE.DEPT_CODE
FROM COURSE, DEPARTMENT
WHERE COURSE.DEPT_CODE=DEPARTMENT.DEPT.CODE;
11.UPDATE ENROLLMENT-NEED TO CREATE A TRIGGER TO CHECK UPON INSERT INTO
THE ENROLLMENT TABLE WHERE COURSE.PREREQ NOT NULL AND ENROLLMENT.GRADE NOT
NULL FOR THAT STUDENT
SYNTAX WILL BE SIMILAR TO:
CREATE OR REPLACE TRIGGER VERIFY_ENROLLMENT
BEFORE INSERT ON ENROLLMENT
BEGIN
******
END
12.POST GRADES: EXAMPLE
UPDATE ENROLLMENT
SET GRADE='A'
WHERE S_ID='123';
13. PURGE:EXAMPLE
DELETE FROM CLASS
WHERE CLASS < 1010;|||Originally posted by harshal_in
can u post the ddl for the base tables ?
and which view is causing the problem?|||Thank you very much, I'll post it after work, if that's not too much of a problem.
Originally posted by harshal_in
can u post the ddl for the base tables ?
and which view is causing the problem?
Showing posts with label writing. Show all posts
Showing posts with label writing. Show all posts
Monday, March 26, 2012
Friday, March 23, 2012
please fine tune these queries
programmers are writing a loop in .net and passing empid,date to server
there are 700 employees so the round trips are increasing
--queries
select '8/5/2005' as daysabsent,e.empid as empid,intime,type,cast('8/5/2005'
as datetime) as absentdays,'A'
from ccsmay26.dbo.employee as e
left outer join
(
select empid,intime,type=case when intime>='12/30/1899 9:00:00 PM' then 'N'
else 'M' end
from
(
select empid,effective_date,end_date,intime
from newccs.dbo.intimings_history
where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
/***********************/
and empid='QU'
/***********************/
union
select empid,effective_date,null as end_date,intime
from newccs.dbo.intimings
where '8/5/2005'>=effective_date
/***********************/
and empid='QU'
/***********************/
) as a
) as t
on e.empid=t.empid
where depid<>'UST' and
(
(emp_status<>'T' and hire_date<='8/1/2005') or (emp_status='T' and
termination_date>'8/5/2005')
)
and e.empid='QU' and e.empid not in
(
/*****************Calculation of no. of days absent of an
employee***********************/
select empid
from
(
SELECT DISTINCT CAST(DATE1 AS DATETIME) as daysabsent,empid
FROM
(
/*****************Employees of the Night shift********************/
SELECT e.*,CAST(MONTH(ENTRYTIME) AS VARCHAR(2))+'/'+CAST(DAY(ENTRYTIME)
AS VARCHAR(2)) +'/'+
CAST(YEAR(ENTRYTIME) AS VARCHAR(4)) AS DATE1
FROM NEWCCS.DBO.EMPLOYEE_TIMINGS as e
left outer join
(
select empid,intime
from newccs.dbo.intimings_history
where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
/***********************/
and empid='QU'
/***********************/
union
select empid,intime
from newccs.dbo.intimings
where '8/5/2005'>=effective_date
/***********************/
and empid='QU'
/***********************/
) as i
on e.empid=i.empid
where entrytime>='8/5/2005 8:00:00 PM' and
entrytime<=dateadd(d,1,'8/5/2005 5:00:00 PM') and intime>='12/30/1899
9:00:00 PM'
--Changes by me, e.empid has to be removed
/*and e.empid='CL'*/ and e.timetype in ('S') --and e.inout='S'
union
/******************Employees of the Morning shift******************/
SELECT e.*,CAST(MONTH(ENTRYTIME) AS VARCHAR(2))+'/'+CAST(DAY(ENTRYTIME)
AS VARCHAR(2))
+'/'+CAST(YEAR(ENTRYTIME) AS VARCHAR(4)) AS DATE1
FROM NEWCCS.DBO.EMPLOYEE_TIMINGS as e
left outer join
(
select empid,intime
from newccs.dbo.intimings_history
where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
/***********************/
and empid='QU'
/***********************/
union
select empid,intime
from newccs.dbo.intimings
where '8/5/2005'>=effective_date
/***********************/
and empid='QU'
/***********************/
) as i
on e.empid=i.empid
where entrytime>='8/5/2005' and entrytime<=dateadd(d,1,'8/5/2005') and
intime<'12/30/1899 9:00:00 PM'
--Changes by me, e.empid has to be removed
/*and e.empid='CL'*/ and e.timetype in ('S') --and e.inout='S'
) as z
) as z /*** Absent employees of Day & Night Shift represented as 'z' ***/
)
/*********** End of calculation of Absent days ************/
union
/*************************** Start of calculation of Present days
**************************************/
select '8/5/2005' as daysabsent,e.empid as empid,intime,type,cast('8/5/2005'
as datetime) as absentdays,'P'
from ccsmay26.dbo.employee as e
left outer join
(
select empid,intime,type=case when intime>='12/30/1899 9:00:00 PM' then 'N'
else 'M' end
from
(
select empid,effective_date,end_date,intime
from newccs.dbo.intimings_history
where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
/***********************/
and empid='QU'
/***********************/
union
select empid,effective_date,null as end_date,intime
from newccs.dbo.intimings
where '8/5/2005'>=effective_date
/***********************/
and empid='QU'
/***********************/
) as a
) as t
on e.empid=t.empid
where depid<>'UST' and
(
(emp_status<>'T' and hire_date<='8/1/2005') or (emp_status='T' and
termination_date>'8/5/2005')
)
and e.empid='QU' and e.empid in
(
/*****************Calculation of no. of days absent of an
employee***********************/
select empid
from
(
SELECT DISTINCT CAST(DATE1 AS DATETIME) as daysabsent,empid
FROM
(
/*****************Employees of the Night shift********************/
SELECT e.*,CAST(MONTH(ENTRYTIME) AS VARCHAR(2))+'/'+CAST(DAY(ENTRYTIME)
AS VARCHAR(2)) +'/'+
CAST(YEAR(ENTRYTIME) AS VARCHAR(4)) AS DATE1
FROM NEWCCS.DBO.EMPLOYEE_TIMINGS as e
left outer join
(
select empid,intime
from newccs.dbo.intimings_history
where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
/***********************/
and empid='QU'
/***********************/
union
select empid,intime
from newccs.dbo.intimings
where '8/5/2005'>=effective_date
/***********************/
and empid='QU'
/***********************/
) as i
on e.empid=i.empid
where entrytime>='8/5/2005 8:00:00 PM' and
entrytime<=dateadd(d,1,'8/5/2005 5:00:00 PM') and intime>='12/30/1899
9:00:00 PM'
--Changes by me, e.empid has to be removed
/*and e.empid='CL'*/ and e.timetype='S' and e.inout='S' --and e.timetype
not in ('V','B')
union
/******************Employees of the Morning shift******************/
SELECT e.*,CAST(MONTH(ENTRYTIME) AS VARCHAR(2))+'/'+CAST(DAY(ENTRYTIME)
AS VARCHAR(2))
+'/'+CAST(YEAR(ENTRYTIME) AS VARCHAR(4)) AS DATE1
FROM NEWCCS.DBO.EMPLOYEE_TIMINGS as e
left outer join
(
select empid,intime
from newccs.dbo.intimings_history
where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
/***********************/
and empid='QU'
/***********************/
union
select empid,intime
from newccs.dbo.intimings
where '8/5/2005'>=effective_date
/***********************/
and empid='QU'
/***********************/
) as i
on e.empid=i.empid
where entrytime>='8/5/2005' and entrytime<=dateadd(d,1,'8/5/2005') and
intime<'12/30/1899 9:00:00 PM'
--Changes by me, e.empid has to be removed
/*and e.empid='CL'*/ and e.timetype='S' and e.inout='S' --and e.timetype
not in ('V','B')
) as z
) as z /*** Absent employees of Day & Night Shift represented as 'z' ***/
)
--end queri3esRags
Can you post DDL/sample data and description on desired out put
--
Regards
R.D
--Knowledge gets doubled when shared
"raghu veer" wrote:
> programmers are writing a loop in .net and passing empid,date to server
> there are 700 employees so the round trips are increasing
> --queries
> select '8/5/2005' as daysabsent,e.empid as empid,intime,type,cast('8/5/200
5'
> as datetime) as absentdays,'A'
> from ccsmay26.dbo.employee as e
> left outer join
> (
> select empid,intime,type=case when intime>='12/30/1899 9:00:00 PM' then '
N'
> else 'M' end
> from
> (
> select empid,effective_date,end_date,intime
> from newccs.dbo.intimings_history
> where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
> /***********************/
> and empid='QU'
> /***********************/
> union
> select empid,effective_date,null as end_date,intime
> from newccs.dbo.intimings
> where '8/5/2005'>=effective_date
> /***********************/
> and empid='QU'
> /***********************/
> ) as a
> ) as t
> on e.empid=t.empid
> where depid<>'UST' and
> (
> (emp_status<>'T' and hire_date<='8/1/2005') or (emp_status='T' and
> termination_date>'8/5/2005')
> )
> and e.empid='QU' and e.empid not in
> (
> /*****************Calculation of no. of days absent of an
> employee***********************/
> select empid
> from
> (
> SELECT DISTINCT CAST(DATE1 AS DATETIME) as daysabsent,empid
> FROM
> (
> /*****************Employees of the Night shift********************/
> SELECT e.*,CAST(MONTH(ENTRYTIME) AS VARCHAR(2))+'/'+CAST(DAY(ENTRYTIME)
> AS VARCHAR(2)) +'/'+
> CAST(YEAR(ENTRYTIME) AS VARCHAR(4)) AS DATE1
> FROM NEWCCS.DBO.EMPLOYEE_TIMINGS as e
> left outer join
> (
> select empid,intime
> from newccs.dbo.intimings_history
> where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
> /***********************/
> and empid='QU'
> /***********************/
> union
> select empid,intime
> from newccs.dbo.intimings
> where '8/5/2005'>=effective_date
> /***********************/
> and empid='QU'
> /***********************/
> ) as i
> on e.empid=i.empid
> where entrytime>='8/5/2005 8:00:00 PM' and
> entrytime<=dateadd(d,1,'8/5/2005 5:00:00 PM') and intime>='12/30/1899
> 9:00:00 PM'
> --Changes by me, e.empid has to be removed
> /*and e.empid='CL'*/ and e.timetype in ('S') --and e.inout='S'
> union
> /******************Employees of the Morning shift******************/
> SELECT e.*,CAST(MONTH(ENTRYTIME) AS VARCHAR(2))+'/'+CAST(DAY(ENTRYTIME)
> AS VARCHAR(2))
> +'/'+CAST(YEAR(ENTRYTIME) AS VARCHAR(4)) AS DATE1
> FROM NEWCCS.DBO.EMPLOYEE_TIMINGS as e
> left outer join
> (
> select empid,intime
> from newccs.dbo.intimings_history
> where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
> /***********************/
> and empid='QU'
> /***********************/
> union
> select empid,intime
> from newccs.dbo.intimings
> where '8/5/2005'>=effective_date
> /***********************/
> and empid='QU'
> /***********************/
> ) as i
> on e.empid=i.empid
> where entrytime>='8/5/2005' and entrytime<=dateadd(d,1,'8/5/2005') and
> intime<'12/30/1899 9:00:00 PM'
> --Changes by me, e.empid has to be removed
> /*and e.empid='CL'*/ and e.timetype in ('S') --and e.inout='S'
> ) as z
> ) as z /*** Absent employees of Day & Night Shift represented as 'z' ***/
> )
> /*********** End of calculation of Absent days ************/
> union
> /*************************** Start of calculation of Present days
> **************************************/
> select '8/5/2005' as daysabsent,e.empid as empid,intime,type,cast('8/5/200
5'
> as datetime) as absentdays,'P'
> from ccsmay26.dbo.employee as e
> left outer join
> (
> select empid,intime,type=case when intime>='12/30/1899 9:00:00 PM' then '
N'
> else 'M' end
> from
> (
> select empid,effective_date,end_date,intime
> from newccs.dbo.intimings_history
> where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
> /***********************/
> and empid='QU'
> /***********************/
> union
> select empid,effective_date,null as end_date,intime
> from newccs.dbo.intimings
> where '8/5/2005'>=effective_date
> /***********************/
> and empid='QU'
> /***********************/
> ) as a
> ) as t
> on e.empid=t.empid
> where depid<>'UST' and
> (
> (emp_status<>'T' and hire_date<='8/1/2005') or (emp_status='T' and
> termination_date>'8/5/2005')
> )
> and e.empid='QU' and e.empid in
> (
> /*****************Calculation of no. of days absent of an
> employee***********************/
> select empid
> from
> (
> SELECT DISTINCT CAST(DATE1 AS DATETIME) as daysabsent,empid
> FROM
> (
> /*****************Employees of the Night shift********************/
> SELECT e.*,CAST(MONTH(ENTRYTIME) AS VARCHAR(2))+'/'+CAST(DAY(ENTRYTIME)
> AS VARCHAR(2)) +'/'+
> CAST(YEAR(ENTRYTIME) AS VARCHAR(4)) AS DATE1
> FROM NEWCCS.DBO.EMPLOYEE_TIMINGS as e
> left outer join
> (
> select empid,intime
> from newccs.dbo.intimings_history
> where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
> /***********************/
> and empid='QU'
> /***********************/
> union
> select empid,intime
> from newccs.dbo.intimings
> where '8/5/2005'>=effective_date
> /***********************/
> and empid='QU'
> /***********************/
> ) as i
> on e.empid=i.empid
> where entrytime>='8/5/2005 8:00:00 PM' and
> entrytime<=dateadd(d,1,'8/5/2005 5:00:00 PM') and intime>='12/30/1899
> 9:00:00 PM'
> --Changes by me, e.empid has to be removed
> /*and e.empid='CL'*/ and e.timetype='S' and e.inout='S' --and e.timetyp
e
> not in ('V','B')
> union
> /******************Employees of the Morning shift******************/
> SELECT e.*,CAST(MONTH(ENTRYTIME) AS VARCHAR(2))+'/'+CAST(DAY(ENTRYTIME)
> AS VARCHAR(2))
> +'/'+CAST(YEAR(ENTRYTIME) AS VARCHAR(4)) AS DATE1
> FROM NEWCCS.DBO.EMPLOYEE_TIMINGS as e
> left outer join
> (
> select empid,intime
> from newccs.dbo.intimings_history
> where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
> /***********************/
> and empid='QU'
> /***********************/
> union
> select empid,intime
> from newccs.dbo.intimings
> where '8/5/2005'>=effective_date
> /***********************/
> and empid='QU'
> /***********************/
> ) as i
> on e.empid=i.empid
> where entrytime>='8/5/2005' and entrytime<=dateadd(d,1,'8/5/2005') and
> intime<'12/30/1899 9:00:00 PM'
> --Changes by me, e.empid has to be removed
> /*and e.empid='CL'*/ and e.timetype='S' and e.inout='S' --and e.timetyp
e
> not in ('V','B')
> ) as z
> ) as z /*** Absent employees of Day & Night Shift represented as 'z' ***/
> )
> --end queri3es|||On Tue, 4 Oct 2005 00:05:04 -0700, raghu veer wrote:
>programmers are writing a loop in .net and passing empid,date to server
>there are 700 employees so the round trips are increasing
(snip)
Hi Raghu,
The above suggests that your query is executed once for each employee.
If that is true, than your first goal should be to rewrite the querty
such that it will process all employees in one pass. Not only to get
back from 700 roundtrips to one, but also to enable SQL Server to create
a much more efficient execution plan.
Unfortunately, your posted SQL is way too long for an in-depth analysis
in the time that I can spare here. Some observations:
- Use unambiguous date and datetime formats:
yyyymmdd (date only),
yyyy-mm-ddThh:mm:ss (date and time), and
yyyy-mm-ddThh:mm:ss.mmm (ditto, incl milliseconds)
- Check if you can use UNION ALL instead of UNION. The latter will
attempt to remove duplicates, whereas the first won't. If you know
you'll never have duplicates, you can safely use UNION ALL and save the
server some work.
- There is some needless complexion in your code. The code below just
struck me:
> select empid,effective_date,end_date,intime
> from newccs.dbo.intimings_history
> where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
> /***********************/
> and empid='QU'
> /***********************/
> union
> select empid,effective_date,null as end_date,intime
> from newccs.dbo.intimings
> where '8/5/2005'>=effective_date
> /***********************/
> and empid='QU'
> /***********************/
is equivalent to
select empid,effective_date,end_date,intime
from newccs.dbo.intimings_history
where '8/5/2005'>=effective_date
/***********************/
and empid='QU'
/***********************/
There might be more similar code; I didn't look for it.
- I get the feeling that you have the same subqueries repeated several
times in your code. This is sometimes necessary - but it can often be
eliminated.
If you want more help, then instead of posting a 200+ line query, post
the following information instead. Check www.,aspfaq.com/5006 as well.
* CREATE TABLE statements for all tables involved in your problem,
including all constraints and properties. Irrelevant columns may be
omitted.
* INSERT statements with a few rows of sample data. We don't need
hundred's of rows - just enough (but not less!!) to illustrate what
you're trying to do and what the important exceptions (if any!) are.
* Expected results.
* A description of the business problem, including all business rules we
need to know in order to solve the problem.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
Not trying to be picky, but you must have missed this in your quick
read:
select empid,effective_date,
end_date = CASE WHEN '8/5/2005'<=end_date
THEN end_date
ELSE NULL END,
intime
from newccs.dbo.intimings_history
where '8/5/2005'>=effective_date
/***********************/
and empid='QU'
/***********************/
Didn't want the OP focusing on a typo when there are larger issues at
hand.
Stu
Hugo Kornelis wrote:
> On Tue, 4 Oct 2005 00:05:04 -0700, raghu veer wrote:
> - There is some needless complexion in your code. The code below just
> struck me:
> is equivalent to
> select empid,effective_date,end_date,intime
> from newccs.dbo.intimings_history
> where '8/5/2005'>=effective_date
> /***********************/
> and empid='QU'
> /***********************/|||Raghu
Better Construct from scratch.Dont try to reconstruct. It is in complete mes
s.
--
Regards
R.D
--Knowledge gets doubled when shared
"Stu" wrote:
> Hugo,
> Not trying to be picky, but you must have missed this in your quick
> read:
> select empid,effective_date,
> end_date = CASE WHEN '8/5/2005'<=end_date
> THEN end_date
> ELSE NULL END,
> intime
> from newccs.dbo.intimings_history
> where '8/5/2005'>=effective_date
> /***********************/
> and empid='QU'
> /***********************/
> Didn't want the OP focusing on a typo when there are larger issues at
> hand.
> Stu
> Hugo Kornelis wrote:
>|||On 4 Oct 2005 19:01:39 -0700, Stu wrote:
>Hugo,
>Not trying to be picky, but you must have missed this in your quick
>read:
> select empid,effective_date,
> end_date = CASE WHEN '8/5/2005'<=end_date
> THEN end_date
> ELSE NULL END,
> intime
> from newccs.dbo.intimings_history
> where '8/5/2005'>=effective_date
> /***********************/
> and empid='QU'
> /***********************/
>Didn't want the OP focusing on a typo when there are larger issues at
>hand.
Hi Stu,
I'm quite sure I missed a lot of things in my quick read :-)
However, I've been starting at this snippet for some time now, thinking
that there must be a reason that you singled this out. But I still fail
to see what is wrong with this code (apart from using non-standard date
formats and being generally hard to read because of the reversed order
for comparisons).
What am I missing?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hey Hugo,
Here's the OP statement:
> select empid,effective_date,end_date,intime
> from newccs.dbo.intimings_history
> where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
> /***********************/
> and empid='QU'
> /***********************/
> union
> select empid,effective_date,null as end_date,intime
> from newccs.dbo.intimings
> where '8/5/2005'>=effective_date
> /***********************/
> and empid='QU'
> /***********************/
And here's the code you posted:
select empid,effective_date,end_date,intime
from newccs.dbo.intimings_history
where '8/5/2005'>=effective_date
/***********************/
and empid='QU'
/***********************/
Note that in your statement, end_date is not NULLified in the resultset
when the '8/25/2005' > end_date. If the end_date is in the past, your
query will return a value, whereas the OP code will NULL out that
value.
Here's my simplified test:
/*CREATE TABLE TestDate (effective_date smalldatetime, end_date
smalldatetime)
INSERT INTO TestDate (effective_date, end_date)
SELECT '8/5/2005', '8/1/2005'
UNION ALL
SELECT '8/5/2005', '8/5/2005'
UNION ALL
SELECT '8/5/2005', '8/15/2005'
*/
--OP code, minus columns and a WHERE clause
select effective_date,end_date
from TestDate
where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
union
select effective_date,null as end_date
from TestDate
where '8/5/2005'>=effective_date
--Hugo code, minus columns and a WHERE clause
select effective_date,end_date
from TestDate
where '8/5/2005'>=effective_date
--my code, minus columns and a WHERE clause
select effective_date,
end_date = CASE WHEN '8/5/2005'<=end_date
THEN end_date
ELSE NULL END
from TestDate
where '8/5/2005'>=effective_date
As I said in my earlier post, there are other issues that the OP needs
to resolve, and your post mentioned many of them. I just didn't want
him to struggle in trying to understand why his results would be
different in the code snippet you sampled.
Stu|||On 5 Oct 2005 14:48:54 -0700, Stu wrote:
>Hey Hugo,
(snip)
>Note that in your statement, end_date is not NULLified in the resultset
>when the '8/25/2005' > end_date. If the end_date is in the past, your
>query will return a value, whereas the OP code will NULL out that
>value.
Hi Stu,
Yoiu're absolutely right. Thanks for pointing this out.
I've now been starting for several minutes at the code, trying to figure
out how I could have madde a mistake like that. It's not just a mere
oversight - I clearly remember thinking about both aprts of the UNION,
how they interact, and that the end effect would be the same as just the
single simple query I posted. I must have had quite a brain lapse when I
wrote my reply. :-(
Dang! I just saw another thing we both overlooked thus far.
The first and the second part of the union actually don't even access
the same table.... I feel SOOOOOO stupid now!!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||DOH!!!
"Why do you see the speck that is in your brother's eye, but don't
consider the beam that is in your own eye?"
I feel really stupid as well. And just think, this post will live on
for years.....
:)
Stu
there are 700 employees so the round trips are increasing
--queries
select '8/5/2005' as daysabsent,e.empid as empid,intime,type,cast('8/5/2005'
as datetime) as absentdays,'A'
from ccsmay26.dbo.employee as e
left outer join
(
select empid,intime,type=case when intime>='12/30/1899 9:00:00 PM' then 'N'
else 'M' end
from
(
select empid,effective_date,end_date,intime
from newccs.dbo.intimings_history
where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
/***********************/
and empid='QU'
/***********************/
union
select empid,effective_date,null as end_date,intime
from newccs.dbo.intimings
where '8/5/2005'>=effective_date
/***********************/
and empid='QU'
/***********************/
) as a
) as t
on e.empid=t.empid
where depid<>'UST' and
(
(emp_status<>'T' and hire_date<='8/1/2005') or (emp_status='T' and
termination_date>'8/5/2005')
)
and e.empid='QU' and e.empid not in
(
/*****************Calculation of no. of days absent of an
employee***********************/
select empid
from
(
SELECT DISTINCT CAST(DATE1 AS DATETIME) as daysabsent,empid
FROM
(
/*****************Employees of the Night shift********************/
SELECT e.*,CAST(MONTH(ENTRYTIME) AS VARCHAR(2))+'/'+CAST(DAY(ENTRYTIME)
AS VARCHAR(2)) +'/'+
CAST(YEAR(ENTRYTIME) AS VARCHAR(4)) AS DATE1
FROM NEWCCS.DBO.EMPLOYEE_TIMINGS as e
left outer join
(
select empid,intime
from newccs.dbo.intimings_history
where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
/***********************/
and empid='QU'
/***********************/
union
select empid,intime
from newccs.dbo.intimings
where '8/5/2005'>=effective_date
/***********************/
and empid='QU'
/***********************/
) as i
on e.empid=i.empid
where entrytime>='8/5/2005 8:00:00 PM' and
entrytime<=dateadd(d,1,'8/5/2005 5:00:00 PM') and intime>='12/30/1899
9:00:00 PM'
--Changes by me, e.empid has to be removed
/*and e.empid='CL'*/ and e.timetype in ('S') --and e.inout='S'
union
/******************Employees of the Morning shift******************/
SELECT e.*,CAST(MONTH(ENTRYTIME) AS VARCHAR(2))+'/'+CAST(DAY(ENTRYTIME)
AS VARCHAR(2))
+'/'+CAST(YEAR(ENTRYTIME) AS VARCHAR(4)) AS DATE1
FROM NEWCCS.DBO.EMPLOYEE_TIMINGS as e
left outer join
(
select empid,intime
from newccs.dbo.intimings_history
where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
/***********************/
and empid='QU'
/***********************/
union
select empid,intime
from newccs.dbo.intimings
where '8/5/2005'>=effective_date
/***********************/
and empid='QU'
/***********************/
) as i
on e.empid=i.empid
where entrytime>='8/5/2005' and entrytime<=dateadd(d,1,'8/5/2005') and
intime<'12/30/1899 9:00:00 PM'
--Changes by me, e.empid has to be removed
/*and e.empid='CL'*/ and e.timetype in ('S') --and e.inout='S'
) as z
) as z /*** Absent employees of Day & Night Shift represented as 'z' ***/
)
/*********** End of calculation of Absent days ************/
union
/*************************** Start of calculation of Present days
**************************************/
select '8/5/2005' as daysabsent,e.empid as empid,intime,type,cast('8/5/2005'
as datetime) as absentdays,'P'
from ccsmay26.dbo.employee as e
left outer join
(
select empid,intime,type=case when intime>='12/30/1899 9:00:00 PM' then 'N'
else 'M' end
from
(
select empid,effective_date,end_date,intime
from newccs.dbo.intimings_history
where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
/***********************/
and empid='QU'
/***********************/
union
select empid,effective_date,null as end_date,intime
from newccs.dbo.intimings
where '8/5/2005'>=effective_date
/***********************/
and empid='QU'
/***********************/
) as a
) as t
on e.empid=t.empid
where depid<>'UST' and
(
(emp_status<>'T' and hire_date<='8/1/2005') or (emp_status='T' and
termination_date>'8/5/2005')
)
and e.empid='QU' and e.empid in
(
/*****************Calculation of no. of days absent of an
employee***********************/
select empid
from
(
SELECT DISTINCT CAST(DATE1 AS DATETIME) as daysabsent,empid
FROM
(
/*****************Employees of the Night shift********************/
SELECT e.*,CAST(MONTH(ENTRYTIME) AS VARCHAR(2))+'/'+CAST(DAY(ENTRYTIME)
AS VARCHAR(2)) +'/'+
CAST(YEAR(ENTRYTIME) AS VARCHAR(4)) AS DATE1
FROM NEWCCS.DBO.EMPLOYEE_TIMINGS as e
left outer join
(
select empid,intime
from newccs.dbo.intimings_history
where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
/***********************/
and empid='QU'
/***********************/
union
select empid,intime
from newccs.dbo.intimings
where '8/5/2005'>=effective_date
/***********************/
and empid='QU'
/***********************/
) as i
on e.empid=i.empid
where entrytime>='8/5/2005 8:00:00 PM' and
entrytime<=dateadd(d,1,'8/5/2005 5:00:00 PM') and intime>='12/30/1899
9:00:00 PM'
--Changes by me, e.empid has to be removed
/*and e.empid='CL'*/ and e.timetype='S' and e.inout='S' --and e.timetype
not in ('V','B')
union
/******************Employees of the Morning shift******************/
SELECT e.*,CAST(MONTH(ENTRYTIME) AS VARCHAR(2))+'/'+CAST(DAY(ENTRYTIME)
AS VARCHAR(2))
+'/'+CAST(YEAR(ENTRYTIME) AS VARCHAR(4)) AS DATE1
FROM NEWCCS.DBO.EMPLOYEE_TIMINGS as e
left outer join
(
select empid,intime
from newccs.dbo.intimings_history
where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
/***********************/
and empid='QU'
/***********************/
union
select empid,intime
from newccs.dbo.intimings
where '8/5/2005'>=effective_date
/***********************/
and empid='QU'
/***********************/
) as i
on e.empid=i.empid
where entrytime>='8/5/2005' and entrytime<=dateadd(d,1,'8/5/2005') and
intime<'12/30/1899 9:00:00 PM'
--Changes by me, e.empid has to be removed
/*and e.empid='CL'*/ and e.timetype='S' and e.inout='S' --and e.timetype
not in ('V','B')
) as z
) as z /*** Absent employees of Day & Night Shift represented as 'z' ***/
)
--end queri3esRags
Can you post DDL/sample data and description on desired out put
--
Regards
R.D
--Knowledge gets doubled when shared
"raghu veer" wrote:
> programmers are writing a loop in .net and passing empid,date to server
> there are 700 employees so the round trips are increasing
> --queries
> select '8/5/2005' as daysabsent,e.empid as empid,intime,type,cast('8/5/200
5'
> as datetime) as absentdays,'A'
> from ccsmay26.dbo.employee as e
> left outer join
> (
> select empid,intime,type=case when intime>='12/30/1899 9:00:00 PM' then '
N'
> else 'M' end
> from
> (
> select empid,effective_date,end_date,intime
> from newccs.dbo.intimings_history
> where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
> /***********************/
> and empid='QU'
> /***********************/
> union
> select empid,effective_date,null as end_date,intime
> from newccs.dbo.intimings
> where '8/5/2005'>=effective_date
> /***********************/
> and empid='QU'
> /***********************/
> ) as a
> ) as t
> on e.empid=t.empid
> where depid<>'UST' and
> (
> (emp_status<>'T' and hire_date<='8/1/2005') or (emp_status='T' and
> termination_date>'8/5/2005')
> )
> and e.empid='QU' and e.empid not in
> (
> /*****************Calculation of no. of days absent of an
> employee***********************/
> select empid
> from
> (
> SELECT DISTINCT CAST(DATE1 AS DATETIME) as daysabsent,empid
> FROM
> (
> /*****************Employees of the Night shift********************/
> SELECT e.*,CAST(MONTH(ENTRYTIME) AS VARCHAR(2))+'/'+CAST(DAY(ENTRYTIME)
> AS VARCHAR(2)) +'/'+
> CAST(YEAR(ENTRYTIME) AS VARCHAR(4)) AS DATE1
> FROM NEWCCS.DBO.EMPLOYEE_TIMINGS as e
> left outer join
> (
> select empid,intime
> from newccs.dbo.intimings_history
> where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
> /***********************/
> and empid='QU'
> /***********************/
> union
> select empid,intime
> from newccs.dbo.intimings
> where '8/5/2005'>=effective_date
> /***********************/
> and empid='QU'
> /***********************/
> ) as i
> on e.empid=i.empid
> where entrytime>='8/5/2005 8:00:00 PM' and
> entrytime<=dateadd(d,1,'8/5/2005 5:00:00 PM') and intime>='12/30/1899
> 9:00:00 PM'
> --Changes by me, e.empid has to be removed
> /*and e.empid='CL'*/ and e.timetype in ('S') --and e.inout='S'
> union
> /******************Employees of the Morning shift******************/
> SELECT e.*,CAST(MONTH(ENTRYTIME) AS VARCHAR(2))+'/'+CAST(DAY(ENTRYTIME)
> AS VARCHAR(2))
> +'/'+CAST(YEAR(ENTRYTIME) AS VARCHAR(4)) AS DATE1
> FROM NEWCCS.DBO.EMPLOYEE_TIMINGS as e
> left outer join
> (
> select empid,intime
> from newccs.dbo.intimings_history
> where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
> /***********************/
> and empid='QU'
> /***********************/
> union
> select empid,intime
> from newccs.dbo.intimings
> where '8/5/2005'>=effective_date
> /***********************/
> and empid='QU'
> /***********************/
> ) as i
> on e.empid=i.empid
> where entrytime>='8/5/2005' and entrytime<=dateadd(d,1,'8/5/2005') and
> intime<'12/30/1899 9:00:00 PM'
> --Changes by me, e.empid has to be removed
> /*and e.empid='CL'*/ and e.timetype in ('S') --and e.inout='S'
> ) as z
> ) as z /*** Absent employees of Day & Night Shift represented as 'z' ***/
> )
> /*********** End of calculation of Absent days ************/
> union
> /*************************** Start of calculation of Present days
> **************************************/
> select '8/5/2005' as daysabsent,e.empid as empid,intime,type,cast('8/5/200
5'
> as datetime) as absentdays,'P'
> from ccsmay26.dbo.employee as e
> left outer join
> (
> select empid,intime,type=case when intime>='12/30/1899 9:00:00 PM' then '
N'
> else 'M' end
> from
> (
> select empid,effective_date,end_date,intime
> from newccs.dbo.intimings_history
> where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
> /***********************/
> and empid='QU'
> /***********************/
> union
> select empid,effective_date,null as end_date,intime
> from newccs.dbo.intimings
> where '8/5/2005'>=effective_date
> /***********************/
> and empid='QU'
> /***********************/
> ) as a
> ) as t
> on e.empid=t.empid
> where depid<>'UST' and
> (
> (emp_status<>'T' and hire_date<='8/1/2005') or (emp_status='T' and
> termination_date>'8/5/2005')
> )
> and e.empid='QU' and e.empid in
> (
> /*****************Calculation of no. of days absent of an
> employee***********************/
> select empid
> from
> (
> SELECT DISTINCT CAST(DATE1 AS DATETIME) as daysabsent,empid
> FROM
> (
> /*****************Employees of the Night shift********************/
> SELECT e.*,CAST(MONTH(ENTRYTIME) AS VARCHAR(2))+'/'+CAST(DAY(ENTRYTIME)
> AS VARCHAR(2)) +'/'+
> CAST(YEAR(ENTRYTIME) AS VARCHAR(4)) AS DATE1
> FROM NEWCCS.DBO.EMPLOYEE_TIMINGS as e
> left outer join
> (
> select empid,intime
> from newccs.dbo.intimings_history
> where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
> /***********************/
> and empid='QU'
> /***********************/
> union
> select empid,intime
> from newccs.dbo.intimings
> where '8/5/2005'>=effective_date
> /***********************/
> and empid='QU'
> /***********************/
> ) as i
> on e.empid=i.empid
> where entrytime>='8/5/2005 8:00:00 PM' and
> entrytime<=dateadd(d,1,'8/5/2005 5:00:00 PM') and intime>='12/30/1899
> 9:00:00 PM'
> --Changes by me, e.empid has to be removed
> /*and e.empid='CL'*/ and e.timetype='S' and e.inout='S' --and e.timetyp
e
> not in ('V','B')
> union
> /******************Employees of the Morning shift******************/
> SELECT e.*,CAST(MONTH(ENTRYTIME) AS VARCHAR(2))+'/'+CAST(DAY(ENTRYTIME)
> AS VARCHAR(2))
> +'/'+CAST(YEAR(ENTRYTIME) AS VARCHAR(4)) AS DATE1
> FROM NEWCCS.DBO.EMPLOYEE_TIMINGS as e
> left outer join
> (
> select empid,intime
> from newccs.dbo.intimings_history
> where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
> /***********************/
> and empid='QU'
> /***********************/
> union
> select empid,intime
> from newccs.dbo.intimings
> where '8/5/2005'>=effective_date
> /***********************/
> and empid='QU'
> /***********************/
> ) as i
> on e.empid=i.empid
> where entrytime>='8/5/2005' and entrytime<=dateadd(d,1,'8/5/2005') and
> intime<'12/30/1899 9:00:00 PM'
> --Changes by me, e.empid has to be removed
> /*and e.empid='CL'*/ and e.timetype='S' and e.inout='S' --and e.timetyp
e
> not in ('V','B')
> ) as z
> ) as z /*** Absent employees of Day & Night Shift represented as 'z' ***/
> )
> --end queri3es|||On Tue, 4 Oct 2005 00:05:04 -0700, raghu veer wrote:
>programmers are writing a loop in .net and passing empid,date to server
>there are 700 employees so the round trips are increasing
(snip)
Hi Raghu,
The above suggests that your query is executed once for each employee.
If that is true, than your first goal should be to rewrite the querty
such that it will process all employees in one pass. Not only to get
back from 700 roundtrips to one, but also to enable SQL Server to create
a much more efficient execution plan.
Unfortunately, your posted SQL is way too long for an in-depth analysis
in the time that I can spare here. Some observations:
- Use unambiguous date and datetime formats:
yyyymmdd (date only),
yyyy-mm-ddThh:mm:ss (date and time), and
yyyy-mm-ddThh:mm:ss.mmm (ditto, incl milliseconds)
- Check if you can use UNION ALL instead of UNION. The latter will
attempt to remove duplicates, whereas the first won't. If you know
you'll never have duplicates, you can safely use UNION ALL and save the
server some work.
- There is some needless complexion in your code. The code below just
struck me:
> select empid,effective_date,end_date,intime
> from newccs.dbo.intimings_history
> where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
> /***********************/
> and empid='QU'
> /***********************/
> union
> select empid,effective_date,null as end_date,intime
> from newccs.dbo.intimings
> where '8/5/2005'>=effective_date
> /***********************/
> and empid='QU'
> /***********************/
is equivalent to
select empid,effective_date,end_date,intime
from newccs.dbo.intimings_history
where '8/5/2005'>=effective_date
/***********************/
and empid='QU'
/***********************/
There might be more similar code; I didn't look for it.
- I get the feeling that you have the same subqueries repeated several
times in your code. This is sometimes necessary - but it can often be
eliminated.
If you want more help, then instead of posting a 200+ line query, post
the following information instead. Check www.,aspfaq.com/5006 as well.
* CREATE TABLE statements for all tables involved in your problem,
including all constraints and properties. Irrelevant columns may be
omitted.
* INSERT statements with a few rows of sample data. We don't need
hundred's of rows - just enough (but not less!!) to illustrate what
you're trying to do and what the important exceptions (if any!) are.
* Expected results.
* A description of the business problem, including all business rules we
need to know in order to solve the problem.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
Not trying to be picky, but you must have missed this in your quick
read:
select empid,effective_date,
end_date = CASE WHEN '8/5/2005'<=end_date
THEN end_date
ELSE NULL END,
intime
from newccs.dbo.intimings_history
where '8/5/2005'>=effective_date
/***********************/
and empid='QU'
/***********************/
Didn't want the OP focusing on a typo when there are larger issues at
hand.
Stu
Hugo Kornelis wrote:
> On Tue, 4 Oct 2005 00:05:04 -0700, raghu veer wrote:
> - There is some needless complexion in your code. The code below just
> struck me:
> is equivalent to
> select empid,effective_date,end_date,intime
> from newccs.dbo.intimings_history
> where '8/5/2005'>=effective_date
> /***********************/
> and empid='QU'
> /***********************/|||Raghu
Better Construct from scratch.Dont try to reconstruct. It is in complete mes
s.
--
Regards
R.D
--Knowledge gets doubled when shared
"Stu" wrote:
> Hugo,
> Not trying to be picky, but you must have missed this in your quick
> read:
> select empid,effective_date,
> end_date = CASE WHEN '8/5/2005'<=end_date
> THEN end_date
> ELSE NULL END,
> intime
> from newccs.dbo.intimings_history
> where '8/5/2005'>=effective_date
> /***********************/
> and empid='QU'
> /***********************/
> Didn't want the OP focusing on a typo when there are larger issues at
> hand.
> Stu
> Hugo Kornelis wrote:
>|||On 4 Oct 2005 19:01:39 -0700, Stu wrote:
>Hugo,
>Not trying to be picky, but you must have missed this in your quick
>read:
> select empid,effective_date,
> end_date = CASE WHEN '8/5/2005'<=end_date
> THEN end_date
> ELSE NULL END,
> intime
> from newccs.dbo.intimings_history
> where '8/5/2005'>=effective_date
> /***********************/
> and empid='QU'
> /***********************/
>Didn't want the OP focusing on a typo when there are larger issues at
>hand.
Hi Stu,
I'm quite sure I missed a lot of things in my quick read :-)
However, I've been starting at this snippet for some time now, thinking
that there must be a reason that you singled this out. But I still fail
to see what is wrong with this code (apart from using non-standard date
formats and being generally hard to read because of the reversed order
for comparisons).
What am I missing?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hey Hugo,
Here's the OP statement:
> select empid,effective_date,end_date,intime
> from newccs.dbo.intimings_history
> where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
> /***********************/
> and empid='QU'
> /***********************/
> union
> select empid,effective_date,null as end_date,intime
> from newccs.dbo.intimings
> where '8/5/2005'>=effective_date
> /***********************/
> and empid='QU'
> /***********************/
And here's the code you posted:
select empid,effective_date,end_date,intime
from newccs.dbo.intimings_history
where '8/5/2005'>=effective_date
/***********************/
and empid='QU'
/***********************/
Note that in your statement, end_date is not NULLified in the resultset
when the '8/25/2005' > end_date. If the end_date is in the past, your
query will return a value, whereas the OP code will NULL out that
value.
Here's my simplified test:
/*CREATE TABLE TestDate (effective_date smalldatetime, end_date
smalldatetime)
INSERT INTO TestDate (effective_date, end_date)
SELECT '8/5/2005', '8/1/2005'
UNION ALL
SELECT '8/5/2005', '8/5/2005'
UNION ALL
SELECT '8/5/2005', '8/15/2005'
*/
--OP code, minus columns and a WHERE clause
select effective_date,end_date
from TestDate
where '8/5/2005'>=effective_date and '8/5/2005'<=end_date
union
select effective_date,null as end_date
from TestDate
where '8/5/2005'>=effective_date
--Hugo code, minus columns and a WHERE clause
select effective_date,end_date
from TestDate
where '8/5/2005'>=effective_date
--my code, minus columns and a WHERE clause
select effective_date,
end_date = CASE WHEN '8/5/2005'<=end_date
THEN end_date
ELSE NULL END
from TestDate
where '8/5/2005'>=effective_date
As I said in my earlier post, there are other issues that the OP needs
to resolve, and your post mentioned many of them. I just didn't want
him to struggle in trying to understand why his results would be
different in the code snippet you sampled.
Stu|||On 5 Oct 2005 14:48:54 -0700, Stu wrote:
>Hey Hugo,
(snip)
>Note that in your statement, end_date is not NULLified in the resultset
>when the '8/25/2005' > end_date. If the end_date is in the past, your
>query will return a value, whereas the OP code will NULL out that
>value.
Hi Stu,
Yoiu're absolutely right. Thanks for pointing this out.
I've now been starting for several minutes at the code, trying to figure
out how I could have madde a mistake like that. It's not just a mere
oversight - I clearly remember thinking about both aprts of the UNION,
how they interact, and that the end effect would be the same as just the
single simple query I posted. I must have had quite a brain lapse when I
wrote my reply. :-(
Dang! I just saw another thing we both overlooked thus far.
The first and the second part of the union actually don't even access
the same table.... I feel SOOOOOO stupid now!!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||DOH!!!
"Why do you see the speck that is in your brother's eye, but don't
consider the beam that is in your own eye?"
I feel really stupid as well. And just think, this post will live on
for years.....
:)
Stu
Tuesday, March 20, 2012
Planning ahead w/ SQL Server...
With the current web app that I am writing, I am starting to plan ahead to the scalability problems that I am planning on encountering with the number of users that I may have...
My hosting plan now allows for a 50MB SQL Server database, but, I know that will not last long, each user will be using 3-5MB each of the database, so I am going to outgrow my space fast.
Would looking into (until i have enough subscribers to get a dedicated host), SQL Hosting be a good idea? Atleast to start off with something like http://www.alentus.com/hosting/sqlserver.asp ?
But then again, would a SQL Database growing to large get bad? Within a few years, i expect to have at max 5,000 users, so that could grow to a 25GB database... with millions of rows.
Would breaking it up into smaller databases for each N amount of users be a wise idea? Or would it not really matter?
Any help is much appreciated
Happy New Year
Harold.Sql Server can easily handle huge amounts of data. In the schema of things 25 Gb and millions of rows isn't that big, particularly if the data is organized correctly and you have appropriate indexes defined. Breaking it up into smaller databases won't really help you and it would create a maintenance headache.
Of greater importance would be planning for the type of database server to handle future needs. But to start off with almost any decent server will do.
Monday, March 12, 2012
Place or book to test writing T-SQL queries?
Hi,
I am looking for a place where I get to write sql queries and test my sql knowledge.If any one can suggest me site or book where I can test my sql knowledge. I have good exposure of SQL syntax and want to test how I implement it. Also, If Forum moderator can suggest me any book on this to improve myself in T-SQL.
Regards
Download the FREE copy of SQL Server 2005 Express, and use it to test your code on your local computer. Get your copy here.
For books, look at BOTH volumes of Itzak Ben-Gan's Microsoft Press titles.
|||Jump start to test your SQL skills...http://www.sql-ex.ru/
|||Is there any book that provides sample scenarios for which one can write queries , like questions handbook? I do not have access to web all time, so , was looking for some offline solution. My requirement is getting some real life scenario query making.|||
SQL Server Express is installed locally on your computer, so it can be used whether or not you're connected to the web.
That will give you the environment in which to practice.
As to what to practice, try one of the Celko "puzzle" books or the SQL Server 2005: Applied techniques Step By Step book.
A search on Amazon.com for beginning SQL Server will also give you some possibilities.
Additionally, come to this forum, or any other sql forum where people post questions looking for help, and work on solving their issue(s).
(Even if someone else has provided a solution, disregard that solution while you work on coming up with your own and then check to see what others came up with.)
Friday, March 9, 2012
pl/sql wrappers newbie guide?
Does anyone know of a good web reasource for writing wrappers for a low level c program?
I've been given the task to write a simple wrapper by tomorrow so I don't have time to get to the book store for an O'Reilly book.
Many thanks!
CraigTry: asktom.oracle.com
Search for "external procedure" "language c"|||Thanks Tony!
I've been given the task to write a simple wrapper by tomorrow so I don't have time to get to the book store for an O'Reilly book.
Many thanks!
CraigTry: asktom.oracle.com
Search for "external procedure" "language c"|||Thanks Tony!
PL/SQL Machine Model (params, return, etc)
Hi,
I'm writing a PL/SQL program that isn't performing so well, and I was wondering if anyone knows of any good references where I can learn more about the machine model PL/SQL is operating under. If not, my specific questions are:
1) Are parameters passed by value or by reference? (i.e. if I pass a big VARCHAR2 does it do a memcpy of the whole thing?)
2) Same thing for return values...
3) How do you typically access big chunks of memory from different points in a PL/SQL program? In C I would just pass around and return a pointer to it, but I don't see an analogous construct here. From experimentation, the REF facility seems very inflexible -- I would like to create a REF to an arbitrary variable in my program, like you would with a pointer in C, but all the examples I've seen assign REF vars their values out of a table.
4) What's the closest I can get to a dynamically allocated array that I'd like to pass around my program? I was using pipelined tables, but they are incredibly slow. Now I'm using a VARRAY but it scares me because it has a max size.
Thanks! Sorry for all the questions, but I find it's very hard to guess at what's happening under the surface here.
PeteYou could start with the Oracle PL/SQL Reference:
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/toc.htm
I can tell you that IN parameters are generally passed by value, but there is a NOCOPY option to use a reference instead, which is normally used only for passing large collections as IN parameters.
I would use an "associative array" (aka index-by table) rather than a VARRAY. These do not have a declared size like a VARRAY. If you declare the array in a package specification, it can be accessed from anywhere in your code as package_name.array_name.
PL/SQL is mainly intended as a language for working closely with the database, and in most cases any performance problems come from badly tuned SQL and database designs rather than from the PL/SQL itself. For computationally intensive processes where you spend more time crunching arrays in memory than querying the database, Pro*C may be a better bet. (Or maybe your code can be redesigned to make use of SQL set-based processing rather than array-crunching).|||Well my experience / understanding is this...
- IN parameters are passed by reference. NOCOPY does not apply.
- IN OUT and OUT parameters are passed by value / copy unless NOCOPY directive is used in which case they are passed by reference (provided NOCOPY restrictions do not apply).
- The RETURN value of a function is copied to the variable to which it is assigned.
All of which means the most efficient way to pass a large variable is in IN OUT or OUT mode using NOCOPY (provided NOCOPY restrictions do not apply!). You might also consider holding the variable as a package global variable if copying it around is causing a problem (see usual reservations about global variables).|||Thanks, Padders, I got that exactly the wrong way round! Should have read the manual before opening my gob.
(FWIW, my incorrect thought process was: for an IN parameter, you only need the value; for an IN OUT or OUT parameter you need a reference so that you know where to write the changes. Seemed so "obvious" I didn't bother to check it!)
I'm writing a PL/SQL program that isn't performing so well, and I was wondering if anyone knows of any good references where I can learn more about the machine model PL/SQL is operating under. If not, my specific questions are:
1) Are parameters passed by value or by reference? (i.e. if I pass a big VARCHAR2 does it do a memcpy of the whole thing?)
2) Same thing for return values...
3) How do you typically access big chunks of memory from different points in a PL/SQL program? In C I would just pass around and return a pointer to it, but I don't see an analogous construct here. From experimentation, the REF facility seems very inflexible -- I would like to create a REF to an arbitrary variable in my program, like you would with a pointer in C, but all the examples I've seen assign REF vars their values out of a table.
4) What's the closest I can get to a dynamically allocated array that I'd like to pass around my program? I was using pipelined tables, but they are incredibly slow. Now I'm using a VARRAY but it scares me because it has a max size.
Thanks! Sorry for all the questions, but I find it's very hard to guess at what's happening under the surface here.
PeteYou could start with the Oracle PL/SQL Reference:
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/toc.htm
I can tell you that IN parameters are generally passed by value, but there is a NOCOPY option to use a reference instead, which is normally used only for passing large collections as IN parameters.
I would use an "associative array" (aka index-by table) rather than a VARRAY. These do not have a declared size like a VARRAY. If you declare the array in a package specification, it can be accessed from anywhere in your code as package_name.array_name.
PL/SQL is mainly intended as a language for working closely with the database, and in most cases any performance problems come from badly tuned SQL and database designs rather than from the PL/SQL itself. For computationally intensive processes where you spend more time crunching arrays in memory than querying the database, Pro*C may be a better bet. (Or maybe your code can be redesigned to make use of SQL set-based processing rather than array-crunching).|||Well my experience / understanding is this...
- IN parameters are passed by reference. NOCOPY does not apply.
- IN OUT and OUT parameters are passed by value / copy unless NOCOPY directive is used in which case they are passed by reference (provided NOCOPY restrictions do not apply).
- The RETURN value of a function is copied to the variable to which it is assigned.
All of which means the most efficient way to pass a large variable is in IN OUT or OUT mode using NOCOPY (provided NOCOPY restrictions do not apply!). You might also consider holding the variable as a package global variable if copying it around is causing a problem (see usual reservations about global variables).|||Thanks, Padders, I got that exactly the wrong way round! Should have read the manual before opening my gob.
(FWIW, my incorrect thought process was: for an IN parameter, you only need the value; for an IN OUT or OUT parameter you need a reference so that you know where to write the changes. Seemed so "obvious" I didn't bother to check it!)
Saturday, February 25, 2012
Pivot Tables using SQL
Hi,
I need some help in writing an SQL script that would convert the results into a pivot table in Toad. I am extracting data from an Oracle database.
I need to have each post date as a column header from the SQL below:
select ca.ACCOUNT_ID, cd.CLIENT_NAME, od.post_date, sum(oi.PRICE)
from order_details od, client_details cd, client_account ca, order_items oi
where od.DELETED = 0
and od.SERVICE_ID = 300009
and cd.CLIENT_ID = od.CLIENT_ID
and cd.CLIENT_ID = ca.CLIENT_ID
and od.ORDER_ID = oi.ORDER_ID
and od.INVOICE_DATE = oi.IDATE
group by ca.ACCOUNT_ID, cd.CLIENT_NAME, od.post_date
Thanks for any helpCheck here:
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:7086279412131,
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:419593546543,
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:766825833740,
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:925229353765,
:eek:|||Thanks...will have a look|||Also, this question has been asked and answered many times on many forums, just search for 'PIVOT TABLE' or 'CROSS TAB QUERY'. :D
I need some help in writing an SQL script that would convert the results into a pivot table in Toad. I am extracting data from an Oracle database.
I need to have each post date as a column header from the SQL below:
select ca.ACCOUNT_ID, cd.CLIENT_NAME, od.post_date, sum(oi.PRICE)
from order_details od, client_details cd, client_account ca, order_items oi
where od.DELETED = 0
and od.SERVICE_ID = 300009
and cd.CLIENT_ID = od.CLIENT_ID
and cd.CLIENT_ID = ca.CLIENT_ID
and od.ORDER_ID = oi.ORDER_ID
and od.INVOICE_DATE = oi.IDATE
group by ca.ACCOUNT_ID, cd.CLIENT_NAME, od.post_date
Thanks for any helpCheck here:
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:7086279412131,
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:419593546543,
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:766825833740,
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:925229353765,
:eek:|||Thanks...will have a look|||Also, this question has been asked and answered many times on many forums, just search for 'PIVOT TABLE' or 'CROSS TAB QUERY'. :D
Monday, February 20, 2012
Pivot Query or Cross Tab queries
I can't seem to figure out how to create a cross tab or pivot query on data.
If I was writing the SQL in Access I would write it like
TRANSFORM SUM(DTL.Amount) AS Amount
SELECT
STC.ClientID
, STC.Client
, STC.SS_ID
, STC.Region
, STC.[Sales Director]
, STC.[Product Consultant]
, STC.CCC
FROM (SELECT
STC.DW_SELLER_ID AS ClientID
, STC.SELLER_NAME AS Client
, STC.SS_ID
, STC.TEAM_ACQ_NAME AS Region
, STC.ACCOUNT_MANAGER_NAME AS [Sales Director]
, STC.[Product Consultant]
, CTC.CLIENT_CENTRIC_CATEGORY_DSCR AS CCC
FROM dbo.tSeller_Team_Current AS STC
LEFT JOIN OPENQUERY(SSOT_DWDB,
'SELECT DW_CLIENT_ID, CLIENT_CENTRIC_CATEGORY_DSCR
FROM DW.DW_SALES_CLIENT_TEAM_CURR') AS CTC
ON STC.DW_SELLER_ID = CTC.DW_CLIENT_ID
WHERE STC.Channel='RF'
AND STC.SELLER_ACTIVATION_DATE Is Not Null
AND SELLER_CLOSED_DATE Is Null) AS STC
LEFT JOIN dbo.tCRC_Details AS DTL
ON STC.ClientID=DTL.ClientID
GROUP BY
STC.ClientID
, STC.Client
, STC.SS_ID
, STC.Region
, STC.[Sales Director]
, STC.[Product Consultant]
, STC.CCC
PIVOT DTL.[Description]
How could I write the query so it works in a View?
Thanks in advance.
Mark
I don't believe you can do crosstab queries within SQL Server like you can
in Access
"MChrist" <MChrist@.discussions.microsoft.com> wrote in message
news:C5FE6FA4-8AB9-49DE-BA7F-1BD0F226B01A@.microsoft.com...
>I can't seem to figure out how to create a cross tab or pivot query on
>data.
> If I was writing the SQL in Access I would write it like
> TRANSFORM SUM(DTL.Amount) AS Amount
> SELECT
> STC.ClientID
> , STC.Client
> , STC.SS_ID
> , STC.Region
> , STC.[Sales Director]
> , STC.[Product Consultant]
> , STC.CCC
> FROM (SELECT
> STC.DW_SELLER_ID AS ClientID
> , STC.SELLER_NAME AS Client
> , STC.SS_ID
> , STC.TEAM_ACQ_NAME AS Region
> , STC.ACCOUNT_MANAGER_NAME AS [Sales Director]
> , STC.[Product Consultant]
> , CTC.CLIENT_CENTRIC_CATEGORY_DSCR AS CCC
> FROM dbo.tSeller_Team_Current AS STC
> LEFT JOIN OPENQUERY(SSOT_DWDB,
> 'SELECT DW_CLIENT_ID, CLIENT_CENTRIC_CATEGORY_DSCR
> FROM DW.DW_SALES_CLIENT_TEAM_CURR') AS CTC
> ON STC.DW_SELLER_ID = CTC.DW_CLIENT_ID
> WHERE STC.Channel='RF'
> AND STC.SELLER_ACTIVATION_DATE Is Not Null
> AND SELLER_CLOSED_DATE Is Null) AS STC
> LEFT JOIN dbo.tCRC_Details AS DTL
> ON STC.ClientID=DTL.ClientID
> GROUP BY
> STC.ClientID
> , STC.Client
> , STC.SS_ID
> , STC.Region
> , STC.[Sales Director]
> , STC.[Product Consultant]
> , STC.CCC
> PIVOT DTL.[Description]
> How could I write the query so it works in a View?
> Thanks in advance.
> Mark
>
|||Thanks for your response Al. After scoping books and the help for several
hours that's the conclusion I came to also. Strange how the little baby
brother can do something that big brother can't. But that's as consistent as
the use of functions and naming conventions across MS software.
Have a great weekend.
Mark
"Al Newbie" wrote:
> I don't believe you can do crosstab queries within SQL Server like you can
> in Access
> "MChrist" <MChrist@.discussions.microsoft.com> wrote in message
> news:C5FE6FA4-8AB9-49DE-BA7F-1BD0F226B01A@.microsoft.com...
>
>
If I was writing the SQL in Access I would write it like
TRANSFORM SUM(DTL.Amount) AS Amount
SELECT
STC.ClientID
, STC.Client
, STC.SS_ID
, STC.Region
, STC.[Sales Director]
, STC.[Product Consultant]
, STC.CCC
FROM (SELECT
STC.DW_SELLER_ID AS ClientID
, STC.SELLER_NAME AS Client
, STC.SS_ID
, STC.TEAM_ACQ_NAME AS Region
, STC.ACCOUNT_MANAGER_NAME AS [Sales Director]
, STC.[Product Consultant]
, CTC.CLIENT_CENTRIC_CATEGORY_DSCR AS CCC
FROM dbo.tSeller_Team_Current AS STC
LEFT JOIN OPENQUERY(SSOT_DWDB,
'SELECT DW_CLIENT_ID, CLIENT_CENTRIC_CATEGORY_DSCR
FROM DW.DW_SALES_CLIENT_TEAM_CURR') AS CTC
ON STC.DW_SELLER_ID = CTC.DW_CLIENT_ID
WHERE STC.Channel='RF'
AND STC.SELLER_ACTIVATION_DATE Is Not Null
AND SELLER_CLOSED_DATE Is Null) AS STC
LEFT JOIN dbo.tCRC_Details AS DTL
ON STC.ClientID=DTL.ClientID
GROUP BY
STC.ClientID
, STC.Client
, STC.SS_ID
, STC.Region
, STC.[Sales Director]
, STC.[Product Consultant]
, STC.CCC
PIVOT DTL.[Description]
How could I write the query so it works in a View?
Thanks in advance.
Mark
I don't believe you can do crosstab queries within SQL Server like you can
in Access
"MChrist" <MChrist@.discussions.microsoft.com> wrote in message
news:C5FE6FA4-8AB9-49DE-BA7F-1BD0F226B01A@.microsoft.com...
>I can't seem to figure out how to create a cross tab or pivot query on
>data.
> If I was writing the SQL in Access I would write it like
> TRANSFORM SUM(DTL.Amount) AS Amount
> SELECT
> STC.ClientID
> , STC.Client
> , STC.SS_ID
> , STC.Region
> , STC.[Sales Director]
> , STC.[Product Consultant]
> , STC.CCC
> FROM (SELECT
> STC.DW_SELLER_ID AS ClientID
> , STC.SELLER_NAME AS Client
> , STC.SS_ID
> , STC.TEAM_ACQ_NAME AS Region
> , STC.ACCOUNT_MANAGER_NAME AS [Sales Director]
> , STC.[Product Consultant]
> , CTC.CLIENT_CENTRIC_CATEGORY_DSCR AS CCC
> FROM dbo.tSeller_Team_Current AS STC
> LEFT JOIN OPENQUERY(SSOT_DWDB,
> 'SELECT DW_CLIENT_ID, CLIENT_CENTRIC_CATEGORY_DSCR
> FROM DW.DW_SALES_CLIENT_TEAM_CURR') AS CTC
> ON STC.DW_SELLER_ID = CTC.DW_CLIENT_ID
> WHERE STC.Channel='RF'
> AND STC.SELLER_ACTIVATION_DATE Is Not Null
> AND SELLER_CLOSED_DATE Is Null) AS STC
> LEFT JOIN dbo.tCRC_Details AS DTL
> ON STC.ClientID=DTL.ClientID
> GROUP BY
> STC.ClientID
> , STC.Client
> , STC.SS_ID
> , STC.Region
> , STC.[Sales Director]
> , STC.[Product Consultant]
> , STC.CCC
> PIVOT DTL.[Description]
> How could I write the query so it works in a View?
> Thanks in advance.
> Mark
>
|||Thanks for your response Al. After scoping books and the help for several
hours that's the conclusion I came to also. Strange how the little baby
brother can do something that big brother can't. But that's as consistent as
the use of functions and naming conventions across MS software.
Have a great weekend.
Mark
"Al Newbie" wrote:
> I don't believe you can do crosstab queries within SQL Server like you can
> in Access
> "MChrist" <MChrist@.discussions.microsoft.com> wrote in message
> news:C5FE6FA4-8AB9-49DE-BA7F-1BD0F226B01A@.microsoft.com...
>
>
Subscribe to:
Comments (Atom)