SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help in a Query		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Oujo
Starting Member

2 Posts
Posted - 02/18/2013 :  10:35:52  Show Profile  Reply with Quote

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

India
47023 Posts
Posted - 02/18/2013 :  10:40:04  Show Profile  Reply with Quote 

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Oujo
Starting Member

2 Posts
Posted - 02/18/2013 :  23:13:51  Show Profile  Reply with Quote
it did not work.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
1390 Posts
Posted - 02/18/2013 :  23:42:18  Show Profile  Reply with Quote
It is easy to provide solution if you post sample input data and expected output

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 02/19/2013 :  00:05:12  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000