Help in a Query - SQL Server Forums

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

Help in a Query

Author

Topic

Oujo
Starting Member

2 Posts

Posted - 2013-02-18 : 10:35:52

Hi all,

I have two tables that i want to combine in a report.
One table has information about the vehicles maintenance work orders.
the second table has information about the shifts details.

I am required to make a report that shows when the maintenance job order is closed and when the driver started the first shift in that vehicle after the maintenance.

Table 1:
Type ,Order_no ,car_no, WrkEndDateTime

Table 2:
car_no, datetimeout

The statement:

select wo.Type,wo.Order_no,wo.car_no,wo.WrkEndDateTime,
m.car_no,min(ray.datetimeout)
from tbl1 wo,
tbl2 ray
where ray.car_no=Unit_no
and (ray.datetimeout) >= wo.WrkEndDateTime
GROUP BY wo.car_no,ray.car_no,wo.Type,wo.Order_no,wo.WrkEndDateTime,ray.datetimeout

The statement does not work well, any help is appreciated

Regards,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 10:40:04

[code]
SELECT t2.car_no,t2.datetimeout,t1.lastOutTime
FROM Table2 t2
CROSS APPLY (SELECT MAX(WrkEndDateTime) AS lastOutTime
WHERE car_no = t2.car_no
AND WrkEndDateTime < datetimeout
)t1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Oujo
Starting Member

2 Posts

Posted - 2013-02-18 : 23:13:51

it did not work.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-18 : 23:42:18

It is easy to provide solution if you post sample input data and expected output

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-19 : 00:05:12

Please elaborate on why it didnt work
Show some sample data and then explain output you desire and output you got from above query
Otherwise we cant make out much as we cant see your system nor have access to your tables

See guidelines on posting data here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources