Setting a condition for MORE THAN ONE attribute to list ANOTHER di

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?
0
Utf
5/22/2010 4:28:01 PM
sqlserver.server 1327 articles. 0 followers. Follow

2 Replies
605 Views

Similar Articles

[PageSpeed] 59

soroush (soroush@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@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

0
Erland
5/22/2010 5:55:22 PM
>soroush (soroush@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. ) 
0
Iain
5/25/2010 12:29:36 PM
Reply:

Similar Artilces:

OWA jumping mouse
I have two computers on my network that are having an interesting issue. When they are creating a message and typing away in the body of the message, randomly when they hit a key, any key, they system will act as if the mouse was clicked. If the mouse is over the send button, it would have sent the email. If the mouse was over the start button, the start menu would have come up. Where ever the mouse was left at, it will act as if it was clicked. It is very random but often. This only happens in OWA, not in outlook or any other place. Both systems are running XP pro SP2 with IE 6 SP2. ...

.pdf save as settings
Hi, In Office 2007 Pro is there a way to change the settings such as preventing content copying etc. when saving a file to pdf? I have been trialling Open Office, alongside Office 2007 Pro (full version not a trial), which allows access to these settings but I can't see a way to do it in MS Office. -- Chas MS Office doesn't do that. Chass wrote: > Hi, In Office 2007 Pro is there a way to change the settings such as > preventing content copying etc. when saving a file to pdf? I have been > trialling Open Office, alongside Office 2007 Pro (full version not...

Web query timeout setting.
Dear Group, I fill an Excel table using data that I take from an Internet site. Unfortunately, this site is very slow and so I often get a "query did not provide any data" error message. How can I increment the default web query timeout limit? Thanks in advance, Enrico. ...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

Setting up a new e-mail account
I'm trying to set up my yahoo account so that I receive my e-mail messages in my Outlook Inbox. I went to Accounts, and servers, and put in mail.yahoo.com for incoming mail (POP3) and smtp.yahoo.com for outgoing mail (smtp). When I click on the send/receive button I get an error message. Dave <anonymous@discussions.microsoft.com> wrote: > I'm trying to set up my yahoo account so that I receive > my e-mail messages in my Outlook Inbox. I went to > Accounts, and servers, and put in mail.yahoo.com for > incoming mail (POP3) and smtp.yahoo.com for outgoing mail &...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Comments to print on invoices and packing lists
We have a client with a specific need for printing customer comments and item comments: 1) They want to store comments on Customer Master. When a sales order is entered these comments should default on the sales order with an option to change. They want to mark these comments to be printed on sales order, invoice, packing list and pick ticket. 2) Items are stored with item specific comments. These comments need to come to the sales order when the order is entered with an option to change. They want to setup comments with similar setup where they print on order, invoice, pick ticket and packi...

I would like to know how to set up a 'fill in the blanks' form?
I would like to set up a templet of sorts to fill in the blanks for certificates. ...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

I want to add a mail account without setting a SMTP server. Is it possible?
I want to add a second mail account that is outside my company. Since the company firewall does not allow us to connect to SMTP-servers I want to add this account without setting the SMTP propertiy. I cannot specify the company server as it is an Exchange server and the outside one an IMAP one. Currently I have specified the outside SMTP server since outook does not allow me to add an account wihtout setting this. I keep getting annoying error messages as my computer can't connect to the outside SMTP. Any one know a way around this? cheers, mortb The company server has SMTP enabled, u...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Cant read one instance of recurring appointment
The problem we're having is a little different that what I've seen posted. A user randomly gets the message "Cant read one instance of recurring appointment. close any open appointments and try again, or recreate the appointment" several times a day. When you click OK the same message appears several times. Outlook doesn't indicate which recurring appointment is having the problem and the message just pops up at various times even if the user isn't in the calendar. We've deleted the Frmcache file and run the clean profile switch, but that hasn't worked. Has an...

Macro to change default setting on startup
I am in need of a macro that can change a default setting in excel and for it to run on startup The task is: Tools Options General Web Options Files uncheck Update links on save Below is the recording of the macro: With ActiveWorkbook.WebOptions ..RelyOnCSS = True ..OrganizeInFolder = True ..UseLongFileNames = True ..DownloadComponents = False ..RelyOnVML = False ..AllowPNG = False ..ScreenSize = msoScreenSize800x600 ..PixelsPerInch = 96 ..Encoding = msoEncodingWestern End With With Application.DefaultWebOptions ..SaveHiddenData = True ..LoadPictures = True ....

Accessing another user's calendar OL2000/OL2003/Ex2000
Here's the situation: Running Exchange2000, v 6.0, sp4. Users are on XP/OL2003 or NT/OL2000. (we're in the process of upgrading all of the workstations to XP.) VIP user is on XP/OL2003. He can access his calendar just fine on his workstation. His secretary and other office admin type people are still on NT/OL2000. They have reviewer or editor rights to his calendar. They try to access his calendar by chosing File/Open/Other User's Folder... and put in his name and calendar. When they do this, the process hangs. In Task Manager, Outlook is "Running." End the Outloo...

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...

