Post reply

Query

  • August 10, 2005 at 5:27 am

    #89341

     

    Hi,

    My DB is a MS SQL 2000 and this specific DB stores projects. Projects that can occur once during a day, but they can also last 3-4 days or up to 3-4 years.

    My question is how a query would look like where I want to find out all projects that are active right now, today, or if I want to see all projects that are planned, for eample, a year from now?

    To start with, do I need more than the two columns I have which contains the "Start-date" and the "End-date" of the projects?

    Besides the information I "of course" need to display who ownes the project, which organisation he/she belongs to id# etc. etc.

    How can a write a query that will work on several projects that last from 1, 2 days up to several years? For example I'm looking for planned projects "01 15 2006" and I have the following projects stored:

    • Project: 1, Startdate: 01 01 2006, Enddate: 01 16 2006 (lasts 15 days)
    • Project: 2, Startdate: 01 14 2006, Enddate: 01 15 2006 (lasts 2 days)
    • Project: 3, Startdate: 01 15 2006, Enddate: 01 15 2006 (lasts 1 day)
    • Project: 4, Startdate: 01 14 2006, Enddate: 01 22 2006 (lasts 8 days)

    If I write a query as follows it will only apply on dates which differs with 3 days and the result would be Project 1 and 4.

    select * from projects

    where getdate(01 15 2006) between startdate and enddate

    I need a query that applies on all four projects... Anyone who can help me?

     

    /Snugge

  • August 10, 2005 at 5:56 am

    #580962

    I do not see why your query should not work, as long as:

    a) StartDate and EndDate are both DateTimes

    b) In the WHERE clause, you are careful with the getdate() bit.  Remember that Getdate() returns a datetime, not just a date, and that might mean that you don't get the projects that have a start or end date on the same date.  There are techniques for handling this.

    Regards

  • August 10, 2005 at 6:03 am

    #580965

    I think you can assume that if getdate() is between a projects start and end date, then it is active; that's pretty straight forward.

    select * from projects where getdate() between startdate and enddate

    projects planned or active for a year from today:

    select * from projects where dateadd(year,1,getdate()) between startdate and enddate

    projects that are active at any time in the calendar year 2005:

    select * from projects where startdate between '01/01/2005' and 12/31/2005 OR enddate between '01/01/2005' and 12/31/2005

     

    HTH.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 10, 2005 at 6:31 am

    #580972

     

     

    Hi and thanks!

    As you can see I'm new at this and the obvious question to follow would be where I can find out more about the specific "problem", what to do and not when it comes to "getdate()"? You see, I really can't make it work with the query I presented earlier, hmmm...

    Thanks in advance.

  • August 10, 2005 at 6:37 am

    #580974

    It's pretty straightforward.  Getdate() takes no parameters and always returns the current (system) datetime.  If you want other dates based on 'now', you'll have to use additional functions (eg for adding days, months, years etc to getdate).

    Lowell's post shows you how to select projects that are planned to be active a year from 'now'.  What else are you trying to do?

  • August 11, 2005 at 5:15 am

    #581336

     

    Well,  I do understand what you have presented for me, but I now have to accept that it really is not as simple as I thought. I'm afraid I have to be more specific and really annoy you guys. I believe that these queries you have presented are a bit simple or have to be combined with each other in a larger query.

    Lets assume a project have started a year ago (2004) and will last three years from that date (2007). Project 2 started today, Aug 2005, and lasts 1 year and the third project will start January 2006 and which lasts 6 months.

            Name          Startdate          Enddate

    • P1              Aug-2004          Aug-2007
    • P2              Aug 2005          Aug-2006
    • P3              Jan-2006          Jul-2006

    1. I want to find out what projects are going on in March 2006 and I don't know any "startdate" och any "enddate" - I don't know if there are any projects going on in March 2006 at all. How would a query, to find that out, look like. 
    2. What projects were active May 2005 ( I don't know startdates or enddates)?

    Somehow I believe that a query would go through the following steps:

    1. Take my searchdate and compare it to the dates in the startdate comlumn. The projects that starts either before or the same day goes on to the next round.
    2. From the above result find out which of these results ends the same day or after my searchdate. 
    3. We now should have a result that works for my two questions above.

    Hopefully you know understand what kind of Query I'm looking for.  I'm really grateful for your help and time you are putting in to my question, Thanks!

  • August 11, 2005 at 5:58 am

    #581346

    I must be missing something, but here's how I would answer questions (1) and (2)

    1)  select * from projects where '2006-03-01 00:00' between StartDate and EndDate

    2) select * from projects where '2005-05-01 00:00' between StartDate and EndDate

  • August 11, 2005 at 6:11 am

    #581354

    Phil's reply is spot on; exactly what I would have suggested for a specific date it identifies all projects open on the date passed in;

    and if you wanted to test if it was open during march 2006 at all, you'd  want something like this:anything that was OPEN in may, regardless of period, would be this:

    select * from projects where startdate < = '03/31/2006' AND  enddate >= '03/01/2006'

    modifing  the example I provided earlier:

    select * from projects where startdate between '03/01/2006' and '03/31/2006' OR enddate between '03/01/2006' and '03/31/2006'

    that would find anything that started in May or anything that ended in may;

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 11, 2005 at 2:04 pm

    #581568

    For #2, another way is:

    select * from projects

     where (DATEPART(year, startdate) = 2005 and DATEPART(month, startdate) = 5 )

     or (DATEPART(year, enddate) = 2005 and DATEPART(month, enddate) = 5)

     

  • August 11, 2005 at 2:08 pm

    #581570

    This is also a great way to garantee an index scan... this is not a sarg condition.

  • August 11, 2005 at 2:17 pm

    #581574

    This might be better, otherwise there's an implicit 00:00:00 for the time portion:

    select * from projects where startdate between '03/01/2006' and '03/31/2006 23:59:59' OR enddate between '03/01/2006' and '03/31/2006 23:59:59'

  • August 12, 2005 at 1:58 am

    #581671

     

     

    Thank you all!

    I tried some of the examples and it really works. What really helped me off was the possibility to use '<=>' together with dates. Before today I thought that '<=>' only where to be used with numbers. Besides that I now have several ways to get exactly what I want.

    Thanks a lot, I'm deeply grateful!

    /Snugge

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply