Solved

Question on SQL query

Posted on 2014-09-13
13
269 Views
Last Modified: 2014-09-13
Dear experts -
We have a table of milestones on a project - the table includes a date/time field. Each project can have many records.
We would like to pull a query that ultimately shows the next deadline (defined as the next date + sort AND sort by record ID ascending, in case of ties), and also include the record ID.
I am stumbling on this - any help would be appreciated.
Thank you!
0
Comment
Question by:terpsichore
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 40321211
Perhaps you could tell us the names of the table and the names of the fields? Here's a guess in the meantime

select
*
from milestones
where date_time_field > date()
order by date_time_field, record_id
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40321221
maybe then the SELECT TOP 1 ...
0
 

Author Comment

by:terpsichore
ID: 40321227
if I am sorting by date, what is the difference please between top, min, and first? I think this will guide me to the solution.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Accepted Solution

by:
Sheils earned 250 total points
ID: 40321236
First you need to decide what exactly is the next deadline. Is it the next date in relation to the current date (PortletPaul code address this) or is it the next uncompleted milestone.

If it is the later then you may complete a milestone before the due date that should push the deadline to the due date of the following milestone. Alternatively you may fall behind and the deadline will be in the past. Using date criteria in your SQL will not work in both scenarios.

The work around is to add a completed field (Boolean) in tblMilestones. The your SQL will be something like:-

Select * from tblMilestone Where fldCompleted=-1 Orderby fldDueDate,fldMilestoneId
0
 

Author Closing Comment

by:terpsichore
ID: 40321243
This gets me where i need to go - i am basically looking for the next uncompleted milestone (complete <> -1). I need to pick the EARLIEST record for each project (linkage through a few tables to derive the project_ID) - so I will find the MIN or FIRST, I guess, as an aggregate function. THANK YOU.
0
 
LVL 16

Expert Comment

by:Sheils
ID: 40321249
If you are sorting by date then first,min,top date will not do much if you relate them to the date field.

The definitions are as follows:-

Min: returns the smallest value of the selected column. That's the earliest date in the table.

First: returns the first value of the selected column. That will be the first date in the table or if you have a where condition the first date in the table that satisfy the where condition.

Top: Allows you to specify the number of records from the first that you want to display. eg

Select Top 5 * from tblMilestone where fldDueDate>date()

will give the next five milestones due after today.
0
 

Author Comment

by:terpsichore
ID: 40321251
thank you
0
 
LVL 16

Expert Comment

by:Sheils
ID: 40321260
If you have multiple projects and you want the next uncompleted milestone in each, you may need to use a union query that will look something like:

Select * from tblMilestone Where fldCompleted<>-1 AND fldProjectId=1 Orderby fldDueDate,fldMilestoneId

Union

Select * from tblMilestone Where fldCompleted<>-1 AND fldProjectId=2 Orderby fldDueDate,fldMilestoneId

Union

Select * from tblMilestone Where fldCompleted<>-1 AND fldProjectId=3 Orderby fldDueDate,fldMilestoneId

If this works, you may consider using vba to generate the sql programmatically so that you don't have to rewrite the query when you add a forth project.
0
 

Author Comment

by:terpsichore
ID: 40321263
thanks - what I am doing (your answers helped guide me) - is to do an aggregate query with the minimum deadline for each project, where complete <> -1. Then I am taking this query and using it to drive another query, where based on that date I look up the ID of the original record and pull data from there, if that makes sense.
0
 
LVL 16

Expert Comment

by:Sheils
ID: 40321269
As the saying goes, there's more than one way to skin a cat (my apology to all cat lovers out there). If this method is working for you then it all good.
0
 

Author Comment

by:terpsichore
ID: 40321271
the basic problem I have been struggling with - for a long time - is that I want to be sorting a record set on multiple fields and then grabbing the record ID of the one that appears first. What I have come to learn is that the First keyword has nothing to do with this, and that the sorting is ignored in aggregate functions. Thus my original question, which really requires a workaround...
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40321275
this makes absolute sense:

do an aggregate query with the minimum deadline for each project, where complete <> -1
Then I am taking this query and using it to drive another query,
0
 

Author Comment

by:terpsichore
ID: 40321277
thank you all very much - you are real experts!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question