WLM transfer to another computer
Hi, I finally moved from Windows 7 RTM to Win7 Pro 64. I did it by installing the new OS on a brand new hard drive, then installed my old hard drive in a 2.5" external enclosure. I've been successful in moving most of my files and settings over, but WLM is the exception. Can someone help answer these questions for me: 1. Where are the actual mail files stored? 2. Where is the account login info stored? 3. In Outlook and OE installing on a new computer, even after moving files, prompted for a full redownload off of the POP server. Anyway to avoid this? Is ther...

copy values generated by conditional formula in one sheet to the other work sheet as values
Hi Everybody, I have data generated by conditional formulae in work sheet1 in columns A to J. If the condition is satisfied the cell will display a realnumber, if the condition doesn't satisfied the cell will display the text"FALSE". Now I wanted to copy the cells which have the real numbers in sheet1 to sheet2 as values(as we do with paste special and paste the values) Do we have any formula or other method to copy the cells in sheet1 to sheet2. can anybody helpme out in this issue. Thanks and Regards Ramana Select your range to copy edit|goto|special|c...

Exchange 2003, Two Sites, Two Smarthosts, One Domain, No Internal mail!!
Hi, I hope there is someone out there who can point me in the right direction. We have two sites, Brisbane and Sydney, connected by a frame realy connection. Sydney is currently running exchange 2003 (upgraded by migration from exchange 5.5 about a month ago). Brisbane is currently running their mail through sendmail and sendmail only. We have just installed exchange 2003 on server 2003 box in Brisbane and are trying to get the two sites working in two routing groups. The topology will be the same in both Brisbane and Sydney, in that each city will have an external mail server running sendma...

Perhaps another Money alternative?
After years of their saying "what's Windows?" I not that the following announcement: --- Announcement: GnuCash 2.1.1 Release - 2007-04-28 17:47 GnuCash 2.1.1 released The GnuCash development team proudly announces GnuCash 2.1.1 aka "Bug-begone", the second of several unstable 2.1.x releases of the GnuCash Open Source Accounting Software which will eventually lead to the stable version 2.2.0. With this new release series, GnuCash is available on Microsoft Windows for the first time... --- I haven't checked into this further just yet, but that will certainly be...

If statment with two conditions
I have an IF statement with two condtions as noted below. I want it to work so that if column G has a y in it, it will be hidden, and also if column G has an N in it and column O has a 0 then it will be hidden. The second part doesnt work. Any thoughts? Sub Hide_new() Dim cell As Range Dim rngisect As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set rngisect = Application.Intersect(ActiveSheet.UsedRange, Range("G19: G4061")) For Each cell In rngisect If cell.Value = "Y" Then cell.EntireRow.Hidden ...

List the UNIQUE certain fields from the database
Dear Experts, Please help related to the above subject. From this discussion, got the following formula which is work well and faster for small of database. =OFFSET($G$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE(DATA)=I$1:I1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1) where $G$1, the first row of DATA. how to convert it to VBA as this time i have a huge database. for Tab Data, Advanced, Unique Record only (works well also, but i need the VBA). basically we would like to do similar like this: Data Sample Vendor Name A B...

distibution list / updating
when we update our contacts how can we get outlook to update our distribution lists at the same time. What do you mean exactly? Can you give an example? Also what version of Outlook are you using? T. >-----Original Message----- >when we update our contacts how can we get outlook to >update our distribution lists at the same time. >. > That's what the Update Now button is for. -- Russ Valentine [MVP-Outlook] "wayne" <wayne@anandasacramento.org> wrote in message news:05ba01c3d4be$e83a5e80$a301280a@phx.gbl... > when we update our contacts how can we g...

Format List Box!
Can I format a list box which has a query,so if one of the fields called [Spelling] had the word "Spelling" it will change the colour of the font for that row Thanks for any help....Bob It's not possible with the native Access ListBox control. Instead, setup a Subform control to resemble a ListBox and use ConditionalFormatting to achieve the desired look. I think Arvin has a sample on his site here: http://www.datastrat.com/Download2.html -- HTH Stephen Lebans http://www.lebans.com Access Code, Tips and Tricks Please respond only to the newsgroups so everyone can ben...

How to detect my tray icon covered by another app.?
Could someone tell me how to detect my system tray icon covered by another application? If the tray icon is covered, I don't want to update the icon. Thank you very much in advance. "L. Zhou" <lzhou97@hotmail.com> wrote in message news:u1HUfApYDHA.2476@tk2msftngp13.phx.gbl... > Could someone tell me how to detect my system tray icon covered by another > application? If the tray icon is covered, I don't want to update the icon. > > Thank you very much in advance. > > Hmm, I doubt that is possible... The only thing I think you can do is to duplicat...

Print a list of movies
I have a spreadsheet with 2 columns (No and Name of Movie). There are 322 rows. I would like to print this so that I have the first 44 rows on the left side of the page and the next 44 rows on the right side of the page. I would like to keep this setup on all pages. These cells have color formatting and numbers that I need to keep with each cell. How do I do this? -- Terri Sprague www.tlsprague.com Hi Teri, See http://www.mvps.org/dmcritchie/excel/snakecol.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/...