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
