Prev: How do I determine default instance vs. named instance install?
Next: For SQL experts: manage automatically updates using a frequency value
From: soroush on 22 May 2010 12:28
I have a charter table containing : charter_nbr (numeric-pk), ...
a charter_leg table containing : charter_nbr(numeric),
cl_leg_nbr(nemeric-pk), cl_etd(date), cl_atd(date),...

each charter flight may have more than one leg.
and each leg has ETD(estimated date+time departure) and a ATD (actual
date+time departure)

here is the question:

I need to desplay the charter nbr of the charters that ALL of its legs have
the same ETD and ATD. meaning that any charter_nbr that ALL of its legs have
this condition will be listed.

notice that, the condition must be true for ALL the legs in order to have
the charter listed.

can anybody help please?
From: Erland Sommarskog on 22 May 2010 13:55
soroush (soroush(a)discussions.microsoft.com) writes:
> I have a charter table containing : charter_nbr (numeric-pk), ...
> a charter_leg table containing : charter_nbr(numeric),
> cl_leg_nbr(nemeric-pk), cl_etd(date), cl_atd(date),...
>
> each charter flight may have more than one leg.
> and each leg has ETD(estimated date+time departure) and a ATD (actual
> date+time departure)
>
> here is the question:
>
> I need to desplay the charter nbr of the charters that ALL of its legs
> have the same ETD and ATD. meaning that any charter_nbr that ALL of its
> legs have this condition will be listed.
>
> notice that, the condition must be true for ALL the legs in order to have
> the charter listed.

If I understand this correctly:

SELECT charter_nbr
FROM (SELECT charter_nbr, cl_etd, cl_atd, COUNT(*)
FROM charterlegs
GROUP BY charter_nbr, cl_etd, cl_atd) AS c
GROUP BY charter_nbr
HAVING COUNT(*) = 1

If this does not cut it, please post:

1) CREATE TABLE statments for your tables.
2) INSERT statements with sample data, covering important test cases.
3) The desired result given the sample.
4) Which version of SQL Server you are using.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Iain Sharp on 25 May 2010 08:29

>soroush (soroush(a)discussions.microsoft.com) writes:
>> I have a charter table containing : charter_nbr (numeric-pk), ...
>> a charter_leg table containing : charter_nbr(numeric),
>> cl_leg_nbr(nemeric-pk), cl_etd(date), cl_atd(date),...
>>
>> each charter flight may have more than one leg.
>> and each leg has ETD(estimated date+time departure) and a ATD (actual
>> date+time departure)
>>
>> here is the question:
>>
>> I need to desplay the charter nbr of the charters that ALL of its legs
>> have the same ETD and ATD. meaning that any charter_nbr that ALL of its
>> legs have this condition will be listed.
>>
>> notice that, the condition must be true for ALL the legs in order to have
>> the charter listed.
>

select charter_nbr
from charter_leg
group by charter_nbr
having max(datediff(day,cl_etd,cl_atd)) = 0

To get more information from charter itself.

select <fields>
from charter C
inner join
(select charter_nbr
from charter_leg
group by charter_nbr
having max(datediff(day,cl_etd,cl_atd)) = 0
) as ontimes on C.charter_nbr = ontimes.charter_nbr


If etd and atd are actually date-times, rather than dates, you may
want to change the test eto something like
having(max(datediff(minute,cl_etd,cl_atd)) < 15
(All those which left within 15 minutes of expected time. )
 | 
Pages: 1
Prev: How do I determine default instance vs. named instance install?
Next: For SQL experts: manage automatically updates using a frequency value