Excluded Data in a calculation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Excluded Data in a calculation

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2007-11-17 : 02:48:04

In the following query, when an es.evstrname DOES have data in os.activity in (5,7), then the query returns the expected results. However, if there was no out of service break, then the query will return results, but there will not be any data returned for the last column of the select statement. This
makes sense to me but I was hoping there was some way that I could have data returned. For instance, is there a way for me to make the query read
the - (os.actualdeparttime - os.actualarrivetime) as zero so the column would still consider the first part of the equation? Or is there a way to write a subquery that would work around this?

Select s.ldate,
es.evstrname as 'Run',
d.lastname+', '+d.firstname as 'Driver',
po.actualarrivetime as 'PullOut',
os.actualarrivetime as 'OOS',
os.actualdeparttime as 'IS',
pi.actualdeparttime as 'PullIn',
os.actualdeparttime - os.actualarrivetime as 'Break'
(pi.actualdeparttime - po.actualarrivetime) - (os.actualdeparttime - os.actualarrivetime) as 'PayTime'

From Schedules S

Join eventstrings es
On s.schid=es.schid

Join employees d
On d.employeeid=es.employeeid

Join events po
on po.evstrid=es.evstrid
and po.schid=es.schid
and po.activity=4

Join events pi
on pi.evstrid=es.evstrid
and pi.schid=es.schid
and pi.activity=3

Left Outer Join events OS
on os.evstrid=es.evstrid
and os.schid=es.schid
and os.activity in (5,7)

Where es.evstrname>=?
AND es.evstrname<=?
AND s.ldate>=?
AND s.ldate<=?

Order by s.ldate, es.evstrname, po.actualarrivetime, os.actualarrivetime, os.actualdeparttime, pi.actualdeparttime

All es.evstrname will always have an 'event activity' (pi.activity, po.activity etc) of 3 and 4. Only es.evstrname that have clocked
out of service will have data with os.activity in (5,7) (this is an out of service break).

I'm not sure if this is a format that would help, but here is some sample data. Currently, it returns the following:

Date Run Pullout OOS IS PullIn Break PayTime
10-1 101 10:00 12:00 12:30 16:00 :30 5:30
10-1 102 11:00 ---- ---- 17:00 ---- -----

I would like it to return this:

Date Run PI OOS IS PullIn Break PayTime
10-1 101 700 900 930 945 30 215
10-1 102 700 --- --- 945 -- 215

Thanks! Craig

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-18 : 09:19:13

quote:

the - (os.actualdeparttime - os.actualarrivetime) as zero so the column would still consider the first part of the equation? Or is there a way to write a subquery that would work around this?

Wrap the part needed in a Coalesce statement or isNull

COALESCE(os.actualdeparttime - os.actualarrivetime,0)

reference article here should be of use to you: http://weblogs.sqlteam.com/mladenp/articles/2937.aspx

Poor planning on your part does not constitute an emergency on my part.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2007-11-18 : 15:51:07

Thanks Data, I'll try that this week!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources