Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    5

    Unanswered: MsSQL compare date help needed

    Good morning, all!

    In the ever changing world of IT, I just got handed this MsSQL 7 database project. I know basic MsSQL, but I am stumped with this issue.

    I've got a table, we'll call it 'engineering'. In this table, I've got the following columns:

    STATUS
    R_DATE
    P_DATE
    TYPE

    What I am trying to do is generate a query that will look at these columns and give me the following data:

    By TYPE, where the P_DATE = R_DATE and where the R_DATE is greater than the P_DATE where the STATUS = CLSD and only show dates for 2003. All I need is a total, for example, the ideal output would be:

    TYPE MADE_DATE MISSED_DATE

    OPRE 13 19


    etc...

    Here is a sample of the output from each column name:

    STATUS= CLSD
    PDATE= 9/22/2000
    RDATE = 10/10/2000 2:06:00 PM (time can be ignored here)
    TYPE = OPRE


    As I mentioned, I am pretty new SQL and my classes for it don't start until next month. Any help any of you could render would be much appreciated.

    Thanks in advance.

    - Peter

  2. #2
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    SELECT TYPE, SUM(MADE_DATE) AS MADE_DATE, SUM(MISSED_DATE) AS MISSED_DATE FROM
    (SELECT TYPE,
    MADE_DATE=CASE(P_DATE) WHEN R_DATE THEN 1 ELSE 0 END,
    MISSED_DATE=CASE WHEN R_DATE>P_DATE THEN 1 ELSE 0 END
    FROM engineering
    WHERE P_DATE>='1/1/2003' OR R_DATE>'1/1/2003') date_status
    GROUP BY TYPE
    -bpd

  3. #3
    Join Date
    Sep 2003
    Posts
    5
    Thanks!

    I will give that a try and let you know my results!

    MUCH appreciated!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •