473,322 Members | 1,417 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

How to select data in a join where the date of the joined has to be the next highest

This statement retuns no data but is i change the statement in the 4th to last line to
(dbo.MIC_Per_Criteria.DT_Period_Start_Date <=
it retuns data ????

Expand|Select|Wrap|Line Numbers
  1. SELECT     dbo.MIC_Active_Rosters.ID_PERSONNEL, 
  2.            dbo.MIC_Active_Rosters.DT_SHEET_DAY, 
  3.            dbo.MIC_Active_Rosters.TI_SHEET_START AS,
  4.            dbo.MIC_Active_Rosters.TI_SHEET_END, 
  5.            dbo.MIC_Active_Rosters.NO_SHEET_TOTAL_TIME,
  6.            dbo.MIC_Active_Rosters.NM_TASK, 
  7.            dbo.MIC_Task.ID_TASK_TYPE AS,
  8.            dbo.MIC_Active_Rosters.ID_COST_CENTRE, 
  9.            dbo.MIC_Per_Criteria.NO_Min_Hours,
  10.            dbo.MIC_Per_Criteria.DT_Period_Start_Date
  11.  
  12. FROM   dbo.MIC_Active_Rosters LEFT OUTER JOIN
  13.        dbo.MIC_Task ON dbo.MIC_Active_Rosters.NM_TASK = dbo.MIC_Task.NM_TASK LEFT OUTER JOIN
  14.        dbo.MIC_Per_Criteria ON dbo.MIC_Active_Rosters.ID_PERSONNEL = dbo.MIC_Per_Criteria.ID_PERSONNEL LEFT OUTER JOIN
  15.        dbo.[All staff CC ORDSal and Pay Group] ON dbo.MIC_Active_Rosters.ID_PERSONNEL = dbo.[All staff CC ORDSal and Pay Group].[Emp No]
  16.  
  17. WHERE  (dbo.MIC_Active_Rosters.DT_SHEET_DAY > GETDATE() - 14) AND 
  18.        (dbo.MIC_Active_Rosters.NM_TASK <> 'RDO') and
  19.        (dbo.MIC_Per_Criteria.DT_Period_Start_Date = 
  20.            (Select MAX(dbo.MIC_Per_Criteria.DT_Period_Start_Date)
  21.                From dbo.MIC_Per_Criteria
  22.                where dbo.MIC_Per_Criteria.DT_Period_Start_Date <= dbo.MIC_Active_Rosters.DT_SHEET_DAY))
Mar 4 '11 #1
0 942

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Mark | last post by:
good spam subject ;). anyway, i'm alittle stumped. i'm in need of putting together a query that gets the next highest salary ( select max ( sal ) - 1?, from an emp_sal type table. another...
7
by: NoKetch | last post by:
I have a simple program to output log2(x) but the result is not whole numbers. Here is the code: ---------------------- #include <stdio.h> #include <math.h> int main() { double x;
5
by: Han Lim | last post by:
Dear All, I have an application written by VB.Net with connect to a Microsoft Access database. One of the forms is to select data using oleAdapter and fill it into a dataset. In the oleAdapter, i...
2
by: nikou_70 | last post by:
Hi guys, I write a program with asp that select data from data base and show data in text box in asp, I want to know how user can change data and save it in this page, (I want to view and save data...
10
by: bsn | last post by:
Hello NG I want to select data from another DB, and use this data in currentDB. I have this SQL in a VBA procedure: Sql = "SELECT * FROM Firma " It is not working Any suggestions... ...
0
by: Lisa Coal | last post by:
I am using the Insert database command several times in a visual basic macro. Every time I use the command in the macro the Select Data Source screen appears even though the source is set in the...
11
by: cooperkuo | last post by:
Dear all, I have a question about ADO in the subform. I know how to use ADO to insert/update/select data into the sigin form, but wehn I try to do it in the form with subform((Datasheet). I don't...
1
by: =?Utf-8?B?RnJhbmsgVXJheQ==?= | last post by:
Hi all I have a DataSet with 2 tables. Now I want to select data like a INNER JOIN from these tables. In SQL Syntax I would write: SELECT * FROM table1 t1 INNER JOIN table2 t2 ON t1.f1 =...
5
by: =?Utf-8?B?RnJhbmsgVXJheQ==?= | last post by:
Hi all I have a DataSet with 2 tables. Now I want to select data like a INNER JOIN from these tables. In SQL Syntax I would write: SELECT * FROM table1 t1 INNER JOIN table2 t2 ON t1.f1 =...
25
by: LBinGA | last post by:
Hello all: I'm having trouble with a nested If Statement working properly. I'm working in MS Access 2002 (OS Windows XP, ver 2002). The non-working code is going on the Form (a Single) as follows:...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.